Як зробити графік погашення заборгованості в excel

На графіку погашення заборгованості буде чітко видно, скільки ще вам тягнути це ярмо, виплачуючи стільки, скільки ви платите зараз. При цьому буде також чітко і наочно видно, скільки ви платите, безпосередньо повертаючи взяту в борг суму, а скільки - кладете в кишеню банкірам у вигляді відсотків. При цьому графік погашення заборгованості можна зробити в Excel. Так що якщо вам хочеться дізнатися, скільки ще платити, але не хочеться платити більше нікому (за складання графіка), то читайте цю статтю!

кроки

1. Запустіть Excel, відкрийте нову книгу.

Зображення з назвою Prepare Amortization Schedule in Excel Step 2

2. В осередках від А1 до А4 напишіть: "Сума позики", "Процентна ставка", "Місяці", "Платежі".

Зображення з назвою Prepare Amortization Schedule in Excel Step 3

3. В осередках від В1 до В3 вкажіть відповідні дані.

Зображення з назвою Prepare Amortization Schedule in Excel Step 4

4. Процентну ставку вкажіть саме як "процентну".

Зображення з назвою Prepare Amortization Schedule in Excel Step 5

5. У осередок В4 введіть без лапок формулу "= PMT ($ B $ 2/12, $ B $ 3, - $ B $ 1,0)" і натисніть на Enter.

Знак $ у формулі означає абсолютне посилання на комірки. Іншими словами, формула буде працювати тільки з конкретно цими осередками, куди б ви її ні скопіювали.

Процентна ставка буде поділена на 12 - адже в році 12 місяців.

Приклад: ви взяли 150 тисяч рублів під 6% річних на 30 років (360 місяців), тоді платити вам 899.33 рублів.

Зображення з назвою Prepare Amortization Schedule in Excel Step 6

6. В осередках від А7 до Н7 напишіть: "Період", "Баланс на початок", "Платіж", "Основний борг", "Відсоток", "Накопичувальний борг", "Накопичувальний відсоток", "Баланс на кінець".

Зображення з назвою Prepare Amortization Schedule in Excel Step 7

7. Заповніть колонку "Період".

Введіть місяць і рік першого платежу в А8. Відформатуйте осередок, якщо треба, щоб та відображала дату як дату.

Виберіть клітинку, клікніть по ній і дотягніть до осередку А367. Головне, не забудьте про опцію "Auto Fill" і виставте її на "Fill Months".

Зображення з назвою Prepare Amortization Schedule in Excel Step 8

8. Заповніть комірки від В8 до Н8.

В В8 напишіть суму боргу на початок вашої епопеї з кредитом / позикою.

У С8 введіть "= $ B $ 4" і натисніть Enter.

У Е8 створіть формулу для підрахунку відповідного параметра, яка виглядає так: "= $ B8 * ($ B $ 2/12)."Значок $ тут вказує на відносне посилання, тобто формула буде працювати для всіх даних в колонці В.

У D8 вам потрібно відняти дані Е8 з С8. Формула вийде такою: "$ C8- $ E8".

У Н8 вам потрібно відняти дані D8 з B8. Формула вийде такою: "$ B8- $ D8".

Зображення з назвою Prepare Amortization Schedule in Excel Step 9

9. Продовжіть таблицю, заповнивши ряд осередків від В9 до Н9.

У В9 потрібно додати відносне посилання на кінець баланс минулого періоду. Для цього введіть у комірку "= $ H8" і натисніть Enter. Скопіюйте комірки С8, D8 і E8, вставте їх у відповідні комірки 9-го рядка. Потім скопіюйте Н8 в Н9. Тут відносні посилання вам дуже знадобляться.

У F9 створіть формулу для підрахунок виплаченого кумулятивного боргу. Формула така: "= $ D9 + $ F8". Аналогічно зробіть для виплаченого кумулятивного відсотка в G9: "= $ E9 + $ G8".

Зображення з назвою Prepare Amortization Schedule in Excel Step 10

10. Завершіть графік.

Виділіть комірки від В9 до Н9, наведіть мишкою на нижній правий кут рамки, поки курсор не стане хрестиком, і тягніть все це вниз до рядка 367. Потім відпустіть кнопку миші.

Головне, не забудьте про опцію "Auto Fill" і виставте її на "Copy Cells". Фінальний підсумковий баланс повинен бути рівний 0 рублів.

Поради

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

Що вам знадобиться

комп`ютер
Microsoft Excel
Дані по позиці