Сплануйте виплати позики за допомогою формул Excel

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

Ця стаття - покрокове керівництво по налаштуванню розрахунків за кредитами.

Ключові вивезення

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

Розуміння вашої іпотеки

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

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

Розрахуйте щомісячний платіж

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

Формула, як показано на скріншоті вище, записується так:

= -PMT (rate; length; present_value; [future_value]; [type])

Знак мінус перед ПМТ необхідний, оскільки формула повертає від’ємне число. Перші три аргументи - це ставка позики, тривалість позики (кількість періодів) та основна сума позики. Останні два аргументи необов’язкові, залишкове значення за замовчуванням до нуля; сплачується заздалегідь (за один) або наприкінці (за нуль), також необов’язково.

Формула Excel, що використовується для розрахунку щомісячного платежу за кредитом:

= -PMT ((1 + B2) ^ (1/12) -1; B4 * 12; B3) = PMT ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000)

Пояснення: Для ставки ми використовуємо місячну ставку (період ставки), потім обчислюємо кількість періодів (120 за 10 років, помножені на 12 місяців) і, нарешті, вказуємо основну позичку. Наш щомісячний платіж становитиме 1161, 88 доларів за 10 років.

Розрахуйте річну процентну ставку

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

Як показано на скріншоті вище, ми спочатку обчислюємо норму періоду (в нашому випадку щомісяця), а потім річну ставку. Використовувана формула буде RATE, як показано на скріншоті вище. Він пишеться так:

= RATE (Nper; pmt; present_value; [future_value]; [type])

Перші три аргументи - тривалість позики (кількість періодів), щомісячний платіж за повернення позики та основна позика. Останні три аргументи необов’язкові, а залишкове значення за замовчуванням до нуля; термін аргумент для керування строком погашення заздалегідь (для одного) або наприкінці (для нуля) також необов’язковий. Нарешті, аргумент оцінки необов’язковий, але може дати початкову оцінку курсу.

Формула Excel, яка використовується для обчислення ставки позики:

= RATE (12 * B4; -B2; B3) = RATE (12 * 13; -960; 120000)

Примітка. Відповідним даним у щомісячному платежі повинно бути вказано негативний знак. Ось чому перед формулою є знак мінус. Період ставок становить 0, 294%.

Ми використовуємо формулу = (1 + В5) - 12-1 ^ = (1 + 0, 294%) ^ 12-1, щоб отримати річну ставку нашої позики, яка становить 3, 58%. Іншими словами, щоб запозичити 120 000 доларів за 13 років, щоб платити 960 доларів щомісяця, нам слід домовитись про позику за щорічною максимальною ставкою 3, 58%.

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

Визначення тривалості позики

Тепер ми побачимо, як визначити тривалість позики, коли ви знаєте річну ставку, основну позику та щомісячний платіж, який підлягає погашенню. Іншими словами, як довго нам потрібно буде погасити іпотеку в розмірі 120 000 доларів США зі ставкою 3, 10% та щомісячною виплатою 1100 доларів ">

Формула, яку ми будемо використовувати, - NPER, як показано на скріншоті вище, і записана так:

= NPER (ставка; pmt; теперішнє значення; [майбутнє_значення]; [тип])

Перші три аргументи - це річна ставка позики, щомісячний платіж, необхідний для погашення позики, і основна сума позики. Останні два аргументи необов’язкові, залишкове значення за замовчуванням до нуля. Термін аргумент, що сплачується заздалегідь (за один) або в кінці (за нуль), також не є обов'язковим.

= NPER ((1 + B2) ^ (1/12) -1; -B4; B3) = NPER ((1 + 3, 10%) ^ (1/12) -1; -1100; 120000)

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

Ми будемо використовувати формулу = B5 / 12 = 127, 97 / 12 протягом кількох років для завершення погашення позики. Іншими словами, щоб позичити 120 000 доларів США, із річною ставкою 3, 10% та платити 1100 доларів щомісяця, ми повинні погашати строки погашення протягом 128 місяців або 10 років та 8 місяців.

Зняття позики

Виплата за кредитом складається з основної суми та відсотків. Відсотки нараховуються за кожен період - наприклад, щомісячні платежі за 10 років дають нам 120 періодів.

У таблиці вище показано розподіл позики (загальний період, рівний 120) за допомогою формул PPMT та IPMT. Аргументи двох формул однакові і розбиваються наступним чином:

= -PPMT (ставка; число_період; довжина; головне; [залишковий]; [термін])

Аргументи такі ж, як і у вже знайденої формули PMT, за винятком "num_period", який додається для показу періоду, за який можна розбити кредит за умови основної суми та відсотків. Ось приклад:

= -PPMT ((1 + B2) ^ (1/12) -1; 1; B4 * 12; B3) = PPMT ((1 + 3, 10%) ^ (1/12) -1; 1; 10 * 12; 120000)

Результат показаний на знімку екрана "Розклад позики" протягом аналізованого періоду, який є "один". тобто перший період або перший місяць. Ми сплачуємо 1161, 88 дол. США, розбиті на основну суму 856, 20, і відсотки 305, 68 дол.

Обчислення позики в Excel

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

= -CUMPRINC (ставка; довжина; головна; початок_дачі; кінець_дачі; тип)

Ми знаходимо аргументи, швидкість, довжину, основну частину та термін (які є обов'язковими), які ми вже бачили в першій частині з формулою PMT. Але тут нам також потрібні аргументи "start_date" і "end_date". "Start_date" вказує початок періоду, який потрібно аналізувати, а "end_date" вказує кінець періоду, який потрібно аналізувати.

Ось приклад:

= -CUMPRINC ((1 + B2) ^ (1/12) -1; B4 * 12; B3; 1; 12; 0)

Результат показаний на скріншоті "Cumul 1-й рік", тому аналізовані періоди коливаються від одного до 12 першого періоду (першого місяця) до дванадцятого (12-го місяця). За рік ми заплатили б основні $ 10 419, 55 і відсотки 3, 522, 99 долара.

Амортизація позики

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

Створення графіку позики

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

У першому стовпці періоду введіть "1" як перший період, а потім перетягніть клітинку вниз. У нашому випадку нам потрібні 120 періодів, оскільки 10-річна виплата за кредитом, помножена на 12 місяців, дорівнює 120.

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

= -PMT (TP-1; B4 * 12; B3) = -PMT ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000)

Третя колонка - це основна сума, яка буде виплачуватися щомісяця. Наприклад, за 40-й період ми повернемо основну суму 945, 51 дол. США на загальну суму щомісячної суми 1161, 88 дол.

Для обчислення погашеної основної суми ми використовуємо наступну формулу:

= -PPMT (TP; A18; $ B $ 4 * 12; $ B $ 3) = -PPMT ((1 + 3, 10%) ^ (1/12); 1; 10 * 12; 120000)

Четвертий стовпець - відсотки, для яких ми використовуємо формулу для обчислення поверненої основної суми на нашу щомісячну суму, щоб виявити, скільки відсотків потрібно сплатити:

= -INTPER (TP; A18; $ B $ 4 * 12; $ B $ 3) = -INTPER ((1 + 3, 10%) ^ (1/12); 1; 10 * 12; 120000)

П'ятий стовпець містить суму, що залишилася до сплати. Наприклад, після 40-го платежу нам доведеться заплатити 83 994, 69 доларів на 120 000 доларів.

Формула така:

= $ B $ 3 + CUMPRINC (TP; $ B $ 4 * 12; $ B $ 3; 1; A18; 0)

Формула використовує комбінацію основного за період, що передує клітинці, що містить основну позичку. Цей період починає змінюватися, коли ми копіюємо та перетягуємо клітинку вниз. З таблиці нижче видно, що наприкінці 120 періодів наша позика погашається.