Формула и расчет аннуитетного платежа по кредиту

Как посчитать в Excel

При возникновении каких-либо затруднений с самостоятельным проведением расчетов Ап они также вычисляются в Excel. Здесь предусмотрена отдельная функция – ПЛТ. Создается новая таблица, и затем в любой ячейке вводится строка.

Подставьте те же исходные числа, как на указанном ранее примере. Затем выводится такое выражение: ПЛТ = 18 %/12, 36 – 30 000 руб.

В формуле обозначаются все исходные данные по очереди: процентная ставка, число месяцев, сумма взятого кредита. Также записывается как вариант и так: ПЛТ (0,015; 36 мес.; -30 000 руб.).

0,015 высчитывается так: (18 % /12 мес. /100). Так или иначе выйдет сумма платежей, которые вносятся заемщиком в месяц – 1084 руб. 57 коп.

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

Прежде всего, надо понимать, что именно является вашим долгом по кредиту, и какие выплаты способствуют его уменьшению. В нашем примере вы берёте в кредит 50 000 рублей – это и есть ваш долг. Переплаченные по кредиту проценты (6157 рублей) вашим долгом не являются, это всего лишь вознаграждение банку за предоставленный кредит. Таким образом, можно сделать вывод:

Погашение процентов по кредиту никак не способствует уменьшению вашего долга перед банком.

В кризисные времена банки часто «идут навстречу» своим должникам. Они говорят как-то так: «Мы понимаем, у вас сейчас проблемы! Окей, наш банк готов пойти вам на уступки – можете нам просто погашать проценты, а само тело кредита погашать не надо. Все же люди братья и должны друг другу помогать! Бла-бла-бла…»

На первый взгляд такое предложение может показаться выгодным, а сам банк – «белым и пушистым лапулей». Ага, как бы ни так! Если взять в руки калькулятор и провести простые арифметические расчёты, то сразу становится ясно, что реальное предложение банка выглядит приблизительно так:

«Ребята, вы попали на деньги! Ничего не поделаешь, это жизнь! Предлагаем вам на время (а может и навсегда) стать нашим рабом – будете ежемесячно выплачивать проценты по кредиту, а сам долг погашать не надо (ну, чтобы сумма выплат по процентам не уменьшалась). Ничего личного – это просто бизнес, друзья!»

Теперь запомните главную мысль:

Именно погашение тела кредита вытаскивает вас из долговой ямы. Не процентов, а именно тела кредита.

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

Sn2 – долг на конец месяца по аннуитетному кредиту; Sn1 – сумма текущей задолженности по кредиту; S – сумма в аннуитетном платеже, которая идёт на погашение тела кредита.

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

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

Итак, при первом платеже текущая задолженность по кредиту у нас равна всей сумме займа (50 000 руб.). Чтобы посчитать долг на конец месяца, мы отнимаем от этой суммы не весь ежемесячный платёж (4680 руб.), а только ту часть, которая ушла на погашение тела кредита (3763 руб.). В результате наш долг на конец месяца составит 46 237 руб., именно на эту сумму будут начисляться проценты в следующем месяце. Естественно, они будут меньше, так как сумма долга уменьшилась

Теперь вы понимаете, почему важно погашать именно тело кредита?

Итак, друзья, мы с вами разобрались с формулами и расчетами аннуитетных платежей. Надеемся, теперь у вас нет вопросов по этой теме, и вы запросто сможете произвести все необходимые расчеты, а также составить график аннуитетных платежей по кредиту. Единственное, что бы вам, наверное, хотелось, это как-то автоматизировать процесс расчетов. Вы не поверите, но это возможно! Хотите узнать как? Тогда переходим к публикации: Расчет аннуитетных платежей по кредиту в Excel.

⇧

Досрочное погашение аннуитетного кредита

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

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

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

Это интересно: Расчет годовых процентов по кредиту — формула

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

Сформулируем основные принципы формирования аннуитета:

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

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

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

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

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

где:

• ЕПа – аннуитетный ежемесячный платеж;
• СК – сумма кредита;
• ПС – годовая процентная ставка;
• КП – кол-во периодов или месяцев.

Пример. Александр взял кредитный продукт на сумму 1000 руб. на год. Ставка составляет 20% годовых.

Платеж	Долг (руб.)	Начисленные %	Основная задолженность (руб.)	Сумма платежа (руб.)
№1	1000	16,67	75,97	92,63
№2	924,03	15,40	77,23	92,63
№3	846,80	14,11	78,52	92,63
№4	768,28	12,80	79,83	92,63
№5	688,45	11,47	81,16	92,63
№6	607,29	10,12	82,51	92,63
№7	524,77	8,75	83,89	92,63
№8	440,89	7,35	85,29	92,63
№9	355,60	5,93	86,71	92,63
№10	268,89	4,48	88,15	92,63
№11	180,74	3,01	89,62	92,63
№12	91,12	1,52	91,12	92,63
Итого	111,61	1000	1111,61

