Обчислення аннуитетного платежу в Microsoft Excel

Перш, ніж брати позику, непогано було б розрахувати всі платежі по ньому. Це вбереже позичальника в майбутньому від різних несподіваних неприємностей і розчарувань, коли з'ясується, що переплата занадто велика. Допомогти в даному розрахунку можуть інструменти програми Excel. Давайте з'ясуємо, як розрахувати ануїтетні платежі по кредиту в цій програмі.

розрахунок оплати

Перш за все, потрібно сказати, що існує два види кредитних платежів:

• диференційовані;
• Ануїтетні.

При диференційованою схемою клієнт вносить в банк щомісячно рівну частку виплат по тілу кредиту плюс платежі за відсотками. Величина процентних виплат щомісяця зменшується, так як зменшується тіло позики, з якого вони розраховуються. Таким чином і загальний щомісячний платіж теж зменшується.

При ануїтетною схемою використовується дещо інший підхід. Клієнт щомісяця вносить однакову суму загального платежу, який складається з виплат по тілу кредиту та сплати відсотків. Спочатку процентні внески нараховуються на всю суму позики, але в міру того, як тіло зменшується, скорочується і нарахування відсотків. Але загальна сума оплати залишається незмінною за рахунок щомісячного збільшення величини виплат по тілу кредиту. Таким чином, з плином часу питома вага відсотків в загальному щомісячному платежі падає, а питома вага оплати по тілу зростає. При цьому сам загальний щомісячний платіж протягом усього терміну кредитування не змінюється.

Якраз на розрахунку аннуитетного платежу ми і зупинимося. Тим більше, це актуально, так як в даний час більшість банків використовують саме цю схему. Вона зручна і для клієнтів, адже в цьому випадку загальна сума оплати не змінюється, залишаючись фіксованою. Клієнти завжди знають скільки потрібно заплатити.

Етап 1: розрахунок щомісячного внеску

Для розрахунку щомісячного внеску при використанні ануїтетною схеми в Ексель існує спеціальна функція - ПЛТ. Вона відноситься до категорії фінансових операторів. Формула цієї функції виглядає наступним чином:

=ПЛТ(ставка;кпер;пс;бс;тип)

Як бачимо, зазначена функція має досить великою кількістю аргументів. Правда, останні два з них не є обов'язковими.

Аргумент «Ставка» вказує на відсоткову ставку за конкретний період. Якщо, наприклад, використовується річна ставка, але платіж по позиці здійснюється щомісяця, то річну ставку потрібно розділити на 12 і отриманий результат використовувати як аргумент. Якщо застосовується щоквартальний вид оплати, то в цьому випадку річну ставку потрібно розділити на 4 і т.д.

«Кпер» означає загальну кількість періодів виплат по кредиту. Тобто, якщо позику береться на один рік з щомісячною оплатою, то число періодів вважається 12, якщо на два роки, то число періодів - 24. Якщо кредит береться на два роки з щоквартальною оплатою, то число періодів дорівнює 8.

«Пс» вказує наведену вартість на даний момент. Говорячи простими словами, це загальна величина позики на початок кредитування, тобто, та сума, яку ви берете в борг, без урахування відсотків та інших додаткових виплат.

«Бс» - це майбутня вартість. Ця величина, яку буде складати тіло позики на момент завершення кредитного договору. У більшості випадків даний аргумент дорівнює «0», так як позичальник на кінець терміну кредитування повинен повністю розрахуватися з кредитором. Зазначений аргумент не є обов'язковим. Тому, якщо він опускається, то вважається рівним нулю.

Аргумент «Тип» визначає час розрахунку: в кінці або на початку періоду. У першому випадку він приймає значення «0», а в другому - «1». Більшість банківських установ використовують саме варіант з оплатою в кінці періоду. Цей аргумент теж є необов'язковим, і якщо його опустити вважається, що він дорівнює нулю.

Тепер настав час перейти до конкретного прикладу розрахунку щомісячного внеску за допомогою функції ПЛТ. Для розрахунку використовуємо таблицю з вихідними даними, де вказана процентна ставка по кредиту (12%), величина позики (500000 рублів) і термін кредиту (24 місяці). При цьому оплата проводиться щомісячно в кінці кожного періоду.

