Кредитний калькулятор для ануїтетних платежів в Excel
Написано admin в Січень 26, 2012. Опубліковано в Фінансовий аналіз
Методика розрахунку ґрунтується на класичній теорії фінансування. Сучасні банки можуть застосовувати інші методи нарахування відсотків, наприклад, з урахуванням точної кількості днів у кожному місяці, або не застосовувати складні процентні ставки, а використовувати метод 360 днів. Якщо вам потрібний точний розрахунок – будуйте точну модель платежів та використовуйте функціюGoal seek (Підбір значення).
Цей калькулятор підійде лише якщо кредит –ануїтетний (рівні платежі, у складі яких змінюється сума погашення та сума виплати відсотків, але загальна сума залишається незмінною).
Якщо ви захочете внести якісь зміни, зніміть захист листа. Аркуш захищений без пароля.
У доданому файлі коригуйте сині комірки. Жовті осередки містять формули.
Розрахунок ефективної процентної ставки
Вихідні дані:
- Сума кредиту:100 000 руб
- Ставка:18% річних
- Щомісячна комісія за обслуговування кредиту:1% від суми кредиту
- Термін кредиту:12 місяців
- Метод погашення:ануїтетні платежі.
Так як платежі у нас ануїтетні, то погашати кредит будемо рівними частками протягом усього терміну, іншими словами, щомісячний платіж – та сама сума. Це зручно позичальнику.
A = K*S, деS – сума кредиту (у разі S = 100 000);K - коефіцієнт ануїтету, вважається за формулою, яка залежить від i і n.
У разі i = 0,015 n = 12, підставляємо в формулу, отримуємо, що К = 0,09168.
Спосіб 2. Можназробити простіше. Щоб розрахувати суму ануїтетного платежу, скористаємося вбудованою в Excel функцієюПЛТ.
Вводимо в комірку формулу виду:
і отримуємо ту саму суму: 9168 руб.
Далі складаємо таблицю виплат за місяцями.
Підсумки розрахунку процентної ставки
Взяли кредит на 100 000 руб. Виплатили 122015,99 руб. Переплата склала 22015,99 руб. Ставка 22015,99/100000 = 22%.
Щомісячна комісія в 1% коштувала нам 12000 рублів, що навіть більше, ніж основні виплати за кредитом! Це пояснюється тим, що комісію ми платили щоразу від повної суми кредиту (100 тисяч), а відсотки – від суми, що залишилася (тобто за вирахуванням того, що вже сплатили), яка з кожним місяцем зменшується. Подібні хитрощі з боку банку можуть зрештою дорого обійтися.