Кредитный калькулятор с убывающими платежами в Excel
Создание кредитного калькулятора в Excel с расчетом платежей по классическому методу. Данный кредитный калькулятор подразумевает уменьшение платежа с каждым предыдущим периодом. Данный метод начисления процентов и оплаты по кредиту применяется в основном в кредитах на недвижимость, реже на автокредиты.
Шаг 1. Создание таблицы.
В новом документе Excel создаем таблицу с кредитными условиями:
- Сумма кредита;
- Процентная ставка;
- Срок кредитования.
А также создаем таблицу с расчетами, которые будем производить:
- Месяц;
- Сумма процентов;
- Сумма оплаты дол;
- Сумма погашения кредита;
- Платеж;
- Остаток кредита.
В строке месяц в примере указано 120 месяцев (10 лет), при необходимости можно делать больше
Шаг 2. Рассчитываем первую строку по условиям.
Сумму процентов рассчитываем путем умножения годовой процентной ставки по кредиту деленной на 12 ( так как рассчитываем ежемесячный платеж) на сумму кредита.
Обязательнуюсумму погашения основного долга рассчитываем путем деления всей суммы кредита на срок кредитования.
Ежемесячный платеж равен сумме процентов и ежемесячного погашения основного долга.
Остаток по кредиту рассчитывает вычитанием из всей суммы кредита сумму месячного погашения кредита.
Шаг 3. Расчет второй строки по условиям.
Сумму процентов рассчитываем исходя из условия, что остаток по кредиту больше нуля, так как нам необходимо рассчитывать проценты именно из остатка кредита.
В данном случае используем функцию "ЕСЛИ":
Аргументы функции:
- Лог_выражение - Остаток по кредиту больше значения "0"
- Значение_если_истинна - расчет суммы процентов. Умножение месячной процентной ставки на остаток по кредиту. В формуле на картинке значение "C3" необходимо заключить в знак "$", для того чтобы при перемещении формулы значение процентной ставки бралось строго с обозначенной ячейки.
- Значение_если_ложь - в противном случае, значение равно "0", то есть сумма процентов равна нулевому значению.
Расчет суммы погашения для основной суммы кредита производим также с использованием логической функции "ЕСЛИ".
Аргументы функции:
- Лог_выражение - Остаток по кредиту больше значения "0"
- Значение_если_истинна - расчет суммы погашения основного долга. Деление всей суммы кредита на срок кредита. В формуле на картинке значение "C2" и "C4" необходимо заключить в знак "$", для того чтобы при перемещении формулы значение процентной ставки бралось строго с обозначенной ячейки.
- Значение_если_ложь - в противном случае, значение равно "0", то есть погашение по кредиту уже не производится.
Платеж по кредиту как и выше будет равен сумме процентов и ежемесячного обязательного погашения основного кредита.
Остаток по кредиту рассчитываем как разница между суммой остатка по кредиту с прошлого месяца и суммой обязательного ежемесячного погашения тела кредита.
Шаг 4. Оформление кредитного калькулятора.
После введения формул во второй строке с расчетами переносим их на все периоды, в файле примера 120 мес. После чего всем значениям устанавливаем формат "Финансовый".
Оформляем кредитный калькулятор по собственному желанию с использованием шрифтов, заливок, выделений и т.д.
