Рассчитать сумму кредита по ежемесячному платежу. Калькулятор досрочного погашения кредита

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. Достаточно выставить для ячейки с результатом процентный формат.

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

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

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

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

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

Как рассчитать кредит

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

Все, что требуется с Вашей стороны – вводить исходные данные, а математические вычисления возьмет на себя “кредитный калькулятор онлайн”.

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

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

Что такое ежемесячный платеж по кредиту?

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

Крайний срок выплаты суммы платежей является важной особенностью любого кредитного соглашения. Совокупность процента и объема кредитования является основой для расчета выплат по погашению.

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

1. Размер кредитования.
2. Учетный процент.
3. Временной период кредитования.
4. График платежей.
5. Банковский год. Каждое финучреждение устанавливает свою цифру – 360 дней либо 365 (в високосный год – 366).

Как рассчитываются ежемесячные платежи по кредиту?

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

Самостоятельный расчет, используя формулы

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

Расчет ежемесячных платежей на официальном сайте банка

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

Онлайн калькулятор

Аннуитетный метод просчета ежемесячных платежей

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

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

Формулу расчета аннуитета можно изобразить так: платеж (А) складывается из размера кредитования (Б), помноженного на величину. Количество месяцев (М) и учетный процент (П1/12), так как месяцев в году двенадцать. Получается – А=К*(П/(1+П)-М-1) . Такая формула может быть использована для потребительских займов и ипотечного кредитования.

Пример по аннуитетному методу

У нас есть сумма займа в размере 300000 рублей, временной период кредитования – 6 месяцев, а учетный процент в год – 9%. Для начала нужно рассчитать окончательную сумму ежемесячной ссуды. 300000*(0,00075+(0,00075/(1+0,00075)-(6-1)) = 32189 рублей.

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

Процент по аннуитетным платежам

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

Представляем полный перечень шагов:

1. За первый месяц – 300000*(0,09/12) = 2250, основная задолженность – 32189 – 2250 = 29939 рублей.
2. За второй месяц – 300000 – 29939 = 270061, процентная составляющая – 270061*(0,09/12) = 2025,46, основная задолженность – 32189 – 2025,46 = 30163,54 рублей.
3. За третий месяц – 270061 – 30163,54 = 239897,46, процентная составляющая платежей – 239897,46*(0,09/12) = 1799,23, основная задолженность выходит – 32189 – 1799,23 = 30389,77 рублей.

Дифференцированный метод просчета ежемесячных платежей

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

Формула просчета – как она выглядит?

Потребуется значение максимальной выплаты.

П (основной взнос) = Р (сумма кредитования)/ М(месяцы кредитования). Начисленные проценты (Н) находим, умножая на остаточный долг по займу (О) на учетный процент (Пр). Затем полученный результат делим на 12 (в году всего двенадцать месяцев – константа), выходит – Н = О*Пр/12.

Остаток по кредиту (О) находим так – О = Р – (П*К (сколько периодов прошло)).

Пример по дифференцированному методу

Сумма кредитования составляет 240000 рублей на полгода по ставке 9%. Основной взнос составит 240000/6 = 40000 рублей.

Платежи после оформления соглашения в каждом месяце:

1. За первый месяц – 40000+(240000-40000*0)*0,09/12 = 41800 рублей.
2. За второй месяц – 40000+(240000-40000*1)*0.09/12 = 41500 рублей.
3. За третий месяц – 40000+(240000-40000*2)*0.09/12 = 41200 рублей.

Какой из методов начисления учетных процентов стоит выбрать?

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

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

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

Клиент сумеет существенно снизить переплату. Но если кредитование займет меньше 5 лет, то переплата не будет существенной. Проще подыскать вариант с меньшей процентной ставкой и выбрать аннуитетный тип.

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

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

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

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

Особые характеристики просчета ежемесячных платежей

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

Ипотечное кредитование

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

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

Автозаймы

Кредитование по автозайму происходит не на долгосрочную перспективу. Здесь нужно подготовить первоначальный взнос. При просчете ежемесячных платежей стоит учитывать страхование (КАСКО в большинстве случаев, реже – ОСАГО) и банковское сопровождение сделки.

Как рассчитать выплаты, если у клиента кредитная карточка?

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

1. Наличие беспроцентного периода. Данный период позволяет потенциальному клиенту пользоваться денежными средствами без начисления %. Обычно период составляет от 30 до 100 дней. Зависит от выбранной структуры.
2. Платеж в месяц. Нужно произвести оплату 5-15% от основной суммы долга + учетные проценты (до 40% в год).

Выводы

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

Видео

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

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

С какой целью используются ипотечные или кредитные калькуляторы

Главная задача онлайн приложения: проведение подсчёта ежемесячных платежей, состоящих из двух частей - основной задолженности и начисленных на неё процентов. Кроме того, в «онлайне» можно рассчитать каким окажется размер переплаты в конце срока кредитования. Также пользователи могут выстроить прогноз: определить остаток по кредиту, если в планах частичное досрочное погашение задолженности. Здесь нужно учитывать, что при такой выплате займа понадобится либо уменьшить размер регулярного взноса (при этом срок выплаты не меняется), либо сократить продолжительность займа (тогда неизменным окажется ежемесячный платёж). Если клиент банка при оформлении ипотеки изначально планировал выплачивать кредит досрочно, ему выгоднее сокращать сумму основного долга, нежели срок. Это позволит существенно сократить ежемесячный платеж и переплату по кредиту. Кредитный калькулятор с частичным досрочным погашением наглядно продемонстрирует выгоду, а также поможет сравнить оба варианта ЧДП друг с другом, выявить плюсы и минусы.

Как посчитать кредит, используя специальное приложение

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

Для использования приложения достаточно владеть следующими данными:

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

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

Частичный досрочный взнос и его преимущества

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

К преимуществам такого погашения относят:

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

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

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

• Дату досрочного внесения средств (если платеж единоразовый) или интервал (если вы собираетесь делать платежи на регулярной основе, например раз в 3 месяца)
• Сумму досрочного платежа
• Выбрать способ перерасчета кредита

Можно задать неограниченное количество частично досрочных погашений.

Особенности частично досрочного погашения кредита

При частично досрочном погашении возможно два типа списаний:

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

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

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

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

Изменяемая процентная ставка

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