Расчет переплаченных процентов по кредиту. Калькулятор расчета кредита в Excel и формулы ежемесячных платежей

Если нужны деньги в долг, стоит обратиться в банк. Сделать этом можно лично, либо ознакомившись с предложениями банков через интернет.
Каждый банк предлагает свои банковские продукты - кредиты наличными и кредитные карты. Первые пользуются большей популярностью благодаря большим суммам кредитования. Самое важное в кредите наличными - это переплата по нему. Ее можно рассчитать вручную на калькуляторе. Достаточно знать несколько легких формул и правил

Что нужно для расчета

Для расчета переплаты по займу необходимо знать следующие данные: сумма кредита, ставка, срок, тип платежей. Данные величины можно узнать из условий кредитования, также они прописаны в вашем кредитном договоре
Обычно банки предлагают аннуитетный тип платежей. Это значит, что вы платите каждый месяц определенную сумму.

Шаг 1. Считаем ежемесячный платеж

Аннуитет рассчитывается по определенной формуле.

1. n - срок кредита в месяцах
2. i - процентная ставка по займу в месяц.
3. В случае, если у вас указана годовая ставка, нужно поделить ее на 12. Т.е. допустим годовая ставка, 12 процентов, тогда

Формула достаточно сложна и требует возведения в степень. Поэтому лучше не считать эту формулу на обычном калькуляторе, а воспользоваться специальным кредитным калькулятором

Исходная сумма: 1 000 000 руб.

Проценты по кредиту: 1 000 000 руб.

Ежемесячный платеж: 56 000 руб.

Процент переплаты: 23,5 %

Ипотечный кредитный калькулятор для Android

Расчет и учет множества кредитов. Экспорт данных по электронной почте. Возможность оценить выгоду досрочных платежей. Графическое представление выгодности с помощью графиков. Расчет с изменением процентной ставки.

Калькулятор кредитов для iPhone/iPad

Доступна версия для iPhone и iPad. Удобный калькулятор, позволяющий рассчитать кредит, а также возможно досрочное погашение кредита

Калькулятор кредитов для Windows Phone 7-8

Шаг 2. Расчет суммарной переплаты

Суммарная переплата по кредиту - это сумма процентов за все месяцы пользования кредитом, которую вы возвратите банку.
Формула расчета суммарной переплаты чисто по кредиту следующая

Для нашего примера в 100 тыс. имеем

Именно эту сумму в итоге придется отдать банку помимо взятых 100 тыс. рублей.

Когда переплата будет выше

Проценты по кредиту зависят от многих величин как видно из формулы.
Есть ряд простых правил

Правило первое
Чем выше ставка по кредиту, тем больше переплата. Аннуитетный платеж зависит от ставки по кредиту. Чем она больше, тем он больше. Уменьшаемое в формуле переплаты зависит от аннуитетного платежа. Чем больше уменьшаемое, тем больше переплата Читайте также: Как работает кредитная карта Тинькофф
Карта позволяет занять на пару месяцев и вообще не платить проценты банку.

Если же речь идет о потребительском кредите, то для уменьшения суммарных нужно его досрочно погашать. При досрочном погашении уменьшается тело кредита, а значит и снижаются проценты.

Переплата и дополнительные платежи

При получении кредита в банке есть ряд дополнительных платежей.
Для ипотеки: страховка, расходы на оформление бумаг, оценку недвижимости, нотариуса, госпошлина в регистрационной палате
Для автокредита: страхование транспортного средства, пошлина за регистрацию транспортного средства
Нужно иметь ввиду, что данные расходы увеличивают переплату по кредиту. Обычно они добавляются к процентам по займу от банка.
Настоящей проблемой является подключение заемщиков к страхованию потребительских кредитов. Обычно при выдаче займа банк навязывает страховку. Решение простое - ни в коем случае не подписываться на коллективную страховку, а попросить чтоб договор страхования был между вами и страховщиком. Тогда страховку можно будет вернуть и избежать излишних расходов
Читайте также: Возврат страховки по кредиту. Инструкция по применению

Три способа расчета переплаты по кредиту наличными

На нашем сайте доступно три способа расчета переплаты по кредиту. Причем вам не нужно знать сложные математические формулы. Потребуется всего лишь заполнить основные параметры интересующего вас кредита, и наш сервис в считанные секунды произведет расчет.

Расчет переплаты с помощью графика платежей в карточке кредита

В каждой карточке кредита, после основных условий по его предоставлению, доступен расчет графика платежей. Эта функция составляет детальный отчет по графику выполнения долговых обязательств, а также рассчитывает итоговую переплату по процентам, за использование кредитных средств.

«Переплата рассчитывается, основываясь на минимальной процентной ставке, которая предусмотрена по выбранной программе кредитования, с точностью до копеек. Банки устанавливают годовую процентную ставку индивидуально. Поэтому итоговая переплата, после одобрения заявки может быть больше, чем указана в расчете графика платежей».

Чтобы воспользоваться этой функцией, вам необходимо сделать три действия:

1. Перейти в карточку заинтересовавшего вас кредита;

2. Пролистать страницу ниже, до раздела «Расчет графика платежей»;

3. Заполнить поле с желаемой суммой, и выбрать подходящий срок кредита, после чего нажать кнопку «Рассчитать».

На экране моментально отобразится таблица с указанием ежемесячных платежей, объема погашаемых процентов и основного долга, а также остатка задолженности в каждом месяце. Внизу графика указывается итоговая переплата по процентам.

«Вы можете сразу оценить вероятность положительного решения по кредиту, используя данные о ежемесячном платеже. Для банков надежным заемщиком считается тот, у которого на обслуживание всех долгов уходит не более 35% от суммы его ежемесячных доходов.»

Функция «Расчет кредита»

Как в общем каталоге, так и отдельных его разделах, для вас доступна специальная функция «Расчет кредита». Она позволяет увидеть ориентировочную переплату по всем предложениям банков одновременно. То есть, вы сможете самостоятельно сравнить стоимость кредитов и выбрать наиболее дешевый.

«Функция «Расчет кредита», помимо своего основного назначения, выполняет еще две немаловажные задачи. Во-первых, оставляет в каталоге только те предложения, которые подходят под заданные параметры суммы, срока кредита и возраста заемщика. Во-вторых, производится расчет ориентировочного ежемесячного платежа. Это дает возможность оценить реальность выплаты кредита, сопоставив расходы на его обслуживание со своими доходами.»

Для выполнения расчета переплаты по кредиту с помощью этой функции вам необходимо сделать всего два простых действия:

1. В блоке «Расчет кредита», который расположен в правой стороне экрана ввести три параметра: необходимую сумму, желаемый срок и ваш возраст;

В виде списка отобразятся все подходящие программы кредитования с указанием не только переплаты, но и ежемесячного платежа, а также полной суммы, которую необходимо будет вернуть. Предложения банков будут выстроены в последовательности – «начиная с самого дешевого».

Кредитный калькулятор

С помощью этой функции вы можете наиболее точно рассчитать свою переплату по кредиту, которая будет включать не только проценты за пользование заемными средствами, но и возможные дополнительные комиссии. Калькулятор производит расчет переплат и параллельно составляет ориентировочный график платежей. Чтобы им воспользоваться, необходимо выполнить всего два действия:

1. С любой страницы нашего сайта перейти в раздел «Кредитный калькулятор»;

2. Заполнить все необходимые поля: валюту кредита, его сумму, срок (доступно указание месяцев и лет), годовую процентную ставку, тип платежа, разовую и ежемесячную комиссию (в процентах либо фиксированную сумму).

Составление графика и расчет переплаты производится автоматически, при изменении хотя бы одного параметра кредита.

Как вы могли сами убедиться, все варианты расчета переплаты по кредиту очень простые и не требуют обширных знаний. Поэтому воспользоваться каждой функцией с легкостью может посетитель нашего сайта любой возрастной категории.

Расчет переплаты по кредиту

Если вы периодически пользуетесь банковскими услугами, в частности, кредитами, очень важно обладать финансовой грамотностью - это позволит избежать многих спорных ситуаций.

Заемщик должен понимать, что процентная ставка по кредиту и переплата по кредиту - разные понятия.

