Расчет кредита в Excel

Кто как, а я считаю кредиты злом. Особенно потребительские. Кредиты для бизнеса - другое дело, а для обычных людей мышеловка"деньги за 15 минут, нужен только паспорт" срабатывает безотказно, предлагая удовольствие здесь и сейчас, а расплату за него когда-нибудь потом. И главная проблема, по-моему, даже не в грабительских процентах или в том, что это "потом" все равно когда-нибудь наступит. Кредит убивает мотивацию к росту. Зачем напрягаться, учиться, развиваться, искать дополнительные источники дохода, если можно тупо зайти в ближайший банк и там тебе за полчаса оформят кредит на кабальных условиях, попутно грамотно разведя на страхование и прочие допы?

Так что очень надеюсь, что изложенный ниже материал вам не пригодится.

Но если уж случится так, что вам или вашим близким придется влезть в это дело, то неплохо бы перед походом в банк хотя бы ориентировочно прикинуть суммы выплат по кредиту, переплату, сроки и т.д. "Помассажировать числа" заранее, как я это называю :) Microsoft Excel может сильно помочь в этом вопросе.

Вариант 1. Простой кредитный калькулятор в Excel

Для быстрой прикидки кредитный калькулятор в Excel можно сделать за пару минут с помощью всего одной функции и пары простых формул. Для расчета ежемесячной выплаты по аннуитетному кредиту (т.е. кредиту, где выплаты производятся равными суммами - таких сейчас большинство) в Excel есть специальная функция ПЛТ (PMT) из категории Финансовые (Financial). Выделяем ячейку, где хотим получить результат, жмем на кнопку fx в строке формул, находим функцию ПЛТ в списке и жмем ОК. В следующем окне нужно будет ввести аргументы для расчета:

Расчет кредита в Excel функцией ПЛТ

  • Ставка - процентная ставка по кредиту в пересчете на период выплаты, т.е. на месяцы. Если годовая ставка 12%, то на один месяц должно приходиться по 1% соответственно.
  • Кпер - количество периодов, т.е. срок кредита в месяцах.
  • Пс - начальный баланс, т.е. сумма кредита.
  • Бс - конечный баланс, т.е. баланс с которым мы должны по идее прийти к концу срока. Очевидно =0, т.е. никто никому ничего не должен.
  • Тип - способ учета ежемесячных выплат. Если равен 1, то выплаты учитываются на начало месяца, если равен 0, то на конец. У нас в России абсолютное большинство банков работает по второму варианту, поэтому вводим 0. 

Также полезно будет прикинуть общий объем выплат и переплату, т.е. ту сумму, которую мы отдаем банку за временно использование его денег. Это можно сделать с помощью простых формул:

Вычисление переплаты по кредиту

Вариант 2. Добавляем детализацию

Если хочется более детализированного расчета, то можно воспользоваться еще двумя полезными финансовыми функциями Excel - ОСПЛТ (PPMT) и ПРПЛТ (IPMT). Первая из них вычисляет ту часть очередного платежа, которая приходится на выплату самого кредита (тела кредита), а вторая может посчитать ту часть, которая придется на проценты банку. Добавим к нашему предыдущему примеру небольшую шапку таблицы с подробным расчетом и номера периодов (месяцев):

Подробный расчет выплат по кредиту

Функция ОСПЛТ (PPMT) в ячейке B17 вводится по аналогии с ПЛТ в предыдущем примере:

Расчет выплаты тела кредита

Добавился только параметр Период с номером текущего месяца (выплаты) и закрепление знаком $ некоторых ссылок, т.к. впоследствии мы эту формулу будем копировать вниз. Функция ПРПЛТ (IPMT) для вычисления процентной части вводится аналогично. Осталось скопировать введенные формулы вниз до последнего периода кредита и добавить столбцы с простыми формулами для вычисления общей суммы ежемесячных выплат (она постоянна и равна вычисленной выше в ячейке C7) и, ради интереса, оставшейся сумме долга:

