Кредитный калькулятор для аннуитетных платежей в Excel. Расчет эффективной процентной ставки
Методика расчета основана на классической теории финансирования. Современные банки могут применять другие методы начисления процентов, например, с учетом точного количества дней в каждом месяце, или не применять сложные процентные ставки, а использовать метод 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.
А = 9168 рублей.
Способ 2.
Можно сделать проще. Чтобы рассчитать сумму аннуитетного платежа, воспользуемся встроенной в Excel функцией ПЛТ.
Вводим в ячейку формулу вида:
=ПЛТ(0,015;12;-100000) |
и получаем ту же сумму: 9168 руб.
0,015 – месячная процентная ставка i = 18 / 12 / 100;
12 – количество выплат n;
-100000 – сумма кредита S
Далее составляем таблицу выплат по месяцам.
Итоги расчёта процентной ставки
Взяли кредит на 100 000 руб.
Выплатили 122015,99 руб.
Переплата составила 22015,99 руб.
Ставка 22015,99 / 100000 = 22%.
Ежемесячная комиссия в 1% обошлась нам в 12000 рублей, что даже больше, чем основные выплаты по кредиту! Это объясняется тем, что комиссию мы платили каждый раз от полной суммы кредита (100 тыщ), а проценты – от оставшейся суммы (то есть за вычетом того, что уже уплатили), которая с каждым месяцем уменьшается. Подобные хитрости со стороны банка могут в конечном итоге дорого обойтись.
Теги:excel, кредит, модель, платежи, расчет
Trackback с вашего сайта.
Комментарии (2)
Здесь с частичным досрочным погашением калькулятор: http://www.homecal.ru/credit/
Здорово ????
Я частичные досрочные погашения и сам платёж вот тут считаю https://www.creditcalcul.ru
С графиком Сбера до копеек совпадает.