Дифференцированный рассчитывается следующим образом:

где:

• НП – начисленные % в периоде;
• ОК – остаток долга по кредиту в месяце;
• ПС – годовая процентная ставка.

Условия остаются теми же.

Платеж	Долг (руб.)	Начисленные %	Основная задолженность (руб.)	Сумма платежа (руб.)
№1	1000	16,67	83,33	100
№2	916,67	15,28	83,33	98,61
№3	833,34	13,89	83,33	97,22
№4	750,01	12,50	83,33	95,83
№5	666,68	11,11	83,33	94,44
№6	583,35	9,72	83,33	93,05
№7	500,02	8,33	83,33	91,66
№8	416,69	6,94	83,33	90,27
№9	33,36	5,56	83,33	88,89
№10	250,03	4,17	83,33	87,50
№11	166,03	2,78	83,33	86,11
№12	83,37	1,39	83,33	84,76
Итого	108,34	1000	1108,34

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

Таблица 1. Сравнение аннуитетного дифференцированного способа погашения кредита

Схемы погашения
Параметры	Аннуитетная	Дифференцированная
Размер ежемесячного платежа на протяжении срока кредитования	сохраняется	уменьшается
Начисление процентов	на всю сумму кредита	на остаток долга
Первые перечисления	меньше	больше
Уменьшение основного долга	постепенно	быстро

Способы расчета суммы кредита с помощью Excel-таблицы

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

Для расчета платежей по кредиту используется специальная формула

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

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

Размер регулярного взноса принято рассчитывать по формуле:

Е = К * S, где

Е — месячный платеж;

К — коэффициент аннуитетного платежа;

S — первоначальная сумма задолженности.

Для расчета коэффициента можно применять такую зависимость:

К = (j * (1 + j)^m) / ((1+j)^m-1), где

j — ежемесячная ставка процентов, которая высчитывается при делении годовой на 12 (кол-во месяцев в году);

m — период кредитования в месяцах.

В таблице расчетов процентов по кредиту для эксель можно применять стандартную формулу аннуитета. Для этого используется аббревиатура ПТЛ:

• вносим входные параметры для расчета месячных взносов по кредиту;
• формируем график погашения с колонками «Номер месяца» и «Платеж»;
• для первой ячейки «Платеж» прописываем формулу =ПЛТ($B$3/12; $B$4; $B$2);
• можно заменить ссылки константными данными, тогда пример будет выглядеть таким образом =ПЛТ(12%/12; 24; 1000000).

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

Дифференцированная схема

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

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

МП = ОСЗ / (ПП + ОСЗ * МС), где

МП — месячный кредитный платеж;

ОСЗ — сумма остатка тела кредита;

ПП — количество периодов до полного погашения;

МС — ежемесячная ставка процентов, которая вычисляется делением годовой на 12 месяцев.

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

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

Дифференцированный кредит03.

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

Вернёмся к вопросу дифференцированных платежей. Почему их размер со временем уменьшается? Всё просто: сумму дифференцируют так, чтобы изначально заёмщик выплатил «тело» кредита – основной займ, при этом погашение «тела» осуществляется равными частями. Что касается начисления процентов, то их максимальное количество сосредотачивается как раз на первых взносах, так как насчитываются они на полную сумму кредитования. Далее “тело” займа уменьшается, а вместе с ним уменьшается и процентная надбавка. Для сравнения, в случае, когда взят аннуитетный кредит, размер взносов остаётся фиксированным на всём выплатном периоде.

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

Примеры дифференцированного кредита04.

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

Потребительский кредит:

Сумма кредита 550 000 рублей

• Процентная ставка по кредиту 15,5%
• Период кредитования 36 месяцев или 3 года

Без комиссий и дополнительных платежей

Ипотечное кредитование:

Сумма кредита 2 500 000 рублей

• Процентная ставка по кредиту 15,5%
• Период кредитования 120 месяцев или 10 лет

Без комиссий и дополнительных платежей

Потребительский кредит. Сумма переплаты составит 131 427 рублей. Первый ежемесячный платёж составит 22 381 рублей, второй 22 184 рублей, десятый 20 605 рублей, двадцатый 18 632 рубля. Если бы это был аннуитетный платёж, мы бы равномерно платили каждый месяц 19 200 рублей, но при этом переплата была бы 141 231 рубль. То есть, разница всего ~10 000 рублей? Да, но перейдем ко второму примеру.

Ипотека. Безусловно, ставка по ипотеке 15,5 очень высокая, но не максимальная. Нам знакомы случаи ипотечного кредитования до 18,5% в 2020 году. Это значение не должно Вас пугать, оно взято лишь для примера. Сумма переплаты составит 1 953 645 рублей, первый платёж 53 125 рублей, тридцатый платёж 45 325 рублей, а шестидесятый (спустя 5 лет) 37 248 рублей. Если бы это был аннуитетный платёж, то переплата составила 2 432 316 рублей, что примерно на 500 000 рублей больше, чем при дифференцированном кредите. При этом Вы бы спокойно платили 41 102 рубля каждый месяц.

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

