Кредитный калькулятор для аннуитетных платежей в 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.

А = 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 тыщ), а проценты – от оставшейся суммы (то есть за вычетом того, что уже уплатили), которая с каждым месяцем уменьшается. Подобные хитрости со стороны банка могут в конечном итоге дорого обойтись.

В Мой Мир

Теги:, , , ,

Trackback с вашего сайта.

Комментарии (2)

  • Алекс
    18 ноября, 2013 в 10:21 дп |

    Здесь с частичным досрочным погашением калькулятор: http://www.homecal.ru/credit/

  • bivshevasasha
    9 января, 2019 в 3:17 дп |

    Здорово ????

    Я частичные досрочные погашения и сам платёж вот тут считаю https://www.creditcalcul.ru
    С графиком Сбера до копеек совпадает.

Оставить комментарий

Войти

Новости

Полезное

Изменить "Кол-во"
Ежедневный курс иностранной валюты ЦБ РФ на 30.11.2020
Валюта Кол-во Рубль RUB
Продажа (руб.)
Доллар США USD
Евро EUR
Фунт стерлингов Соединенного королевства GBP
Use data from the cache