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

1.

Перше, що потрiбно зробити, це ввести вiдпов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рки.

Пот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. Можна легко розрахувати суму

$$\begin{array}{llll}\hfill \text{Основна виплата}& =\frac{\text{Сума кредиту}}{\text{Кiлькiсть перiодичних платежiв}}& \hfill & \\ \hfill & =\frac{350000\text{kr}}{20}& \hfill & \\ \hfill & =17500\text{kr}& \hfill & \end{array}$$

Можна ввести $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дбувається так:

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

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

=B6-E6

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, яка знаходиться вище.

Зверни увагу! Кожен рядок залежить в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.

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

5.

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

=SUM(C6:C25)

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

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

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