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

Автоматизировать процесс дифференцированного расчёта кредита можно при помощи кредитного калькулятора, разработанного в программе Microsoft Excel. В этой публикации мы вам расскажем и покажем, как это делается. Давайте приступим!

Где можно бесплатно скачать такой калькулятор

Не удивляйтесь, друзья, но вначале вам действительно надо скачать готовый калькулятор дифференцированных платежей, который мы разработали в Excel. Именно его мы и будем «разбирать на запчасти». Также, при желании, вы сможете его доработать под свои требования.

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

Получилось? Вот и отлично! Приступаем к «разбору полётов»!

Разрабатываем калькулятор дифференцированных платежей в Excel

Прежде всего давайте разберемся, по какому принципу работает наш калькулятор. Откройте скачанный «экселевкий» файл. В верхнем левом углу страницы вы увидите две таблицы. Они называются: «Укажите данные для расчёта» и «Результаты расчёта». Также сверху над всеми столбцами нашей страницы Excel есть буквы A, B, C, D, E, F и т.д., а слева напротив строк – цифры 1, 2, 3, 4, 5, 6 и т. д. Именно эти буквы и цифры определяют координаты каждой ячейки таблицы.

Кликните левой кнопкой мыши по ячейке со значением «5958р.» , которое находится в результатах расчёта в строке «Переплата по кредиту». В нашем калькуляторе эта ячейка имеет координаты B8 . Вот вам картинка для наглядности:

На изображении данную ячейку мы обвели красной линией и обозначили цифрой один. Обратите внимание ещё вот на что. Когда вы кликаете по какой-либо ячейке в таблице Excel, то эта ячейка выделяется чёрной жирной рамкой, а её буквенно-цифровые координаты сверху и слева окрашиваются другим фоном. Например, на нашем изображении буква B сверху и цифра 8 слева изменили цвет фона с серо-голубого на желтоватый. Также в верхней строке формул, слева от которой есть кнопка «fx» (на рисунке она обведена красным и обозначена цифрой два) указано значение или формула, по которой выполняется расчёт данных для выделенной ячейки. В нашем примере для ячейки с координатой B8 выполняется расчёт по следующей формуле: =B7-B2 . В окне с координатой B7 указана общая сумма выплат по кредиту, которая в нашем примере равна 55 958 рублей , а B2 – это сам кредит, который равен 50 000 рублей . Выполнив простое математическое вычисление, наша программа занесла в ячейку B8 значение 5958 (55 958 – 50 000=5958).

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

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

• «Ежемесячный платёж» – это ежемесячный дифференцированный платёж по займу. Он состоит из двух частей: суммы, идущей на погашение процентов (ячейка F14 ), и суммы, идущей на погашение тела кредита (ячейка G14 ). Именно потому ежемесячный платёж в первой строке рассчитан по формуле: =F14+ G14 .
• «Погашение процентов» – здесь работает формула расчёта процентов по кредиту за данный период: остаток задолженности (в первом платеже он равен сумме кредита 50 000 руб. , вынесенную в ячейку H13 ) умножить на (она равна 22% и вынесена в ячейку A14 ) и разделить на 12 (мы вынесли это значение в ячейку B14 ). Собственно, эти условия и прописаны в формуле для ячейки F14 : =H13*A14/B14 . Кстати, вместо B14 можно просто указать фиксированную цифру – 12 .
• «Погашение тела кредита» – это фиксированное значение, которое не меняется на протяжении всего срока кредитования. Рассчитывается этот показатель очень просто: сумма кредита (ячейка B2 ) делится на общий срок кредитования (ячейка B4 ). В итоге для ячейки G14 получаем такую формулу: = B2/B4 .
• «Долг на конец месяца» – из суммы долга на конец предыдущего месяца (в первом платеже он у нас равен сумме кредита – 50 000 рублей и вынесен в ячейку H13 ) вычитаем выплату по телу кредита в текущем периоде (4167 рублей – ячейка G14 ). В результате, долг на конец месяца по первому платежу у нас равен 45 833 рубля (50 000 – 4167 = 45 833), что и записано в формуле для ячейки H14 : = H13- G14 .

Вот таким нехитрым способом разработан кредитный калькулятор дифференцированных платежей в Excel. Он рассчитан на кредиты сроком до 12 месяцев. При желании, вы можете его усовершенствовать и расширить данный диапазон до 24, 36 и более месяцев. В общем, теперь всё в ваших руках, друзья. Как говорится, мы вам дали удочку, а вы сами решайте, что с ней дальше делать.