1. Виділяємо елемент на аркуші, в який буде виводитися результат розрахунку, і клацаємо по піктограмі «Вставити функцію», розміщену близько рядки формул.
2. Виробляється запуск віконця Майстра функцій. У категорії «Фінансові» виділяємо найменування «ПЛТ» і тиснемо на кнопку «OK».
3. Після цього відкривається вікно аргументів оператора ПЛТ.

   В поле «Ставка» слід вписати величину відсотків за період. Це можна зробити вручну, просто поставивши відсоток, але у нас він вказаний в окремій клітинці на аркуші, тому дамо на неї посилання. Встановлюємо курсор в поле, а потім натискаємо по відповідній клітинці. Але, як ми пам'ятаємо, у нас в таблиці задана річна процентна ставка, а період оплати дорівнює місяцю. Тому ділимо річну ставку, а вірніше посилання на осередок, в якій вона міститься, на число 12, що відповідає кількості місяців у році. Розподіл виконуємо прямо в поле вікна аргументів.

   В поле «Кпер» встановлюється термін кредитування. Він у нас дорівнює 24 місяцям. Можна занести в поле число 24 вручну, але ми, як і в попередньому випадку, вказуємо посилання на місце розташування даного показника у вихідній таблиці.

   В поле «Пс» вказується первісна величина позики. Вона дорівнює 500000 рублів. Як і в попередніх випадках, вказуємо посилання на елемент листа, в якому міститься даний показник.

   В поле «Бс» вказується величина позики, після повної його оплати. Як пам'ятаємо, це значення практично завжди дорівнює нулю. Встановлюємо в даному полі число «0». Хоча цей аргумент можна взагалі опустити.

   В поле «Тип» вказуємо на початку або в кінці місяця проводиться оплата. У нас, як і в більшості випадків, вона проводиться в кінці місяця. Тому встановлюємо число «0». Як і у випадку з попереднім аргументом, в цьому полі можна нічого не вводити, тоді програма за замовчуванням буде вважати, що в ньому розташовано значення рівне нулю.

   Після того, як всі дані введені, тиснемо на кнопку «OK».

4. Після цього в комірку, яку ми виділили в першому пункті даного керівництва, виводиться результат обчислення. Як бачимо, величина щомісячного загального платежу за позикою становить 23536,74 рубля. Нехай вас не бентежить знак «-» перед даною сумою. Так Ексель вказує на те, що це витрата коштів, тобто, збиток.
5. Для того, щоб розрахувати загальну суму оплати за весь термін кредитування з урахуванням погашення тіла позики і щомісячних відсотків, досить перемножити величину щомісячного платежу (23536,74 рубля) на кількість місяців (24 місяці). Як бачимо, загальна сума платежів за весь термін кредитування в нашому випадку склала 564881,67 рубля.
6. Тепер можна підрахувати суму переплати по кредиту. Для цього потрібно відняти від загальної величини виплат по кредиту, включаючи відсотки і тіло позики, початкову суму, взяту в борг. Але ми пам'ятаємо, що перше з цих значень уже зі знаком «-». Тому в конкретно нашому випадку виходить, що їх потрібно скласти. Як бачимо, загальна сума переплати по кредиту за весь термін склала 64881,67 рубля.

урок: Майстер функцій в Ексель

Етап 2: деталізація платежів

А тепер за допомогою інших операторів Ексель зробимо помісячну деталізацію виплат, щоб бачити, скільки в конкретному місяці ми платимо по тілу позики, а скільки становить величина відсотків. Для цих цілей креслимо в Ексель таблицю, яку будемо заповнювати даними. Рядки цієї таблиці будуть відповідати відповідного періоду, тобто, місяцю. З огляду на, що період кредитування у нас становить 24 місяці, то і кількість рядків теж буде відповідним. У стовпчиках зазначена виплата тіла позики, виплата відсотків, загальний щомісячний платіж, який є сумою попередніх двох колонок, а також сума, що залишилася до виплати.

