Razpored dolgov s formulami PMT, IPMT in IF - vodnik in primeri

Za ustvarjanje urnika dolgov lahko uporabimo Excelove formule PMT, IPMT in IF. Najprej moramo model postaviti z vnosom nekaterih predpostavk o dolgu. V tem primeru predpostavljamo, da znaša dolg 5.000.000 USD, rok plačila 5 let in obrestna mera Obrestna mera Obrestna mera se nanaša na znesek, ki ga posojilojemalec zaračuna posojilojemalcu za katero koli obliko dolga, splošno izraženo kot odstotek glavnice. 4,5%.

1. Začetno stanje v našem razporedu dolgov je enako znesku posojila v višini 5 milijonov USD, zato v celico E29 vnesemo = B25, da ga povežemo z vnosom predpostavke. Nato lahko s formulo PMT izračunamo skupno plačilo za prvo obdobje = PMT ($ 27 USD, $ 26 USD, $ 25 USD) . Formula izračuna znesek plačila z uporabo zneska posojila, roka in obrestne mere, navedene v poglavju o prevzemu.

Razpored dolgov

2. V celico E28 vnesite obdobje, v katerem smo, to je 1. V celico E29 vnesite = E28 + 1 in vnesite formulo na desno. Nato uporabite formulo IPMT, da ugotovite plačilo obresti za prvo obdobje = IPMT ($ 27, E28, $ 26, $ 25 $) .

3. Plačilo glavnice je razlika med celotnim plačilom in plačilom obresti, ki je = E30-E31 . Zaključno stanje je začetno stanje plus plačilo glavnice, ki je = E29 + E32 . Začetno stanje za obdobje 2 je končno stanje za obdobje 1, ki je = E33 .

4. Kopirajte vse formule iz celice E29 v E33 v naslednji stolpec in nato kopirajte vse na desno. Preverite, ali je končno stanje za obdobje 5 = 0, da se prepričate, da se uporabljajo pravilne formule in številke.

5. Opazite, da se od 6. obdobja pojavlja nekaj sporočil o napakah, ker je začetno stanje 0. Tu lahko uporabimo funkcijo IF za čiščenje napak. V celico E30 vnesite = IF (E29> 0, PMT ($ B $ 27, $ B $ 26, $ B $ 25), 0) . Formula navaja, da če je začetno stanje manjše od 0, bo skupna vrednost plačila prikazana kot 0.

6. V celico 31 vnesite = IF (E29> 0, IPMT ($ B $ 27, E28, $ B $ 26, $ B $ 25), 0) . Ta formula je podobna prejšnji, ki pravi, da če je začetno stanje manjše od 0, bo plačilo obresti prikazano kot 0.

7. Kopirajte celici E30 in E31, pritisnite SHIFT + puščico desno in nato CTRL + R, da zapolnite desno. Videti bi morali, da so vsa sporočila o napakah zdaj prikazana kot 0.

XNPV XIRR s funkcijama DATE in IF

NPV in IRR lahko izračunamo na podlagi določenih datumov z uporabo Excelovih funkcij XNPV in XIRR s funkcijama DATUM in IF.

8. Pojdite v celico E6 in vnesite = DATE (E5,12,31), da prikažete datum. Kopiraj na desno. Videli boste #Vrednost! sporočilo po letu 2021. To lahko popravimo s funkcijo IFERROR = IFERROR (DATUM (E5,12,31), ””) .

9. Zdaj lahko začnemo izračunavati NPV in IRR. Najprej moramo vnesti zneske prostega denarnega toka. Predvidevamo, da so zneski FCF od 1. do 5. leta -1000, 500, 600, 700, 900. V celico C37 bomo vnesli diskontno stopnjo 15%. V celici B37 izračunajte NPV z uporabo formule XNPV = XNPV (C37, E35: I35, E6: I6) .

10. V celici B38 izračunajte IRR z uporabo formule XIRR = XIRR (E35: I35, E6: I6) .

Dodajanje OFFSET v XNPV in XIRR

Lahko uporabimo formuli XNPV in XIRR, da s pomočjo funkcije OFFSET naredimo bolj dinamične formule.

11. V celici B42 spremenite formulo na = XNPV (C42, E40: OFFSET (E40,0, $ F $ 3-1), E6: I6) . Formula je bolj dinamična, ker se bo, če se bo število obdobij povečalo, povečala tudi obdobja prostega denarnega toka. Če je napovedno obdobje daljše, nam ni treba spreminjati formule NPV. Za funkcijo IRR jo spremenite v = XIRR (E40: OFFSET (E40,0, $ F $ 3-1), E6: I6) .

12. Po prilagoditvi formule za število obdobij bi morali nadomestiti datume. V celici B42 spremenite formulo na = XNPV (C42, E40: OFFSET (E40,0, $ F $ 3-1), E6: OFFSET (E6,0, $ F $ 3-1)) . To omogoča, da formuli NPV in IRR izbereta pravo število prostega denarnega toka s spremembo števila obdobij.

Povzetek formul seznama ključnih dolgov

  • Formula PMT za izračun zneska plačila dolga: = PMT (obrestna mera, število pogojev, sedanja vrednost)
  • Formula IPMT za izračun plačila obresti: = IPMT (obrestna mera, obdobje, število pogojev, sedanja vrednost)
  • Formula XNPV za iskanje neto sedanje vrednosti: = XNPV (diskontna stopnja, prosti denarni tokovi, datumi)
  • XIRR formula za iskanje notranje stopnje donosa: = XIRR (prosti denarni tokovi, datumi)
  • Formula OFFSET za izračun dinamične NPV: = XNPV (diskontna stopnja, 1. FCF: OFFSET (1. FCF, 0, # obdobja - 1), 1. datum: OFFSET (1. datum, 0, # obdobja - 1))
  • Formula OFFSET za izračun dinamičnega IRR: = XIRR (1. FCF: OFFSET (1. FCF, 0, # obdobja - 1), 1. datum: OFFSET (1. datum, 0, # obdobja - 1))

Drugi viri

Hvala, ker ste prebrali finančni priročnik o časovnem razporedu dolgov s formulami PMT, IPMT in IF. Za nadaljnje učenje in napredovanje v karieri vam bodo v pomoč naslednji finančni viri:

  • Osnovne Excelove formule Osnovne Excelove formule Obvladovanje osnovnih Excelovih formul je ključnega pomena za začetnike, da postanejo strokovnjaki v finančni analizi. Microsoft Excel velja za industrijski standard programske opreme pri analizi podatkov. Microsoftov program za preglednice je tudi ena izmed najbolj priljubljenih programov investicijskih bankirjev
  • Najboljše prakse finančnega modeliranja Najboljše prakse finančnega modeliranja Ta članek ponuja bralcem informacije o najboljših praksah finančnega modeliranja in enostaven vodnik po korakih za izdelavo finančnega modela.
  • Seznam funkcij Excela Funkcije Seznam najpomembnejših funkcij Excela za finančne analitike. Ta goljufiv zajema 100-ih funkcij, ki jih je nujno poznati kot Excel-ov analitik
  • Pregled bližnjic do Excela Pregled bližnjic Excel Pregled bližnjic Excel je spregledan način povečanja produktivnosti in hitrosti v Excelu. Bližnjice v Excelu finančnemu analitiku ponujajo močno orodje. Te bližnjice lahko izvajajo številne funkcije. tako enostavno kot navigacija v preglednici do izpolnjevanja formul ali združevanja podatkov.

Zadnje objave