Портал сайт – ваш надёжный информационный помощник в вопросах кредитования. Оставайтесь с нами!

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

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

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

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

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

Процентная ставка

Процентная ставка - очень важный параметр при рассчете ипотеки. Измеряется в процентах годовых. Этот параметр показывает сколько процентов начисляется на ваш долг в год. Для наглядности возьмем конкретное значение процентной ставки - 12%. Это значит, что в год к вашему долгу прибавляется ещё 12% от суммы долга, НО: при ипотечном кредитовании банк начисляет вам проценты не раз в год, а ежедневно на оставшуюся сумму долга. Не трудно посчитать сколько процентов начисляется каждый день: 12% / 12 месяцев / 30 дней = 0.033%.

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

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

Таблица 1. Демонстрация влияния процентной ставки на параметры кредита.

Фиксированная и плавающая процентная ставка

Фиксированная процентная ставка - это ставка по кредиту, которая устанавливается на весь срок кредита. Она прописана в кредитном договоре и не может быть изменена.

Плавающая процентная ставка - это ставка по кредиту, которая не является постоянной величиной, а рассчитывается по формуле, которая определена в договоре. Размер ставки состоит из двух частей: Первая составляющая - плавающая, привязана к какому либо рыночному индикатору (например Mosprime3m или ставка рефинансирования ЦБ) и изменяется с периодичностью, определенной в кредитном договоре (например, ежемесячно, ежеквартально или раз в полгода). Вторая составляющая, фиксированная - это процент, который берет себе банк. Эта часть остается всегда постоянной.

Аннуитетный и дифференцированный платеж

• Аннуитетный платеж - вариант ежемесячного платежа по кредиту, когда размер ежемесячного платежа остаётся постоянным на всём периоде кредитования.
• Дифференцированный платеж - вариант ежемесячного платежа по кредиту, когда размер ежемесячного платежа по погашению кредита постепенно уменьшается к концу периода кредитования.

В настоящее время наиболее распространен аннуитетный платеж.

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

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

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

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

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

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

Расчет аннуитетных платежей по кредиту в 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. Достаточно выставить для ячейки с результатом процентный формат.

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

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

На данный момент в банках выдается достаточно большое количество кредитов.
Как известно существует два способа погашения кредита — с помощью аннуитетных или дифференцированных платежей.
Кредиты с дифференцированным типом платежей преобладают в Сбербанке. Сбербанк выдает кредиты с дифференцированным типом платежей. К таким кредитам относятся автокредиты и ипотека.
Расчет дифференцированного платежа достаточно прост. Рассмотрим формулы расчета дифференцированных платежей.

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

Формула для расчета дифференцированных платежей по кредиту:

Остаток основного долга берется на дату платежа.
Количество процентных периодов – срок (в месяцах) до окончания кредита.
Процентная ставка – годовая процентная ставка по кредиту.
Деление на 100 и на 12 производится для перевода ставки в проценты и вычисления процентов за один расчетный период (месяц).
Как видно формула состоит из 2х частей. Первая часть формулы — это константа всегда, т.е для любого месяца. Сумма долга деленная на число месяцев.
Эта часть платежа идет в погашение основного долга.
К примеру если брать кредит 120 тыс на 12 месяцев по ставке 10%, то постоянная часть дифференцированного платежа будет равна

120 000 /12 = 10 000 рублей.

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

Эта часть идет в погашение процентов.
Рассчитаем сумму в погашение процентов для первого платежа

120 000 * 10/(100 * 12) = 1000 рублей.

Первый платеж по дифференцированному займу таким образом будет равен постоянная часть + переменная часть =

10 тыс + 1 тыс = 11 тыс. рублей.

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

120 тыс — 10 тыс = 110 тыс.

Исходя из новой суммы основного долга посчитаем проценты по кредиту

110 000 * 10/(100 * 12) = 916,67

Второй платеж по дифференцированному займу равен

10000 + 916.67 = 10916.67

Дифференцированные платежи и досрочное погашение.

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

Сумма ОД = Остаток долга — Сумма в уменьшение ОД — Досрочное погашение

Подставим данные в формулу
Остаток долга= 110 тыс.
Сумма в погашение ОД = 10 тыс.
Досрочные погашения = 20 тыс.

110 000 — 10 000 — 20 000 = 80 000 рублей

