Зміст
- Розуміння вашої іпотеки
- Розрахуйте щомісячний платіж
- Розрахуйте річну процентну ставку
- Визначення тривалості позики
- Зняття позики
- Обчислення позики в Excel
- Амортизація позики
- Створення графіку позики
Погашення позики - це акт повернення грошей, які раніше були позичені у позикодавця, як правило, через низку періодичних платежів, що включають основну плюс відсотки. Чи знаєте ви, що можете скористатися програмною програмою Excel для розрахунку погашення кредиту?
Ця стаття - покрокове керівництво по налаштуванню розрахунків за кредитами.
Ключові вивезення
- Використовуйте Excel, щоб отримати обробку своєї іпотечної кредити шляхом визначення щомісячного платежу, процентної ставки та вашого кредитного графіку. Ви можете більш глибоко ознайомитись з розбиткою кредиту за допомогою excel та створити графік погашення, який підходить для вас. Існують розрахунки для кожного кроку, який ви можете налаштувати, щоб задовольнити ваші конкретні потреби. Поступово знижуючи та вивчаючи кредит, ви можете зробити процес погашення поганим і більш керованим.
Розуміння вашої іпотеки
Використовуючи Excel, ви можете краще зрозуміти свою іпотеку в три простих кроки. Перший крок визначає щомісячний платіж. Другий крок обчислює процентну ставку, а третій крок визначає графік позики.
Ви можете побудувати таблицю в Excel, яка розповість про процентну ставку, розрахунок позики протягом тривалості позики, депозит позики, амортизацію та щомісячний платіж.
Розрахуйте щомісячний платіж
По-перше, ось як розрахувати щомісячний платіж за іпотеку. Використовуючи річну процентну ставку, основну суму та тривалість, ми можемо визначити суму, що підлягає погашенню щомісяця.
Формула, як показано на скріншоті вище, записується так:
= -PMT (швидкість; довжина; теперішнє значення;;)
Знак мінус перед ПМТ необхідний, оскільки формула повертає від’ємне число. Перші три аргументи - це ставка позики, тривалість позики (кількість періодів) та основна сума позики. Останні два аргументи необов’язкові, залишкове значення за замовчуванням до нуля; сплачується заздалегідь (за один) або наприкінці (за нуль), також необов’язково.
Формула 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;;)
Перші три аргументи - це тривалість позики (кількість періодів), щомісячний платіж за повернення позики та основна позика. Останні три аргументи необов’язкові, а залишкове значення за замовчуванням до нуля; термін аргумент для керування строком погашення заздалегідь (для одного) або наприкінці (для нуля) також необов'язковий. Нарешті, аргумент оцінки необов’язковий, але може дати початкову оцінку ставки.
Формула Excel, яка використовується для обчислення ставки позики:
= RATE (12 * B4; -B2; B3) = RATE (12 * 13; -960; 120000)
Примітка: відповідним даним у щомісячному платежі повинно бути вказано негативний знак. Ось чому перед формулою є знак мінус. Період ставок становить 0, 294%.
Ми використовуємо формулу = (1 + B5) - 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 дол. США на загальну суму щомісячної суми 1, 161, 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 періодів наша позика погашається.
