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

Необхідні дані для розрахунку графіка платежів по кредиту в Excel

Основні питання, пов'язані з розрахунком кредиту, полягають, як правило, в наступному:

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

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

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

Термін кредиту, як правило, може вибиратися позичальником. Зазвичай він є кратним 12 місяців і не перевищує 7 років (по іпотеці - до 30 років).

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

Якщо величина платежу за ануїтетною схемою відома і потрібно визначити можливий розмір кредиту, використовуйте формулу ПС. Її аргументи:

ставка (в річних відсотках, розділена на 12);
період кредиту (в місяцях);
сума передбачуваного платежу.

Для розрахунку ануїтетних платежів по кредиту в Excel використовуються наступні функції:

ПЛТ - визначає суму платежу з урахуванням частини основного боргу і відсотків. Аргументи: ставка (в річних відсотках, розділена на 12); період кредиту (в місяцях); розмір позики.
ПРПЛТ - розраховує величину відсотків у складі платежу. Аргументи: ставка (в річних, розділена на 12); номер періоду виплат; час кредиту (в місяцях); сума займу.
ОСПЛТ - визначає суму основного боргу в структурі платежу. Аргументи: ставка (в річних, розділена на 12); номер періоду виплат; час кредиту (в місяцях); сума займу.

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

Дані для проведення обчислень:

ставка 20% річних;
термін кредиту 12 місяців;
сума платежу 5 тис. р. в місяць (для розрахунку розміру кредиту);
сума позики 100 тис. р. (Для розрахунку розміру платежу).

В даному випадку функція ПС представлена наступним чином: ПС (20% / 12; 12; 5000). Результатом обчислень є максимально можлива сума кредиту 53 976 р.

Функції, які використовуються при розрахунку платежу, будуть представлені таким чином:

Підсумком розрахунків будуть значення:

сума регулярного платежу 9 263 р .;
величина відсотків в складі ануїтету від 1 667 р. до 152 р .;
розмір погашається боргу в структурі платежу від 7 597 р. до 9 112 р.

У разі розрахунку диференційованого платежу сума погашається основного боргу залишається однаковою протягом всього періоду. Її розмір розраховується як відношення суми кредиту до терміну кредитування в місяцях. Обчислення розміру відсотків у складі платежу відбувається наступним чином:

Розмір поточної заборгованості * процентна ставка (%) / 365 (366) днів на рік * фактична кількість днів у місяці.

Для простоти розрахунків (без обчислення кількості днів у кожному з періодів) використовується наступна формула:

Розмір поточної заборгованості * процентна ставка / 12 місяців в році.

У прикладі нижче використаний саме такий підхід.

Завантажити таблицю розрахунку платежів по кредиту в Excel з наведеними прикладами можна тут.

Розрахунок графіка платежів по кредиту в Excel. Поради

Самостійний розрахунок щомісячного платежу по кредиту в Excel носить інформативний характер, і може незначно відрізнятися від даних банку. Це пов'язано з кількома причинами: різний облік кількості днів в періоді, що відрізняється підхід при округленні значень і т.д.
При виборі між аннуїтетним і диференційованим платежем необхідно звернути увагу, що сума переплати по ануїтету завжди буде вище. Так, згідно з наведених прикладів, при ануїтетних платежах загальна сума переплати становить 11 161 р. при диференційованих - 10 833 р.
Для розрахунків доцільно використовувати заявлену ставку того банку, в якому планується взяти кредит. або її середньоринкове значення.