Ставка по кредиту осталась той же, 10 процентов
А вот срок кредита изменится.

Новый срок = Число месяцев — Номер месяца досрочного погашения

Посчитаем новый срок

12 — 2 = 10 месяцев

Теперь рассчитаем новый кредит на сумму 80 тыс. на 10 месяцев под 10 процентов годовы

Сумма в погашение ОД = 80 000/10 =8 000 рублей

Рассчитаем платеж по процентам для кредита после досрочного платежа

Проценты по кредиту = 80 000 * 10/(100 * 12) =666,67

Теперь рассчитаем сумму нового платежа после досрочного погашения

Сумма диффер. платежа = 8000 + 666,67 = 8666.67

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

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

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

Рассмотрим входные данные для расчета ипотеки
Входные данные для расчета кредита с дифференцированными платежами.
Пусть мы хотим взять ипотеку на 2 млн. рублей
Процентая ставка = 12.5%
Срок 10 лет или 120 месяцев
Дата выдачи — текущее число.
Вводим эти данные на экран расчет кредита, указываем дифференцированный тип платежей.

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

Составим в MS EXCEL график погашения кредита дифференцированными платежами.

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

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

График погашения кредита дифференцированными платежами

Задача . Сумма кредита =150т.р. Срок кредита =2 года, Ставка по кредиту = 12%. Погашение кредита ежемесячное, в конце каждого периода (месяца).

Решение. Сначала вычислим часть (долю) основной суммы кредита, которую заемщик выплачивает за период: =150т.р./2/12, т.е. 6250р. (сумму кредита мы разделили на общее количество периодов выплат =2года*12 (мес. в году)).
Каждый период заемщик выплачивает банку эту часть основного долга плюс начисленные на его остаток проценты. Расчет начисленных процентов на остаток долга приведен в таблице ниже – это и есть график платежей.

Для расчета начисленных процентов может быть использована функция ПРОЦПЛАТ(ставка;период;кпер;пс), где Ставка - процентная ставка за период ; Период – номер периода, для которого требуется найти величину начисленных процентов; Кпер - общее число периодов начислений; ПС – на текущий момент (для кредита ПС - это сумма кредита, для вклада ПС – начальная сумма вклада).

Примечание . Не смотря на то, что названия аргументов совпадают с названиями аргументов – ПРОЦПЛАТ() не входит в группу этих функций (не может быть использована для расчета параметров аннуитета).

Примечание . Английский вариант функции - ISPMT(rate, per, nper, pv)

Функция ПРОЦПЛАТ() предполагает начисление процентов в начале каждого периода (хотя в справке MS EXCEL это не сказано). Но, функцию можно использовать для расчета процентов, начисляемых и в конце периода для это нужно записать ее в виде ПРОЦПЛАТ(ставка;период-1;кпер;пс), т.е. «сдвинуть» вычисления на 1 период раньше (см. файл примера ).
Функция ПРОЦПЛАТ() начисленные проценты за пользование кредитом указывает с противоположным знаком, чтобы отличить денежные потоки (если выдача кредита – положительный денежный поток («в карман» заемщика), то регулярные выплаты – отрицательный поток «из кармана»).

Расчет суммарных процентов, уплаченных с даты выдачи кредита

Выведем формулу для нахождения суммы процентов, начисленных за определенное количество периодов с даты начала действия кредитного договора. Запишем суммы процентов начисленных в первых периодов (начисление и выплата в конце периода):
ПС*ставка
(ПС-ПС/кпер)*ставка
(ПС-2*ПС/кпер)*ставка
(ПС-3*ПС/кпер)*ставка
…
Просуммируем полученные выражения и, используя формулу суммы арифметической прогрессии, получим результат.
=ПС*Ставка* период*(1 - (период-1)/2/кпер)
Где, Ставка – это процентная ставка за период (=годовая ставка / число выплат в году), период – период, до которого требуется найти сумму процентов.
Например, сумма процентов, выплаченных за первые полгода пользования кредитом (см. условия задачи выше) = 150000*(12%/12)*6*(1-(6-1)/2/(2*12))=8062,50р.
За весь срок будет выплачено =ПС*Ставка*(кпер+1)/2=18750р.
Через функцию ПРОЦПЛАТ() формула будет сложнее: =СУММПРОИЗВ(ПРОЦПЛАТ(ставка;СТРОКА(ДВССЫЛ("1:"&кпер))-1;кпер;-ПС))