Вычитать проценты по кредиту. Как рассчитать проценты по кредиту: формула

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

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

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

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

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

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

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

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

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

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

Возможности онлайн калькулятора кредита

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

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

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

Относительно каких параметров происходит расчет?

На что опирается банковский клиент, когда выбирает ссуду? На условия кредитования. Их уже учитывает онлайн-калькулятор сайт, в который нужно ввести всю совокупность параметров - сумму кредита, ставку, срок, схему платежей и др. Подробнее:

Сумма кредита. Речь об объеме средств, который клиент занимает у банка (без учета процентов, комиссий и страховки). В 2020 году сумма потребкредитов в среднем составляет от 30 тыс. до 5 млн. рублей. Если предоставить банку залог, сумма значительно вырастает - до нескольких десятков млн. рублей.
Процентная ставка. Представляет собой плату за пользование заемными средствами, выраженную в процентах. Начисляется по отношению к сумме кредита за определенный период (обычно - год). Сегодня средняя ставка составляет от 9 до 13% годовых. Считается, что это ключевое условие любой ссуды. Кроме динамики рынка, на ставку влияет кредитное прошлое клиента, его текущая финансовая нагрузка и доход.
Срок. Это время, на которое выдается кредит. Судя по актуальным предложениям банков, может составлять от полугода до пяти лет, но окончательно утверждается самим заемщиком. Чем больше срок - тем меньше платеж (и наоборот). Важен и момент выдачи - день, с которого стартовал срок кредитования. Именно тогда банк начинает взимать проценты.
Способ погашения. Иначе это называют схемой начисления платежей, которая существует в двух вариантах - аннуитетных и дифференцированных выплат. Обычно схему для конкретного кредита определяет банк, но иногда выбирает и сам клиент, исходя из того, какой платеж ему выгоднее.

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

Аннуитетный платеж по кредиту

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

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

Дифференцированный платеж по кредиту

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

✯Выгодно ли досрочно погашать кредит?

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

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

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

Исходные условия кредита			Результат расчета
Сумма	Срок	Ставка	Ежемесячный платеж, руб.	Величина переплаты, тыс. руб.
450 тыс. руб.	Полгода	12%	77 647	15,785	0,466
500 тыс. руб.	Год	12%	44 424	32,978	0,533
1 млн. руб.	Год	18%	91 680	99,798	1,100
1,5 млн. руб.	2 года	15%	72 730	245,144	1,745
2 млн. руб.	Год	11%	176 763	120,744	2,121
3 млн. руб.	5 лет	11%	65 227	912,792	3,913
5 млн. руб.	5 лет	10%	106 235	1 372,894	6,373
5 млн. руб.	7 лет	10%	83 006	1 971,075	6,971

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

Расчет процентов по кредиту

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

Исходные условия кредита			Результат расчета
Сумма	Срок	Ставка	Размер переплаты	Ежемесячный платеж	Общая сумма выплат, млн. руб.
500 тыс. руб.	Год	10%	27 402 руб.	43 958 руб.	0,527
500 тыс. руб.	1,5 года	10%	40 433 руб.	30 029 руб.	0,540
750 тыс. руб.	Год	10%	41 102 руб.	65 937 руб.	0,791
1 млн. руб.	Год	10%	54 803 руб.	87 916 руб.	1,055
750 тыс. руб.	1,5 года	10%	60 650 руб.	45 043 руб.	0,811
1 млн. руб.	1,5 год	10%	80 866 руб.	60 057 руб.	1,081 млн. руб.

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

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

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

Онлайн заявка на кредит

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

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

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

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

Процентная ставка

Процентная ставка - это стоимость кредита, которую предлагает банк. Каждый банк имеет свои программы кредитования населения и предлагает разные процентные ставки. Даже в одном банке процентная ставка может сильно отличаться при различных условиях. Она может зависеть от таких факторов, как возраст заемщика, его кредитная история, цель предоставления кредита, сумма кредита, наличие поручителей. Бывает, что банки предоставляют своим постоянным клиентам (например, владельцам дебетовых карт или лицам, которые уже пользовались кредитом) более выгодные условия кредитования, чем клиентам "с улицы". Актуальные процентные ставки банков вы можете узнать на сайтах этих банков.

Тип ежемесячного платежа

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

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

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

Как рассчитать ежемесячный платеж?

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

Аннуитетные. Данная схема погашения кредита предусматривает регулярные выплаты равного размера на протяжении всего срока действия займа. Следствием применения такого варианта является одинаковая финансовая нагрузка на заемщика;
Дифференцированные. Размер ежемесячной выплаты рассчитывается с учетом оставшейся на данный момент задолженности по кредиту. В результате использования такого варианта погашения займа финансовая нагрузка на клиента банка постепенно сокращается.

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

Расчет ежемесячного платежа по кредиту онлайн

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

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

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

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

Расчет в отделении банка

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

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

Другие способы расчета

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

Формула расчета

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

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

Из чего состоит ежемесячный платеж?

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

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

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

Особенности платежа в зависимости от вида кредитования

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