Формула розрахунку ануїтетного платежу в Excel

Платежі по кредиту легше та швидше розрахувати за допомогою Microsoft Office Excel. Значно більше часу витрачається на ручний розрахунок. У цій статті мова піде про ануїтетні виплати, особливості їх нарахування, переваги та недоліки.

Що таке ануїтетний платіж

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

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

Класифікація ануїтету

Рентні платежі можна розділити на такі види:

1. Виправлено. Платежі, які не змінюються, мають фіксовану ставку незалежно від зовнішніх умов.
2. Валюта. Можливість зміни суми платежу в разі падіння або зростання курсу.
3. індексується. Виплати залежно від рівня, показника інфляції. Протягом періоду позики їх розмір часто змінюється.
4. Змінні. Ануїтет, який може змінюватися в залежності від стану фінансової системи, інструментів.

Звернути увагу! Фіксовані платежі переважні для всіх позичальників, оскільки мають невеликий ризик.

Переваги та недоліки ануїтетних платежів

Для кращого розуміння теми необхідно вивчити ключові особливості даного виду кредитних платежів. Він має такі переваги:

• Встановлення конкретної суми платежу та дати його сплати.
• Висока доступність для позичальників. Подати заявку на отримання ренти може практично кожен, незалежно від свого матеріального становища.
• Можливість зниження розміру щомісячного внеску при зростанні інфляції.

Не без недоліків:

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

Що таке платіж по кредиту?

Рентний платіж має такі складові:

• Відсотки, які сплачує особа при погашенні кредиту.
• Частина основної суми.

У результаті загальна сума відсотків майже завжди перевищує суму, внесену позичальником для зменшення боргу.

Основна формула ануїтетного платежу в Excel

Як було сказано вище, в Microsoft Office Excel можна працювати з різними видами платежів за кредитами та авансами. Ануїтет не є винятком. У загальному вигляді формула, за допомогою якої можна швидко розрахувати ануїтетні внески, виглядає наступним чином:  

Важливо! Не можна розкривати дужки в знаменнику цього виразу, щоб спростити його.

Основні значення формули розшифровуються наступним чином:

• АП – ануїтетний платіж (назва скорочена).
• О – розмір основного боргу позичальника.
• PS – відсоткова ставка, щомісячно висувається конкретним банком.
• C — кількість місяців, протягом яких кредит триває.

Для засвоєння інформації досить навести кілька прикладів використання цієї формули. Про них і піде мова далі.

Приклади використання функції PMT в Excel

Наведемо просту умову задачі. Розраховувати щомісячний платіж по кредиту необхідно, якщо банк висуває відсоток 23%, а загальна сума становить 25000 рублів. Кредитування триватиме 3 роки. Задача розв’язується за алгоритмом:

1. На основі вихідних даних скласти загальну електронну таблицю в Excel.

2. Активуйте функцію PMT і введіть для неї аргументи у відповідному полі.
3. У полі «Ставка» введіть формулу «B3/B5». Це буде відсоткова ставка за кредитом.
4. У рядку «Nper» запишіть значення у вигляді «B4*B5». Це буде загальна кількість платежів за весь термін кредиту.
5. Заповніть поле «PS». Тут потрібно вказати початкову суму, взяту з банку, написавши значення «В2».

6. Переконайтеся, що після натискання «ОК» у вихідній таблиці було розраховано значення «Щомісячний платіж».

Додаткова інформація! Від’ємне число означає, що позичальник витрачає гроші.

Приклад розрахунку суми переплати по кредиту в Excel

У цій задачі вам потрібно розрахувати суму, яку переплатить людина, яка взяв кредит 50000 рублів під 27% на 5 років. Загалом позичальник здійснює 12 платежів на рік. рішення:

1. Скласти вихідну таблицю даних.

2. Від загальної суми виплат відніміть початкову суму за формулою «=ABS(ПЛТ(B3/B5;B4*B5;B2)*B4*B5)-B2». Його необхідно вставити в рядок формул у верхній частині головного меню програми.
3. У результаті в останньому рядку створеної таблички з'явиться сума переплат. Зверху позичальник переплатить 41606 руб.

Формула розрахунку оптимального місячного платежу по кредиту в Excel

Завдання з наступною умовою: у клієнта зареєстрований банківський рахунок на 200000 4 рублів з можливістю щомісячного поповнення. Необхідно розрахувати суму платежу, яку людина повинна вносити щомісяця, щоб через 2000000 роки на його рахунку було 11 рублів. Ставка XNUMX%. рішення:

1. Створіть електронну таблицю на основі вихідних даних.

2. Введіть формулу в рядок введення Excel «=ПЛТ(B3/B5;B6*B5;-B2;B4)» і натисніть «Enter» на клавіатурі. Літери відрізнятимуться залежно від клітинок, у яких розміщена таблиця.
3. Переконайтеся, що сума внеску автоматично розраховується в останньому рядку таблиці.

Звернути увагу! Таким чином, щоб клієнт накопичив 4 рубля за ставкою 2000000% за 11 років, йому потрібно щомісяця вносити 28188 рублів. Мінус у сумі означає, що клієнт несе збитки, віддаючи гроші банку.

Особливості використання функції PMT в Excel

