Можем да използваме формулите на PMT, IPMT и IF на Excel, за да създадем график на дълга. Първо, трябва да настроим модела, като въведем някои предположения за дълга. В този пример приемаме, че дългът е 5 000 000 щатски долара, срокът на плащане е 5 години и лихвеният процент Лихвен процент Лихвеният процент се отнася до сумата, начислена от заемодателя на кредитополучател за каквато и да е форма на дълг, обикновено изразен като процент от главницата. да бъде 4,5%.
1. Началният баланс в нашия график на дълга е равен на сумата на заема от 5 милиона долара, така че в клетка E29 въвеждаме = B25, за да го свържем с входа на предположението. След това можем да използваме формулата PMT, за да изчислим общото плащане за първия период = PMT ($ B $ 27, $ B $ 26, $ B $ 25) . Формулата изчислява сумата на плащането, като използва сумата на заема, срока и лихвения процент, посочени в раздела за приемане.
2. В клетка E28 въведете периода, в който се намираме, който е 1. В клетка E29 въведете = E28 + 1 и попълнете формулата вдясно. След това използвайте формулата IPMT, за да разберете лихвеното плащане за първия период = IPMT ($ B $ 27, E28, $ B $ 26, $ B $ 25) .
3. Главното плащане е разликата между общото плащане и плащането на лихви, което е = E30-E31 . Крайното салдо е началното салдо плюс главницата, която се извършва, което е = E29 + E32 . Началното салдо за период 2 е крайното салдо за период 1, което е = E33 .
4. Копирайте всички формули от клетка E29 до E33 в следващата колона, след което копирайте всичко вдясно. Проверете дали затварящото салдо за период 5 = 0, за да се уверите, че се използват правилни формули и числа.
5. Забележете, че има съобщения за грешки, започващи от период 6, защото началното салдо е 0. Тук можем да използваме функцията IF, за да почистим грешките. В клетка E30 въведете = IF (E29> 0, PMT ($ B $ 27, $ B $ 26, $ B $ 25), 0) . Формулата гласи, че ако началното салдо е по-малко от 0, тогава общата стойност на плащането ще бъде показана като 0.
6. В клетка 31 въведете = IF (E29> 0, IPMT ($ B $ 27, E28, $ B $ 26, $ B $ 25), 0) . Тази формула е подобна на предишната, която гласи, че ако началното салдо е по-малко от 0, тогава плащането на лихви ще бъде показано като 0.
7. Копирайте клетките E30 и E31, натиснете SHIFT + стрелка надясно, след това CTRL + R, за да попълните надясно. Трябва да видите, че всички съобщения за грешка сега се показват като 0.
XNPV и XIRR с функции DATE и IF
Можем да изчислим NPV и IRR въз основа на конкретни дати, като използваме функциите на Excel XNPV и XIRR с функциите DATE и IF.
8. Отидете до клетка E6 и въведете = DATE (E5,12,31), за да се покаже датата. Копирайте вдясно. Ще видите #VALUE! съобщение след 2021 г. Можем да поправим това, като използваме функцията IFERROR = IFERROR (ДАТА (E5,12,31), ””) .
9. Сега можем да започнем да изчисляваме NPV и IRR. Първо, трябва да въведем сумите на свободния паричен поток. Предполагаме, че сумите на FCF от период 1 до 5 са -1 000, 500, 600, 700, 900. В клетка C37 ще въведем дисконтов процент от 15%. В клетка B37 изчислете NPV, като използвате формулата XNPV = XNPV (C37, E35: I35, E6: I6) .
10. В клетка B38 изчислете IRR, като използвате формула XIRR = XIRR (E35: I35, E6: I6) .
Добавяне на OFFSET към XNPV и XIRR
Можем да преминем към формулите XNPV и XIRR, за да направим по-динамични формули, използвайки функцията OFFSET.
11. В клетка B42 променете формулата на = XNPV (C42, E40: OFFSET (E40,0, $ F $ 3-1), E6: I6) . Формулата е по-динамична, защото ако броят на периодите се увеличи, периодите на свободен паричен поток също ще се увеличат. Не е необходимо да променяме формулата на NPV, ако прогнозният период е по-дълъг. За функцията IRR го променете на = XIRR (E40: OFFSET (E40,0, $ F $ 3-1), E6: I6) .
12. След коригиране на формулата за броя на периодите, трябва да компенсираме датите. В клетка B42 променете формулата на = XNPV (C42, E40: OFFSET (E40,0, $ F $ 3-1), E6: OFFSET (E6,0, $ F $ 3-1)) . Това позволява на формулите NPV и IRR да избират точния брой свободен паричен поток с промяната в броя на периодите.
Резюме на формулите за график на ключови дългове
- Формула PMT за изчисляване на размера на плащането на дълга: = PMT (лихвен процент, брой условия, настояща стойност)
- IPMT формула за изчисляване на лихвено плащане: = IPMT (лихвен процент, период, брой срокове, настояща стойност)
- Формула XNPV за намиране на нетната настояща стойност: = XNPV (дисконтов процент, свободни парични потоци, дати)
- XIRR формула за намиране на вътрешната норма на възвръщаемост: = XIRR (свободни парични потоци, дати)
- Формула OFFSET за изчисляване на динамична NPV: = XNPV (дисконтов процент, 1-ва FCF: OFFSET (1-ва FCF, 0, # периоди - 1), 1-ва дата: OFFSET (1-ва дата, 0, # периоди - 1))
- Формула OFFSET за изчисляване на динамична IRR: = XIRR (1-ва FCF: OFFSET (1-ва FCF, 0, # периоди - 1), 1-ва дата: OFFSET (1-ва дата, 0, # точки - 1))
Други ресурси
Благодарим Ви, че прочетохте ръководството на Finance относно Графика на дълга с формули PMT, IPMT и IF. За да продължите да учите и напредвате в кариерата си, следните финансови ресурси ще ви бъдат полезни:
- Основни формули на Excel Основни формули на Excel Овладяването на основните формули на Excel е от решаващо значение за начинаещите, за да станат специалисти във финансовия анализ. Microsoft Excel се счита за индустриален стандартен софтуер за анализ на данни. Програмата за електронни таблици на Microsoft също е един от най-предпочитаните софтуерни средства от инвестиционните банкери
- Най-добри практики за финансово моделиране Най-добри практики за финансово моделиране Тази статия е да предостави на читателите информация за най-добрите практики за финансово моделиране и лесно за проследяване, поетапно ръководство за изграждане на финансов модел.
- Списък на функциите на Excel Функции Списък на най-важните функции на Excel за финансовите анализатори. Този измамен лист обхваща 100-те функции, които е критично важно да знаете като анализатор на Excel
- Общ преглед на преки пътища на Excel Преглед на преки пътища Преглед на преки пътища Excel е пренебрегван метод за увеличаване на производителността и скоростта в Excel. Преките пътища на Excel предлагат на финансовия анализатор мощен инструмент. Тези преки пътища могат да изпълняват много функции. толкова просто, колкото навигацията в електронната таблица за попълване на формули или групиране на данни.