1. Для визначення величини оплати по тілу позики використовуємо функцію ОСПЛТ, яка як раз призначена для цих цілей. Встановлюємо курсор в осередок, яка знаходиться в рядку «1» і в стовпці «Виплата по тілу кредиту». Тиснемо на кнопку «Вставити функцію».
2. Переходимо в Майстер функцій. У категорії «Фінансові» відзначаємо найменування «ОСПЛТ» і тиснемо кнопку «OK».
3. Запускається вікно аргументів оператора ОСПЛТ. Він повинен виглядати так:

   =ОСПЛТ(Ставка;Период;Кпер;Пс;Бс)

   Як бачимо, аргументи цієї функції майже повністю збігаються з аргументами оператора ПЛТ, тільки замість необов'язкового аргументу «Тип» доданий обов'язковий аргумент «Період». Він вказує на номер періоду виплати, а в нашому конкретному випадку на номер місяця.

   Заповнюємо вже знайомі нам поля вікна аргументів функції ОСПЛТ тими самими даними, що були використані для функції ПЛТ. Тільки з огляду на той факт, що в майбутньому буде застосовуватися копіювання формули за допомогою маркера заповнення, потрібно зробити все посилання в полях абсолютними, щоб вони не змінювалися. Для цього потрібно поставити знак долара перед кожним значенням координат по вертикалі і горизонталі. Але легше це зробити, просто виділивши координати і натиснувши на функціональну клавішу F4. Знак долара буде розставлено в потрібних місцях автоматично. Також не забуваємо, що річну ставку потрібно розділити на 12.

4. Але у нас залишається ще один новий аргумент, якого не було у функції ПЛТ. Цей аргумент «Період». У відповідне поле встановлюємо посилання на перший осередок шпальти «Період». Даний елемент листа містить в собі число «1», яке позначає номер першого місяця кредитування. Але на відміну від попередніх полів, в зазначеному полі ми залишаємо посилання відносної, а не робимо з неї абсолютну.

   Після того, як всі дані, про які ми говорили вище, введені, тиснемо на кнопку «OK».

5. Після цього в комірці, яку ми раніше виділили, відобразиться величина виплати по тілу позики за перший місяць. Вона складе 18536,74 рубля.
6. Потім, як уже говорилося вище, нам слід скопіювати цю формулу на інші комірки стовпчика за допомогою маркера заповнення. Для цього встановлюємо курсор в нижній правий кут комірки, в якій міститься формула. Курсор перетвориться при цьому в хрестик, який називається маркером заповнення. Затискаємо ліву кнопку миші і тягнемо його вниз до кінця таблиці.
7. У підсумку все комірки стовпчика заповнені. Тепер ми маємо графік виплати тіла позики помісячно. Як і говорилося вже вище, величина оплати за цією статтею з кожним новим періодом збільшується.
8. Тепер нам потрібно зробити місячний розрахунок оплати за відсотками. Для цих цілей будемо використовувати оператор ПРПЛТ. Виділяємо першу вільну позицію в стовпці «Виплата за відсотками». Тиснемо на кнопку «Вставити функцію».
9. У запустити вікні Майстра функцій в категорії «Фінансові» виробляємо виділення найменування ПРПЛТ. Виконуємо клацання по кнопці «OK».
10. Відбувається запуск вікна аргументів функції ПРПЛТ. Її синтаксис виглядає наступним чином:

    =ПРПЛТ(Ставка;Период;Кпер;Пс;Бс)

    Як бачимо, аргументи цієї функції абсолютно ідентичні аналогічним елементам оператора ОСПЛТ. Тому просто заносимо в вікно ті ж дані, які ми вводили в попередньому вікні аргументів. Не забуваємо при цьому, що посилання в поле «Період» повинна бути відносною, а у всіх інших полях координати потрібно привести до абсолютного увазі. Після цього клацаємо по кнопці «OK».