Предлагая кредиты населению, сотрудники банка чаще говорят о фактической переплате по кредиту. В целях привлечения клиентов менеджеры отделов кредитования называют сумму переплаты по кредиту, которая значительно меньше, чем процентная ставка.

Расчет переплаты по кредиту можно произвести самостоятельно, но сначала определим разницу между двумя понятиями.

Итак, процентная ставка - это величина, которая складывается из размера процентной ставки Центробанка и процентной ставки банка, предоставляющего кредит. Следовательно, годовая процентная ставка банка всегда больше ставки Центрального Банка России.

Фактическая переплата - это другая величина, ее можно рассчитать. Давайте рассмотрим на примере, как рассчитать переплату по кредиту. К примеру, вы берете в банке кредит на 120 тыс. рублей под 19% годовых на 12 месяцев. Как посчитать переплату по кредиту?

Во-первых, посчитаем аннуитетный коэффициент, являющийся базой для остальных расчетов по кредиту:

где i - 19%/12 мес. = 1,58% (0,0158)
n – 12 месяцев
K = 0,0922

При умножении полученной величины на срок кредита, получаем формулу расчета переплаты по кредиту. Точнее, сначала рассчитываем сумму, которую заемщик фактически выплатит банку:

0,0922 х 120000 х 12 = 132768 руб.

Разница между ней и взятой в кредит суммой и будет величиной фактической переплаты. Произведем расчет переплаты по кредиту:

132768 - 120000 = 12768 руб.

Размер переплаты по кредиту можно уменьшить, выплачивая кредит досрочно. Для банка это тоже в какой-то степени выгодно - несмотря на то, что в этом случае банк получит меньший доход с начисленных процентов, он снижает риски по невыплатам.

При оформлении кредита в договоре всегда указывается величина процентной ставки. Расчет переплаты по кредиту вы можете осуществить самостоятельно, применив указанную выше формулу.

Прежде чем оформлять кредит, имеет смысл сравнить условия, предоставляемые разными банками, такую информацию можно найти на соответствующих сайтах в Интернете. Почти каждый из них имеет онлайн-функцию «Кредитный калькулятор », с помощью которой в том числе можно произвести расчет переплаты по кредиту .

Кредит - одна из самых популярных и востребованных банковских услуг. Когда необходима определенная сумма денег, которой нет в наличии, лучше всего обратиться в банк. При этом важно знать как посчитать переплату по кредиту. Частные клиенты банков обычно берут кредиты наличными либо заводят кредитную карту. При этом далеко не все из них знают как рассчитать переплату по кредиту и сколько придётся выплатить банку за пользование финансами. В случае с кредитом наличными, клиентов привлекают большие суммы, которые выдаются на руки. Пользуясь кредитными картами, заёмщики рассчитывают на льготный период, который позволяет некоторое время (до двух месяцев) пользоваться деньгами бесплатно. Следует обратить внимание на то, что открытие и обслуживание кредитной карты может быть платным. Рассчитывать выгоду такой карты приходится с учётом комиссии, которая будет взиматься при каждом пользовании банкоматом.

Многие банки выдают клиентам кредит не из собственных средств, а из денег, полученных в кредит у Центробанка под значительно более низкий процент, чем предлагается физическим лицам, поэтому банк практически в любом случае остается в плюсе. А вот конечному потребителю важно знать как рассчитать переплату по кредиту правильно, ведь переплачивать не нравится никому. Для получения максимальной пользы от кредита нужно рассчитать в точности условия, на которых он предоставляется, сколько денег Вы получите и сколько придётся вернуть. Осведомлённость и контроль позволят избежать недобросовестности со стороны банковских служащих, исключат навязывание ненужных услуг (например, страхование, которое часто необязательно). Также это важно, когда необходимо учесть и рассчитать запланированные расходы.

Что включает в себя переплата по кредиту

Переплата по кредиту — это совокупная сумма, которую клиент будет должен выплатить банку сверх возврата займа. Переплата рассчитывается с учётом:

• одноразовой комиссии за выдачу кредитных средств;
• страховки (на случай форс-мажорной невозможности погасить кредит);
• аннуитетного платежа (регулярный платёж за пользование деньгами банка);
• процентной ставки по кредиту.

