Как рассчитать дифференцированный платеж по кредиту в excel

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

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.

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

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

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

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

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

Что такое дифференцированный платеж и как он расчитывается

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

График дифференциального платежа на фото

Дифференцированный платеж – что это значит?

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

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

Внимание: При дифференцированном типе погашения долга возможно досрочное закрытие кредита.

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

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

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

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

• часть основного долга;
• начисленные проценты;
• дополнительные комиссии и сборы организации.

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

При этом платежи в середине срока кредитования сопоставимы с аннуитетом.

Сумма транша изменяется от выплаты к выплате в связи с тем, что основная сумма задолженности по кредиту уменьшается с каждым платежом, а вместе с ней – и сумма процентов, рассчитываемая от «тела» кредита.

Что выгоднее заемщику?

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

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

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

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

Использование при расчете платежей по кредиту

Любой дифференцированный платеж включает в себя две основные составляющие:

• «тело» (основная сумма) кредита;
• проценты, ежемесячно начисляемые на фактический остаток долга.

Сумма делится на равные транши, количество которых зависит от периода кредитования. К примеру, оформив кредит на сумму в 240 тысяч рублей сроком на 12 месяцев, заемщик должен быть к тому, что сумма ежемесячных взносов, направленных на погашение «тела» кредита, составит около 20 тысяч рублей.

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

• обращение заемщика к кредитору с просьбой продлить срок кредитования;
• частичное досрочное погашение задолженности.

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

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

При повторном частичном погашении долга ситуация также изменится в пользу заемщика.

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

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

Формула

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

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

Формула для расчета основного платежа выглядит следующим образом:

b – сумма основного платежа;

S – общая сумма кредита;

N – количество месяцев, отведенных на погашение долга.

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

Формула для подсчета процентов будет выглядеть так:

p – сумма начисленных процентов;

Sn – фактический остаток задолженности на текущий период;

P – оговоренная сторонами годовая процентная ставка по кредиту.

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

Sn – фактический остаток задолженности на определенный период;

S – общая сумма задолженности перед банком;

b – размер основного платежа;

n – количество прошедших периодов.

Расчет в Excel: пример

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

• сумма кредита: 100 тысяч рублей;
• годовая процентная ставка: 10 процентов;
• предполагаемый срок погашения кредита: 6 месяцев.

Таким образом, сумма основного ежемесячного платежа составит: 100 тысяч рублей/ 6 месяцев = 16,666 тысяч рублей и 67 копеек.

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

• первый месяц: 16 666,67 рублей + (100 000 рублей – (16 666,67 рублей * 0 месяцев)) * 0,1 / 12 месяцев = 17 500 рублей;
• второй месяц: 16 666,67 рублей + (100 000 рублей – (16 666,67 рублей * 1 месяцев)) * 0,1 / 12 месяцев = 17 361,11 рублей;
• третий месяц: 16 666,67 рублей + (100 000 рублей – (16 666,67 рублей * 2 месяцев)) * 0,1 / 12 месяцев = 17 222,22 рублей;
• четвёртый месяц: 16 666,67 рублей + (100 000 рублей – (16 666,67 рублей * 3 месяцев)) * 0,1 / 12 месяцев = 17 083,33 рублей;
• пятый месяц: 16 666,67 рублей + (100 000 рублей – (16 666,67 рублей * 4 месяцев)) * 0,1 / 12 месяцев = 16 944,44 рублей;
• шестой месяц: 16 666,67 рублей + (100 000 рублей – (16 666,67 рублей * 5 месяцев)) * 0,1/12 месяцев = 16 805,56 рублей.

Таким образом, в течение всего срока кредитования заемщик выплатит кредитору 102 916,67 рублей, переплатив по кредиту всего лишь 2 619,67 рублей.

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

Ознакомиться с другим примером графика дифференцированных платежей можно, скачав документ по ссылке: https://yadi.sk/i/MZm3Lnc03H8nVV.

Ипотека с дифференцированными платежами: список банков

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

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

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

«Сбербанк»

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

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

Видео

Смотрите на видео ролике советы по выбору между аннуитетным или дифференциальным платежам:

Дифференцированный платеж по кредиту в разрезе большого промежутка времени намного выгоднее аннуитетного.