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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Всем удачи!