Кредитный калькулятор с плавающей процентной савкой в Excel
Существуют кредиты с плавающей процентной ставкой. Как составить график платежей по погашению кредита с изменяемой процентной ставкой? Дисконтирование таких кредитов часто зависит от официальных финансовых индексов. Например, LIBOR (London Interbank Offered Rate) при повышении его показателя на определенное процентное значение часто описано как: «LIBOR +3%». Рассмотрим все на конкретном примере.
Кредитный калькулятор с плавающей процентной ставкой в Excel
Ниже на рисунке изображен график платежей по кредиту с плавающей процентной ставкой. Он содержит столбец «Изменение процентов» благодаря которому наглядно видно изменение процентной ставки. В отдельной таблице записаны изменяемые значения процентной ставки.
В столбце «Изменение процентов» записанная формула, которая подставляет соответствующие процентные ставки из дополнительной таблицы по условию:
Формула в столбце «Сумма процентов» использует данные из ячеек в столбце «Изменение процентов»:
Примечание: Все формулы в каждом столбце изображены выше заголовков столбцов за исключением столбца G, там формула: =ВПР(D11;$K$11:$L$23;2;ИСТИНА).
В столбце «Изменение процентов» используется функция ВПР, в которой четвертый аргумент имеет значение ИСТИНА. Чтобы можно было использовать такой аргумент, данные первого столбца в дополнительной таблице с плавающими процентными ставками должны быть отсортированы по возрастанию. Функция ВПР находит ближайшее большее значение в первом столбце дополнительной таблице для каждого текущего номера платежа в исходной таблице. Функция при поиске не требует точного совпадения, а только ближайшее большее значение соответственное числу из первого столбца дополнительной таблицы. При нахождении ближайшего большего значения функция ВПР возвращает процентную ставку с ячейки на против найденного числа. Например, когда функция ищет значения процентной ставки для номера 16, она возвращает значение со второй строки, так как ближайшее большее значение чем 16 в очередной строке равно 98. То есть 98 – это самое ближайшее большее число от искомого номера 16.
График платежей по кредиту с плавающей процентной ставкой в Excel
В графиках платежей по кредитам вместо номеров платежей обычно используются даты. Чтобы изменить номера платежей на даты в графике выполните ряд последовательных действий:
- В ячейке D4 введите дату первого платежа (пусть будет для примера: 10.01.2020).
- В ячейке D4 введите формулу, которую следует скопировать в остальные нижние ячейки столбца D (теперь уже «Дата платежа», а не «№»):
- В столбце K «Дата платежа» в дополнительной таблице с процентами (диапазон: K2:L15) введите даты, от которых будет изменяться процентная ставка в графике платежей по кредиту.
Следует также создать условное форматирование для автоматического скрытия ячеек с отрицательными значениями долга по кредиту при уменьшении срока кредитования. Например: не 30, а 20 лет. Изменив цвет шрифта на белый мы скроем лишние значения графика по условию. Для этого выделите диапазон табличной части графика D4:I363 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
В появившемся окне «Создание правила форматирования» выберите опцию «Использовать формулу для определения форматируемых ячеек», а в поле «Изменение описания правила» введите формулу:
Скачать кредитный калькулятор с плавающей процентной ставкой
После чего нажмите на кнопку формат и задайте шрифту значений ячеек белый цвет.
В результате график платежей динамически скрывает ненужные отрицательные значения при уменьшении срока кредитования в параметрах условий первой таблицы слева.