У загальному вигляді ця формула записується так: =PMT(ставка; nper; ps; [bs]; [тип]). Функція має такі особливості:

1. При розрахунку щомісячних внесків до уваги береться лише річна ставка.
2. При визначенні відсоткової ставки важливо зробити перерахунок виходячи з кількості платежів на рік.
3. Замість аргументу «Nпер» у формулі вказується конкретне число. Це період оплати.

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

Загалом ануїтетний платіж розраховується в два етапи. Для розуміння теми необхідно розглянути кожен з етапів окремо. Про це йтиметься далі.

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

Щоб розрахувати в Excel суму, яку потрібно платити щомісяця за кредитом з фіксованою ставкою, необхідно:

1. Складіть вихідну таблицю та виберіть клітинку, у якій ви хочете відобразити результат, і натисніть кнопку «Вставити функцію» зверху.

2. У списку функцій виберіть «PLT» і натисніть «ОК».

3. У наступному вікні задайте аргументи функції, вказавши відповідні рядки у складеній таблиці. В кінці кожного рядка потрібно натиснути на іконку, а потім виділити потрібну комірку в масиві.

4. Коли всі аргументи будуть заповнені, в рядку для введення значень буде записана відповідна формула, а в полі таблиці «Щомісячний платіж» з’явиться результат розрахунку зі знаком мінус.

Важливо! Після розрахунку розстрочки можна буде розрахувати суму, яку позичальник переплатить за весь період кредитування.

Етап 2: реквізити платежу

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

1. Створіть електронну таблицю на 24 місяці.

2. Встановіть курсор у першу клітинку таблиці та вставте функцію «OSPLT».

3. Таким же чином заповніть аргументи функції.

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

5. Перевірте, чи заповнена перша клітинка в графі «Оплата за тілом кредиту».
6. Щоб заповнити всі рядки першого стовпця, потрібно розтягнути клітинку до кінця таблиці

7. Виберіть функцію «PRPLT», щоб заповнити другий стовпець таблиці.
8. У вікні, що відкрилося, заповніть всі аргументи відповідно до скріншоту нижче.

9. Розрахуйте загальний місячний платіж, додавши значення в попередніх двох стовпцях.

10. Щоб розрахувати «Залишок до сплати», вам потрібно додати відсоткову ставку до платежу за тілом позики та розтягнути її до кінця пластини, щоб заповнити всі місяці позики.

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

Розрахунок ануїтетних платежів по кредиту в Excel

Функція PMT відповідає за розрахунок ануїтету в Excel. Принцип розрахунку в загальному вигляді полягає в наступних етапах:

1. Скласти вихідну таблицю даних.
2. Складіть графік погашення боргу на кожен місяць.
3. Виберіть першу клітинку в графі «Платежі по кредиту» і введіть формулу розрахунку «PLT ($B3/12;$B$4;$B$2)».
4. Отримане значення розтягується на всі стовпці таблички.

Розрахунок в MS Excel погашення основної суми боргу

Ануїтетні платежі повинні здійснюватися щомісяця у фіксованих сумах. І процентна ставка не змінюється.

Розрахунок залишку основної суми (з BS=0, тип=0)

Припустимо, взято кредит 100000 10 рублів на 9 років під 1%. Розрахувати суму основного боргу необхідно в 3-му місяці XNUMX-го року. рішення:

1. Складіть таблицю даних і розрахуйте щомісячний платіж за допомогою наведеної вище формули PV.
2. Обчисліть частку платежу, необхідну для погашення частини боргу за формулою «=-PMT-(PS-PS1)*item=-PMT-(PS +PMT+PS*item)».
3. Розрахувати суму основного боргу за 120 періодів за відомою формулою.
4. За допомогою оператора HPMT знайдіть суму сплачених відсотків за 25-й місяць.
5. Перевірте результат.

Розрахунок суми основної суми, сплаченої між двома періодами

Цей розрахунок найкраще зробити простим способом. Для розрахунку суми в інтервалі за два періоди потрібно використовувати такі формули:

• =«-BS(item; con_period; plt; [ps]; [type]) /(1+type *item)».
• = “+ BS(ставка; початковий_період-1; plt; [ps]; [тип]) /IF(початковий_період =1; 1; 1+тип *ставка)”.

Звернути увагу! Букви в дужках замінюються конкретними значеннями.

Дострокове погашення зі скороченим терміном або платіж

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

Щоб зменшити виплати, потрібно перераховувати внесок за кожен попередній місяць.

Кредитний калькулятор з нерегулярними платежами

Є кілька варіантів ануїтету, коли позичальник може вносити змінні суми в будь-який день місяця. У такій ситуації залишок боргу та відсотки розраховуються за кожен день. При цьому в Excel потрібно:

1. Введіть дні місяця, за які здійснюються платежі, та вкажіть їх кількість.
2. Перевірте наявність негативних і позитивних сум. Перевага віддається негативним.
3. Порахуйте дні між двома датами внесення грошей.

Розрахунок періодичного платежу в MS Excel. Строковий депозит

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

Висновок

Таким чином, ануїтетні платежі простіше, швидше та ефективніше розраховувати в Excel. За їх розрахунок відповідає оператор ФЕУ. Більш докладні приклади можна знайти вище.