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

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

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

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

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

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


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

1. Рассчитаем сумму ежемесячного платежа.

Чтобы понять, сколько клиенту придется платить за кредит каждый месяц при аннуитетной схеме, можно воспользоваться специальным оператором «ПЛТ», который имеет следующий синтаксис: =ПЛТ(ставка;кпер;пс;бс;тип).

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

В аргумент «ставка» необходимо занести непосредственно саму процентную ставку за кредит. В случае годовой ставки этот аргумент необходимо разделить на 12, если квартальная ставка – на 4.

В аргумент «кпер» заносится количество периодов выплат. Если кредит погашается ежемесячно в течение года, в аргумент необходимо занести «12», если в течение двух лет – «24». Квартальные выплаты в течение тех же двух лет – «8».

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

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

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

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

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


В диалоговом окне мастера функций выбираем категорию – «Финансовые» и непосредственно оператор «ПЛТ». Приступим к заполнению аргументов.


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

Срок кредитования «кпер» равен 24 месяцам, а первоначальная сумма «пс» - 500 000. Остальные аргументы можно пропустить. Аргументы можно как вводить вручную, так и с помощью указателя в таблице. Применяем операцию.


Сумма ежемесячного платежа составит 23 536,74.


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

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


По итогу получаем сумму в 565 881,67. Чтобы узнать сумму переплат, необходимо вычесть из общей суммы выплат тело кредита. В нашем случае, сумма переплаты составит 64 881,67.


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

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

Рейтинг:(голосов:1)