Акция 6+6 при подписке на полгода. 8(800)505-86-89
Журнал "Финансовый директор"

Расчет кредита: формулы и Excel-модели

10 июня 2019
2557
Средний балл: 0 из 5
Расчет кредита: формулы и Excel-модели

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

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

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

Расчет кредита - формула

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

Как выгоднее получить и погашать кредит - рассчитайте в Excel

Скачайте себе готовый кредитный калькулятор от журнала "Финансовый директор". Вы сможете просто и быстро планировать поступления и выплаты по кредитным договорам.

Скачать Excel-модель

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

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

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

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

Провести расчет банковского кредита с помощью Excel за 5 минут

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

Скачать Excel-модель

Размер процентной ставки зависит от:

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

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

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

Расчет кредита равными частями (аннуитетная схема)

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

Преимущества: простота расчета, фиксированный платеж

Недостатки: очень большая переплата по сравнению с дифференцированной схемой платежа.

Формула:

Расчет кредита: формулы и Excel-модели

Пример. ООО «АльфаОмега» взяла кредит на приобретение объекта основных средств в размере 2 000 000 рублей на срок 2 года, при этом годовая процентная ставка составила 18%.

  1. Рассчитаем месячную процентную ставку:

Месячная ставка = 18% / 12 месяцев = 1,5%.

  1. расчет ежемесячного платежа:

Расчет кредита: формулы и Excel-модели

Таким образом за весь срок кредитования переплата составит 396 356,89 рубля, а график платежей будет выглядеть следующим образом:

Таблица расчета кредита

№ п/п

Платеж

Погашение тела кредита

Погашение %

Остаток долга

1

99 848,20

69 848,20

30 000,00

1 930 151,80

2

99 848,20

70 895,93

28 982,28

1 859 255,87

3

99 848,20

71 959,37

27 888,84

1 787 296,50

4

99 848,20

73 038,76

26 809,45

1 714 257,75

5

99 848,20

74 134,34

25 713,87

1 640 123,41

6

99 848,20

75 246,35

24 601,85

1 564 877,06

7

99 848,20

76 375,05

23 473,16

1 488 502,01

8

99 848,20

77 520,67

22 327,53

1 410 981,33

9

99 848,20

78 683,48

21 164,72

1 332 297,85

10

99 848,20

79 863,74

19 984,47

1 252 434,11

11

99 848,20

81 061,69

18 786,51

1 171 372,42

12

99 848,20

82 277,62

17 570,59

1 089 094,80

13

99 848,20

83 511,78

16 336,42

1 005 583,02

14

99 848,20

84 764,46

15 083,75

920 818,56

15

99 848,20

86 035,93

13 812,28

834 782,64

16

99 848,20

87 326,46

12 521,74

747 456,17

17

99 848,20

88 636,36

11 211,84

658 819,81

18

99 848,20

89 965,91

9 882,30

568 853,38

19

99 848,20

91 315,40

8 532,81

477 538,51

20

99 848,20

92 685,13

7 163,08

384 853,38

21

99 848,20

94 075,40

5 772,80

290 777,98

22

99 848,20

95 486,53

4 361,67

195 291,45

23

99 848,20

96 918,83

2 929,37

98 372,61

24

99 848,20

98 372,61

1 475,59

0,00

Расчет дифференцированной схемы

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

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

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

Формула:

Расчет кредита: формулы и Excel-модели

Пример: Обратимся к данным вышеописанного примера. Расчет платежа в первый месяц будет следующим:

  1. Платеж 1 = 2 000 000 / 24 * (2 000 000 * 18%/12) = 113 333,33 рубля

Остаток долга составит 2 000 000 – 113 333,33 руб = 1 916 666,67 руб.

  1. Платеж 2 = 1 916 666,67/23 * (1 916 666,67 * 18%/12) = 112 083,33 руб.

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

График расчета кредита

№ п/п

Платеж

Погашение тела кредита

Погашение %

Остаток долга

1

113 333,33

83 333,33

30 000,00

1 916 666,67

2

112 083,33

83 333,33

28 750,00

1 833 333,33

3

110 833,33

83 333,33

27 500,00

1 750 000,00

4

109 583,33

83 333,33

26 250,00

1 666 666,67

5

108 333,33

83 333,33

25 000,00

1 583 333,33

6

107 083,33

83 333,33

23 750,00

1 500 000,00

7

105 833,33

83 333,33

22 500,00

1 416 666,67

8

104 583,33

83 333,33

21 250,00

1 333 333,33

9

103 333,33

83 333,33

20 000,00

1 250 000,00

10

102 083,33

83 333,33

18 750,00

1 166 666,67

11

100 833,33

83 333,33

17 500,00

1 083 333,33

12

99 583,33

83 333,33

16 250,00

1 000 000,00

13

98 333,33

83 333,33

15 000,00

916 666,67

14

97 083,33

83 333,33

13 750,00

833 333,33

15

95 833,33

83 333,33

12 500,00

750 000,00

16

94 583,33

83 333,33

11 250,00

666 666,67

17

93 333,33

83 333,33

10 000,00

583 333,33

18

92 083,33

83 333,33

8 750,00

500 000,00

19

90 833,33

83 333,33

7 500,00

416 666,67

20

89 583,33

83 333,33

6 250,00

333 333,33

21

88 333,33

83 333,33

5 000,00

250 000,00

22

87 083,33

83 333,33

3 750,00

166 666,67

23

85 833,33

83 333,33

2 500,00

83 333,33

24

84 583,33

83 333,33

1 250,00

0,00

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

Подготовлено по материалам
"Системы Финансовый директор"

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

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

logo
×
Чтобы скачать документ, зарегистрируйтесь на сайте!

"Финансовый директор" - единственный профессиональный ресурс по управлению финансами компании. Материалы подготовлены финансовыми директорами и экспертами. Пройдите короткую регистрацию и получите доступ

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
или войти через соц сети
Зарегистрироваться
×
Пожалуйста, войдите на сайт

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться
Простите, что прерываем чтение

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

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
или войдите через соц сети
Зарегистрироваться
Сайт использует файлы cookie. Они позволяют узнавать вас и получать информацию о вашем пользовательском опыте. Это нужно, чтобы улучшать сайт. Посещая страницы сайта и предоставляя свои данные, вы позволяете нам предоставлять их сторонним партнерам. Если согласны, продолжайте пользоваться сайтом. Если нет – установите специальные настройки в браузере или обратитесь в техподдержку.