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

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

Что такое аннуитетный платеж

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

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

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

Зато аннуитетный график платежей выглядит очень просто и понятно.

При аннуитете все взносы уплачиваются через одинаковые интервалы времени и в равном размере.

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

У такого подхода есть свои плюсы:

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

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

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

Далее будет рассматриваться подход «постнумерандо», как более распространенный и привычный.

Аннуитетный платеж может быть двух видов: постнумерандо и пренумерандо

Как рассчитывается аннуитетный платеж

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

K = A / S, где S – взятые в кредит средства, A – размер платежа. То есть каждый месяц нужно платить K * S.

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

Важно! Не стоит слишком увлекаться «низкой» годовой ставкой. Лучше точно подсчитать сумму, чтобы не разочароваться размерами платежа впоследствии.

Настоящая формула аннуитетного платежа, которую кредитные организации предпочитают не упоминать, включает месячную (а вовсе не годовую!) ставку. Выглядит она так:

Здесь i – месячная ставка по кредиту. Для ее расчета значение годовой ставки необходимо поделить на 12 и также поделить на 100 процентов. То есть считается, что в году все месяцы одинаковы. Под n подразумевается срок кредита, который рассчитывается в месяцах.

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

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

Давайте рассмотрим, как рассчитывается аннуитетный платеж для конкретного примера. Допустим, банк предлагает взять ипотечный кредит размером 500 000 под 24% годовых на 10 лет.

Сначала рассчитываем срок кредита в месяцах. Для десятилетнего срока количество месяцев n будет равно 10 * 12 = 120, таким же будет количество разовых взносов.

Месячная ставка составит 24 / 12 = 2%. Для использования в формуле ее нужно дополнительно поделить на 100%, получится 0,02.

Теперь можно вычислить коэффициент аннуитета по формуле выше:

Соответственно, разовый взнос в каждый месяц будет такой (обратите внимание, что значение коэффициента указано с округлением):

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

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

Аннуитетные платежи выгодны для кредитной организации

Как рассчитать аннуитетный платеж в Excel

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

К счастью, для этого вообще не требуется запоминать формулу аннуитетного коэффициента или самостоятельно возводить дробные числа в различные степени. Ведь в Excel уже есть встроенная формула ПЛТ, то есть «платеж» (в английской версии она называется PMT). Можно прямо прописать ее в ячейку, введя «=ПЛТ» и далее списком параметры в скобках. Или же можно собрать ее при помощи конструктора формул, найдя её имя в разделе «Финансовые».

Всего используется 5 параметров, но для использования достаточно прописать только 3 из них. Первый – процент за один период начисления по кредиту. Фактически это i, то есть месячная ставка по кредиту. По ней будет производиться расчет процентов. Если взять приведенный выше пример (500 тысяч на 4 года под 24% «годовых»), то i = 0,02.

Совет: Может возникнуть сложность, если годовой процент не делится красиво на 12. Например, как быть, если кредит выдается под 27% или 33%? К счастью, разработчики Excel предусмотрели такие ситуации. В формуле можно просто написать соответствующую дробь через знак черты «/». То есть потребуется написать «27%/12» или «33%/12». Все вычисления Excel произведет самостоятельно.

Второй параметр – количество периодов за весь срок кредитования. Сюда нужно подставить величину n, которая для примера равна 120.

Наконец, в качестве третьего параметра для формулы необходимо указать сумму кредита. Вписываем сюда 500 000. В итоге получается такая формула расчета ипотеки в Excel:
=ПЛТ(0,02;120;500000)

Если вставить ее в документ, получится в точности то же значения, что и при ручном расчете: 11024,05.

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

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

• желаемый баланс на момент окончания кредитного периода. Поскольку необходимо рассчитаться полностью, это значение должно быть равно 0;
• время уплаты взноса. Если указать 1, то расчет будет произведен из предположения, что платить нужно в начале периода. Если же 0 – то в конце.

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

Расчет в Екселе производится по имеющимся в программе формулам

Как в Excel посчитать погашение основной части и процентов по долгу

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

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

Первая из них – ОСПЛТ (по-английски PPMT). Она отвечает за вычисление доли платежа, которая пойдет в зачет погашения самого кредита. Вот список ее параметров:

• ставка за период: указывается то же значение i, как и в формуле ПЛТ. При ежемесячных взносах годовой процент нужно поделить на 12 (в примере выше получается 0,02);
• порядковый номер платежа. Например, для вычисления за 3-й месяц 2 года нужно указать 15;
• количество платежей. В примере с 10-летней ипотекой это 120;
• размер кредита;
• сумма на момент окончания выплат (необязательный параметр, как и в ПЛТ по умолчанию равен 0).

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

Вторая функция ПРПЛТ (англ. IPMT) вычисляет сумму процентов в составе аннуитетного платежа. Список параметров у нее аналогичен ОСПЛТ. Она также считает результат в отрицательном виде, поэтому можно сумму кредита ввести с минусом.

Примеры

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

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

=ОСПЛТ(0,02;1;120;-500000)
=ПРПЛТ(0,02;1;120;-500000)

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

=ОСПЛТ(0,02;120;120;-500000)
=ПРПЛТ(0,02;120;120;-500000)

Подставим эти формулы в Excel. Для первого платежа в счет процентов идет максимальная сумма, то есть 10000. И только 1024,05 из 11024,05 (менее десятой части от суммы взноса!) пойдет на погашение основной части долга. Для последнего платежа проценты составят всего 216,16. Основная часть будет полностью погашена выплатой в размере 10807,89.

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

Пример онлайн-сервиса для расчета аннуитетного платежа

Расчет аннуитетного платежа с помощью онлайн сервисов

Альтернативный способ предполагает расчет онлайн. В таком случае не требуется Microsoft Office или иной офисный пакет. Достаточно иметь выход в интернет и браузер. Это удобно, поскольку расчеты можно выполнить даже со смартфона, находясь непосредственно в банке.

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

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

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

Мы разобрали несколько способов, как рассчитать аннуитетный платеж по кредиту. Для расчета можно использовать готовую формулу, встроенные функции Excel или кредитные онлайн-калькуляторы. Все варианты дают одинаковые ответы (с точностью до округления), но отличаются по сложности. Самый простой и быстрый метод – онлайн сервисы, самый трудоемкий – ручной расчет.

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