В случае ипотечного кредита также дополнительно нужно считать: расходы на нотариуса, страховку, оценку недвижимости и госпошлину. Досрочный платёж по ипотеке тоже может предполагать дополнительные траты. При оформлении автокредита также придётся оплачивать пошлину за регистрацию транспортного средства и страхование.

Перечисленные дополнительные выплаты добавляются к процентам по займу. Чем длительнее срок кредита, тем на более высокую переплату приходится рассчитывать. За досрочное погашение банк также иногда требует комиссию.

Как посчитать переплату по кредиту

Для заёмщиков доступны две схемы погашения кредита: аннуитетная и дифференцированная. Поскольку при последней схеме погашения ежемесячный платёж определяется из текущего долга по кредиту, рассчитать размер переплаты можно с помощью кредитного калькулятора или электронных таблиц. Выплаты по кредиту также можно дифференцировать, но эта опция рассчитана на крупные компании, как и буллитная схема погашения кредита. Интерфейс кредитного калькулятора подскажет как рассчитать переплату по кредиту в онлайне. Кредитный калькулятор позволяет вычислить сумму переплаты, введя срок и сумму кредита, размер процентной ставки, начало выплаты кредита и иные дополнительные параметры. После обработки, калькулятор предоставит информацию о сумме выплат, размеру переплаты и процентном соотношении переплаты и суммы кредита. Существует множество онлайн сервисов, предлагающих воспользоваться кредитным калькулятором. А досрочный кредитный калькулятор и вовсе позволяет рассчитать переплату с учётом досрочного погашения.

Аннуитетный платёж может быть посчитан по формуле:

где СК - сумма кредита, ПС - процентная ставка в долях за месяц, М - количество месяцев, на которое берётся кредит.

Суммарная переплата может быть посчитана по формуле:

Суммарная переплата = (аннуитетный платёж * срок кредита в месяцах) - сумма кредита.

Так мы получим общую сумму, которую мы должны будем заплатить с учётом суммы кредита и переплаты по нему.

Электронные таблицы

Расчёт переплаченных средств по кредиту также можно произвести с помощью электронных таблиц, например, Microsoft Excel. В случае аннуитетной схемы применяемые в электронных таблицах формулы будут чуть проще. Электронные таблицы позволяют учитывать каждый досрочный платёж. Если вы умеете работать с электронными таблицами, вам не составит труда разобраться, как посчитать переплату по кредиту с их помощью. В ином случае, рекомендуем воспользоваться калькулятором или обратиться к специалисту.

Кредитный калькулятор

Внесите досрочное погашение, изменяемую процентную ставку и определитесь с выбором вида кредита: аннуитетный или дифференцированный.

Нажмите кнопку “Рассчитать” и получите полные результаты расчёта переплаты по кредиту. Наш кредитный калькулятор использует алгоритмы, определенные ЦБ Российской Федерации, поэтому Вы получите самый правильный график платежей по кредиту.

Переплата по кредиту отображается в блоке “Общие данные”. Вы так же можете раскрыть полный график платежей и получить более детальную информацию по кредиту. Распечатайте, отправьте на электронную почту или сохраните график платежей. Это позволит спланировать семейный бюджет или сравнить график полученный на сайте с графиком, представленным в банке.

Услуга банка

При обращении в банк за кредитом банковский служащий обязан произвести для вас правильные расчёты переплат по кредиту и ознакомить с результатами, а также объяснить алгоритм и показать подробно, как рассчитывается переплата по вашему кредиту. Чтобы меньше переплачивать, следует выбирать кредит, у которого минимальный процент: чем ниже ставка, тем меньше придётся переплатить. Размер переплаты рассчитывается пропорционально сроку, на который берётся кредит. Чем быстрее кредит будет погашен, тем меньше будет переплачено. Регулярно проверяйте остаток задолженности и своевременно вносите ежемесячные платежи, по возможности пользуйтесь досрочным погашением.

Более выгодные процентные ставки предусмотрены по кредитам с обеспечением:

• или ;
• с поручительством третьих лиц.

