Vi kan bruge Excels PMT-, IPMT- og IF-formler til at oprette en gældsplan. For det første er vi nødt til at oprette modellen ved at indtaste nogle gældsforudsætninger. I dette eksempel antager vi, at gælden er $ 5.000.000, betalingsperioden er 5 år, og renten Rentesats En rentesats henviser til det beløb, som en långiver opkræver en låntager for enhver form for gæld, der generelt gives, udtrykt som en procentdel af hovedstolen. at være 4,5%.
1. Åbningsbalancen i vores gældsplan er lig med lånebeløbet på $ 5 millioner, så i celle E29 indtaster vi = B25 for at linke det til antagelsesinputet. Derefter kan vi bruge PMT-formlen til at beregne den samlede betaling for den første periode = PMT ($ B $ 27, $ B $ 26, $ B $ 25) . Formlen beregner betalingsbeløbet ved hjælp af lånebeløbet, løbetiden og renten angivet i afsnittet om antagelse.
2. Indtast den periode, vi er i celle E28, som er 1. I celle E29 skal du indtaste = E28 + 1 og udfylde formlen til højre. Brug derefter IPMT-formlen til at finde ud af rentebetalingen for den første periode = IPMT ($ B $ 27, E28, $ B $ 26, $ B $ 25) .
3. Hovedbetalingen er forskellen mellem den samlede betaling og rentebetalingen, som er = E30-E31 . Den afsluttende saldo er åbningssaldoen plus den primære betaling, der foretages, hvilket er = E29 + E32 . Åbningsbalancen for periode 2 er den afsluttende saldo for periode 1, som er = E33 .
4. Kopier alle formler fra celle E29 til E33 til næste kolonne, og kopier derefter alt til højre. Kontroller, om den afsluttende saldo for periode 5 = 0 er for at sikre, at de korrekte formler og tal bruges.
5. Bemærk, at der er nogle fejlmeddelelser startende fra periode 6, fordi åbningsbalancen er 0. Her kan vi bruge IF-funktionen til at rense fejlene. I celle E30 skal du skrive = IF (E29> 0, PMT ($ B $ 27, $ B $ 26, $ B $ 25), 0) . Formlen siger, at hvis åbningssaldoen er mindre end 0, vises den samlede betalingsværdi som 0.
6. I celle 31 skal du skrive = IF (E29> 0, IPMT ($ B $ 27, E28, $ B $ 26, $ B $ 25), 0) . Denne formel svarer til den foregående, som siger, at hvis åbningssaldoen er mindre end 0, vises rentebetalingen som 0.
7. Kopiér celle E30 og E31, tryk på SKIFT + højrepil og derefter CTRL + R for at udfylde højre. Du skal se, at alle fejlmeddelelser nu vises som 0.
XNPV en XIRR med DATE- og IF-funktioner
Vi kan beregne NPV og IRR baseret på specifikke datoer ved hjælp af Excel-funktionerne XNPV og XIRR med DATE- og IF-funktionerne.
8. Gå til celle E6, og indtast = DATO (E5,12,31) for at få vist datoen. Kopier til højre. Du vil se #VÆRDI! meddelelse efter 2021. Vi kan løse dette ved at bruge IFERROR-funktionen = FEJL (DATO (E5,12,31), ””) .
9. Nu kan vi begynde at beregne NPV og IRR. Først skal vi indtaste de frie pengestrømsbeløb. Vi antager, at FCF-beløbene fra periode 1 til 5 er -1.000, 500, 600, 700, 900. I celle C37 indtaster vi en diskonteringsrente på 15%. I celle B37 beregnes NPV ved hjælp af XNPV-formlen = XNPV (C37, E35: I35, E6: I6) .
10. I celle B38 beregnes IRR ved hjælp af XIRR-formel = XIRR (E35: I35, E6: I6) .
Tilføjelse af OFFSET til XNPV og XIRR
Vi kan skifte til XNPV- og XIRR-formlerne for at skabe mere dynamiske formler ved hjælp af OFFSET-funktionen.
11. I celle B42 skal du ændre formlen til = XNPV (C42, E40: OFFSET (E40,0, $ F $ 3-1), E6: I6) . Formlen er mere dynamisk, for hvis antallet af perioder stiger, vil perioderne med fri pengestrøm også stige. Vi behøver ikke at ændre NPV-formlen, hvis prognoseperioden er længere. For IRR-funktionen skal du ændre den til = XIRR (E40: OFFSET (E40,0, $ F $ 3-1), E6: I6) .
12. Efter at have justeret formlen for antallet af perioder, skal vi udligne datoerne. I celle B42 skal du ændre formlen til = XNPV (C42, E40: OFFSET (E40,0, $ F $ 3-1), E6: OFFSET (E6,0, $ F $ 3-1)) . Dette gør det muligt for NPV- og IRR-formlerne at hente det rigtige antal frie pengestrømme med ændringen i antallet af perioder.
Oversigt over vigtige formler for gældsplan
- PMT-formel til beregning af gældsbeløb: = PMT (rentesats, antal vilkår, nutidsværdi)
- IPMT-formel til beregning af rentebetaling: = IPMT (rentesats, periode, antal vilkår, nutidsværdi)
- XNPV-formel til at finde den nuværende nutidsværdi: = XNPV (diskonteringsrente, frie pengestrømme, datoer)
- XIRR-formel til at finde den interne afkast: = XIRR (frie pengestrømme, datoer)
- OFFSET formel til beregning af dynamisk NPV: = XNPV (diskonteringsrente, 1. FCF: OFFSET (1. FCF, 0, # perioder - 1), 1. dato: OFFSET (1. dato, 0, # perioder - 1))
- OFFSET formel til beregning af dynamisk IRR: = XIRR (1. FCF: OFFSET (1. FCF, 0, # perioder - 1), 1. dato: OFFSET (1. dato, 0, # perioder - 1))
Andre ressourcer
Tak, fordi du læste Finansvejledning om gældsplan med PMT-, IPMT- og IF-formler. For at fortsætte med at lære og fremme din karriere vil følgende finansressourcer være nyttige:
- Grundlæggende Excel-formler Grundlæggende Excel-formler At beherske grundlæggende Excel-formler er afgørende for begyndere at være dygtige i økonomisk analyse. Microsoft Excel betragtes som branchens standard software i dataanalyse. Microsofts regnearkprogram er tilfældigvis også en af de mest foretrukne software fra investeringsbankfolk
- Best Practices for finansiel modellering Best Practices for finansiel modellering Denne artikel skal give læsere information om bedste praksis for økonomisk modellering og en let at følge, trin-for-trin guide til opbygning af en finansiel model.
- Liste over Excel-funktioner Funktioner Liste over de vigtigste Excel-funktioner for finansanalytikere. Dette snydeark dækker 100'ere af funktioner, der er vigtige at kende som en Excel-analytiker
- Oversigt over Excel-genveje Excel-genveje Oversigt Excel-genveje er en overset metode til at øge produktiviteten og hastigheden i Excel. Excel-genveje tilbyder finansanalytikeren et kraftfuldt værktøj. Disse genveje kan udføre mange funktioner. så simpelt som at navigere i regnearket for at udfylde formler eller gruppere data.