Примеры расчета ежемесячного платежа по кредиту

При расчете суммы платежа любым из способов важно понимать, что полученный результат не может быть на 100% достоверным. Ведь мы не можем заранее определить размер возможных комиссии за выдачу кредита, за обслуживание счета или карты и т.п

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

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

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

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

Пример расчета, где ЕП будет ежемесячным платежом:

• Сумма – 10 000 рублей;
• Срок – 5 месяцев;
• Процентная ставка – 20%.

В первую очередь разделим сумму займа на срок в месяцах:
10 000 / 5 = 2 000

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

ЕП% = ОД*ГС*КД/ДГ

где:

• ЕП% — ЕП начисленных процентов
• ОД – остаток основного долга
• ГС – годовая процентная ставка
• КД – количество дней в месяце (возьмем 30 дней)
• ДГ – количество дней в году (возьмем 365 дней)

Итак:

• 1-й месяц = 10 000*20%*30/365= 164,38
• 2-й = 8 000*20%*30/365=131,50
• 3-й = 6 000*20%*30/365= 98, 63
• 4-й= 4 000*20%*30/365=65,75
• 5-й = 2 000*20%*30/365=32,87

Как видим каждый месяц тело кредита уменьшается на 2 000.
Исходя из расчетов ЕП будет составлять 2000+ЕП%, а именно:

• 2164,38
• 2131,50
• 2098,63
• 2065,75
• 2032,87

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

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

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

• Сумма кредита – 100 000;
• Срок кредита – 12 месяцев;
• Ставка – 20 %.

Определим процентную ставку в месяц: 20/12/100 = 0,016

Затем нужно высчитать коэффициент аннуитета. С его помощью определяется размер выплат: (0,016*(1+0,016)^36)/((1+0,016)^36-1)=0,037

ЕП рассчитывается путем умножения полученного коэффициента и суммы заёма: 100 000*0,037=3700.

Таким образом каждый месяц придется платить 3 700 на протяжении года.

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

Быстрая форма заявки
Заполни заявку сейчас и получи деньги через 30 минут

• Оплата налогов через Сбербанк Онлайн: пошаговая инструкция
• Золотая карта Мир от Сбербанка: условия оформления и использования
• Как подключить Сбербанк Онлайн: 5 способов
• Как открыть расчетный счет для ИП в Сбербанке?
• Как аннулировать карту Сбербанка, если она больше не нужна
• Порядок отключения услуги «Автоплатеж» Сбербанк
• Цифровая карта Сбербанка: что это?
• Как проверить долги по кредитам?

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

Каждый способ оплаты имеет свои преимущества и недостатки. Если рассматривать оплату займа одинаковыми суммами, то плюсы в этом следующие:

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

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

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

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

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

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

Формула расчета аннуитетного графика

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

П=К*С,

где П – ежемесячная плата;

К – коэффициент аннуитета;

С – сумма займа.

Чтобы сосчитать величину месячного платежа, нужно еще вывести коэффициент, а формула его расчета несколько сложнее:

К=%*(1+%)мес/(1+%)мес-1

где % — процентная ставка по займу, разделена на 12 месяцев;

мес. – срок действия договора в месяцах;

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

Итак, имеем основные параметры соглашения:

• процентную ставку – 15% годовых или 15/12= 1,25% месячных;
• сумму ссуды – 1 000 000 рублей;
• срок действия договора – 5 лет, или 60 месяцев

Посмотрим, как рассчитать аннуитетный платеж по кредиту. Сначала считаем коэффициент: 1,25%*(1+1,25%)60/(1+1,25%)60-1=0,02379, затем уже ежемесячную уплату по кредиту: 0,02379*1 000 000 = 23 789,93 рублей. Эту сумму будет ежемесячно вносить заемщик в банк для оплаты по ссуде.

Далее путем нехитрых математический расчетов получаем, что за 5 лет пользования займом клиент выплатит банку: 60*23 789,93 = 1 427 395, 81 рублей. Переплата составляет 427 395, 81 рублей

В случае применения классической схемы погашения при этих же параметрах кредитного соглашения величина переплаты составила бы 387 097,15 рублей, что на 40 тысяч рублей меньше. Это пример отрицательной стороны погашения ссуды равными частями.

Чтобы показать положительные черты этого способа выплаты займа, необходимо рассмотреть размеры ежемесячного платежа по кредиту. При аннуитете он составляет 23 789,93 рублей. Если же рассчитывать стандартный график, тогда максимальная плата в первый месяц составит приблизительно 29 600 рублей и лишь только через 27 месяцев пользования займом размер ежемесячного взноса по классической схеме сравняется с аннуитетным платежом.

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

Чем хорош и плох аннуитет?

Хотя аннуитетный платёж выгоден банку, для клиента тут тоже есть свои положительные стороны.

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

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

Это очень серьёзный минус.

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

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

Аннуитетный платёж – это про “медленно и почти безболезненно”.