Пример функции ПЛТ для расчета аннуитетного платежа в Excel

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

Примеры использования функции ПЛТ в Excel

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

Расчет ежемесячного платежа по кредиту в Excel

Пример 1. Определить размер ежемесячного платежа по кредиту с процентной ставкой 23% на сумму 25000 рублей, который должен быть выплачен на протяжении 3 лет.

Вид таблицы данных:

Для получения искомого значения введем следующую формулу:

Описание аргументов:

• B3/B5 – процентная ставка, приведенная к числу периодов выплат в году;
• B4*B5 – число периодов выплат на протяжении действия кредитного договора;
• B2 – начальная стоимость кредита (тело кредита).

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

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



Пример расчета суммы переплаты по кредиту в Excel

Пример 2. Определить сумму, которую переплатит заемщик, взявший кредит на сумму 50000 с годовой процентной ставкой 27% и 12 периодами выплат в год. Срок кредитования составляет 5 лет.

Вид таблицы данных:

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

=ABS(ПЛТ(B3/B5;B4*B5;B2)*B4*B5)-B2

Произведение результата, возвращаемого функцией ПЛТ и количества периодов выплат (B4*B5) соответствует общей сумме выплат за 5 лет. Поскольку функция ПЛТ возвращает отрицательное значение, используем функцию ABS для получения абсолютного значения. В результате вычислений получим:

Клиент банка выплатит 50000 рублей тела кредита и еще около 42000 рублей процентов.

Формула вычисления оптимального ежемесячного платежа по кредиту в Excel

Пример 3. В банке был открыт депозитный счет с начальной суммой 200 000 рублей. Условия договора позволяют выполнять ежемесячное пополнение данного счета. Определить, какую сумму необходимо вносить ежемесячно, чтобы спустя 4 года получить 2000000 рублей. Процентная ставка составляет 11% годовых.

Вид таблицы данных:

Искомое значение может быть определено с помощью следующей формулы:

=ПЛТ(B3/B5;B6*B5;-B2;B4)

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

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

Для накопления 2 млн. рублей клиенту банка потребуется ежемесячно вносить на депозитный счет примерно 28000 рублей.

Особенности использования функции ПЛТ в Excel

Функция имеет следующую синтаксическую запись:

=ПЛТ(ставка; кпер; пс; [бс]; [тип])

Описание аргументов:

• ставка – обязательный аргумент, характеризующий числовое значение годовой ставки по задолженности. Может быть указан числовым значением в виде десятичной дроби (например, 0,2 – соответствует 20% годовой ставки) или числом в процентном формате;
• кпер – обязательный аргумент, принимающий числовое значение, характеризующее число периодов выплат по задолженности. Может принимать дробные числа, не усекая дробную часть и равномерно распределяя фиксированную сумму платежей между указанным числом периодов;
• пс – обязательный аргумент, принимающий числовое значение, которое характеризует начальную стоимость финансового продукта. В случае с выдачей кредита, это значение эквивалентно телу кредита, то есть сумме средств, которую получает заемщик в кредитной организации;
• [бс] – необязательный для заполнения аргумент, принимающий числовое значение, которое характеризует оставшуюся сумму задолженности. Например, если этот аргумент явно указан, можно определить, какой должна быть сумма ежемесячного взноса, чтобы при текущей процентной ставке остаток задолженности составил указанную сумму спустя определенное число периодов выплат. Если аргумент явно не указан, он принимается равным 0 (нулю), то есть задолженность будет полностью списана.
• [тип] – необязательный для заполнения аргумент, принимающий одно из двух возможных числовых значений:

1. 0 – выплаты производятся в конце периода (если явно не указан, используется по умолчанию).
2. 1 – выплаты в начале периода.

Примечания:

1. При расчете суммы ежемесячных выплат учитывается только значение годовой процентной ставки. В договорах некоторых финансовых организациях могут быть указаны дополнительные сборы и комиссии, влияющие на итоговый результат.
2. При указании процентной ставки необходимо выполнять перерасчет в зависимости от количества периодов выплат в году. Платежи могут быть, например, ежемесячными или ежеквартальными. В первом случае аргумент ставка должен быть указан как n%/12, а во втором – n%/4, где n% - годовая процентная ставка.
3. Аргумент кпер должен быть указан числом, которое получено в результате произведения количества лет, на которые выдан кредит, и количества периодов выплат в году. Например, если ипотечный кредит был выдан на 20 лет, а платежи необходимо делать ежемесячно, аргумент кпер должен быть указан как 20*12 или 240.
4. Для расчета общей суммы платежей по кредиту можно умножить возвращаемый рассматриваемой функцией результат на количество периодов выплат.