По таким продуктам самая высокая вероятность одобрения даже при отсутствии официальной работы. Переплата по ним минимальна за счет того, что банку получает гарантию оплаты и риски по займу сводятся к нулю.

1. — от 9,9% для наших авто и иномарок;
2. — от 11,9% даже без подтверждения дохода.
3. — от 11,4% абсолютно на любые цели.

Основной недостаток - длительная процедура оформления. Клиент должен привести поручителя или предъявить бумаги, подтверждающие право собственности на имущество. Рассмотрение заявки может продлиться до нескольких дней. Это не самый лучший вариант для тех, кому деньги нужны срочно.

3. Целевое кредитование

Ссуда на конкретные цели всегда обходится дешевле, чем стандартный кредит наличными.

Банки выдают деньги со сниженной ставкой:

• на покупку машины (автокредит) или квартиры (ипотека);
• на образование;
• на санаторно-курортное лечение и т.д.

Но в данном случае заемщик должен отчитаться о расходовании средств. Сюда же можно отнести товарные ссуды. В этом случае вы можете получить на покупку определенного товара. Но ставки там обычно выше — лучше оформить карту рассрочки и покупать в тех же магазинах, но вообще без переплат, укладываясь в беспроцентный период погашения долга.

4. Страхование

Страхование при оформлении займа носит добровольный характер, и клиент не обязан соглашаться на эту услугу. Сумма страховки за год достигает 3-4% от всей стоимости ссуды. Но заемщик выплачивает ее не сразу, а частями, включенными в ежемесячный платеж.

Заемщик, отказавшийся от страхования, автоматически снижает кредитную нагрузку. Ставка по самому кредиту при этом повысится, но эта переплата все равно будет ниже страховки.

Есть еще одно решение - можно оформить кредит со страховкой и в течение 5 дней после этого отказаться от услуги.

5. Досрочное погашение

Возвращая кредит досрочно, вы отдаете банку только основной долг, не давая ему возможности получить всю сумму процентов за полный период. Согласно законодательству банк не имеет права запретить клиенту досрочно вернуть деньги, а также не может взыскать за это дополнительную комиссию.

Погашать долг досрочно можно частично или в полном объеме. Проценты при этом должны быть пересчитаны за фактический срок пользования деньгами.

Стоит отметить, что банкам не выгодно такое сотрудничество, и при последующем обращении этого клиента они могут отклонить заявку. Кроме того, факт досрочного погашения негативно сказывается на кредитном досье и может препятствовать дальнейшему получению денег и в других банках.

6. Рефинансирование

Платежеспособные заемщики с хорошей финансовой репутацией могут перекредитоваться на более привлекательных условиях в другой организации или в том же самом банке.

Рефинансирование (перекредитование) - это получение нового кредитного продукта с более низкой процентной ставкой для полного или частичного погашения предыдущего долга.

Пример: вы взяли 3 займа с разными сроками и ставками на сумму 300000 рублей. Каждый месяц вы возвращаете 25000 рублей и на текущий момент остаток долга составляет 190 тыс. руб. Можно взять один новый кредит, чтобы покрыть три предыдущих - 200 тыс. руб. под 26,8% на полтора года. Ежемесячный платеж уменьшится на 10 тыс. руб.

Обращаясь в другую финансовую организацию:

• не сообщайте банку о своих финансовых трудностях;
• избегайте навязывания лишних услуг, например, страхового полиса;
• перед подачей заявки учитывайте не только размер ставки и полную сумму кредита, но и общую сумму процентов, которую придется переплатить.

7. Карта с льготным периодом

Большинство банков предлагают своим клиентам кредитные карты с льготным периодом, в течение которого можно вернуть долг без переплаты.

Перед оформлением карты узнайте точную дату истечения срока и уточните, можно ли не платить проценты только в случае безналичного пользования или и при снятии денег через банкомат.

Если клиент не успевает вернуть деньги в срок, ему придется выплачивать достаточно высокую комиссию - выше стандартной ставки по потребительским кредитам.

