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

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

Зміст

кроки
Відео: Як створити графік роботи в excel
Відео: Як побудувати графік функції в excel
Поради
Що вам знадобиться

кроки

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

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

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

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

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

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

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

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

Зображення з назвою Prepare Amortization Schedule in Excel Step 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

Відео: Як створити графік роботи в excel

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

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

заповніть колонку “період”.

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

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

Зображення з назвою Prepare Amortization Schedule in Excel Step 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.

Відео: Як побудувати графік функції в Excel

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

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

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

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

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

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

Поради

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

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

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

Увага, тільки СЬОГОДНІ!