Калькулятор расчета кредита в Excel и формулы ежемесячных платежей. Делаем кредитный калькулятор в Excel

Excel – это универсальный аналитическо-вычислительный инструмент, который часто используют кредиторы (банки, инвесторы и т.п.) и заемщики (предприниматели, компании, частные лица и т.д.).

Быстро сориентироваться в мудреных формулах, рассчитать проценты, суммы выплат, переплату позволяют функции программы Microsoft Excel.

Как рассчитать платежи по кредиту в Excel

Ежемесячные выплаты зависят от схемы погашения кредита. Различают аннуитетные и дифференцированные платежи:

Аннуитет предполагает, что клиент вносит каждый месяц одинаковую сумму.
При дифференцированной схеме погашения долга перед финансовой организацией проценты начисляются на остаток кредитной суммы. Поэтому ежемесячные платежи будут уменьшаться.

Чаще применяется аннуитет: выгоднее для банка и удобнее для большинства клиентов.

Расчет аннуитетных платежей по кредиту в Excel

Ежемесячная сумма аннуитетного платежа рассчитывается по формуле:

А = К * S

А – сумма платежа по кредиту;
К – коэффициент аннуитетного платежа;
S – величина займа.

Формула коэффициента аннуитета:

К = (i * (1 + i)^n) / ((1+i)^n-1)

где i – процентная ставка за месяц, результат деления годовой ставки на 12;
n – срок кредита в месяцах.

В программе Excel существует специальная функция, которая считает аннуитетные платежи. Это ПЛТ:

Ячейки окрасились в красный цвет, перед числами появился знак «минус», т.к. мы эти деньги будем отдавать банку, терять.

Расчет платежей в Excel по дифференцированной схеме погашения

Дифференцированный способ оплаты предполагает, что:

сумма основного долга распределена по периодам выплат равными долями;
проценты по кредиту начисляются на остаток.

Формула расчета дифференцированного платежа:

ДП = ОСЗ / (ПП + ОСЗ * ПС)

ДП – ежемесячный платеж по кредиту;
ОСЗ – остаток займа;
ПП – число оставшихся до конца срока погашения периодов;
ПС – процентная ставка за месяц (годовую ставку делим на 12).

Составим график погашения предыдущего кредита по дифференцированной схеме.

Входные данные те же:

Составим график погашения займа:

Остаток задолженности по кредиту: в первый месяц равняется всей сумме: =$B$2. Во второй и последующие – рассчитывается по формуле: =ЕСЛИ(D10>$B$4;0;E9-G9). Где D10 – номер текущего периода, В4 – срок кредита; Е9 – остаток по кредиту в предыдущем периоде; G9 – сумма основного долга в предыдущем периоде.

Выплата процентов: остаток по кредиту в текущем периоде умножить на месячную процентную ставку, которая разделена на 12 месяцев: =E9*($B$3/12).

Выплата основного долга: сумму всего кредита разделить на срок: =ЕСЛИ(D9

Итоговый платеж: сумма «процентов» и «основного долга» в текущем периоде: =F8+G8.

Внесем формулы в соответствующие столбцы. Скопируем их на всю таблицу.

Сравним переплату при аннуитетной и дифференцированной схеме погашения кредита:

Красная цифра – аннуитет (брали 100 000 руб.), черная – дифференцированный способ.

Формула расчета процентов по кредиту в Excel

Проведем расчет процентов по кредиту в Excel и вычислим эффективную процентную ставку, имея следующую информацию по предлагаемому банком кредиту:

Рассчитаем ежемесячную процентную ставку и платежи по кредиту:

Заполним таблицу вида:

Комиссия берется ежемесячно со всей суммы. Общий платеж по кредиту – это аннуитетный платеж плюс комиссия. Сумма основного долга и сумма процентов – составляющие части аннуитетного платежа.

Сумма основного долга = аннуитетный платеж – проценты.

Сумма процентов = остаток долга * месячную процентную ставку.

Остаток основного долга = остаток предыдущего периода – сумму основного долга в предыдущем периоде.

Опираясь на таблицу ежемесячных платежей, рассчитаем эффективную процентную ставку:

взяли кредит 500 000 руб.;
вернули в банк – 684 881,67 руб. (сумма всех платежей по кредиту);
переплата составила 184 881, 67 руб.;
процентная ставка – 184 881, 67 / 500 000 * 100, или 37%.
Безобидная комиссия в 1 % обошлась кредитополучателю очень дорого.

Эффективная процентная ставка кредита без комиссии составит 13%. Подсчет ведется по той же схеме.

Расчет полной стоимости кредита в Excel

Согласно Закону о потребительском кредите для расчета полной стоимости кредита (ПСК) теперь применяется новая формула. ПСК определяется в процентах с точностью до третьего знака после запятой по следующей формуле:

ПСК = i * ЧБП * 100;
где i – процентная ставка базового периода;
ЧБП – число базовых периодов в календарном году.

Возьмем для примера следующие данные по кредиту:

Для расчета полной стоимости кредита нужно составить график платежей (порядок см. выше).

Нужно определить базовый период (БП). В законе сказано, что это стандартный временной интервал, который встречается в графике погашения чаще всего. В примере БП = 28 дней.

Теперь можно найти процентную ставку базового периода:

У нас имеются все необходимые данные – подставляем их в формулу ПСК: =B9*B8

Примечание. Чтобы получить проценты в Excel, не нужно умножать на 100. Достаточно выставить для ячейки с результатом процентный формат.

ПСК по новой формуле совпала с годовой процентной ставкой по кредиту.

Таким образом, для расчета аннуитетных платежей по кредиту используется простейшая функция ПЛТ. Как видите, дифференцированный способ погашения несколько сложнее.

Всем вам наверняка рано или поздно приходит мысль о кредите. Кому нужно машину, кому квартиру. Сам проходил, знаю . И тут уже надо считать. Когда вы только прицениваетесь к кредиту, то достаточно тех калькуляторов, что есть в сети. Хотя и они отвечают не на все вопросы, к ним адресованные. Но когда вы уже берете кредит, то без расчета графика платежей никуда. Поэтому предлагаю завести собственный кредитный (ипотечный) калькулятор у себя в книге Excel.

Итак, любой кредит имеет 4 основных параметра:

Срок
Сумма
Ставка
Ежемесячный платеж. Состоит из части погашения основного долга и процентов, набежавших по нему за прошедший период.

Так же есть две формы платежей – аннуитетные (когда вы каждый месяц платите одну и ту же сумму) и дифференцированные (когда постоянной остается часть ежемесячного платежа – та, которая погашает основной долг, а вторая часть регулярно пересчитывается).

Если вы знаете 3 показателя, то сможете подобрать четвертый.

Мы сделаем сначала калькулятор. За расчет всех четырех показателей отвечают эти функции:

Срок – Функция ПС()

Сумма – Функция КПЕР()

Ставка – Функция СТАВКА()

Ежемесячный платеж – Функция ПЛТ()

Параметры функций одни и те же – знаете три из 4-х показателей, соответствующая функция выдаст 4-й. Нагляднее смотрите первый лист .

Чтобы подготовить график платежей, нам понадобится дата выдачи кредита.

Небольшое отступление по досрочному погашению . Досрочное погашение уменьшает сумму основного долга, поэтому после него обычно пересчитывается ежемесячный платеж или меняется срок кредита.

Переходим ко второму листу.

Первая строчка графика – дата выдачи, поэтому тут будет только первоначальная сумма кредита.

На второй строке –

Дата – определяется как то же число, что и выдача кредита, но следующего месяца. Используем функцию ДАТА , где год и число те же, что и в предыдущем периоде, а месяц на один больше. Но есть нюанс – банк ведь не примет платеж в выходной день. Поэтому делаем корректировку числа с помощью функции ДЕНЬНЕД . Важно : дату можно корректировать вручную, на следующую дату влияния не окажет.
Сумма ежемесячного платежа (которая определяется по функции ПЛТ ).
Сумма погашения процентов как умножение величины прошедшего периода на соответствующий процент. Используется функция ДОЛЯГОДА , чтобы убрать последствия високосности. Банки скрупулезно подходят к расчетам, поэтому период считается в днях, иначе можно было бы сделать проще – взять годовой процент, поделить на 12 месяцев и умножить на сумму.
Сумма погашения основного долга – берется как разница ежемесячного платежа и суммы погашения процентов.
Досрочное погашения и его дата ставятся произвольно. Единственное условие – ставится в тот период, где дата или меньше или совпадает с датой досрочного погашения.
Сумма долга после платежа определяется как сумма предыдущего периода за вычетом погашения основной части и суммы досрочного погашения.

И последний нюанс построения графика – на третьей строчке мы меняем немного формулу ежемесячного платежа – ставим условие, что если было досрочное погашение, то сумма пересчитывается по функции ПЛТ , а если нет, остается как в предыдущей строке.

Теперь сделаем такой же график для дифференцированных платежей.

Меняем две формулы:

1) Сумму погашения основного долга. Она будет неизменной - сумма долга разделить на количество периодов (месяцев).

2) Ежемесячный платеж определяем как сумму двух частей - погашений основного долга и процентов.

Разница двух форм по сути в том, что вы больше платите в месяц по дифференцированному платежу, но быстрее расплачиваетесь и поэтому в итоге платите меньше процентов.

Какие еще можно вытащить показатели, которые важны нам, но не учитываются в доступных калькуляторах?

Для меня это была обоснованность взятия кредита. Я тогда снимал квартиру и поэтому мне нужно было рассчитать цену кредита. Цена кредита для меня равнялась сумме выплаченных процентов за минусом арендных платежей за весь период кредита. Если сумма небольшая или вообще отрицательная, то кредит брать стоит. Бонусом для меня было проживание в СВОЕМ (!) доме, где я знал, что могу забить гвоздь в МОЮ стенку, да и вообще психологическое влияние большое.

Первый кредитный калькулятор в Excel можно скачать по .
Но Excel есть не на всех компьютерах. Пользователи MAC и Linux не пользуются Excel обычно, т.к. это продукт Microsoft.
Для расчета досрочного погашения можно также воспользоваться калькулятора с досрочным погашением. В нем предусмотрена возможность экспорта результатов расчета в Excel.

На основе этого калькулятора был разработан ипотечный калькулятор для Android и iPhone. Найти и скачать мобильные версии калькуляторов можно с .

Достоинства данного калькулятора:

Кредитный калькулятор в Excel практически точно считает аннуитетный график платежей и дифференцированный график платежей
Изменения в графике платежей — учет досрочных погашений в уменьшение суммы основного долга
Построение и расчет графика платежей в виде таблицы в Excel. Таблица графика платежей может также редактироваться
При расчете учитывается високосный и невисокосный год. За счет этого сумма начисленных процентов практически совпадает с значениями, рассчитываемыми ВТБ24 и Сбербанком
Точность расчетов — рассчеты совпадают с расчетами кредитного калькулятора ВТБ24 и Сбербанка
Калькулятор можно редактировать под себя, задавая разные варианты расчета.

Недостатки калькулятора

Нет учета возможное изменение процентной ставки во время выплат кредита
Если сделать расчет, делая досрочные платежи в изменение срока и суммы, то расчет будет неверным
Если сумма процентов, начисленных за период больше суммы аннуитетного платежа, то расчет будет не верным
Не рассчитывается вариант — первый платеж только проценты. В случае когда дата выдачи не совпадает с датой первого платежа, вам нужно будет заплатить проценты банку за период между датой выдачи и датой первого платежа.
Расчет производится для процентой ставки с 2мя знаками после запятой.

Всех выше названных недостатков лишен . В целом недостатки не сильно критичны и они присущи любому кредитному калькулятору онлайн.
Другой кредитный калькулятор в Excel можно скачать по . Данный кредитный калькулятор не позволяет рассчитать досрочное погашение. Однако его плюс в том, что он рассчитывает кредит с несколькими процентными периодами. Если сумма процентов по кредиту за данный месяц больше суммы аннуитетного платежа, то график для первого кредитного калькулятора в excel строится некорректно. В графике получаются отрицательные суммы.

Попробуйте посчитать к примеру кредит 1 млн. руб под 90 процентов на срок 30 лет.
У второго калькулятора нет данного недостатка. Однако он делит кредит на 2 периода, т.е. возможно что после деления в графике снова будут отрицательные значения. Тогда график платежей нужно делить на 3 и более периода.
Естественно сам файл также можно отредактировать под свои нужды.

Добрый день уважаемый пользователь!

Сегодня я хотел бы поговорить о таком необходимом зле, как кредит. Почему зло, вы и так знаете, особенно это касается потребительского кредитования, когда за вещь вы переплачиваете в 2-3 раза больше ее реальной цены. Это всё необходимо учитывать и просчитывать, поэтому и научитесь создавать свой личный кредитный калькулятор в котором вы реально увидите картинку «мышеловки», в которую попадают обычный обыватель. Хотя есть еще кредиты для бизнеса, но там немного другая история, их берут, чтобы зарабатывать деньги. Главная проблема кредита даже не в «космических» процентах, а в том, что вы получаете удовольствие сейчас, а расплата и проблемы вас ждут в будущем, а это убивает личную мотивацию практически в зародыше. Пропадает желание, что-то делать, развиваться, напрягаться, учиться, создавать источники дохода, когда можно «тупо» взять паспорт и за 15 минут в ближайшем банке вас быстренько возьмут в кабалу и грамотно навешают на вас кучу всего разного и лишнего, лишь бы было, типа страховку и прочее.

Поэтому я очень хочу, чтобы материал, который я дам в своей статье будет вам полезен в принятии ваших решений.

Несмотря на то, что я не являюсь приверженцем кредитов, всё же осознаю их необходимость. Недавно мой ребенок попал в больницу, и я был вынужден, в силу обстоятельств, использовать средства кредитной линии. Ну а потом на протяжении 2 недель полностью закрыл долг, не отлаживая его в долгий ящик. Ну не мог я по-иному, нужны были деньги и срочно, ну и долг я сразу же закрыл и не ждал ни окончания льготного периода, ни начисления процентов.

Вот исходя из этих соображений и всё же возникновению необходимости получения кредита вами или вашими близкими, необходимо, я бы сказал желательно, перед путешествием в банк прикинуть ориентировочно сумму, сроки переплаты и т.п... После того как вы прочувствуете цифры вы или будете готовы оформить кредит или попросту откажетесь от него. И в этом вопросе вам очень поможет Microsoft Excel.

Рассмотрим три самых популярных варианта использования кредитного калькулятора в Excel:

Кредитный калькулятор для расчёта простых кредитов

Начнём с простого варианта, быстро прикинем, сколько нам нужно ежемесячно оплачивать по аннуитетному кредиту, это когда выплаты делают одинаковыми суммами, как в большинстве случаев. Это можно произвести одной функцией Excel и несколькими простыми формулами. Для получения результата в Excel существует функция ПЛТ в разделе «Финансовые» .
Указываем, в какую ячейку нужен результат, вызываем «Мастер функций» ищем функцию ПЛТ, нажимаем кнопочку «ОК» и в окне мастера вводим необходимые аргументы для нашего расчёта, формула получается следующего вида:

=ПЛТ(B5/12;B6;B4;0;0) , где:

Ставка (B5/12) – является аргументом, указывающим на процентную ставку по взятому кредиту в разрезе периодов выплат, в нашем случае это месяцы. Если ставка по кредиту в год 18%, то за один месяц будет составлять 1,5%;
Кпер (B6) – аргумент, указывающий на количество периодов, то есть, на сколько месяцев взят кредит;
Бс (B4) – указываем, какую сумму кредита будем рассчитывать;
Пс (0) – это финишная пряма, какой итог кредита должен быть в конце, скорее всего это будет 0, что означает, что вы никому и ничего не должны;
Тип (0) – аргумент необходимый для учёта выплат каждый месяц. Если равно 1 – это учитываем выплаты к началу месяца, если 0 – то учитываем на конец. В постсоветском пространстве большинство банков используют последний вариант, а значит вводим 0.

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

Теперь в поле «Тело кредита» в ячейку Е2 вводим формулу функции ОСПЛТ следующего вида:

=ОСПЛТ($B$4/12;D2;$B$5;$B$3;0)

Как видите, ее орфография практически аналогична функции ПЛТ, добавился только аргумент «Период» , который указывает на номер текущего месяца, и дополнительно рассматривать ее я не буду. Единственное, на что обращу ваше внимание, это то, что формула будет растянута на диапазон, а значит, аргументы необходимо закрепить .
Следующим шагом для столбика «Проценты» будем использовать возможности функции ПРПЛТ. Вводится она аналогично вышеописанной и с теми же условиями и будет иметь такой вид:

=ПРПЛТ($B$4/12;D2;$B$5;$B$3;0)
Теперь в оставшиеся столбики будем вводить простые формулы, для получения суммы выплаты нам нужна формула: =E2+F2 , а для определения суммы остатка кредита используем формулу: =$B$3+СУММ($E$2:E2) .
При необходимости, возможно, немножко улучшить и автоматизировать ваш кредитный калькулятор в Excel для уменьшения количества .

Для начала пропишем формулу в ячейку D3 для того чтобы она подстраивала и отслеживала срок кредита:

=ЕСЛИ(D2>=$B$5;"«;D2+1)

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

=ЕСЛИ(D3<>»";ОСПЛТ($B$4/12;D3;$B$5;$B$3;0);"")

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

Этот вариант рассмотрим, когда вы будете не удовлетворены суммами платежей или сроками погашения и вам захочется закрыть кредит, досрочно, используя дополнительные платежи при наличии свободных средств.

Для реализации этого добавляем дополнительный столбик «Доп.платёж» в котором будут указываться сумма платежей уменьшающий остаток кредита. Но у банков есть два варианта развития событий:

во-первых, сокращения суммы выплат по кредиту на каждый месяц;
во-вторых, уменьшения срока выплат.

Для лучшей наглядности будем рассматривать каждый случай в отдельности.

Рассмотрим расчеты, когда происходит погашения кредита раньше срока, в этом случае будем использовать функционал функции ЕСЛИ и проверим, достигли ли мы нулевой задолженности раньше указанного срока:
В другом случае, когда у вас происходит уменьшение суммы выплат по кредиту, формула пересчитывает ваш ежемесячный платёж сразу же после внесённого дополнительного платежа.

Последний из рассматриваемых вариантов будет расчёт кредита с нерегулярными платежами, это когда на повышенную процентную ставку вам предоставляют лояльную программу вносить платежи на нерегулярной основе и без определений сумм взносов. Согласно таким кредитным программам банк может вам выделять еще дополнительно денег на ваши нужды, но для расчётов такой структуры кредитования производить расчёты нужно с точностью до , а не до .
Ну вот в принципе и всё, единственно что хочу сказать, что подсчёт сколько точно дней находится между двумя указанными датами, лучше производить при помощи функции ДОЛЯГОДА.

А на этом у меня всё! Я очень надеюсь, что всё о создании кредитного калькулятора в Excel вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями, прочитанным и ставьте лайк!