1. — бесплатная карта с периодом без процентов до 60 дней;
2. — карта от Альфабанка, позволяющая более 3х месяцев не возвращать долг и не переплачивать.
3. — аналогичный вариант от Почтабанк, но уже за 900 рублей в год. Зато без процентов можно пользоваться деньгами до 120 дней — 4 полных месяца.
4. — предлагает те же 120 дней льготного периода, но уже с бесплатным обслуживанием.

Интересная фишка кредитки. Если вы получаете кешбек за покупки и возвращаете долг в течение беспроцентного периода, то вы не только не переплачиваете, но еще и зарабатываете.

8. Комплексное обслуживание в банке

Комплексное обслуживание поможет клиенту стать ценным заемщиком в банке и получить лучшие предложения со сниженным процентом. Для этого необходимо активно пользоваться услугами организации:

• оформлять кредитные и дебетовые карты;
• открыть депозит и регулярно его пополнять;
• участвовать в акциях.

Активным клиентам банки часто предлагают льготные продукты. Так что ориентируйтесь на будущее — сотрудничество с одним банком позволить в итоге уменьшить переплату и получать наиболее выгодные кредитные предложения.

9. Реструктуризация

Реструктуризация не уменьшает общую сумму долга, но позволяет сократить размер ежемесячного платежа. Чтобы претендовать на эту услугу, нужны веские основания, например, утрата дохода из-за болезни или сокращения.

При реструктуризации срок кредитования увеличивается, количество платежей становится больше, а их сумма - меньше.

Кроме того клиент может попросить кредитные каникулы, предоставив соответствующие документы, подтверждающие потерю дохода.

Еще один вид реструктуризации - перевод долга кредитной карты на стандартный потребительский кредит (на более длительный, но фиксированный срок).

10. Правильно гасим проценты

Существует аннуитетная и дифференцированная схема погашения процентов. Иногда банк сам решает, по какой схеме у него гасится займ. Иногда вам дается возможность самому выбрать, как гасить проценты. Чаще всего банки применяют наименее выгодную.

Вкратце: Аннуитентная - переплата больше, но финансовая нагрузка меньше, а платежи каждый месяц одинаковые. Дифференцированная - экономия на переплате, но большие первые платежи - могут быть проблемы с возможностью их выплаты.

Какой вариант выбрать - решать только вам, но если вы хотите сэкономить на кредите, лучше оформлять договор по дифференцированной схеме.

Заключение

Чтобы не переплачивать банку лишние деньги, оформляйте кредит на небольшой срок - до 1-3 лет. Предоставляйте полный комплект документов, в том числе справки с работы. Если вы не отличаетесь идеальной кредитной историей, лучше всего оформить займ под залог, но перед этим объективно оцените свои финансовые возможности.

Кроме того сэкономить деньги можно, вернув себе 13% от фактически уплаченных процентов в виде налоговых вычетов.

Расчет, во сколько обойдется кредит многим заемщикам, представляется крайне запутанным делом, требующим глубоких познаний в математике. Другие же полагают, что ставка 20% годовых означает переплату за кредит именно в эти проценты. Все не так сложно, но и не так уж просто. Истина находится где-то посередине. В этой статье мы расскажем, как посчитать переплату по кредиту.

Сколько стоит кредит?

Понять, как посчитать размер переплаты можно, если знать, какова полная стоимость кредита. Сегодня контролирующие организации требуют от банков предоставлять заемщикам информацию о полной стоимости кредита и даже указывать формулу расчета.

А на случай ненадлежащего исполнения клиентом условий договора, банк обязан раскрывать перечень и ставки платежей, чтобы можно было посчитать переплату.

Полная стоимость кредита включает в себя четыре составляющих:

• тело кредита (основной долг);
• процентная ставка, начисленная на основной долг;
• все комиссии, взимаемые банком;
• выплаты в адрес третьих лиц.

Варианты погашения кредита

Сумма переплаты зависит от выбранного заемщиком типа погашения кредита. Расскажем о них подробнее.

Аннуитетные платежи

При этой схеме погашения кредита размеры ежемесячных взносов фиксированы и не могут меняться на протяжении действия кредитного договора. Такой способ платежей комфортен для заемщика тем, что не нужно каждый месяц подстраивать свой семейный бюджет под очередной платеж.

Сегодня практически все кредитные организации применяют расчет по аннуитетной схеме. Однако, несмотря на удобность, этот тип платежей предполагает более высокую переплату в процентах за пользование кредитом.

В начале срока действия договора с банком взносы по кредиту будут состоять из процентов, но по мере выплат клиент переходит к погашению собственно тела кредита.

Для образца приведем простой пример формулы расчета выплат по аннуитетной схеме для кредита в 3 млн. рублей:

ПЛТ(годовая ставка/12;кол-во платежей по кредиту; — тело кредита)

ПЛТ(0,2/12;60;-3000000)

Результат: фиксированный ежемесячный платеж в 79 481,65 руб.

Считаем выплату по процентам, 31 день в первом месяце, 365 дней в этом году:

3 000 000 x 0,2 x 31 / 365 = 50 958,90 руб.

В погашение долга таким образом уйдет:

79 481,65 — 50 958,90 = 28 522,75 руб.

Для второго платежа выплата по процентам составит (28 дней в месяце и те же 365 дней в году):

(3 000 000 – 28 522,75) x 0,2 x 28 / 365 = 45 589,78 руб

Что видно в итоге: мы наблюдаем постепенное снижение переплаты в процентах и переход на оплату основного долга. Клиенту придется долго ожидать, когда оплата процентов банку сменится погашением основного долга. В данном случае сумма за кредит растет вместе со сроком кредита.

Дифференцированные платежи

Дифференцированная схема взносов – это погашение основного долга равными долями с первого дня, но при этом проценты банк начисляет на еще не погашенный остаток займа.

При таком способе платежей наибольшая переплата ожидает заемщика в начале кредита, однако, ежемесячные взносы уменьшают тело кредита и величину начисленных процентов.

Дифференцированные платежи за кредит выглядят для многих заемщиков слишком сложными, однако именно эта схема взносов наиболее выгодна. Популярность, тем не менее, у дифференцированных платежей невелика, так как большинство заемщиков не имеет возможности делать наибольшие взносы в первые месяцы кредита.

Размер взноса по телу кредита при дифференцированной схеме на протяжении 5 лет равен:

3 000 000 / 5 x 12 = 50 000 руб.

плата по процентам в первый месяц будет равна (20% годовых = 0,2):

3 000 000 x 0,2 / 12 = 50 000 руб.

Выплата процентов во втором месяце:

(3 000 000 – 50 000) x 0,2 / 12 = 49 166,67 руб.

Итого 100 000 руб. в первом месяце и 99 166,67 руб. во втором

В результате по телу кредита платеж неизменен, а выплаты процентов снижаются, таким образом можно получить общее снижение размера выплат каждый месяц.

Excel – это универсальный аналитическо-вычислительный инструмент, который часто используют кредиторы (банки, инвесторы и т.п.) и заемщики (предприниматели, компании, частные лица и т.д.).

Быстро сориентироваться в мудреных формулах, рассчитать проценты, суммы выплат, переплату позволяют функции программы Microsoft Excel.

Как рассчитать платежи по кредиту в Excel

Ежемесячные выплаты зависят от схемы погашения кредита. Различают аннуитетные и дифференцированные платежи:

1. Аннуитет предполагает, что клиент вносит каждый месяц одинаковую сумму.
2. При дифференцированной схеме погашения долга перед финансовой организацией проценты начисляются на остаток кредитной суммы. Поэтому ежемесячные платежи будут уменьшаться.

Чаще применяется аннуитет: выгоднее для банка и удобнее для большинства клиентов.

Расчет аннуитетных платежей по кредиту в Excel

Ежемесячная сумма аннуитетного платежа рассчитывается по формуле:

А = К * S

• А – сумма платежа по кредиту;
• К – коэффициент аннуитетного платежа;
• S – величина займа.

Формула коэффициента аннуитета:

К = (i * (1 + i)^n) / ((1+i)^n-1)

• где i – процентная ставка за месяц, результат деления годовой ставки на 12;
• n – срок кредита в месяцах.

В программе Excel существует специальная функция, которая считает аннуитетные платежи. Это ПЛТ:

Ячейки окрасились в красный цвет, перед числами появился знак «минус», т.к. мы эти деньги будем отдавать банку, терять.

