Как рассчитать кредит в экселе: график получения и погашения, выплаты процентов

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

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

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

Можно выделить два основных наиболее популярных вида кредита по способу представления:

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

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

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

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

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

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

Шаблон для расчета необходимой суммы кредита, а также процентов к уплате

В шаблоне мы рассмотрим движение по кредитной линии, как наиболее сложной для расчета.

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

Шаблон по расчету кредита в экселе можно скачать здесь.

Несколько допущений, которые используются в шаблоне:

1. шаблон ежемесячный, поэтому считаем, что кредитный транш привлекаем раз в месяц. Если необходимо рассчитать ежедневные транши, то данный шаблон можно переделать из ежемесячного в ежедневный.
2. погашение траншей происходит в зависимости от наличия свободных денежных средств, при этом на расчетном счете должен оставаться лимит (в примере указан 100 000 руб).
   
3. расчет денежных потоков внесен цифрами, обычно эти данные поступают из отчета о движении денежных средств (ДДС) (если будет необходимо вставить шаблон в модель по бюджетированию, то в эти строки можно добавить данные из своего расчета по ДДС).
   
4. Долгосрочные кредиты и займы – займы, которые получены на срок более 12 мес.
   
5. Краткосрочные кредиты и займы – займы, которые получены на срок менее 12 мес.
   

Этапы заполнения шаблона по расчету кредита в экселе

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

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

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

Данные, полученные в строках «Проценты по кредитам начисленные», Проценты по кредитам выплаченные – попадают в «Отчет о прибылях и убытках» и «Отчет о движении денежных средств» соответственно.

• Комиссия банка за выдачу траншей

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

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

• Сумма кредитов и займов на конец периода

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

• Денежные потоки от финансовых операций

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

В строку 32 «Денежные потоки без учета финансовых операций» попадают данные из «Отчета о движении денежных средств», которые показывают покрывают ли доходы от операционной деятельности расходы (в этом случае значение положительное) или нет (сумма отрицательная).

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

• Лимит для остатка денежных средств на расчетном счете

Эта опция нужна для того, что на счетах компании после всех необходимых операций на конец месяца на расчетном счете оставалась какая-то сумма денежных средств. Например, в примере – 100 000 руб.

Лимит проставляем в строке 49 «Лимит денежных средств и эквивалентов»

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

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