Za izradu rasporeda duga možemo koristiti Excel-ove formule PMT, IPMT i IF. Prvo, moramo postaviti model unošenjem nekih pretpostavki duga. U ovom primjeru pretpostavljamo da je dug 5.000.000 USD, rok plaćanja 5 godina, a kamatna stopa Kamatna stopa Kamatna stopa odnosi se na iznos koji zajmodavac naplaćuje zajmoprimcu za bilo koji oblik danog duga, općenito izražen kao postotak glavnice. biti 4,5%.
1. Početno stanje u našem rasporedu duga jednako je iznosu zajma od 5 milijuna USD, tako da u ćeliju E29 unosimo = B25 da bismo ga povezali s ulazom pretpostavke. Zatim, pomoću PMT formule možemo izračunati ukupnu uplatu za prvo razdoblje = PMT (27 USD, 26 USD, 25 USD) . Formula izračunava iznos plaćanja pomoću iznosa zajma, roka i kamatne stope navedene u odjeljku pretpostavke.
2. U ćeliju E28 unesite razdoblje u kojem se nalazimo, a to je 1. U ćeliju E29 unesite = E28 + 1 i ispunite formulu s desne strane. Dalje, upotrijebite IPMT formulu da biste saznali plaćanje kamata za prvo razdoblje = IPMT (27 B $, E28, B $ 26, 25 B $ 25) .
3. Uplata glavnice je razlika između ukupne isplate i isplate kamata, koja iznosi = E30-E31 . Završni saldo je početni saldo plus plaćena glavnica koja je = E29 + E32 . Početno stanje za razdoblje 2 je završno stanje za razdoblje 1, što je = E33 .
4. Kopirajte sve formule iz ćelije E29 u E33 u sljedeći stupac, a zatim kopirajte sve udesno. Provjerite je li završno stanje za razdoblje 5 = 0 kako biste bili sigurni da se koriste ispravne formule i brojevi.
5. Primijetite da postoje neke poruke o pogreškama počevši od razdoblja 6, jer je početno stanje 0. Ovdje možemo koristiti funkciju IF za čišćenje pogrešaka. U ćeliju E30 upišite = IF (E29> 0, PMT ($ B $ 27, $ B $ 26, $ B $ 25), 0) . Formula kaže da će, ako je početno stanje manje od 0, ukupna vrijednost plaćanja biti prikazana kao 0.
6. U ćeliju 31 upišite = IF (E29> 0, IPMT ($ 27, E28, $ 26, $ 25), 0) . Ova je formula slična prethodnoj, koja kaže da će, ako je početno stanje manje od 0, plaćanje kamata biti prikazano kao 0.
7. Kopirajte ćelije E30 i E31, pritisnite SHIFT + strelica udesno, a zatim CTRL + R za popunjavanje udesno. Trebali biste vidjeti da su sve poruke o pogreškama sada prikazane kao 0.
XNPV i XIRR s funkcijama DATE i IF
NPV i IRR možemo izračunati na temelju određenih datuma pomoću Excel funkcija XNPV i XIRR s funkcijama DATUM i IF.
8. Idite u ćeliju E6 i unesite = DATUM (E5,12,31) za prikaz datuma. Kopiraj udesno. Vidjet ćete #Vrijednost! poruka nakon 2021. To možemo popraviti pomoću funkcije IFERROR = IFERROR (DATUM (E5,12,31), ””) .
9. Sada možemo početi izračunavati NPV i IRR. Prvo, moramo unijeti iznose slobodnog novčanog toka. Pretpostavljamo da su iznosi FCF-a od razdoblja 1 do 5 -1000, 500, 600, 700, 900. U ćeliju C37 uvest ćemo diskontnu stopu od 15%. U ćeliji B37 izračunajte NPV koristeći XNPV formulu = XNPV (C37, E35: I35, E6: I6) .
10. U ćeliji B38 izračunajte IRR koristeći XIRR formulu = XIRR (E35: I35, E6: I6) .
Dodavanje OFFSET u XNPV i XIRR
Možemo prijeći na XNPV i XIRR formule kako bismo napravili dinamičnije formule pomoću funkcije OFFSET.
11. U ćeliji B42 promijenite formulu u = XNPV (C42, E40: OFFSET (E40,0, $ F $ 3-1), E6: I6) . Formula je dinamičnija, jer ako se broj razdoblja poveća, tada će se povećati i razdoblja slobodnog novčanog toka. Ne trebamo mijenjati formulu NPV ako je razdoblje predviđanja duže. Za IRR funkciju promijenite je u = XIRR (E40: OFFSET (E40,0, $ F $ 3-1), E6: I6) .
12. Nakon prilagođavanja formule za broj razdoblja, trebali bismo pomaknuti datume. U ćeliji B42 promijenite formulu u = XNPV (C42, E40: OFFSET (E40,0, $ F $ 3-1), E6: OFFSET (E6,0, $ F $ 3-1)) . To omogućuje formulama NPV i IRR da odaberu pravi broj slobodnog novčanog tijeka s promjenom broja razdoblja.
Sažetak formula rasporeda ključnih dugova
- PMT formula za izračunavanje iznosa plaćanja duga: = PMT (kamatna stopa, broj rokova, sadašnja vrijednost)
- IPMT formula za izračunavanje isplate kamata: = IPMT (kamatna stopa, razdoblje, broj rokova, sadašnja vrijednost)
- XNPV formula za pronalaženje neto sadašnje vrijednosti: = XNPV (diskontna stopa, slobodni novčani tijekovi, datumi)
- XIRR formula za pronalaženje interne stope povrata: = XIRR (slobodni novčani tijekovi, datumi)
- OFFSET formula za izračunavanje dinamičkog NPV: = XNPV (diskontna stopa, 1. FCF: OFFSET (1. FCF, 0, # razdoblja - 1), 1. datum: OFFSET (1. datum, 0, # razdoblja - 1))
- OFFSET formula za izračunavanje dinamičke IRR: = XIRR (1. FCF: OFFSET (1. FCF, 0, # točka - 1), 1. datum: OFFSET (1. datum, 0, # točka - 1))
Ostali izvori
Zahvaljujemo što ste pročitali Financijski vodič o rasporedu dugova s formulama PMT, IPMT i IF. Kako biste nastavili učiti i napredovati u karijeri, sljedeći financijski izvori će vam biti od pomoći:
- Osnovne Excel formule Osnovne Excel formule Ovladavanje osnovnim Excel formulama presudno je za početnike da postanu vješti u financijskoj analizi. Microsoft Excel smatra se industrijskim standardnim softverom u analizi podataka. Microsoftov program proračunskih tablica također je jedan od najpoželjnijih softvera investicijskih bankara
- Najbolje prakse financijskog modeliranja Najbolji postupci financijskog modeliranja Ovaj članak daje čitateljima informacije o najboljim praksama financijskog modeliranja i jednostavan vodič za izgradnju financijskog modela.
- Popis funkcija programa Excel Popis najvažnijih funkcija programa Excel za financijske analitičare. Ova varalica pokriva stotine funkcija koje je presudno znati kao Excel analitičara
- Pregled prečaca za Excel Pregled prečaca za Excel Pregled Prečaci za Excel previdjena su metoda povećanja produktivnosti i brzine u programu Excel. Prečaci za Excel nude financijskom analitičaru moćan alat. Ovi prečaci mogu izvršavati mnoge funkcije. jednostavno poput navigacije unutar proračunske tablice do popunjavanja formula ili grupiranja podataka.