Excel – это универсальный аналитическо-вычислительный инструмент, который часто используют кредиторы (банки, инвесторы и т.п.) и заемщики (предприниматели, компании, частные лица и т.д.).
Быстро сориентироваться в мудреных формулах, рассчитать проценты, суммы выплат, переплату позволяют функции программы Microsoft Excel.
Как рассчитать платежи по кредиту в Excel
Ежемесячные выплаты зависят от схемы погашения кредита. Различают аннуитетные и дифференцированные платежи:
- Аннуитет предполагает, что клиент вносит каждый месяц одинаковую сумму.
- При дифференцированной схеме погашения долга перед финансовой организацией проценты начисляются на остаток кредитной суммы. Поэтому ежемесячные платежи будут уменьшаться.
Чаще применяется аннуитет: выгоднее для банка и удобнее для большинства клиентов.
Расчет аннуитетных платежей по кредиту в Excel
Ежемесячная сумма аннуитетного платежа рассчитывается по формуле:
А = К * S
где:
- А – сумма платежа по кредиту;
- К – коэффициент аннуитетного платежа;
- S – величина займа.
Формула коэффициента аннуитета:
К = (i * (1 + i)^n) / ((1+i)^n-1)
- где i – процентная ставка за месяц, результат деления годовой ставки на 12;
- n – срок кредита в месяцах.
В программе Excel существует специальная функция, которая считает аннуитетные платежи. Это ПЛТ:
- Заполним входные данные для расчета ежемесячных платежей по кредиту. Это сумма займа, проценты и срок.
- Составим график погашения кредита. Пока пустой.
- В первую ячейку столбца «Платежи по кредиту» вводиться формула расчета кредита аннуитетными платежами в 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 и вычислим эффективную процентную ставку, имея следующую информацию по предлагаемому банком кредиту:
Рассчитаем ежемесячную процентную ставку и платежи по кредиту:
Заполним таблицу вида:
Комиссия берется ежемесячно со всей суммы. Общий платеж по кредиту – это аннуитетный платеж плюс комиссия. Сумма основного долга и сумма процентов – составляющие части аннуитетного платежа.
Сумма основного долга = аннуитетный платеж – проценты.
Сумма процентов = остаток долга * месячную процентную ставку.
Остаток основного долга = остаток предыдущего периода – сумму основного долга в предыдущем периоде.
Опираясь на таблицу ежемесячных платежей, рассчитаем эффективную процентную ставку:
- взяли кредит 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
Таким образом, для расчета аннуитетных платежей по кредиту используется простейшая функция ПЛТ. Как видите, дифференцированный способ погашения несколько сложнее.
Источник
Рассмотрим Сложный процент (Compound Interest) – начисление процентов как на основную сумму долга, так и на начисленные ранее проценты.
Немного теории
Владелец капитала, предоставляя его на определенное время в долг, рассчитывает на получение дохода от этой сделки. Размер ожидаемого дохода зависит от трех факторов: от величины капитала, предоставляемого в кредит, от срока, на который предоставлен кредит, и от величины ссудного процента или иначе процентной ставки.
Существуют различные методы начисления процентов. Основное их различие сводится к определению исходной суммы (базы), на которую начисляются проценты. Эта сумма может оставаться постоянной в течение всего периода или меняться. В зависимости от этого различают метод начисления по
простым
и сложным процентам.
При использовании сложных ставок процентов процентные деньги, начисленные после каждого периода начисления, присоединяются к сумме долга. Таким образом, база для начисления сложных процентов в отличие от использования
простых процентов
изменяется в каждом периоде начисления. Присоединение начисленных процентов к сумме, которая послужила базой для их начисления, называется капитализацией процентов. Иногда этот метод называют «процент на процент».
В
файле примера
приведен график для сравнения наращенной суммы с использованием простых и сложных процентов.
В этой статье рассмотрим начисление по сложным процентам в случае постоянной ставки. О переменной ставке в случае сложных процентов
читайте здесь
.
Начисление процентов 1 раз в год
Пусть первоначальная сумма вклада равна Р, тогда через один год сумма вклада с присоединенными процентами составит =Р*(1+i), через 2 года =P*(1+i)*(1+i)=P*(1+i)^2, через n лет – P*(1+i)^n. Таким образом, получим формулу наращения для сложных процентов: S = Р*(1+i)^n где S — наращенная сумма, i — годовая ставка, n — срок ссуды в годах, (1+ i)^n — множитель наращения.
Начисление процентов несколько раз в год
В рассмотренном выше случае капитализация производится 1 раз в год. При капитализации m раз в год формула наращения для сложных процентов выглядит так: S = Р*(1+i/m)^(n*m) i/m – это ставка за период. На практике обычно используют дискретные проценты (проценты, начисляемые за одинаковые интервалы времени: год (m=1), полугодие (m=2), квартал (m=4), месяц (m=12)).
В MS EXCEL вычислить наращенную сумму к концу срока вклада по сложным процентам можно разными способами.
Рассмотрим задачу
: Пусть первоначальная сумма вклада равна 20т.р., годовая ставка = 15%, срок вклада 12 мес. Капитализация производится ежемесячно в конце периода.
Способ 1. Вычисление с помощью таблицы с формулами
Это самый трудоемкий способ, но зато самый наглядный. Он заключается в том, чтобы последовательно вычислить величину вклада на конец каждого периода. В
файле примера
это реализовано на листе
Постоянная ставка
.
За первый период будут начислены проценты в сумме
=20000*(15%/12)
, т.к. капитализация производится ежемесячно, а в году, как известно, 12 мес. При начислении процентов за второй период, в качестве базы, на которую начисляются %, необходимо брать не начальную сумму вклада, а сумму вклада в конце первого периода (или начале второго). И так далее все 12 периодов.
Способ 2. Вычисление с помощью формулы Наращенных процентов
Подставим в формулу наращенной суммы S = Р*(1+i )^n значения из задачи. S = 20000*(1+15%/12)^12 Необходимо помнить, что в качестве процентной ставки нужно указывать ставку за период (период капитализации). Другой вариант записи формулы – через функцию
СТЕПЕНЬ()
=20000*СТЕПЕНЬ(1+15%/12; 12)
Способ 3. Вычисление с помощью функции БС().
Функция
БС()
позволяет определить
будущую стоимость
инвестиции при условии периодических равных платежей и постоянной процентной ставки, т.е. она предназначена прежде всего для расчетов в случае
аннуитетных платежей
. Однако, опустив 3-й параметр (ПЛТ=0), можно ее использовать и для расчета сложных процентов.
=-БС(15%/12;12;;20000)
Или так
=-БС(15%/12;12;0;20000;0)
Примечание
.
В случае переменной ставки для нахождения Будущей стоимости по методу сложных процентов
используется функция
БЗРАСПИС()
.
Определяем сумму начисленных процентов
Рассмотрим задачу: Клиент банка положил на депозит 150 000 р. на 5 лет с ежегодным начислением сложных процентов по ставке 12 % годовых. Определить сумму начисленных процентов.
Сумма начисленных процентов I равна разности между величиной наращенной суммы S и начальной суммой Р. Используя формулу для определения наращенной суммы S = Р*(1+i )^n, получим: I = S – P= Р*(1+i)^n – Р=P*((1+i)^n –1)=150000*((1+12%)^5-1) Результат: 114 351,25р. Для сравнения: начисление по простой ставке даст результат 90 000р. (см.
файл примера
).
Определяем Срок долга
Рассмотрим задачу: Клиент банка положил на депозит некую сумму с ежегодным начислением сложных процентов по ставке 12 % годовых. Через какой срок сумма вклада удвоится? Логарифмируя обе части уравнения S = Р*(1+i)^n, решим его относительно неизвестного параметра n.
В
файле примера
приведено решение, ответ 6,12 лет.
Вычисляем ставку сложных процентов
Рассмотрим задачу: Клиент банка положил на депозит 150 000 р. с ежегодным начислением сложных процентов. При какой годовой ставке сумма вклада удвоится через 5 лет?
В
файле примера
приведено решение, ответ 14,87%.
Примечание
. Об эффективной ставке процентов
читайте в этой статье
.
Учет (дисконтирование) по сложным процентам
Дисконтирование основывается на базе концепции стоимости денег во времени: деньги, доступные в настоящее время, стоят больше, чем та же самая сумма в будущем, вследствие их потенциала обеспечить доход. Рассмотрим 2 вида учета: математический и банковский.
Математический учет
. В этом случае решается задача обратная наращению по сложным процентам, т.е. вычисления производятся по формуле Р=S/(1+i )^n Величину Р, полученную дисконтированием S, называют современной, или текущей стоимостью, или приведенной величиной S. Суммы Р и S эквивалентны в том смысле, что платеж в сумме S через n лет равноценен сумме Р, выплачиваемой в настоящий момент. Здесь разность D = S — P называется дисконтом.
Пример
. Через 7 лет страхователю будет выплачена сумма 2000000 руб. Определить современную стоимость суммы при условии, что применяется ставка сложных процентов в 15% годовых. Другими словами, известно: n = 7 лет, S = 2 000 000 руб., i = 15% .
Решение. P = 2000000/(1+15% )^7 Значение текущей стоимости будет меньше, т.к. открыв
сегодня
вклад на сумму Р с ежегодной капитализацией по ставке 15% мы получим через 7 лет сумму 2 млн. руб.
Тот же результат можно получить с помощью формулы
=ПС(15%;7;;-2000000;1)
Функция
ПС()
возвращает приведенную (к текущему моменту) стоимость инвестиции и
рассмотрена здесь
.
Банковский учет
. В этом случае предполагается использование сложной учетной ставки. Дисконтирование по сложной учетной ставке осуществляется по формуле: Р = S*(1- dсл )^n где dcл — сложная годовая учетная ставка.
При использовании сложной учетной ставки процесс дисконтирования происходит с прогрессирующим замедлением, так как учетная ставка каждый раз применяется к сумме, уменьшенной за предыдущий период на величину дисконта.
Сравнив формулу наращения для сложных процентов S = Р*(1+i )^n и формулу дисконтирования по сложной учетной ставке Р = S*(1- dсл )^n придем к выводу, что заменив знак у ставки на противоположный, мы можем для расчета дисконтированной величины использовать все три способа вычисления наращения по сложным процентам, рассмотренные в разделе статьи
Начисление процентов несколько раз в год
.
Источник
У компании есть два варианта, как гасить долг: аннуитетными либо дифференцированными платежами. Система выплат будет отличаться в зависимости от того, какой из способов выбрала компания. Расскажем, как рассчитать проценты по кредиту и не переплачивать.
Как рассчитывается кредит
У компании есть два варианта, как гасить долг. Сделать это можно либо аннуитетными, либо дифференцированными платежами. Система выплат будет отличаться в зависимости от того, какой из способов выбрала компания. Узнайте, как учитывать банковские кредиты в управленческом учете
Для обеих систем идентично то, что заемщик гасит тело кредита и проценты. Но при аннуитете сумма ежемесячного платежа не меняется, а при дифференцированной схеме к концу срока выплаты становятся меньше. Сколько заемного капитала нужно компании, чтобы расти
Соответственно, первая система подразумевает равные платежи, а вторая – каждый месяц разные. Узнайте также, как сравнить и выбрать: рублевый кредит с фиксированной или плавающей процентной ставкой
Модель для расчета аннуитетного платежа
В книге «Excel для финансового директора» подробно рассказано, как составить финансовую модель для расчета аннуитетного платежа. Скачайте и инструкция будет всегда под рукой.
Как рассчитать процент по кредиту за год по аннуитетной схеме
Для начала вычислим сумму ежемесячного платежа можно по формуле, приведенной ниже. А после на основе полученных данных вычислим годовой процент:
Ежемесячный платеж = Сумма кредита × Ставка/ 1- (1 + Ставка)^ — Срок кредита
Обратите внимание, что в этой формуле используется ставка в месяц. Ее нужно вычислить отдельно, разделив годовой процент сначала на 100, а затем на 12. Срок кредита в формуле нужно показать в виде количества месяцев (например, три года – 36 месяцев). Знак «^» здесь обозначает возведение в степень.
Пример 1.
Разберем на конкретном примере, как работает формула. Узнаем размер ежемесячного платежа, если известны следующие исходные данные:
- сумма кредита – 800 тыс. руб.;
- срок кредита – 2 года;
- ставка – 12 процентов.
Тогда размер процентной ставки за месяц составит 0,01 (12/100/12). А платеж на каждый месяц вычислим по формуле:
800 000 х 0,01 / 1- (1+0,01)^ — 24 = 38 095
Значит, общая сумма к выплате за все два года составит 38 095 х 24 = 914 280.
Из этой суммы 114 280 руб. уйдет на выплату процентов. Значит, за один год компания отдаст на уплату процентов 57 140 руб. Теперь очевидно, как рассчитать проценты по кредиту за месяц. 57 140 руб. разделим на 12, получается примерно 4 762 руб. Читайте также: Каким должен быть идеальный график платежей>>
Как планировать платежи по кредиту с помощью Excel
Скачайте кредитный калькулятор в Excel. Введите сумму и стоимость кредита, выберите аннуитетный, дифференцированный или индивидуальный график его погашения и спланируйте выгодный график погашения кредита.
Скачать шаблон в Excel
Как рассчитать годовой процент по кредиту по дифференцированной схеме
Один из способов расчета дифференцированного платежа выглядит так:
Платеж = (Сумма / Срок) + (Остаток × Ставка/12)
Пример 2.
Исходные данные:
- сумма кредита – 1 млн руб.;
- срок кредита – 3 года;
- ставка – 17 процентов.
Сделаем расчет, для этого есть все необходимые данные. Узнаем сумму платежа для первых трех месяцев:
1 месяц: (1 000 000 / 36) + (1 000 000 х 0,17/12) = 27 778 + 14 167 = 41 945
2 месяц: (1 000 000 / 36) + (41 945 х 0,17/12) = 27 778 + 594 = 28 372
3 месяц: (1 000 000 / 36) + (28 372 х 0,17/12) = 27 778 + 402 = 28 179
Как сохранить проценты по кредиту в налоговых расходах, если есть отсрочки и авансы
Если перечисляете авансы поставщикам и даете отсрочки покупателям, а кассовые разрывы покрываете за счет банковских кредитов, налоговики доначислят налог на прибыль! Узнайте, как действовать, чтобы инспекторы не сочли банковские проценты экономически не обоснованными расходами.
Сложные проценты по кредиту
В кредитовании также используется такое понятие как «сложные проценты» или «проценты на проценты». Это полная противоположность аннуитетной схеме, которую называют простой. При сложной системе процентная ставка начисляется не только на основную сумму долга. См. подробнее про капитализацию процентов>>
Эту систему можно встретить редко, но о ее существовании все же стоит знать. Если кредитное учреждение хочет применить эту схему, с ним можно спорить. Ведь закон позволяет начислять только на основную сумму долга (ст. 317.1, 809 и 819 ГК). Схема «проценты на проценты» совсем не выгодна клиентам банка. Читайте также: Расчет процентов от суммы в Excel>>
Посмотрим, как рассчитать по формуле сложные проценты по кредиту. Условно ее можно выразить так:
Долг = Первоначальная сумма × (1 + Ставка за расчетный период/100%)^Количество расчетных периодов
Пример 3.
Рассчитаем сложные проценты по кредиту, если известно следующее:
- сумма кредита — 3 млн руб.
- срок – 5 лет;
- ставка – 20 процентов.
Во-первых, определим ежемесячную процентную ставку 20/100/12. Она составляет 0,0167 процентов.
Далее применим формулу для вычисления сложных процентов:
3 000 000 х (1 + 0,0167/100)^1 = 3 000 501
Тогда размер долга за весь период составит:
3 000 000 х (1 + 0,0167/100)^60 = 3 030 210
Что выбрать: срочный кредит или кредитную линию
Срочный кредит и кредитная линия – одни из самых востребованных компаниями инструментов заемного финансирования. Причем и у того и у другого инструмента есть свои преимущества и недостатки. Предлагаемое решение поможет сделать правильный, экономически обоснованный выбор.
Что выбрать
Какая схема лучше?
Нельзя однозначно ответить на вопрос, как лучше рассчитать проценты по кредиту: по дифференцированной или аннуитетной системе. Этот вопрос стоит решать самостоятельно. Для кого-то аннуитет окажется выгоднее, а кому-то, напротив, дифференцированная система поможет сберечь средства. Как найти баланс: свои деньги или заемные
Срок кредита — основной критерий, на который стоит ориентироваться при выборе схемы для выплаты кредита. Если компания или предприниматель берут заем на короткий срок, то больше подойдет аннуитет. А дифференцированные платежи стоит применять для долгосрочных кредитов. Заем от нерезидента: как выгоднее учесть
Если по этому критерию определиться с системой платежей не удалось, следует изучить плюсы и минусы каждой из схем. В этом поможет таблица, которую мы привели ниже.
Таблица 1. Как выбрать схему платежей
Как рассчитать лизинговые платежи с помощью Exсel
Если нужно рассчитать, во сколько обойдется лизинг оборудования, выяснить, какие условия договора лизинга приемлемы для компании, возьмите специальную модель в Excel.
Модель для расчета
Резюме
Приведенные выше варианты, как рассчитать сумму процентов по кредиту, приблизительные. Банки могут использовать свои методы расчета. Формулы и примеры расчета из материала могут помочь составить примерную картину того, в какой срок и как именно компания будет гасить кредит.
Обратите внимание также на то, что сумма ежемесячного платежа зависит от комиссии банка. Возможно, потребуется внести дополнительные платежи за обслуживание банковской карты, просрочку платежей и пр.
Гость, уже успели прочесть в свежем номере?
Финансовый директор. Live – бизнес-встречи финансистов. Каждую неделю финансовые директоры делятся опытом онлайн. Участие бесплатное.
29 июня — 3 июля
Евгения Губарева, независимый финансовый директор
Онлайн-интенсив: CFO как антикризисный менеджер
7 июля, вторник. 12-13.00 мск
Анна Глазкова, финансовый директор ГК «Партнер-Электро»
Антикризисный кейс. Управлять финансами в производстве
14 июля, вторник. 12-13.00 мск
Виктория Филиппова, партнер Cornerstone, PCC, ICF Coach
Искусство оказывать влияние и добиваться своих целей. Лайфхаки CFO
Источник