Подробный кредитный калькулятор

Чтобы сделать наш калькулятор более универсальным и способным автоматически подстраиваться под любой срок кредита, имеет смысл немного подправить формулы. В ячейке А18 лучше использовать формулу вида:

=ЕСЛИ(A17>=$C$7;"";A17+1)

Эта формула проверяет с помощью функции ЕСЛИ (IF) достигли мы последнего периода или нет, и выводит пустую текстовую строку ("") в том случае, если достигли, либо номер следующего периода. При копировании такой формулы вниз на большое количество строк мы получим номера периодов как раз до нужного предела (срока кредита). В остальных ячейках этой строки можно использовать похожую конструкцию с проверкой на присутствие номера периода:

=ЕСЛИ(A18<>""; текущая формула; "")

Т.е. если номер периода не пустой, то мы вычисляем сумму выплат с помощью наших формул с ПРПЛТ и ОСПЛТ. Если же номера нет, то выводим пустую текстовую строку:

credit6.png

Вариант 3. Досрочное погашение с уменьшением срока или выплаты

Реализованный в предыдущем варианте калькулятор неплох, но не учитывает один важный момент: в реальной жизни вы, скорее всего, будете вносить дополнительные платежи для досрочного погашения при удобной возможности. Для реализации этого можно добавить в нашу модель столбец с дополнительными выплатами, которые будут уменьшать остаток. Однако, большинство банков в подобных случаях предлагают на выбор: сокращать либо сумму ежемесячной выплаты, либо срок. Каждый такой сценарий для наглядности лучше посчитать отдельно.

В случае уменьшения срока придется дополнительно с помощью функции ЕСЛИ (IF) проверять - не достигли мы нулевого баланса раньше срока:

credit8.png

А в случае уменьшения выплаты - заново пересчитывать ежемесячный взнос начиная со следующего после досрочной выплаты периода:

Кредитный калькулятор с уменьшением выплаты

Вариант 4. Кредитный калькулятор с нерегулярными выплатами

Существуют варианты кредитов, где клиент может платить нерегулярно, в любые произвольные даты внося любые имеющиеся суммы. Процентная ставка по таким кредитам обычно выше, но свободы выходит больше. Можно даже взять в банке еще денег в дополнение к имеющемуся кредиту. Для расчета по такой модели придется рассчитывать проценты и остаток с точностью не до месяца, а до дня:

credit7.png

Предполагается что:

  • в зеленые ячейки пользователь вводит произвольные даты платежей и их суммы
  • отрицательные суммы - наши выплаты банку, положительные - берем дополнительный кредит к уже имеющемуся
  • подсчитать точное количество дней между двумя датами (и процентов, которые на них приходятся) лучше с помощью функции ДОЛЯГОДА (YEARFRAC)