Расчет платежей в Excel по дифференцированной схеме погашения

Дифференцированный способ оплаты предполагает, что:

• сумма основного долга распределена по периодам выплат равными долями;
• проценты по кредиту начисляются на остаток.

Формула расчета дифференцированного платежа:

ДП = ОСЗ / (ПП + ОСЗ * ПС)

• ДП – ежемесячный платеж по кредиту;
• ОСЗ – остаток займа;
• ПП – число оставшихся до конца срока погашения периодов;
• ПС – процентная ставка за месяц (годовую ставку делим на 12).

Составим график погашения предыдущего кредита по дифференцированной схеме.

Входные данные те же:

Составим график погашения займа:

Остаток задолженности по кредиту: в первый месяц равняется всей сумме: =$B$2. Во второй и последующие – рассчитывается по формуле: =ЕСЛИ(D10>$B$4;0;E9-G9). Где D10 – номер текущего периода, В4 – срок кредита; Е9 – остаток по кредиту в предыдущем периоде; G9 – сумма основного долга в предыдущем периоде.

Выплата процентов: остаток по кредиту в текущем периоде умножить на месячную процентную ставку, которая разделена на 12 месяцев: =E9*($B$3/12).

Выплата основного долга: сумму всего кредита разделить на срок: =ЕСЛИ(D9

Итоговый платеж: сумма «процентов» и «основного долга» в текущем периоде: =F8+G8.

Внесем формулы в соответствующие столбцы. Скопируем их на всю таблицу.

Сравним переплату при аннуитетной и дифференцированной схеме погашения кредита:

Красная цифра – аннуитет (брали 100 000 руб.), черная – дифференцированный способ.

Формула расчета процентов по кредиту в Excel

Проведем расчет процентов по кредиту в Excel и вычислим эффективную процентную ставку, имея следующую информацию по предлагаемому банком кредиту:

Рассчитаем ежемесячную процентную ставку и платежи по кредиту:

Заполним таблицу вида:

Комиссия берется ежемесячно со всей суммы. Общий платеж по кредиту – это аннуитетный платеж плюс комиссия. Сумма основного долга и сумма процентов – составляющие части аннуитетного платежа.

Сумма основного долга = аннуитетный платеж – проценты.

Сумма процентов = остаток долга * месячную процентную ставку.

Остаток основного долга = остаток предыдущего периода – сумму основного долга в предыдущем периоде.

Опираясь на таблицу ежемесячных платежей, рассчитаем эффективную процентную ставку:

• взяли кредит 500 000 руб.;
• вернули в банк – 684 881,67 руб. (сумма всех платежей по кредиту);
• переплата составила 184 881, 67 руб.;
• процентная ставка – 184 881, 67 / 500 000 * 100, или 37%.
• Безобидная комиссия в 1 % обошлась кредитополучателю очень дорого.

Эффективная процентная ставка кредита без комиссии составит 13%. Подсчет ведется по той же схеме.

Расчет полной стоимости кредита в Excel

Согласно Закону о потребительском кредите для расчета полной стоимости кредита (ПСК) теперь применяется новая формула. ПСК определяется в процентах с точностью до третьего знака после запятой по следующей формуле:

• ПСК = i * ЧБП * 100;
• где i – процентная ставка базового периода;
• ЧБП – число базовых периодов в календарном году.

Возьмем для примера следующие данные по кредиту:

Для расчета полной стоимости кредита нужно составить график платежей (порядок см. выше).

Нужно определить базовый период (БП). В законе сказано, что это стандартный временной интервал, который встречается в графике погашения чаще всего. В примере БП = 28 дней.

Теперь можно найти процентную ставку базового периода:

У нас имеются все необходимые данные – подставляем их в формулу ПСК: =B9*B8

Примечание. Чтобы получить проценты в Excel, не нужно умножать на 100. Достаточно выставить для ячейки с результатом процентный формат.

ПСК по новой формуле совпала с годовой процентной ставкой по кредиту.

Таким образом, для расчета аннуитетных платежей по кредиту используется простейшая функция ПЛТ. Как видите, дифференцированный способ погашения несколько сложнее.