Как правильно произвести расчет процентов по кредиту в таблице Эксель
- для банка такой способ расчета – это высокая прибыль даже тогда, когда клиент готов погасить всю сумму досрочно;
- для должника фиксированный платеж – это всегда удобно, поскольку можно рассчитывать финансовую нагрузку на бюджет, зная планируемые траты;
- размер переплаты – в данном случае он будет на порядок выше, чем при дифференцированном варианте погашения займа.
Данный сервис – это калькулятор, который даст возможность быстро посчитать различные виды выплат по кредитному соглашению, не требуя при этом прямого доступа к интернет-ресурсу. Человеку просто необходимо скачать приложение и выполнять математические подсчеты в любое удобное для него, время.
Обратите внимание! Программа достаточно популярна в бухгалтерском учете.
Для того чтобы получить данные, потребуется минимум информации:
Программа выглядит в виде таблицы, поля которой заполняются данными. При работе с ней используются следующие функции:
- КПЕР – размер кредита в рублях;
- СТАВКА – величина переплаты. Измеряется в процентах на определенный промежуток времени. Чаще всего – за год;
- ПС – целевое предназначение займа;
- ПЛТ – текущий взнос по кредиту.
Если ввести в программу верхние три пункта, сервис в автоматическом режиме подсчитает последний, четвертый.
Справка! В программе так же есть опция составления персонального графика внесения денег на счет банка. Для этого дополнительно потребуется дата, с которой начал свое действие договор по займу. Она указывается в соглашении, подписанном сторонами.
Расчет аннуитетных платежей по кредиту в Excel: скачать кредитный калькулятор
- произвести такие расчеты достаточно сложно – при таком методе сумма платежа каждый месяц меняется;
- способ крайне редко применяется банковской системой, что делает рассмотрение здесь математических формулировок не целесообразным.
От суммы ежемесячного платежа необходимо отнять то самое тело кредита, направляемое на погашение непосредственно занятой у банка суммы. Разница и будет процентами, постепенно снижающимися при дифференцированном порядке их начисления.
Пример 1
Предположим, человек планирует взять кредит в размере 50 000 руб. (ячейка В8 ) в банке под 14% годовых (B6 ) на 24 месяца (В7 ) (см. файле примера ).
Расчет Месячной суммы платежа по такому кредиту с помощью функции ПЛТ()
СОВЕТ :
Убедитесь, что Вы последовательны в выборе временных единиц измерения для задания аргументов «ставка» и «кпер». В нашем случае рассчитываются ежемесячные выплаты по двухгодичному займу (24 месяца ) из расчета 14 процентов годовых (14% / 12 месяцев ).
Расчет Месячной суммы платежа по такому кредиту с помощью БЕЗ функции ПЛТ()
Для нахождения суммы переплаты, умножьте возвращаемое функцией ПЛТ() значение на «кпер» (получите число со знаком минус) и прибавьте сумму кредита. В нашем случае переплата составит 7 615,46 руб. (за 2 года).
Кредитный калькулятор excel: формулы, расчет процентов, график погашения при аннуитетных и дифференцированных выплатах
- Ставка. Процентная ставка по кредиту (ссуде).
- Кпер. Общее число выплат по кредиту.
- пс. Сумма кредита.
- Бс. Необязательный аргумент. Требуемое значение остатка по кредиту после последнего платежа. Если этот аргумент опущен, предполагается, что он равен 0 (кредит будет полностью возвращен).
- Тип. Необязательный аргумент. Принимает значение 0 (нуль) или 1. Если =0 (или опущен), то принимается, что регулярный платеж осуществляется в конце периода, если 1, то в начале периода (сумма регулярного платежа будет несколько меньше).
5️⃣ Снятие средств и пополнение счета
Сложный процент в связке с регулярными взносами дают наилучший результат.
Снятие средств напротив, ослабляет эффект от капитализации процентов.
Формула определения суммы процентов по взятому кредиту
Вычисление сумм, перечисляемых на погашение процентов, возможно с использованием функции ПРПЛТ. Ее аргументы не отличаются от необходимых в ОСПЛТ:
- Ставка в процентах за период (годовые, поделенные на 12).
- Период – от первого до какого-либо нужного периода.
- Общее количество периодов платежей по кредиту.
- Приведенная стоимость, которая равна совокупности будущих платежей на данный момент.
- Требуемое значение будущей стоимости, то есть остатка после последних выплат (если же этот аргумент вообще не указывать, то будет предполагаться, что он приравнен к нулю).
- Тип – срок выплаты (0 – конец периода, 1 – начало периода).
Такие расчеты подходят для аннуитетных платежей, когда не известно тело кредита. Определить процент при дифференцированной схеме начисления процентов можно, узнав, какие денежные средства направляются ежемесячно на погашение задолженности.
От суммы ежемесячного платежа необходимо отнять то самое тело кредита, направляемое на погашение непосредственно занятой у банка суммы. Разница и будет процентами, постепенно снижающимися при дифференцированном порядке их начисления.
Как рассчитать процентные платежи за период или общую сумму с помощью формул Excel?
- Общая сумма выплат – это ежемесячный аннуитетный платёж (ячейка С4) умноженный на общий срок кредитования (ячейка С6). В строку формул вводим следующие данные: =C4*C6.
- Переплата (проценты) по кредиту – это общая сумма выплат (ячейка С12) минус сумма кредита (ячейка С11). В строку формул записываем: =C12-C11.
- Эффективная процентная ставка (или полная стоимость кредита) – это общая сумма выплат (ячейка С12) делённая на сумму кредита (ячейка С11) и минус единица. Затем всё это делим на срок кредитования, выраженный в годах (ячейка C6 делённая на 12). В строку формул записываем: = (C12/C11-1)/(C6/12).
Финансовые функции ПЛТ() , ОСПЛТ() , ПРПЛТ() , КПЕР() , СТАВКА() , БС() , ПС() , а также ОБЩДОХОД() и ОБЩПЛАТ() тесно связаны между собой, т.к. все они вычисляют параметры аннуитета и, соответственно, используют один и тот же набор аргументов. В этом можно убедиться, перечислив все функции вместе с аргументами:
