Каждый заемщик банка желает получать актуальную информацию о текущей задолженности и иметь под рукой график ежемесячных платежей. Результат расчетов в такой ситуации зависит от применяемой для вычисления процентов системы в финансовом учреждении. Нередко заемщикам помогает в расчетах аннуитетных платежей по потребительскому кредиту понятный калькулятор в Excel.
Содержание статьи
Способы расчета суммы кредита с помощью Excel-таблицы
Размер платежа зависит от способа исчисления. Современные отечественные банки применяют два способа расчета: аннуитетный и дифференцированной. В каждом из них имеется тело кредита и начисляемые проценты. Оба типа можно включить в Excel-таблицу, где заранее будут прописаны формулы.
Важно правильно применять переменные и операции с ними, чтобы результат оказался верным или с минимальной погрешностью.
Для расчета платежей по кредиту используется специальная формула
Аннутитетные платежи
В 2019 году российские банки для оформления кредитов берут чаще всего аннуитетные системы, которые подразумевают ежемесячные выплаты по кредиту, при этом вносимая заемщиком сумма не изменяется на протяжении всего периода кредитования. Такая практика пришла к нам из Европы, где банкиры успели ее оценить по достоинству.
Размер регулярного взноса принято рассчитывать по формуле:
Е = К * S, где
Е — месячный платеж;
К — коэффициент аннуитетного платежа;
S — первоначальная сумма задолженности.
Для расчета коэффициента можно применять такую зависимость:
К = (j * (1 + j)^m) / ((1+j)^m-1), где
j — ежемесячная ставка процентов, которая высчитывается при делении годовой на 12 (кол-во месяцев в году);
m — период кредитования в месяцах.
В таблице расчетов процентов по кредиту для эксель можно применять стандартную формулу аннуитета. Для этого используется аббревиатура ПТЛ:
- вносим входные параметры для расчета месячных взносов по кредиту;
- формируем график погашения с колонками «Номер месяца» и «Платеж»;
- для первой ячейки «Платеж» прописываем формулу =ПЛТ($B$3/12; $B$4; $B$2);
- можно заменить ссылки константными данными, тогда пример будет выглядеть таким образом =ПЛТ(12%/12; 24; 1000000).
В полях «Платежи» значения примут красный цвет и будут отрицательными. Это связано с тем, что суммы нужно будет отдавать.
Дифференцированная схема
Проводить расчеты потребительского кредита в Эксель можно по дифференцированному принципу. Суть дифференцированных платежей заключается в том, что во время погашения уменьшается остаток долга, на который начисляются проценты. Соответственно месячный платеж постоянно снижается. Фактически долг распределяется равномерно по всему периоду, а процентный платеж, уплачиваемый ежемесячно на остаток, становится меньше, за счет чего снижается общая сумма месячного платежа.
Так как формулы расчета аннуитетного и дифференцированного платежа по кредиту в Excel отличаются, то приведем ее для второго типа:
МП = ОСЗ / (ПП + ОСЗ * МС), где
МП — месячный кредитный платеж;
ОСЗ — сумма остатка тела кредита;
ПП — количество периодов до полного погашения;
МС — ежемесячная ставка процентов, которая вычисляется делением годовой на 12 месяцев.
Для первого месяца задолженность по кредиту составит =$B$2. Дальнейшие оплаты со второй включительно необходимо рассчитывать по формуле в таблицах эксель =ЕСЛИ(D10>$B$4;0;E9-G9). В данной зависимости под D10 скрывается номер периода, под В4 время кредита, в Е9 вносится остаток от предыдущего периода, а G9 – размер основной задолженности в прошлом периоде. При сравнении одинаковых сумм и времени погашения будет такой результат.
Очевидно, что в черном цвете дифференцированный способ выгодней для клиента. При его расчете оказывается меньшая переплата.
Формула для расчета процентной части
Большинству клиентов, оформляющих ссуду или кредит в банке, интересно знать эффективную процентную ставку. В этом параметре отражается общая переплата по всему кредиту, включающая все предусмотренные финансовой организацией комиссии и платежи.
Для примера возьмем расчет кредита в полмиллиона рублей на два года с процентной ставкой 12% и ежемесячной комиссией 1% (снимается ежемесячно со всей суммы) при аннуитетных платежах. Основная задолженность рассчитывается в разнице аннуитетных платежей и процентов. Сумма процентов будет произведением месячной ставки на остаток долга, который равен разнице от остатка предыдущего периода и суммы основного долга в прошлом периоде.
В итоге по расчетам нужно будет вернуть банкирам от полумиллиона 684881,67 руб. Соответственно разница – 184 881, 67 руб. Тогда эффективная процентная ставка окажется на уровне 37%. Если же вести расчет без учета однопроцентной комиссии, то выйдем на эффективную процентную ставку в 13%.
Схема полного расчета кредита в Excel
Главная формула определения потребительского кредита регламентирована законом. В ней процентная ставка определяется до тысячных долей. Полную стоимость кредита (ПСК) рассчитываем по алгоритму:
ПСК = j * КБП * 100, где
j — процентная ставка для базового периода;
КБП — количество базовых периодов в расчетном году.
Чтобы иметь под рукой полный график, параметры нужно ввести в ячейки электронной таблицы. На некоторых сайтах уже установлен онлайн-калькулятор, помогающий высчитать период и процент для погашения.
Как рассчитать сумму переплаты при досрочном погашении займа
В реальной практике каждый клиент стремится поскорей избавиться от кредитного бремени. В такой ситуации не обойтись без дополнительных внеплановых взносов в счет погашения долга. Чтобы сделать расчет универсальным, придется учесть данное обстоятельство в формуле. До достижения нулевого баланса расчет ведется исходя из представленных зависимостей.
Обратите внимание! При уменьшении выплаты приходится перерасчитывать месячные взносы со следующего периода после досрочного погашения.
Выводы
При желании клиент может самостоятельно перепроверить банк, вычисляющий проценты по кредиту. Для этого достаточно воспользоваться электронными таблицами, установленными практически в каждом компьютере.
