Кредитный калькулятор в Excel с равными платежами
В данном уроке будем создавать кредитный калькулятор Аннуитета (оплата кредита равными платежами) в Excelе для расчета по таких параметров как:
- ежемесячный платеж;
- сумму оплаты за пользованием кредита;
- эффективную ставку по кредиту.
Важно понимать, что данные, которые рассчитаем, будут близки к банковским расчетам, но все же могут незначительно отличаться.
Шаг 1. Создаем таблицу значений
В новом документе Excel создаем таблицу с данными, которые будем использовать для расчета:
- Сумма кредита;
- Процентная ставка (годовая);
- Ежемесячная комиссия;
- Единоразовая комиссия;
- Срок кредита в месяцах.
Ячейки для ввода данных обозначим желтым.
Данные которые будем рассчитывать:
- Ежемесячный платеж;
- Сумма переплаты по кредиту;
- Процент переплаты;
- Эффективнаяставка.
Шаг 2. Рассчитываем ежемесячный платеж
Для того, чтобы рассчитать ежемесячный платеж используем функцию «ПЛТ», она находится в категории «Финансовые».
Аргументы функции «ПЛТ»
- Ставка — Выбираем ячейку процентной ставки и делим ее на 12. Это связано с тем, что процентную ставку указываем годовую, а платеж мы рассчитываем ежемесячный
- Кпер — срок кредитования;
- ПС — сумма кредита, обязательно ставим знак «-» перед значением. Так как в параметрах есть Единоразовая комиссия Сумма долга = Сумма кредита + Сумма кредита * Единоразовою комиссию. Все кредитные учреждения Единоразовою комиссию включают в основной долг и насчитывают на них годовую процентную ставку.
После использования формулы расчета ежемесячного платежа по аннуитету «ПЛТ» с учетом «Единоразовой комиссии», остается учесть еще ежемесячную комиссию. Таким образом, в строке формулы к функции добавляем расчет суммы ежемесячной комиссии.
Шаг 3. Расчет оплаты за кредит.
Расчет суммы оплаты по кредиту производит путем умножение ежемесячного платежа по кредиту на срок кредита и вычитаем основную сумму кредита.
Процент переплаты по кредиту рассчитывается как сумма оплаты деленная на сумму кредита и умноженная на 100.
Шаг 4. Расчет эффективной ставки по кредиту
Эффективная ставка по кредиту включает в себя все проценты и все платежи по кредиту:
- Процентная ставка;
- Единоразовая комиссия;
- Ежемесячная комиссия.
Для расчет эффективной ставки используем функцию «СТАВКА» в категории функций «ФИНАНСОВЫЕ».
Аргументы функции:
- Кпер — срок кредитования;
- Плт — рассчитанный ежемесячный платеж, который включает в себя все проценты и комиссии;
- Пс — сумма кредита, обязательно со знаком «-«.
После использования функции «СТАВКА» необходимо в строке формулы умножить данную функцию на 12, чтобы вычислить годовую эффективную ставку.
С помощью данного калькулятора, легко, просто и быстро рассчитать ежемесячный платеж по любому кредиту, а также высчитать эффективную ставку.