Створення графіка погашення кредиту в Excel може бути цінним навиком, що дозволить вам візуалізувати та ефективно керувати погашенням кредиту. Графік амортизації — це таблиця, у якій детально описано кожен періодичний платіж за амортизаційною позикою (зазвичай це іпотека чи кредит на автомобіль). Він розбиває кожен платіж на відсотки та основну частину, показуючи залишок після кожного платежу. Давайте зануримося в покроковий посібник зі створення такого розкладу в Excel.

Що таке графік амортизації?

Створіть графік амортизації в Excel

Створіть графік амортизації для змінної кількості періодів

Створіть графік амортизації з доплатами

Створіть графік амортизації (з доплатами) за допомогою шаблону Excel

Що таке графік амортизації?

Графік амортизації – це детальна таблиця, яка використовується для розрахунків позики, яка відображає процес погашення позики з часом. Графіки амортизації зазвичай використовуються для позик із фіксованою процентною ставкою, таких як іпотека, автокредити та особисті позики, де сума виплати залишається незмінною протягом усього терміну позики, але пропорція виплати відсотків до основної суми змінюється з часом.

Для створення графіка амортизації позики в Excel надзвичайно важливі вбудовані функції PMT, PPMT і IPMT. Давайте розберемося, що робить кожна функція:

• Функція PMT: ця функція використовується для розрахунку загальної суми платежу за період позики на основі постійних платежів і постійної процентної ставки.
• Функція IPMT: ця функція обчислює процентну частину платежу за певний період.
• Функція PPMT: ця функція використовується для розрахунку основної частини платежу за певний період.

Використовуючи ці функції в Excel, ви можете створити детальний графік амортизації, який показує відсотки та основну суму кожного платежу, а також залишок позики після кожного платежу.

Створіть графік амортизації в Excel

У цьому розділі ми представимо два різні методи створення графіка амортизації в Excel. Ці методи задовольняють різні уподобання користувачів і рівні навичок, гарантуючи, що будь-хто, незалежно від їх навичок роботи з Excel, зможе успішно скласти детальний і точний графік погашення своєї позики.

Формули пропонують глибше розуміння базових обчислень і забезпечують гнучкість налаштування розкладу відповідно до конкретних вимог. Цей підхід ідеально підходить для тих, хто хоче отримати практичний досвід і чітке уявлення про те, як кожен платіж розбивається на компоненти основної суми та відсотків. Тепер давайте покроково розберемо процес створення графіка амортизації в Excel:

⭐️ Крок 1: Налаштуйте інформацію про позику та таблицю амортизації

1. Введіть відповідну інформацію про позику, таку як річна процентна ставка, термін позики в роках, кількість платежів на рік і сума позики, у клітинки, як показано на знімку екрана:
   
2. Потім створіть таблицю амортизації в Excel із зазначеними мітками, такими як Період, Платіж, Відсотки, Основна сума, Залишок у клітинках A7:E7.
3. У стовпці Період введіть номери періодів. У цьому прикладі загальна кількість платежів становить 24 місяці (2 роки), тому ви введете числа від 1 до 24 у стовпець Період. Перегляньте скріншот:
   
4. Після того, як ви налаштували таблицю з мітками та номерами періодів, ви можете продовжити введення формул і значень для стовпців «Платіж», «Відсотки», «Основна сума» та «Залишок» відповідно до особливостей вашої позики.

⭐️ Крок 2: обчисліть загальну суму платежу за допомогою функції PMT

Синтаксис PMT:

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

= -PMT($B$1/$B$3, $B$2*$B$3, $B$4)

⭐️ Крок 3: розрахуйте відсотки за допомогою функції IPMT

На цьому кроці ви обчислите відсотки за кожен платіжний період за допомогою функції IPMT Excel.

Введіть наведену нижче формулу в клітинку C7, а потім перетягніть маркер заповнення стовпця вниз, щоб заповнити цю формулу та отримати відсотки за кожен період.

=-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ Крок 4: обчисліть головну суму за допомогою функції PPMT

Після розрахунку відсотків за кожен період наступним кроком у створенні графіка амортизації є розрахунок основної частини кожного платежу. Це робиться за допомогою функції PPMT, яка призначена для визначення основної частини платежу за певний період на основі постійних платежів і постійної процентної ставки.

Синтаксис IPMT:

Синтаксис і параметри для формули PPMT ідентичні тим, що використовуються у раніше обговорюваній формулі IPMT.

Введіть наведену нижче формулу в клітинку D7, а потім перетягніть маркер заповнення вниз по стовпцю, щоб заповнити основне для кожного періоду. Перегляньте скріншот:

=-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ Крок 5: обчисліть залишок

Після розрахунку відсотків і основної суми кожного платежу наступним кроком у вашому графіку погашення є розрахунок залишку позики після кожного платежу. Це важлива частина графіка, оскільки вона показує, як залишок кредиту зменшується з часом.

1. У першій клітинці стовпця вашого балансу – E7, введіть таку формулу, яка означає, що залишок балансу буде початковою сумою кредиту мінус основна частина першого платежу:

   =B4-D7

   
2. Для другого та всіх наступних періодів обчисліть залишковий баланс шляхом віднімання основного платежу за поточний період із залишку за попередній період. Застосуйте таку формулу до клітинки E8:

   =E7-D8

    примітки: посилання на клітинку балансу має бути відносним, тому воно оновлюється, коли ви перетягуєте формулу вниз.
   
3. А потім перетягніть маркер заповнення вниз до стовпця. Як бачите, кожна клітинка буде автоматично налаштована для розрахунку залишку на основі оновлених платежів основної суми.
   

⭐️ Крок 6: Зробіть резюме кредиту

Після встановлення детального графіка амортизації, створення короткого опису позики може надати швидкий огляд ключових аспектів вашої позики. Цей підсумок зазвичай включає загальну вартість позики та загальну суму сплачених відсотків.

● Щоб розрахувати загальні платежі:

=SUM(B7:B30)

● Щоб розрахувати загальну суму відсотків:

=SUM(C7:C30)

⭐️ Результат:

Тепер успішно створено простий, але комплексний графік погашення позики. дивіться знімок екрана:

Створіть графік амортизації для змінної кількості періодів

У попередньому прикладі ми створюємо графік погашення кредиту для фіксованої кількості платежів. Цей підхід ідеально підходить для оформлення певної позики чи іпотеки, умови якої не змінюються.

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

⭐️ Крок 1: Налаштуйте інформацію про позику та таблицю амортизації

1. Введіть відповідну інформацію про позику, таку як річна процентна ставка, термін позики в роках, кількість платежів на рік і сума позики, у клітинки, як показано на знімку екрана:
   
2. Потім створіть таблицю амортизації в Excel із зазначеними мітками, такими як Період, Платіж, Відсотки, Основна сума, Залишок у клітинках A7:E7.
3. У стовпці «Період» введіть найбільшу кількість платежів, яку ви можете розглянути для будь-якої позики, наприклад, заповніть числа в діапазоні від 1 до 360. Це може покривати стандартну 30-річну позику, якщо ви робите щомісячні платежі.
   

⭐️ Крок 2: змініть формули платежу, відсотків і основної суми за допомогою функції IF

Введіть наведені нижче формули у відповідні комірки, а потім перетягніть маркер заповнення, щоб розширити ці формули до максимальної кількості встановлених періодів платежу.

● Формула оплати:

Зазвичай для розрахунку платежу використовується функція PMT. Щоб включити оператор IF, синтаксична формула така:

Отже, формули такі:

=IF(A7<=$B$2*$B$3, -PMT($B$1/$B$3, $B$2*$B$3, $B$4), "")

● Формула відсотка:

Синтаксична формула:

Отже, формули такі:

=IF(A7<=$B$2*$B$3,-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

● Принципова формула:

Синтаксична формула:

Отже, формули такі:

=IF(A7<=$B$2*$B$3,-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

⭐️ Крок 3: Налаштуйте формулу залишку

Щоб отримати залишок, ви зазвичай можете відняти основну суму від попереднього балансу. За допомогою оператора IF змініть його так:

● Перша клітинка балансу: (E7)

=B4-D7

● Друга комірка балансу: (E8)

=IF(A8<=$B$2*$B$3, E7-D8, "") 

⭐️ Крок 4: Зробіть резюме кредиту

Після того, як ви налаштували графік амортизації за допомогою змінених формул, наступним кроком є ​​створення підсумку позики.

● Щоб розрахувати загальні платежі:

=SUM(B7:B366)

● Щоб розрахувати загальну суму відсотків:

=SUM(C7:C366)

⭐️ Результат:

Тепер у вас є вичерпний і динамічний графік амортизації в Excel разом із детальним підсумком позики. Щоразу, коли ви коригуєте термін періоду платежу, весь графік амортизації автоматично оновлюється відповідно до цих змін. Дивіться демонстрацію нижче:

Створіть графік амортизації з доплатами

Здійснюючи додаткові платежі понад заплановані, кредит можна погасити швидше. Створений у Excel графік амортизації, який включає додаткові платежі, демонструє, як ці додаткові платежі можуть прискорити погашення кредиту та зменшити загальну суму сплачених відсотків. Ось як це можна налаштувати:

⭐️ Крок 1: Налаштуйте інформацію про позику та таблицю амортизації

1. Введіть відповідну інформацію про позику, таку як річна відсоткова ставка, термін позики в роках, кількість платежів на рік, сума позики та додатковий платіж у клітинки, як показано на знімку екрана:
   
2. Потім розрахуйте плановий платіж.
   Крім вхідних комірок, для наших подальших обчислень потрібна ще одна попередньо визначена комірка - сума запланованого платежу. Це регулярна сума платежу за кредитом за умови відсутності додаткових платежів. Застосуйте таку формулу до клітинки B6:

   =IFERROR(-PMT($B$1/$B$3, $B$2*$B$3, $B$4),"")

   
   
3. Потім створіть таблицю амортизації в Excel:
   • Установіть вказані мітки, такі як Період, Плановий платіж, Додатковий платіж, Загальний платіж, Відсотки, Основна сума, Залишок у клітинках A8:G8;
   • У стовпці «Період» введіть найбільшу кількість платежів, яку ви можете розглянути для будь-якої позики. Наприклад, введіть цифри від 0 до 360. Це може покрити стандартну 30-річну позику, якщо ви робите щомісячні платежі;
   • Для періоду 0 (рядок 9 у нашому випадку) отримайте значення балансу за допомогою цієї формули = B4, що відповідає початковій сумі кредиту. Усі інші клітинки в цьому рядку слід залишити порожніми.
   • 

⭐️ Крок 2: Створення формул для графіка амортизації з доплатами

Одну за одною введіть наступні формули у відповідні комірки. Щоб покращити обробку помилок, ми вкладаємо цю та всі майбутні формули у функцію IFERROR. Цей підхід допомагає уникнути багатьох потенційних помилок, які можуть виникнути, якщо будь-яка клітинка введення залишиться порожньою або містить неправильні значення.

● Розрахувати запланований платіж:

Введіть таку формулу в клітинку B10:

=IFERROR(IF($B$6<=G9, $B$6, G9+G9*$B$1/$B$3), "")

● Розрахувати доплату:

Введіть таку формулу в клітинку C10:

=IFERROR(IF($B$5<G9-E10,$B$5, G9-E10), "")

● Розрахуйте загальну суму платежу:

Введіть таку формулу в клітинку D10:

=IFERROR(B10+C10, "")

● Обчисліть основну суму:

Введіть таку формулу в клітинку E10:

=IFERROR(IF(B10>0, MIN(B10-F10, G9), 0), "")

● Розрахуйте відсотки:

Введіть таку формулу в клітинку F10:

=IFERROR(IF(B10>0, $B$1/$B$3*G9, 0), "")

● Розрахувати залишок

Введіть таку формулу в клітинку G10:

=IFERROR(IF(G9 >0, G9-E10-C10, 0), "")

Заповнивши кожну з формул, виберіть діапазон клітинок B10:G10 і використовуйте маркер заповнення, щоб перетягнути та розширити ці формули вниз через увесь набір платіжних періодів. Для будь-яких невикористаних періодів клітинки відображатимуть 0 с. Перегляньте скріншот:

⭐️ Крок 3: Зробіть резюме кредиту

● Отримайте заплановану кількість платежів:

=B2:B3

● Отримати фактичну кількість платежів:

=COUNTIF(D10:D369,">"&0)

● Отримайте загальну суму додаткових платежів:

=SUM(C10:C369)

● Отримайте загальний відсоток:

=SUM(F10:F369)

⭐️ Результат:

Виконуючи ці дії, ви створюєте динамічний графік амортизації в Excel, який враховує додаткові платежі.

Створіть графік амортизації за допомогою шаблону Excel

Створення графіка амортизації в Excel за допомогою шаблону є простим і економним методом. Excel містить вбудовані шаблони, які автоматично обчислюють відсотки, основну суму та залишок кожного платежу. Ось як створити графік амортизації за допомогою шаблону Excel:

1. Натисніть філе > Нові, у полі пошуку введіть графік амортизації, і натисніть Що натомість? Створіть віртуальну версію себе у ключ. Потім виберіть шаблон, який найкраще відповідає вашим потребам, клацнувши його. Наприклад, тут я виберу шаблон простого кредитного калькулятора. Перегляньте скріншот:
   
2. Вибравши шаблон, натисніть на Створювати кнопку, щоб відкрити її як нову книгу.
3. Потім введіть власні дані кредиту, шаблон має автоматично розрахувати та заповнити графік на основі ваших введених даних.
4. Нарешті збережіть нову книгу графіка амортизації.