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

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

Шаг второй. Укажите единоразовые и ежемесячные комиссии. Вообще, о скрытых комиссиях в 2018-2019 году уже говорить не приходиться. Банки отказываются это мелкого текста в договоре, а напрямую обязывают Вас использовать страхование жизни или вводят комиссию “за обслуживание счёта ”. Страхование жизни мы относим к единоразовым, а обслуживание счёта к ежемесячным комиссиям.

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

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

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

Самостоятельный расчёт кредита

Чтобы самостоятельно провести расчёт дифференцированного платежа по кредиту, необходимо понимать из чего он состоит. Есть две основные части: первая - кредитное тело, вторая - проценты, начисляемые на остаток каждый месяц. Как уже говорилось, сумма “тела кредита” делится на равные части. Размер долей же зависит от продолжительности кредитования.

Допустим, кредитный срок составляет 12 месяцев, а сумма займа - 240 тысяч рублей. Несложно посчитать, что раз в месяц клиенту нужно будет отдавать в банк по 20 тысяч, причём это только тело (сумма без процентной надбавки). Указанная посчитанная сумма не меняется на протяжении всего выплатного периода. Однако, возможны исключения:

• Заёмщик пишет заявление о необходимости продления кредита;
• Происходит частичное досрочное погашение задолженности.

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

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

ДП = ОЗ / КП+ ОЗ х МС , где

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

ОЗ - Остаток задолженности по кредиту;

КП - Количество оставшихся месяцев для погашения кредита;

МС - Месячная ставка по кредиту. Рассчитывается простым делением Вашей кредитной ставки на 12.

Не всё так просто, как кажется? Говоря простым языком, мы уже отметили, что наиболее удобный вариант - использовать калькулятор дифференцированных платежей по кредиту. Тем не менее, провести необходимые подсчёты можно и вручную с помощью простого калькулятора или таблицы Excel. Для этого достаточно выполнить простую манипуляцию: берём общую сумму задолженности и делим на количество месяцев, в течение которых она должна быть погашена. Таким образом, получается тело займа, то есть основной платёж.

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

Преимущества дифференцированного кредита

Взять кредит с подобной схемой выплат, заёмщиков в основном побуждает постепенная минимизация финансовой нагрузки. Момент важный особенно для тех, кому необходим долгосрочный займ. Кроме того, итоговый размер переплаты окажется значительно меньше , нежели при любых других выплатных схемах. Главное убедиться, что сотрудники банка изначально всё правильно и прозрачно рассчитали. Для проверки можно использовать сторонний кредитный калькулятор. Некоторые дифференцируют сумму и начисляемую надбавку по специальным формулам (процесс трудоёмкий
и требующий полной концентрации внимания).

Главное преимущество дифференцированного кредита - это погашение в большей степени основной суммы долга или “тела кредита” и в меньшей степени погашение процентов. За счёт начисления процентов именно на оставшуюся сумму долга, Вы получите постоянно уменьшающийся ежемесячный платёж.

Недостатки дифференцированного кредита

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

Пример №1. Вы взяли ипотеку на 20 лет в размере 4 000 000 рублей. Вам, безусловно, повезло и процент по кредиту составил 9,5% годовых. Первый платёж по кредиту составит 48,333 рублей, а последний 16,798 рублей. А переплата по кредиту составит 3 815 000 рублей. А при аннуитетном платеже 4 948 000 рублей.

Пример №2. Вы взяли потребительский кредит 400 000 рублей на 5 лет под 18% годовых. Процент не маленький, но и не самый большой. По статистике кредитных брокеров, процент по потребительскому кредиту на конец 2018 и начало 2019 года составляет от 15 до 30% годовых. При дифференцированном кредите переплата составит 183 000 рублей, а при аннуитетном 209 442 рублей.

Краткий вывод . Дифференцированный кредит всегда был выгоден клиенту с высоким показателем дохода, поэтому многие банки отказались от дифференцированного типа кредита. К примеру, в банке ВТБ этой возможности нет с 2014 года, а в Росбанке с 2016. Аннуитетный платёж стал символом кредитования, когда доход банка значительно больше, а клиент получает меньший и одинаковый ежемесячный платёж. Это снижает финансовую нагрузку на заёмщика и делает кредиты наиболее популярными.

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

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

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

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

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

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

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

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

Кстати, пользуясь кредитным калькулятором, следует иметь в виду, что при его использовании не учитываются возможные дополнительные платежи связанные с кредитом. Это могут быть страховка, комиссии за ведение ссудного счета (что уже незаконно), комиссии за рассмотрение и выдачу, РКО и пр.), которые могут различаться в каждом конкретном случае.

Я лично при выборе кредита стараюсь выбирать следующие условия (возможность) его погашения:

1. График с аннуитетными платежами. Ежемесячная сумма платежа меньше, чем при дифференцированном варианте, не так давит на семейный бюджет. Срок по кредиту увеличивается, но это не беда, если (см. п.2)…
2. Возможность досрочного гашения кредита. Если появились «лишние» деньги возможно пустить их на погашение. Тут же пересчитываются в меньшую сторону аннуитетные платежи, строится новый график со старым сроком окончательного погашения.

Мне кажется, что это самая оптимальная долговая нагрузка.

При составлении графика погашения долга по кредиту может использоваться один из двух способов расчета ежемесячного (ежеквартального) платежа. Первый – аннуитетный метод, когда основной долг вместе с начисленными процентами разбивается на одинаковые суммы в течение всего периода. Второй – дифференцированный, который считается более выгодным, но менее удобным для заемщика . Что представляет собой дифференцированный платеж? Как рассчитывается и какие имеет преимущества и недостатки?

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

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

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

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

Недостатками такого способа погашения выступают:

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

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

• расчет погашения основного долга;
• расчет процентов.

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

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

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

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