11. Потім результат розрахунку суми оплати за відсотками за кредит за перший місяць виводиться в відповідному полі.
12. Застосувавши маркер заповнення, виробляємо копіювання формули в інші елементи стовпця, таким способом отримавши помісячний графік оплат за відсотками за позику. Як бачимо, як і було сказано раніше, з місяця в місяць величина даного виду платежу зменшується.
13. Тепер нам належить розрахувати загальний щомісячний платіж. Для цього обчислення не слід вдаватися до якого-небудь оператора, так як можна скористатися простою арифметичною формулою. Складаємо комірки першого місяця стовпців «Виплата по тілу кредиту» і «Виплата за відсотками». Для цього встановлюємо знак «=» в першу порожню осередок шпальти «Загальна щомісячна виплата». Потім натискаємо по двох вищевказаних елементів, встановивши між ними знак «+». Тиснемо на клавішу Enter.
14. Далі за допомогою маркера заповнення, як і в попередніх випадках, заповнюємо колонку даними. Як бачимо, протягом усієї дії договору сума загального щомісячного платежу, що включає платіж по тілу позики і оплату відсотків, складе 23536,74 рубля. Власне цей показник ми вже розраховували раніше за допомогою ПЛТ. Але в даному випадку це представлено більш наочно, саме як сума оплати по тілу позики і відсотків.
15. Тепер потрібно додати дані в стовпець, де буде щомісяця відображатися залишок суми по кредиту, який ще потрібно заплатити. У першій осередку шпальти «Залишок до виплати» розрахунок буде найпростіший. Нам потрібно відняти від початкової величини позики, яка вказана в таблиці з первинними даними, платіж по тілу кредиту за перший місяць в розрахунковій таблиці. Але, з огляду на той факт, що одне з чисел у нас вже йде зі знаком «-», то їх слід не відняти, а скласти. Робимо це і тиснемо на кнопку Enter.
16. А ось обчислення залишку до виплати після другого і наступних місяців буде трохи складніше. Для цього нам потрібно відняти від тіла кредиту на початок кредитування загальну суму платежів по тілу позики за попередній період. Встановлюємо знак «=» у другій осередку шпальти «Залишок до виплати». Далі вказуємо посилання на осередок, в якій міститься початкова сума кредиту. Робимо її абсолютної, виділивши і натиснувши на клавішу F4. Потім ставимо знак «+», так як друге значення у нас і так буде негативним. Після цього натискаємо на кнопку «Вставити функцію».
17. Запускається Майстер функцій, в якому потрібно переміститися в категорію «Математичні». Там виділяємо напис «СУМ» і тиснемо на кнопку «OK».
18. Запускається вікно аргументів функції СУММ. Зазначений оператор служить для того, щоб підсумувати дані в осередках, що нам і потрібно виконати в стовпці «Виплата по тілу кредиту». Він повинен виглядати так:

    =СУММ(число1;число2;…)

    В якості аргументів виступають посилання на комірки, в яких містяться числа. Ми встановлюємо курсор в поле «Число1». Потім затискаємо ліву кнопку миші і виділяємо на аркуші перші дві комірки стовпчика «Виплата по тілу кредиту». В поле, як бачимо, відобразилася посилання на діапазон. Вона складається з двох частин, розділених двокрапкою: посилання на першу комірку діапазону і на останню. Для того, щоб в майбутньому мати можливість скопіювати зазначену формулу за допомогою маркера заповнення, робимо першу частину посилання на діапазон абсолютної. Виділяємо її і тиснемо на функціональну клавішу F7. Другу частину посилання так і залишаємо відносної. Тепер при використанні маркера заповнення перша осередок діапазону буде закріплена, а остання буде розтягуватися у міру просування вниз. Це нам і потрібно для виконання поставлених цілей. Далі тиснемо на кнопку «OK».

19. Отже, результат залишку кредитної заборгованості після другого місяця виводиться в клітинку. Тепер, починаючи з даного осередку, виробляємо копіювання формули в порожні елементи стовпця за допомогою маркера заповнення.
20. Помісячний розрахунок залишків до оплати по кредиту зроблений за весь кредитний період. Як і належить, на кінець терміну ця сума дорівнює нулю.

Таким чином, ми зробили не просто розрахунок оплати по кредиту, а організували своєрідний кредитний калькулятор. Який буде діяти за ануїтетною схемою. Якщо у вихідній таблиці ми, наприклад, поміняємо величину позики і річної відсоткової ставки, то в підсумковій таблиці відбудеться автоматичний перерахунок даних. Тому її можна використовувати не тільки один раз для конкретного випадку, а застосовувати в різних ситуаціях для розрахунку кредитних варіантів за ануїтетною схемою.

урок: Фінансові функції в Excel

Як бачимо, за допомогою програми Excel в домашніх умовах можна без проблем розрахувати загальний щомісячний кредитний платіж за ануїтетною схемою, використовуючи для цих цілей оператор ПЛТ. Крім того, за допомогою функцій ОСПЛТ і ПРПЛТ можна зробити розрахунок величини платежів по тілу кредиту та по відсотках за вказаний період. Застосовуючи весь цей багаж функцій разом, існує можливість створити потужний кредитний калькулятор, який можна буде використовувати не один раз для обчислення аннуитетного платежу.

• ← Установка дня тижня по даті в Microsoft Excel
• Застосування функції ПСТР в Microsoft Excel →