Как рассчитать кредит в Excel: функции ПЛТ, ОСПЛТ, ПРПЛТ с примерами

Как рассчитать кредит в Excel: функции ПЛТ, ОСПЛТ, ПРПЛТ с примерами

Формулы расчета кредита в Excel – это востребованный кредитный калькулятор в финансовом Excel. Они позволяют быстро моделировать различные сценарии погашения долга и принимать обоснованные решения. Рассмотрим три ключевые функции этой группы:

Функция ПЛТ в Excel

Функция ПЛТ(ставка; кпер; пс; [бс]; [тип]) - финансовая функция, которая рассчитывает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянной процентной ставки.

Аргументы функции ПЛТ:

• Ставка - Обязательный аргумент. Процентная ставка по ссуде.
• Кпер - Обязательный. Общее число выплат по ссуде.
• Пс - Обязательный. Приведенная к текущему моменту стоимость или общая сумма, которая на текущий момент равноценна ряду будущих платежей, называемая также основной суммой.
• Бс - Необязательный аргумент. Значение будущей стоимости, то есть желаемого остатка средств после последней выплаты. Если аргумент "бс" опущен, предполагается значение 0 (например, значение будущей стоимости для займа равно 0).
• Тип - Необязательный. Число 0 (нуль) или 1, обозначающее, когда должна производиться выплата.

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

Функция ОСПЛТ в Excel

Функция ОСПЛТ(ставка;период;кпер;пс;[бс];[тип]) - рассчитывает величину платежа в погашение основной суммы по кредиту за данный период на основе постоянных периодических платежей и постоянной процентной ставки.

Аргументы функции ОСПЛТ:

• Ставка – Обязательный аргумент. Процентная ставка за период.
• Период - Обязательный. Период: значение должно находиться в диапазоне от 1 до "кпер".
• Кпер - Обязательный. Общее количество периодов платежей по аннуитету.
• Пс - Обязательный. Приведенная (текущая) стоимость, т. е. общая сумма, которая на данный момент равноценна ряду будущих платежей.
• Бс – Необязательный аргумент. Значение будущей стоимости, то есть желаемого остатка средств после последней выплаты. Если аргумент "бс" опущен, предполагается значение 0 (например, значение будущей стоимости для займа равно 0).
• Тип - Необязательный. Число 0 или 1, обозначающее, когда должна производиться выплата.

Далее определим сумму платежей по процентам для кредита.

Функция ПРПЛТ в Excel

Функция ПРПЛТ(ставка;период;кпер;пс;[бс];[тип]) — рассчитывает сумму платежей по процентам для кредита за данный период на основе постоянства сумм периодических платежей и постоянства процентной ставки.

Аргументы функции ПРПЛТ:

• Ставка – Обязательный аргумент. Процентная ставка за период.
• Период - Обязательный. Период, для которого требуется найти платежи по процентам; число в интервале от 1 до "кпер".
• Кпер - Обязательный. Общее количество периодов платежей по аннуитету.
• Пс - Обязательный. Приведенная к текущему моменту стоимость, т. е. общая сумма, которая на текущий момент равноценна ряду будущих платежей.
• Бс – Необязательный аргумент. Значение будущей стоимости, то есть желаемого остатка средств после последней выплаты. Если значение fv опущено, предполагается, что оно равно 0 (будущее значение кредита, например, равно 0).
• Тип - Необязательный. Число 0 или 1, обозначающее срок выплаты. Если аргумент "тип" опущен, предполагается значение 0.

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

Пример 1: Как рассчитать стоимость ипотеки. Кредитный калькулятор

Допустим, вы планируете взять ипотеку на 5 000 000 рублей на 20 лет под 9% годовых. Как рассчитать ежемесячный платеж и понять структуру платежей?

Рассмотрим подробный расчет ипотеки на 5 млн. руб. под 9% годовых на 20 лет (240 месяцев) с пошаговыми формулами и наглядной таблицей платежей.

Формулы в Excel:

1. Ежемесячный платеж (ПЛТ):
   =ПЛТ($E$2/12;$D$2;$B$6) Результат: -44 986,30 (отрицательное число — это исходящий платеж)

Рис.1 - Функция ПЛТ в Excel:

2. Разбивка платежа на 1-й месяц:

• Проценты (ПРПЛТ):
  =ПРПЛТ($E$2/12;1;$D$2;$B$6), где – 1 первый расчетный период и первая строка в таблице. Для последующих строк меняем на 2, 3 и тд.
• Результат: -37 500,00 руб.

Рис.2 Функция ПРПЛТ в Excel:

3. Основной долг (ОСПЛТ):
=ОСПЛТ($E$2/12;1;$D$2;$B$6) – для первой строки.
Результат: -7 486,30 руб.

Рис.3 Функция ОСПЛТ в Excel:

Проверка:
37 500 ₽ (проценты) + 7 486,30 ₽ (основной долг) = 44 986,30 ₽ — совпадает с ПЛТ.

Таблица погашения кредита (ипотеки) – на Рис.1 - 3. Что видно из таблиц:

• В начале кредита большая часть платежа — проценты – 37500 руб. из 44986,30 руб.
• К концу срока основной долг погашается быстрее - 44 651,41 руб. из 44986,30 руб.

Как принять взвешенное финансовое решение:

• Сравнение кредитов: Меняйте ставку и срок, чтобы найти выгодный вариант.
• Визуализация: Постройте график, чтобы увидеть, как уменьшается долг.

Далее рассчитаем стоимость кредита в Excel.

Пример 2: Как рассчитать стоимости кредита в Excel

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

• сумма кредита 3 млн.руб.;
• срок кредитования – 7 месяцев;
• процентная ставка – 25% годовых.

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

Рис.4 Функция ПЛТ, функция ОСПЛТ и функция ПРПЛТ в Excel.

Формулы в Excel (для третьей строки таблицы-калькулятора):

• Основной долг =ОСПЛТ($E$2/12;3;$C$2;$B$6)
• Процента по кредиту =ПРПЛТ($E$2/12;3;$C$2;$B$6)
• Общий платеж =ПЛТ($E$2/12;$C$2;$B$6)

Рис.5 Кредитный калькулятор в Excel.

При сумме кредита 3 млн. руб. платежи составят 3,3 млн. руб. Сумма переплаты – 255153 руб.

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

Вывод. Функции ПЛТ, ОСПЛТ, ПРПЛТ помогают:

• Узнать ежемесячный платёж;
• Разделить его на проценты и основной долг;
• Спланировать досрочное погашение.

Подписывайтесь на канал! Мы подробно разбираем как сделать формулы в Эксель!