Добрый день, уважаемые подписчики и читатели блога. Очень много поступает вопросов по поводу «кредитных калькуляторов» как их создать в 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. Нажимаем «ОК»
Останется понаблюдать за результатом.
Как видно из строки формул — появились фигурные скобки (признак массива) и функция ТАБЛИЦА. Не ищите её просто так, она появится только при использовании «Таблицы данных» из «Анализ «что если?».
Наш небольшой калькулятор готов. Можно будет с помощью пользовательских форматов дописать «месяцев» к нашим периодам. Всем удачи!