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

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

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

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

Например, кредит на сумму 10000 рублей взят на срок 1 год (12 месяцев) под 20% годовых. При погашении по аннуитету, сумма каждого платежа примерно равна 936 рублей. Часть этого значения составляет тело кредита (основная задолженность), остальная – насчитанные проценты за период выплат.

Для расчета полной суммы платежа в Excel используют формулу ПЛТ. Для расчета части суммы платежа, идущей на оплату основной задолженности – ОСПЛТ. Таким образом, между перечисленными и рассматриваемой функциями существует следующая взаимосвязь: ПЛТ = ОСПЛТ + ПРПЛТ. Главная особенность состоит в том, что соотношение между ОСПЛТ и ПРПЛТ (при ПЛТ = const) меняется от периода к периоду. Поэтому в Excel введена функция ПРПЛТ, учитывающая номер периода платежа, для которого производится расчет процентной части этого платежа.

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

Пример 1. Клиент получил кредит 450000 руб. со ставкой 23% годовых на 4 года. Определить сумму процентов, которую он выплатит за любой период.

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

Пример 1.

С помощью инструмента «ДАННЫЕ»-«Проверка данных» создадим выпадающий список в ячейке B5. В отдельном месте на листе заполним ряд ячеек числами от 1 до 48 (4*12=48 – общее число периодов выплат по кредиту) и используем полученный ряд в качестве источника для списка. Получим следующее:

выпадающий список.

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

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

  • B3/12 – приведенное значение ставки к числу периодов в году;
  • B5 – номер периода из списка;
  • B4*12 – общее число периодов выплат;
  • B2*(-1) – приведенная стоимость финансового продукта.

Результат вычислений:

ПРПЛТ.

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



Расчет суммы годовых процентов по кредиту в Excel

Пример 2. Рассчитать общую сумму процентов, которые заплатит клиент за все периоды выплат по кредиту на 25000 рублей сроком на 12 месяцев, используя функцию ПРПЛТ. Годовая процентная ставка – 17%.

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

Пример 2.

Для расчета используем следующую формулу массива CTRL+SHIFT+Enter:

Функция ПРПЛТ возвращает массив значений сумм, потраченных на оплату процентной части, которые суммируются с помощью функции СУММ. Для определения числа периодов выплат используется функция СЧЁТЗ.

Результат вычислений:

Расчет суммы процентов по кредиту.

То есть, заемщик переплатит около 2360 рублей за год использования финансового продукта.

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

Функция имеет следующий синтаксис:

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

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

  1. ставка – обязательный, принимает числовое значение ставки за конкретный период выплат. То есть, при расчетах необходимо использовать приведенное значение. Если финансовый продукт выдан под 24% годовых с 12-ю периодами выплат, вводят значение 0,24/12.
  2. период – обязательный, принимает числовое значение из диапазона от 1 (первый период) до числа периодов выплат, указанных следующим аргументом.
  3. кпер – обязательный, указывается в виде числа, соответствующего количеству периодов выплат, например, по банковскому кредиту.
  4. пс – обязательный, указывается в виде числа, характеризующего приведенную стоимость финансового продукта (средства, которые банк выдает клиенту в момент оформления займа).
  5. [бс] – необаятельный для заполнения, принимает числовое значение будущей стоимости финансового продукта. Для расчета банковского кредита обычно указывают значение 0, поскольку последний платеж (за последний период выплат) должен погашать всю задолженность перед кредитной организацией. Если явно не указан, принимается значение 0 по умолчанию.
  6. [тип] – необязательный, может иметь только два значения: 0 (выплаты в конце периода) и 1 (выплаты в начале периода). Если не указан, по умолчанию принимается значение 0.

Примечания:

  1. Если один или несколько аргументов функции ПРПЛТ указаны в виде данных недопустимого типа (например, не преобразуемой к числовому значению текстовой строкой), результатом расчетов будет код ошибки #ЗНАЧ!
  2. Для обозначения выплачиваемых средств (например, при использовании формулы для расчетов банковских депозитов) необходимо указывать отрицательные значения сумм. Получаемые средства указывают в виде положительных чисел.

en ru