01.11.2014 12:45:24
А в общем, лучше в этой жизни обходиться без кредитов. Особенно при нынешних ставках.
"- Алло! Вы даете кредиты пациентам психоневрологического диспансера?
— Даем, но под сумасшедший процент."
04.11.2014 23:22:07
Функция возможно и считает правильно, с точной механикой не разбирался, но договора все разные и расчет у всех по разному.
я лично хотел обратить внимание на вычисление ежемесячной ставки
12% / 12 =...
если брать выплату 0 в месяце то мы за год получим  больше чем 112%  (игра %, и годовой %  другой)
поэтому когда нужно было просчитывать подобные задачи я создавал соответствующие формулы под условия, для пользования данной функции нужно знать механику её вычисления что бы не попасть в неловкую ситуацию
05.11.2014 13:27:57
Алекс, функция считает правильно, но по классической честной кредитной модели. Многие наши российские банки действительно вносят свою "специфику" в виде скрытых дополнительных поборов, это факт. Но хотя бы примерно понимать порядок чисел до заключения договора или иметь цифру, с которой можно сравнить ту, что предлагают в банке - по-любому полезно.
16.12.2014 09:38:40
Здравствуйте! Не могу подобрать формулу чтобы в случае (уменьшение срока) в столбце дополнительно, выводился регулярный(назначеный возможно в шапке под сроком) дополнительный платеж, в зависимости от столбца "осталось выплатить". То есть чтобы была возможность задать ежемесячный дополнительный платеж, и столбец "Осталось выплатить не ушел в "-", а как достиг нуля, добил остаток по кредиту.
24.12.2014 22:09:51
Николай добрый вечер по моему времени суток, нужна помощь в моем вопросе по кредитному калькулятору  проблема в том что нужно сделать так чтоб первый месяц он высчитывал не 30 календарных дней а по факту сколько есть то есть платеж в принципе  всегда больше. Как это сделать есть график на руках то есть>>>>>>>>> ставится определенная дата и все сумма совсем иная я сдела как у вас у меня вышло 91 169 еж. платеж
Дано: сумма кредита 3,348,250 тн.  ;   оформление кредита  12.12.2014г ; дата платежа стоит 10.02.2015г ; первый платеж 128,637тн.; последующие 88,271 ; срок 60 мес ;размер ставки вознаграждения в годовых процентах- фиксированная, 15,50% годовых ; Размер ставки вознограждеия в достоверном, годовом, эффективном, сопоставимом исчислении 22,2% годовых ; общая сумма к погашению 5,336,626
Как задать чтоб он как в системе банковской считал всегда то есть ставлю дату соответственно первый платеж менялся а остальные  были равными  и просто можно было задать сумму и срок и первоначальный взнос к примеру и он сам считал процентную ставку и т.д. заранее благодарю! с н.п.  
08.05.2015 00:24:10
Добрый день!
Николай, а как сделать аннуитетный график, но не с ежемесячными выплатами, а еженедельными?
11.05.2015 20:27:44
Если позволите комментарий:)
Скорей всего по факту ваш платеж не будет совпадать с данными таблицы, хотя разница и будет минимальной. Во многих банках процент  в месяц рассчитывается чуть по другому. Не 12% /  12мес, а 12% / 365*(кол-во дней в мес). И т.к. кол-во дней в мес. может быть 28,30,31~ соответственно % будет отличаться.
А в остальном просто шикарно ))) наглядно, красиво, понятно.  И еще я бы добавил в центр ячейку с суммой руб. отданных процентов ;).
28.06.2015 20:20:15
Еще есть формула БС (расчет будущей стоимости), но почему-то у меня идет сильная разница. Почему?
Не знаю, почему, но эксель-файл вставить не могу
07.04.2016 09:35:45
Дброго времени суток! =)
отличная форма для расчета, а подскажите пожалуйста как правильнней связать 2 таблицы, а точнее сделать одну таблицу в которой отражено и можно учитывать как и досрочное погашение 1) на уменьшение срок, так и 2) на уменьшение платежа.
может быть поднимался этот вопрос но пока не нашел в архивах ресурса ни чего(
11.04.2016 05:44:25
Здравствуйте!
Вставил между столбцами "Период" и "Выплата кредита" столбец "Дата". А как сделать чтобы даты выплат, которые выпадают на одно и тоже число каждого последующего месяца, также останавливались как и период?
Спасибо
... разобрался...
Здравствуйте. Проблема такая у меня. Я даю деньги под 10%, на 28 дней, с ежедневной выплатой. Как можно заполнить такую формулу в Excel, с базой нескольких людей, подскажите пожалуйста.
07.03.2017 23:03:27
Доброе время суток!
У меня такой вопрос, как можно посчитать аннуитетный платеж если за период используются несколько процентных ставок. Например выдается кредит на 24 месяца, в первые 12 месяцев ставка 5% годовых, а дальше - 20%.
Заранее спасибо  
19.04.2018 13:41:06

У меня такой вопрос, как можно посчитать аннуитетный платеж если за период используются несколько процентных ставок
Присоединяюсь к вопросу!
Наверх