Расчет процентов по договору займа

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

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

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

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

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

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

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

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

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

А = К * S

где:

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

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

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

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

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

  1. Заполним входные данные для расчета ежемесячных платежей по кредиту. Это сумма займа, проценты и срок.
  2. Составим график погашения кредита. Пока пустой.
  3. В первую ячейку столбца «Платежи по кредиту» вводиться формула расчета кредита аннуитетными платежами в Excel: =ПЛТ($B$3/12; $B$4; $B$2). Чтобы закрепить ячейки, используем абсолютные ссылки. Можно вводить в формулу непосредственно числа, а не ссылки на ячейки с данными. Тогда она примет следующий вид: =ПЛТ(18%/12; 36; 100000).

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



Расчет платежей в 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 и вычислим эффективную процентную ставку, имея следующую информацию по предлагаемому банком кредиту:

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

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

Комиссия берется ежемесячно со всей суммы.

Расчет кредита в excel: скачайте готовые формулы и калькуляторы

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

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

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

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

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

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

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

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

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

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

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

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

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

Далее находим ЧБП: 365 / 28 = 13.

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

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

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

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

Скачать кредитный калькулятор в Excel

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

Расчет процентов по договору займа

Статья 395 ГК РФ. Ответственность за неисполнение денежного обязательства

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

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

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

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

3. Проценты за пользование чужими средствами взимаются по день уплаты суммы этих средств кредитору, если законом, иными правовыми актами или договором не установлен для начисления процентов более короткий срок. Из Постановления Пленума Верховного Суда РФ и Пленума Высшего Арбитражного Суда РФ от 8 октября 1998 г. №13/14: 2. При расчете подлежащих уплате годовых процентов по ставке рефинансирования Центрального банка Российской Федерации число дней в году (месяце) принимается равным соответственно 360 и 30 дням, если иное не установлено соглашением сторон, обязательными для сторон правилами, а также обычаями делового оборота. Проценты начисляются до момента фактического исполнения денежного обязательства, определяемого исходя из условий о порядке платежей, форме расчетов и положений статьи 316 ГК РФ о месте исполнения денежного обязательства, если иное не установлено законом либо соглашением сторон.

Основные правила расчета процентов

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

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

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

Способы расчета процентов

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

Простой способ — начисление процентов только на невозвращенную сумму займа. Расчет процентов за определенный период времени (месяц, квартал) будет осуществляться по формуле:

Сумма = ( Долг х Срок х Ставка ) / Дней в году

  • Долг — сумма займа, которую должник еще не вернул. Частичное погашение долга уменьшает сумму процентов, но не останавливает их начисление.
  • Ставка — проценты по договору займа. Когда ставка не указана, применяется ставка рефинансирования Центробанка. В формуле ставку переводят в десятичную дробь, разделив ставку на 100. Например, 11% = 0,11.
  • Дней в году — количество дней в году: 365 или 366, если год високосный.
  • Срок — срок использования займа в календарных днях.

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

Расчет процентов за просрочку возврата долга

Кроме договорных процентов, кредитор вправе потребовать с заёмщика штраф за нарушение срока возврата — неустойку или пени. Размер неустойки стороны указывают в договоре.

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

Сумма процентов за просрочку возврата долга рассчитывается по формуле:

Неустойка = ( Сумма х Срок х  Ставка ) / 360

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

Заполните анкету

  • Получите решение по кредиту за 1 час!
  • Наши консультации и услуги бесплатны!
  • Ваша анкета отправится сразу в 5 лучших банков!

Анкета на кредит

Заполните анкету за пять минут и получите одобрение не выходя из дома!

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

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

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

Вычисление аннуитетного платежа в Microsoft Excel

Для расчета погашаемой суммы кредита равными частями отметьте галочкой окошко «Выплаты равными частями»

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

По кнопке производится установка текущей даты, очистка табличной части формы, полей «Наименование» и «Авансовый взнос».

На основании полученных данных можно распечатать отчет «График платежей по кредиту». Данные отчета можно экспортировать из режима предварительного просмотра в файл формата MS Excel. Встроенный дизайнер отчетов позволяет редактировать отчет.

Письма с пожеланиями и вопросами по индивидуальной доработке отправляйте по адресу: all@pvision.ru.

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

Подписаться на рассылку

Калькулятор

Результаты

Базовые условия

Срок кредитования : 36 месяцев
Сумма кредита : 30000 денежных единиц
Процентная ставка : 10 % годовых (аннуитетная схема начисления)
Единоразовая комиссия : 0 %
Ежемесячная комиссия : 0 денежных единиц

Итоговые значения, денежных единиц

Ежемесячная сумма выплат : 968.02
Итого сумма выплат : 34848.53
в том числе % : 4848.53
тело кредита : 30000.00
комиссии : 0.00

Схема выплат по кредиту

Месяц Задолженность по кредиту Погашение кредита Проценты по кредиту Комиссии Выплаты в месяц
1 30000.00 718.02 250.00 0.00 968.02
2 29281.98 724.00 244.02 0.00 968.02
3 28557.98 730.04 237.98 0.00 968.02
4 27827.94 736.12 231.90 0.00 968.02
5 27091.82 742.25 225.77 0.00 968.02
6 26349.57 748.44 219.58 0.00 968.02
7 25601.13 754.68 213.34 0.00 968.02
8 24846.45 760.97 207.05 0.00 968.02
9 24085.48 767.31 200.71 0.00 968.02
10 23318.17 773.70 194.32 0.00 968.02
11 22544.47 780.15 187.87 0.00 968.02
12 21764.32 786.65 181.37 0.00 968.02
13 20977.67 793.21 174.81 0.00 968.02
14 20184.46 799.82 168.20 0.00 968.02
15 19384.64 806.48 161.54 0.00 968.02
16 18578.16 813.20 154.82 0.00 968.02
17 17764.96 819.98 148.04 0.00 968.02
18 16944.98 826.81 141.21 0.00 968.02
19 16118.17 833.70 134.32 0.00 968.02
20 15284.47 840.65 127.37 0.00 968.02
21 14443.82 847.65 120.37 0.00 968.02
22 13596.17 854.72 113.30 0.00 968.02
23 12741.45 861.84 106.18 0.00 968.02
24 11879.61 869.02 99.00 0.00 968.02
25 11010.59 876.27 91.75 0.00 968.02
26 10134.32 883.57 84.45 0.00 968.02
27 9250.75 890.93 77.09 0.00 968.02
28 8359.82 898.35 69.67 0.00 968.02
29 7461.47 905.84 62.18 0.00 968.02
30 6555.63 913.39 54.63 0.00 968.02
31 5642.24 921.00 47.02 0.00 968.02
32 4721.24 928.68 39.34 0.00 968.02
33 3792.56 936.42 31.60 0.00 968.02
34 2856.14 944.22 23.80 0.00 968.02
35 1911.92 952.09 15.93 0.00 968.02
36 959.83 959.83 8.00 0.00 967.83
Итого   30000.00 4848.53 0.00 34848.53

Сохранение

Распечатать результаты расчета на принтере

Скачать как Excel файл (формат Excel 5)

Скачать как Excel файл (формат Excel 2007)

Скачать как веб-страницу

Скачать как CSV-файл

Скачать в формате PDF

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

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *