Шаблон Excel для графіка амортизації кредиту

Ось вказiвки до виконання огляду амортизованого кредиту в електроннiй таблицi.

Зверни увагу! Пам’ятай, що сума основних виплат завжди дорiвнює амортизованому кредиту.

Iнструкцiя Excel

Виконання огляду кредиту з класичною схемою погашення

1.
Вiдкрий нову електронну таблицю та введи значення «суми кредиту», «вiдсоткiв» i «термiну погашення».
2.
Створи шаблон таблицi. Тобi знадобляться стовпцi «Рiк», «Залишок кредиту», «Вiдсотки», «Основнi виплати» та «Перiодичнi платежi». Кiлькiсть рядкiв визначається тривалiстю перiоду погашення.
3.
Введи формулу за рiк 1, а також залишок кредиту за рiк 2.
4.
Скопiюй формули вiдповiдних стовпцiв до останнього рядка платежу.
5.
Пiдсумуй стовпцi «Вiдсотки», «Основнi виплати» та «Перiодичнi платежi».

Приклад 1

Уяви, що ти береш амортизований кредит у розмiрi 350000 гривень за вiдсотковою ставкою 2.1% на 20-рiчний перiод. Зроби таблицю з оглядом кредиту в програмi Excel.

1.
Перше, що потрiбно зробити, це ввести вiдповiднi числа для твого кредиту:

Електронна таблиця в Excel з iнформацiєю про кредит

2.
Наступне, що треба зробити, це створити шаблон таблицi погашення амортизованого кредиту. У цiй таблицi потрiбнi стовпцi «Рiк», «Залишок кредиту», «Вiдсотки», «Основнi виплати» та «Перiодичнi платежi».

Створи рядки для рокiв з 1 до 20. Замiсть створення рядка для всiх цiлих чисел вручну, можна використати розумну функцiю в Excel, яка зробить усю роботу за тебе. Введи числа 1, 2, 3, як показано нижче, а потiм познач комiрки.

Електронна таблиця в Excel iз оглядом структури кредиту

Потiм натисни й утримуй маленький зелений квадратик у нижньому правому кутку комiрки A8 i потягни його до упору донизу, доки не з’явиться число 20. (Коли ти тягнеш, маленький сiрий квадрат пiдраховує кiлькiсть рядiв).

3.
Залишок кредиту для першого року — це початкова сума кредиту. Можна ввести 350000 гривень в комiрку B6, але ти вже ввiв/ввела число в комiрку B1 електронної таблицi. Елегантнiше рiшення — використовувати функцiональнi можливостi електронної таблицi, набравши в комiрцi B6:

=B1

Отже, якщо ти бажаєш змiнити початкову суму кредиту, потрiбно лише змiнити значення комiрки B1.

Вiдсотки за рiк 1 будуть такими самими, як залишок кредиту за рiк 1, помножений на вiдсотки. Щоб розрахувати вiдсотки за рiк 1, введи в комiрку C6:

=B6*$B$2

Зверни увагу! Знак $ перед лiтерними та числовими координатами комiрки B2 призначений для використання функцiональної можливостi електронної таблицi, що дозволяє копiювати формули. Це пояснено докладнiше нижче.

Основнi виплати за амортизованим кредитом завжди однаковi. Можна легко розрахувати суму

Основна виплата = Сума кредиту Кiлькiсть перiодичних платежiв = 350000kr 20 = 17500kr

Можна ввести 17500 гривень у комiрку для основної виплати за рiк 1, але якщо ми бажаємо змiнити початкову суму кредиту, також необхiдно змiнити комiрку перiодичного платежу. Тому, можливо розумно ввести формулу для обчислення розстрочки в комiрку перiодичного платежу D6. Отже, в комiрку D6 вводимо:

=$B$1/$B$3

Розмiр перiодичного платежу дорiвнює вiдсотки + основний платiж. У комiрку E6, де вказано розмiр платежу за рiк 1, введи:

=C6+D6

Нам довелося витратити певний час на заповнення кiлькох комiрок, але незабаром ти вiдчуєш магiю електронних таблиць! Насамкiнець потрiбно ввести залишок кредиту за рiк 2. Розрахунок вiдбувається так:

Залишок кредиту пiсля 2 рокiв = Залишок кредиту пiсля 1 рокуПерiодичний платiж у рiк 1

У комiрку B7 введи:

=B6-E6

Електронна таблиця в Excel iз докладною iнформацiєю щодо першого року  погашення кредиту

Електронна таблиця в Excel iз зазначенням використаних формул

4.
Ось тут i починається найцiкавiше! Тепер потрiбно скопiювати формулу для залишку кредиту, видiливши комiрку B7. Пiсля цього схопи мишею маленький зелений квадрат i потягнути його до 20-го року. Excel вважає, що формула в зазначенiй комiрцi має бути скопiйована й виконана в наведених нижче комiрках, що належать до того самого стовпця. Excel бачить, що формула в зазначенiй комiрцi складається з вiднiмання числа в комiрцi, розташованiй на одну комiрку вгору й на двi вправо, вiд числа, що мiститься в комiрцi, яка знаходиться вище.

Електронна таблиця в Excel з формулами для подальших розрахункiв кредиту

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

Тепер можна скопiювати формулу з комiрки D6 у всi комiрки, що знаходяться нижче в стовпцi «Перiодичнi платежi». Рiзниця мiж стовпцем «Перiодичнi платежi» i стовпцем «Залишок кредиту» полягає в тому, що завжди потрiбно посилатися на комiрки B1 та B3 в стовпцi «Перiодичнi платежi». Щоб Excel знав, що ми завжди бажаємо використовувати цi двi комiрки замiсть перемiщення вниз, введемо знак $ перед лiтерними та числовими координатами комiрок. Отже, знову видiли комiрку D6 i потягни її до 20-го рядка.

Тепер можна побачити, що числа в стовпцi «Залишок кредиту» правильнi.

Електронна таблиця в Excel, що показує розвиток позики

Зроби те саме зi стовпцями «Вiдсотки» та «Основнi платежi», Тобто видiли комiрку C6 i потягни її до 20-го року, а потiм видiли комiрку E6 i зроби те саме. Зверни увагу, що в стовпцi «Вiдсотки» треба використати стовпець «Залишок кредиту» в тому самому рядку, але завжди з тими самими вiдсотками (B2). Тому використай знак $, коли посилаєшся на вiдсотки у формулi C6, але не коли посилаєшся на залишок кредиту.

Електронна таблиця в Excel з усiма розрахунками щодо кредиту

Електронна таблиця в Excel iз зазначенням використаних формул

5.
Нарештi може бути корисно пiдсумувати вiдсотки, перiодичнi платежi та основнi виплати в комiрках C26, D26 i E26. У комiрку C26 введи:

=SUM(C6:C25)

Зроби те саме для стовпцiв для «Перiодичнi платежi» та «Основнi виплати». Остаточна електронна таблиця матиме такий вигляд:

Електронна таблиця в Excel з усiма розрахунками щодо кредиту

I з формулами:

Електронна таблиця в Excel iз зазначенням використаних формул

Сума перiодичних платежiв повинна дорiвнювати сумi кредиту — й можна побачити, що в нас це так. Сума вiдсоткiв дорiвнює тому, що ми заплатили як вiдсотки, тобто 77175 гривень, а сума основних виплат — це всi кошти, якi ми повернули до банку, а це 427175 гривень. Iнакше кажучи, амортизований кредит у розмiрi 350000 гривень коштуватиме тобi 427175 гривень за 20 рокiв.

Бажаєш дізнатися більше?ЗареєструйсяЦе безплатно!