Когда вы взяли кредит, вы так или иначе думаете о досрочном погашении.
Есть люди которые платят кредит и все. А есть те, которые каждый раз смотрят, сколько осталось платить, какая сумма основного долга. Я отношу себя ко второму типу людей, я смотрю сколько сейчас сумма основного долга, пытаюсь рассчитать, сколько будет платеж, если я сделаю досрочное погашение.
На данный момент у меня есть два калькулятора кредита для своих расчетов. Оба калькулятора сделаны в Excel. Калькуляторы позволяют достаточно быстро и просто рассчитать ипотеку.
Читайте также: Как рассчитать кредит в Excel самому?
Первый кредитный калькулятор в Excel можно скачать по ссылке.
Но Excel есть не на всех компьютерах. Пользователи MAC и Linux не пользуются Excel обычно, т.к. это продукт Microsoft.
Для расчета досрочного погашения можно также воспользоваться онлайн версией калькулятора с досрочным погашением. В нем предусмотрена возможность экспорта результатов расчета в Excel. 
На основе этого калькулятора был разработан ипотечный калькулятор для Android и iPhone. Найти и скачать мобильные версии калькуляторов можно с главной страницы сайта.
- Кредитный калькулятор в Excel практически точно считает аннуитетный график платежей и дифференцированный график платежей
- Изменения в графике платежей — учет досрочных погашений в уменьшение суммы основного долга
- Построение и расчет графика платежей в виде таблицы в Excel. Таблица графика платежей может также редактироваться
- При расчете учитывается високосный и невисокосный год. За счет этого сумма начисленных процентов практически совпадает с значениями, рассчитываемыми ВТБ24 и Сбербанком
- Точность расчетов — рассчеты совпадают с расчетами кредитного калькулятора ВТБ24 и Сбербанка
- Калькулятор можно редактировать под себя, задавая разные варианты расчета.
- Нет учета возможное изменение процентной ставки во время выплат кредита
- Если сделать расчет, делая досрочные платежи в изменение срока и суммы, то расчет будет неверным
- Если сумма процентов, начисленных за период больше суммы аннуитетного платежа, то расчет будет не верным
- Не рассчитывается вариант — первый платеж только проценты. В случае когда дата выдачи не совпадает с датой первого платежа, вам нужно будет заплатить проценты банку за период между датой выдачи и датой первого платежа.
- Расчет производится для процентой ставки с 2мя знаками после запятой.
Всех выше названных недостатков лишен кредитный калькулятор для iPad/iPhone. В целом недостатки не сильно критичны и они присущи любому кредитному калькулятору онлайн.
Другой кредитный калькулятор в Excel можно скачать по данной ссылке. Данный кредитный калькулятор не позволяет рассчитать досрочное погашение. Однако его плюс в том, что он рассчитывает кредит с несколькими процентными периодами. Если сумма процентов по кредиту за данный месяц больше суммы аннуитетного платежа, то график для первого кредитного калькулятора в excel строится некорректно. В графике получаются отрицательные суммы.
Попробуйте посчитать к примеру кредит 1 млн. руб под 90 процентов на срок 30 лет.
У второго калькулятора нет данного недостатка. Однако он делит кредит на 2 периода, т.е. возможно что после деления в графике снова будут отрицательные значения. Тогда график платежей нужно делить на 3 и более периода.
Естественно сам файл также можно отредактировать под свои нужды.
В наш век высоких технологий и автоматизации как-то неприлично вручную выполнять сложные расчёты. Хоть аннуитетные платежи рассчитать не так и трудно, но как говорит Юрий Ашер:
В нашей ситуации к вам на помощь придут: компьютер и программа Microsoft Excel.
Те, кто читал предыдущую публикацию, наверняка ещё долго будут с ужасом вспоминать формулу аннуитетного платежа. Но сейчас вы, дорогие друзья, можете облегчённо вздохнуть, ибо все расчёты за вас сделает программа Microsoft Excel.
Мы сделаем не просто файлик с одной циферкой. Нет! Мы разработаем настоящий инструмент, с помощью которого вы сможете рассчитать аннуитетный платёж не только для себя, но и для соседа, который ставит свою машину на детской площадке; прыщавого студента, который сутками курит в вашем подъезде; тётки, которая выгуливает свою собаку прямо под вашими окнами – короче, для всех особо одарённых. Кстати, можете поставить где-нибудь возле монитора купюроприёмник и брать с этой публики деньги.
Давайте приступим к разработке нашего кредитного калькулятора. Смотрим на первый рисунок:
Итак, вы видите два блока. Один с исходными данными, а второй – с расчётами. Исходные данные (сумма кредита, годовая процентная ставка, срок кредитования) вы будете вводить вручную, а во втором блоке будут мгновенно появляться расчёты.
Здесь нам требуется заполнить три поля:
Итак, в данный момент сумма нашего аннуитетного платежа составляет 4680 руб (на рисунке он обведён и указан под номером 1). Если вы будете менять сумму кредита, процентную ставку и общий срок кредитования, то автоматически будет меняться значение вашего аннуитетного платежа.
Зная размер аннуитетного платежа несложно посчитать остальные значения нашего расчётного блока:
На рисунке наглядно показано, как рассчитана общая сумма выплат (обведена и указана под номером 1). Так как она равна сумме аннуитетного платежа (ячейка C11) умноженной на общее количество месяцев кредитования (ячейка C6), то мы и вписываем в строку формул следующую формулу: =C11*C6 (на рисунке она обведена и указана под номером 2). В результате мы получили значение 56 157 рублей.
Переплата по кредиту рассчитывается ещё проще. От общей суммы выплат (ячейка C12) надо отнять сумму кредита (ячейка C4). В строку вписываем такую формулу: =C12-C4. В нашем примере переплата равна: 6157 рублей.
Ну и последнее значение – эффективная процентная ставка (или полная стоимость кредита). Она рассчитывается так: общую сумму выплат (ячейка C12) делим на сумму кредита (ячейка C4), отнимаем единицу, затем делим всё это на срок кредитования в годах (ячейка C6 делённая на 12). В строке будет такая формула: =(C12/C4-1)/(C6/12). В нашем примере эффективная процентная ставка составляет 12,3%.
Всё! Вот таким нехитрым способом мы с вами составили в программе Microsoft Excel автоматический калькулятор расчета аннуитетных платежей по кредиту, скачать который можно ссылке ниже:
Что же, друзья, не будем терять время! Открываем программу Microsoft Excel и приступаем к разработке нашего кредитного калькулятора!
Итак, структура нового кредитного калькулятора почти не изменилась. Здесь также есть блок с исходными данными и блок с расчётами. Единственное изменение, это то, что в исходных данных мы вводим ежемесячный аннуитетный платёж, который готовы выплачивать, а в расчётах получаем сумму кредита, на которую мы можем рассчитывать. Собственно, она на нашем рисунке обведена и отмечена под номером 1.
Кстати, обратите внимание на данные в строке формул (на рисунке они обведены и указаны под номером 2). Вы всё правильно поняли, друзья! Да, это те данные, которые необходимы для расчёта суммы кредита в нашем калькуляторе: =ПС(C5/12;C6;-C4). Те самые параметры, которые мы вводили в таблице функции ПС.
Расчёт остальных показателей выполняется по такому же принципу, как и в предыдущем калькуляторе:
- Общая сумма выплат – это ежемесячный аннуитетный платёж (ячейка С4) умноженный на общий срок кредитования (ячейка С6). В строку формул вводим следующие данные: =C4*C6.
- Переплата (проценты) по кредиту – это общая сумма выплат (ячейка С12) минус сумма кредита (ячейка С11). В строку формул записываем: =C12-C11.
- Эффективная процентная ставка (или полная стоимость кредита) – это общая сумма выплат (ячейка С12) делённая на сумму кредита (ячейка С11) и минус единица. Затем всё это делим на срок кредитования, выраженный в годах (ячейка C6 делённая на 12). В строку формул записываем: = (C12/C11-1)/(C6/12).
Кстати, интересный момент. Вот в нашем примере, выплачивая ежемесячно в течение года по 5000 рублей, мы можем рассчитывать на сумму кредита равную 53 422 рубля. А что делать, если надо больше денег? Как вариант, можно увеличить срок кредитования. Если вместо 12 месяцев поставить 24, то сумма кредита увеличится до 96 380 рублей. Эти данные нам мгновенно выдал наш кредитный калькулятор, который вы можете скачать ссылке ниже:
Два предыдущих кредитных калькулятора очень удобны, но они выполняют краткие (общие) расчёты. А иногда заёмщику нужна расширенная информация – график ежемесячных аннуитетных платежей с детальной расшифровкой каждой выплаты (с указанием сумм, идущих на погашение процентов, и сумм, погашающих тело кредита). В общем, сейчас мы сделаем в программе Excel ещё один кредитный калькулятор, который будет автоматически рассчитывать график аннуитетных платежей. Щёлкаем мышкой по рисунку:
Перед вами расширенная и доработанная версия нашего первого кредитного калькулятора (того, который рассчитывает размер ежемесячного аннуитетного платежа по кредиту). Здесь кроме стандартных блоков с исходными данными и расчётами, появилась таблица, в которой детально расписаны все наши будущие ежемесячные выплаты. Таблица имеет пять колонок:
Вот так легко и непринуждённо мы разработали кредитный калькулятор по расчёту графика аннуитетных платежей. Скачать его можно ссылке ниже:
Итак, друзья, теперь у вас есть целых три кредитных калькулятора по расчёту аннуитетных платежей, разработанных в программе Microsoft Excel. В следующей публикации мы расскажем о досрочном погашении аннуитетного кредита.
Рассчитаем в MS EXCEL сумму регулярного аннуитетного платежа при погашении ссуды. Сделаем это как с использованием функции ПЛТ() , так и впрямую по формуле аннуитетов. Также составим таблицу ежемесячных платежей с расшифровкой оставшейся части долга и начисленных процентов.
При кредитовании банки наряду с дифференцированными платежами часто используют аннуитетную схему погашения. Аннуитетная схема предусматривает погашение кредита периодическими равновеликими платежами (как правило, ежемесячными), которые включают как выплату основного долга, так и процентный платеж за пользование кредитом. Такой равновеликий платеж называется аннуитет.
В аннуитетной схеме погашения предполагается неизменность процентной ставки по кредиту в течение всего периода выплат.
Определить величину ежемесячных равновеликих выплат по ссуде, размер которой составляет 100 000 руб., а процентная ставка составляет 10% годовых. Ссуда взята на срок 5 лет.
Разбираемся, какая информация содержится в задаче:
- Заемщик ежемесячно должен делать платеж банку. Этот платеж включает: сумму в счет погашения части ссуды и сумму для оплаты начисленных за прошедший период процентов на остаток ссуды;
- Сумма ежемесячного платежа (аннуитета) постоянна и не меняется на протяжении всего срока, так же как и процентная ставка. Также не изменяется порядок платежей – 1 раз в месяц;
- Сумма для оплаты начисленных за прошедший период процентов уменьшается каждый период, т.к. проценты начисляются только на непогашенную часть ссуды;
- Как следствие п.3 и п.1, сумма, уплачиваемая в счет погашения основной суммы ссуды, увеличивается от месяца к месяцу.
- Заемщик должен сделать 60 равновеликих платежей (12 мес. в году*5 лет), т.е. всего 60 периодов (Кпер);
- Проценты начисляются в конце каждого периода (если не сказано обратное, то подразумевается именно это), т.е. аргумент Тип=0. Платеж должен производиться также в конце каждого периода;
- Процент за пользование заемными средствами в месяц (за период) составляет 10%/12 (ставка);
- В конце срока задолженность должна быть равна 0 (БС=0).
Расчет суммы выплаты по ссуде за один период, произведем сначала с помощью финансовой функции MS EXCEL ПЛТ() .
Примечание. Обзор всех функций аннуитета в статье найдете здесь.
Эта функция имеет такой синтаксис:
ПЛТ(ставка; кпер; пс; [бс]; [тип])
PMT(rate, nper, pv, [fv], [type]) – английский вариант.
Первый аргумент – Ставка. Это процентная ставка именно за период, т.е. в нашем случае за месяц. Ставка =10%/12 (в году 12 месяцев).
Кпер – общее число периодов платежей по аннуитету, т.е. 60 (12 мес. в году*5 лет)
Пс — Приведенная стоимость всех денежных потоков аннуитета. В нашем случае, это сумма ссуды, т.е. 100 000.
Бс — Будущая стоимость всех денежных потоков аннуитета в конце срока (по истечении числа периодов Кпер). В нашем случае Бс = 0, т.к. ссуда в конце срока должна быть полностью погашена. Если этот параметр опущен, то он считается =0.
Тип — число 0 или 1, обозначающее, когда должна производиться выплата. 0 – в конце периода, 1 – в начале. Если этот параметр опущен, то он считается =0 (наш случай).
Примечание:
В нашем случае проценты начисляются в конце периода. Например, по истечении первого месяца начисляется процент за пользование ссудой в размере (100 000*10%/12), до этого момента должен быть внесен первый ежемесячный платеж.
В случае начисления процентов в начале периода, в первом месяце % не начисляется, т.к. реального пользования средствами ссуды не было (грубо говоря % должен быть начислен за 0 дней пользования ссудой), а весь первый ежемесячный платеж идет в погашение ссуды (основной суммы долга).
Решение1
Итак, ежемесячный платеж может быть вычислен по формуле =ПЛТ(10%/12; 5*12; 100 000; 0; 0) , результат -2 107,14р. Знак минус показывает, что мы имеем разнонаправленные денежные потоки: +100000 – это деньги, которые банк дал нам, -2107,14 – это деньги, которые мы возвращаем банку.
Альтернативная формула для расчета платежа (общий случай):
=-(Пс*ставка*(1+ ставка)^ Кпер /((1+ ставка)^ Кпер -1)+
ставка /((1+ ставка)^ Кпер -1)* Бс)*ЕСЛИ(Тип;1/(ставка +1);1)
Если процентная ставка = 0, то формула упростится до =(Пс + Бс)/Кпер
Если Тип=0 (выплата в конце периода) и БС =0, то Формула 2 также упрощается:
Вышеуказанную формулу часто называют формулой аннуитета (аннуитетного платежа) и записывают в виде А=К*S, где А — это аннуитетный платеж (т.е. ПЛТ), К — это коэффициент аннуитета, а S — это сумма кредита (т.е. ПС). K=-i/(1-(1+i)^(-n)) или K=(-i*(1+i)^n)/(((1+i)^n)-1), где i=ставка за период (т.е. Ставка), n — количество периодов (т.е. Кпер). Напоминаем, что выражение для K справедливо только при БС=0 (полное погашение кредита за число периодов Кпер) и Тип=0 (начисление процентов в конце периода).
Таблица ежемесячных платежей
Составим таблицу ежемесячных платежей для вышерассмотренной задачи.
Для вычисления ежемесячных сумм идущих на погашение основной суммы долга используется функция ОСПЛТ(ставка; период; кпер; пс; [бс]; [тип]) практически с теми же аргументами, что и ПЛТ() (подробнее см. статью Аннуитет. Расчёт в MS EXCEL погашение основной суммы долга). Т.к. сумма идущая на погашение основной суммы долга изменяется от периода к периоду, то необходим еще один аргумент период, который определяет к какому периоду относится сумма.
Для вычисления ежемесячных сумм идущих на погашение процентов за ссуду используется функция ПРПЛТ (ставка; период; кпер; пс; [бс]; [тип]) с теми же аргументами, что и ОСПЛТ() (подробнее см. статью Аннуитет. Расчет в MS EXCEL выплаченных процентов за период).
Примечание. Для определения суммы переплаты по кредиту (общей суммы выплаченных процентов) используйте функцию ОБЩПЛАТ() , см. здесь.
Конечно, для составления таблицы ежемесячных платежей можно воспользоваться либо ПРПЛТ() или ОСПЛТ() , т.к. эти функции связаны и в любой период: ПЛТ= ОСПЛТ + ПРПЛТ
Соотношение выплат основной суммы долга и начисленных процентов хорошо демонстрирует график, приведенный в файле примера .
Примечание. В статье Аннуитет. Расчет периодического платежа в MS EXCEL. Срочный вклад показано как рассчитать величину регулярной суммы пополнения вклада, чтобы накопить желаемую сумму.
График платежей можно рассчитать без использования формул аннуитета. График приведен в столбцах K:P файла примера лист Аннуитет (ПЛТ) , а также на листе Аннуитет (без ПЛТ) . Также тело кредита на начало и конец периода можно рассчитать с помощью функции ПС и БС (см. файл примера лист Аннуитет (ПЛТ), столбцы H:I ).
Ссуда 100 000 руб. взята на срок 5 лет. Определить величину ежеквартальных равновеликих выплат по ссуде, чтобы через 5 лет невыплаченный остаток составил 10% от ссуды. Процентная ставка составляет 15% годовых.
Решение2
Ежеквартальный платеж может быть вычислен по формуле =ПЛТ(15%/12; 5*4; 100 000; -100 000*10%; 0) , результат -6 851,59р.
Все параметры функции ПЛТ() выбираются аналогично предыдущей задаче, кроме значения БС, которое = -100000*10%=-10000р., и требует пояснения.
Для этого вернемся к предыдущей задаче, где ПС = 100000, а БС=0. Найденное значение регулярного платежа обладает тем свойством, что сумма величин идущих на погашение тела кредита за все периоды выплат равна величине займа с противоположным знаком. Т.е. справедливо равенство: ПС+СУММ(долей ПЛТ, идущих на погашение тела кредита)+БС=0: 100000р.+(-100000р.)+0=0.
То же самое и для второй задачи: 100000р.+(-90000р.)+БС=0, т.е. БС=-10000р.