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

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

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

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

Давайте попробуем воспользоваться данной функцией. Построим небольшую таблицу:

  • сумма кредита;
  • срок;
  • процент.

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

 

 

Позовём нашу функцию и посмотрим на её аргументы.

 

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

 

Аргументов много (в принципе каждый аргумент ПЛТ это отдельная функция):

Ставка - это ставка для периода (если ставка квартальная то 13% я делю на 4 квартала, если ставка месячная то 13% делим на 12 и т.д), в нашем случае берём именно второй вариант.

Кпер - количество периодов для выплат по займу.

Пс - текущая стоимость займа (в нашем случае 700000 рублей).

Бс - будущая стоимость займа.

Тип - принимает значения 0 или 1 в зависимости от платежа вначале или в в конце периода (в конце 0, в начале 1).

Заполним аргументы функции нашими данными. 

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

 

В итоге получим. Оставим "Бс" и "Тип" пустыми, они примут значение 0, он то нам и нужен! 

 

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

 

Результат со знаком минус - мы теряем эти деньги. Если хочется видеть положительную сумму - сумму кредита нужно ввести со знаком минус (-700000).

Результат налицо! Это будет наш ежемесячный платёж. Нетрудно посчитать, что за весь период мы выплатим банку 750365,12 рублей.

Идём дальше, давайте проведём небольшой анализ по процентной ставке и сроку кредита. Возьмём ставки - 13%, 15%, 19% и 25%. Периоды кредитования - 12, 24, 36, 48 и 60 месяцев.

 

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

 

Из формул массивов мы знаем, что можно умножать диапазон на диапазон, но нам также нужно учесть и первоначальную сумму кредита. Поэтому воспользуемся возможностью программы "Анализ что если?". Предварительно выделим всю таблицу данных (от А8 до F12): 

  • переходим на вкладку "Данные";
  • в блоке кнопок "Работа с данными" нажимаем кнопку "Анализ что если?";
  • выбираем "Таблица данных"

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

 

Теперь нужно указать куда (в какие ячейки подставлять) наши показания по количеству месяцев (столбцы) и процентную ставку (строки). Укажем соответствующие ячейки - B4 и B5. Нажимаем "ОК"

 

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

 

Останется понаблюдать за результатом.

 

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

 

Как видно из строки формул - появились фигурные скобки (признак массива) и функция ТАБЛИЦА. Не ищите её просто так, она появится только при использовании "Таблицы данных" из "Анализ "что если?". 

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

Всем удачи!

Автор записи: Иван

Добавить комментарий

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