Формула расчета кредита дифференцированными платежами в 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 и более месяцев. В общем, теперь всё в ваших руках, друзья. Как говорится, мы вам дали удочку, а вы сами решайте, что с ней дальше делать.

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

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

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

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

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

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

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

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

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

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

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

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

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

  1. Заполним входные данные для расчета ежемесячных платежей по кредиту. Это сумма займа, проценты и срок.
  2. Составим график погашения кредита. Пока пустой.
  3. В первую ячейку столбца «Платежи по кредиту» вводиться формула расчета кредита аннуитетными платежами в Excel: =ПЛТ($B$3/12; $B$4; $B$2). Чтобы закрепить ячейки, используем абсолютные ссылки. Можно вводить в формулу непосредственно числа, а не ссылки на ячейки с данными. Тогда она примет следующий вид: =ПЛТ(18%/12; 36; 100000).

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

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

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

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

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

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

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

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

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

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

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

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

Выплата основного долга: сумму всего кредита разделить на срок: =ЕСЛИ(D9 Итоговый платеж: сумма «процентов» и «основного долга» в текущем периоде: =F8+G8.

Читайте также:  Автокредит россельхозбанк процентные ставки в 2018 году

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

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

Красная цифра – аннуитет (брали 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 дней.

Далее находим ЧБП: 365 / 28 = 13.

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

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

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

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

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

Аннуитетный платеж

Формула расчета.

Аннуитетные платежи — выплаты по кредиту или займу, когда все платежи (выплата основного долга плюс выплата процентов) равны между собой. Данный метод начисления начал набирать свою популярность еще до кризиса 2008 года и на сегодняшний момент практически все банки предлагают рассчитывать графики оплат по потребительским и ипотечным кредитам. Расчет ежемесячного платежа осуществляется по математической формуле:

  • АП = ОСЗ х ПС / [1 — (1 + ПС) -ПП ] , где
  • АП — размер ежемесячного аннуитетного платежа
  • ОСЗ — остаток ссудной задолженности
  • ПС — месячная процентная ставка по кредитному договору (равна 1/12 годовой процентной ставки)
  • ПП — количество периодов, оставшихся до погашения кредита.

Рассчитать любой из видов платежей вы можете с помощью калькуляторов.

Пример расчета.

сумма кредита — 10 000 руб.

процентная ставка по кредиту — 15% годовых

срок получения кредита — 12 мес.

в таком случае, подставляя наши данные в формулу получим ее следующий вид:

АП = 10 000 х 1,25 / [1 — (1 + 1,25) -12 ] = 902,58

График с расшифровкой оплаченных процентов и суммы основного долга представлены в следующей таблице:

период остаток ссудной задолженности сумма выплаченных процентов сумма выплаты основного долга итоговая выплата по кредиту
1 10 000,00 125,00 777,58 902,58
2 9 222,42 115,28 787,30 902,58
3 8 435,11 105,44 797,14 902,58
4 7 637,97 95,47 807,11 902,58
5 6 830,86 85,39 817,20 902,58
6 6 013,66 75,17 827,41 902,58
7 5 186,25 64,83 837,75 902,58
8 4 348,50 54,36 848,23 902,58
9 3 500,27 43,75 858,83 902,58
10 2 641,44 33,02 869,57 902,58
11 1 771,87 22,15 880,43 902,58
12 891,44 11,14 891,44 902,58
Итого 831,00 10 000,00 10 831,00
Читайте также:  Газпромбанк красноярск официальный сайт кредит наличными

Для расчета аннуитетных платежей очень удобно применять электронные таблицы Excel. Здесь можно скачать форму Excel для построения графика аннуитетных платежей.

Плюсы и минусы.

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

Дифференцированный платеж

Формула расчета.

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

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

  • ДП = ОСЗ / ПП + ОСЗ х ПС , где
  • ДП — размер дифференцированного платежа
  • ОСЗ — остаток ссудной задолженности
  • ПП — количество периодов, оставшихся до погашения кредита
  • ПС — месячная процентная ставка по кредитному договору, равная 1/12 годовой процентной ставки

Рассчитать любой из видов платежей вы можете с помощью калькуляторов.

Пример расчета.

Для примера рассмотрим предыдущий заем:

сумма кредита — 10 000 руб.

процентная ставка по кредиту — 15% годовых

срок получения кредита — 12 мес.

В таком случае, подставляя ежемесячно данные по кредиту получим следующий график гашения кредита:

период остаток ссудной задолженности сумма выплаченных процентов сумма выплаты основного долга итоговая выплата по кредиту
1 10 000,00 125,00 833,33 958,33
2 9 166,67 114,58 833,33 947,92
3 8 333,33 104,17 833,33 937,50
4 7 500,00 93,75 833,33 927,08
5 6 666,67 83,33 833,33 916,67
6 5 833,33 72,92 833,33 906,25
7 5 000,00 62,50 833,33 895,83
8 4 166,67 52,08 833,33 885,42
9 3 333,33 41,67 833,33 875,00
10 2 500,00 31,25 833,33 864,58
11 1 666,67 20,83 833,33 854,17
12 833,33 10,42 833,33 843,75
Итого 812,50 10 000,00 10 812,50

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

Здесь можно скачать готовую форму расчета.

Плюсы и минусы.

Основным плюсом "классического" метода начисления является меньшая сумма переплаты. Если вы посмотрите на приведенные примеры, то увидите, что суммы процентов, выплаченных по кредиту с дифференцированными платежами составляет 812,50 руб., а с аннуитетными — 831,00 руб. Причем чем больше сумма кредита и срок займа, тем больше разница между этим показателями. Вторым плюсом этого метода является то, что к окончанию срока гашения, оплата процентов по нему будет практически минимальной. Третьим плюсом является то, что вы практически в любой момент можете произвести предварительную оплату по кредиту и банк в любой день примет его, тогда как при аннуитетном методе начисления можно осуществить досрочный платеж только в дату погашения очередного платежа.

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

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

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Adblock detector