Vi kan använda Excels PMT-, IPMT- och IF-formler för att skapa ett skuldschema. Först måste vi ställa in modellen genom att ange några skuldantaganden. I det här exemplet antar vi att skulden är $ 5 000 000, betalningstiden är 5 år och räntesatsen Ränta En ränta avser det belopp som en långivare debiterar en låntagare för vilken form av skuld som helst, generellt uttryckt som en procentandel av rektorn. till 4,5%.
1. Ingångssaldot i vårt skuldschema är lika med lånebeloppet på 5 miljoner dollar, så i cell E29 anger vi = B25 för att koppla det till antagningsingången. Sedan kan vi använda PMT-formeln för att beräkna den totala betalningen för den första perioden = PMT ($ B $ 27, $ B $ 26, $ B $ 25) . Formeln beräknar betalningsbeloppet med hjälp av lånebeloppet, löptiden och räntan som anges i avsnittet om antagande.
2. Ange den period vi befinner oss i cell E28, som är 1. I cell E29 anger du = E28 + 1 och fyller formeln till höger. Använd sedan IPMT-formeln för att ta reda på räntebetalningen för den första perioden = IPMT ($ B $ 27, E28, $ B $ 26, $ B $ 25) .
3. Huvudbetalningen är skillnaden mellan total betalning och räntebetalning, vilket är = E30-E31 . Utgående saldo är ingångssaldot plus huvudbetalningen som görs, vilket är = E29 + E32 . Ingående balans för period 2 är utgående balans för period 1, vilket är = E33 .
4. Kopiera alla formler från cell E29 till E33 till nästa kolumn och kopiera sedan allt till höger. Kontrollera om utgående saldo för period 5 = 0 för att säkerställa att korrekta formler och siffror används.
5. Observera att det finns några felmeddelanden som börjar från period 6 eftersom öppningsbalansen är 0. Här kan vi använda IF-funktionen för att rensa felen. I cell E30, skriv = IF (E29> 0, PMT ($ B $ 27, $ B $ 26, $ B $ 25), 0) . Formeln anger att om ingående saldo är mindre än 0, så visas det totala betalningsvärdet som 0.
6. I cell 31, skriv = IF (E29> 0, IPMT ($ B $ 27, E28, $ B $ 26, $ B $ 25), 0) . Denna formel liknar den tidigare, som anger att om ingående saldo är mindre än 0, visas räntebetalningen som 0.
7. Kopiera cell E30 och E31, tryck på SKIFT + högerpil och sedan CTRL + R för att fylla till höger. Du bör se att alla felmeddelanden nu visas som 0.
XNPV en XIRR med DATE- och IF-funktioner
Vi kan beräkna NPV och IRR baserat på specifika datum med hjälp av Excel-funktionerna XNPV och XIRR med DATE- och IF-funktionerna.
8. Gå till cell E6 och ange = DATUM (E5,12,31) för att visa datumet. Kopiera till höger. Du kommer att se #VÄRDE! meddelande efter 2021. Vi kan åtgärda detta med IFERROR-funktionen = FEL (DATUM (E5,12,31), ””) .
9. Nu kan vi börja beräkna NPV och IRR. Först måste vi ange de fria kassaflödesbeloppen. Vi antar att FCF-beloppen från period 1 till 5 är -1 000, 500, 600, 700, 900. I cell C37 kommer vi att ange en diskonteringsränta på 15%. I cell B37 beräknar du NPV med hjälp av XNPV-formeln = XNPV (C37, E35: I35, E6: I6) .
10. I cell B38 beräknar du IRR med XIRR-formel = XIRR (E35: I35, E6: I6) .
Lägga till OFFSET till XNPV och XIRR
Vi kan byta till XNPV- och XIRR-formlerna för att skapa mer dynamiska formler med OFFSET-funktionen.
11. I cell B42 ändrar du formeln till = XNPV (C42, E40: OFFSET (E40,0, $ F $ 3-1), E6: I6) . Formeln är mer dynamisk, för om antalet perioder ökar kommer perioderna med fritt kassaflöde också att öka. Vi behöver inte ändra NPV-formeln om prognosperioden är längre. För IRR-funktionen, ändra den till = XIRR (E40: OFFSET (E40,0, $ F $ 3-1), E6: I6) .
12. Efter att ha justerat formeln för antalet perioder ska vi kompensera datumen. Ändra formeln i cell B42 till = XNPV (C42, E40: OFFSET (E40,0, $ F $ 3-1), E6: OFFSET (E6,0, $ F $ 3-1)) . Detta gör att NPV- och IRR-formlerna kan hämta rätt antal fritt kassaflöde med förändringen i antalet perioder.
Sammanfattning av viktiga formler för skuldschema
- PMT-formel för beräkning av skuldbetalningsbelopp: = PMT (räntesats, antal villkor, nuvärde)
- IPMT-formel för beräkning av räntebetalning : = IPMT (räntesats, period, antal villkor, nuvärde)
- XNPV-formel för att hitta nuvärdet netto: = XNPV (diskonteringsränta, fria kassaflöden, datum)
- XIRR-formel för att hitta den interna avkastningskursen: = XIRR (fria kassaflöden, datum)
- OFFSET-formel för beräkning av dynamisk NPV: = XNPV (diskonteringsränta, 1: a FCF: OFFSET (1: a FCF, 0, # perioder - 1), 1: a datum: OFFSET (1: a datum, 0, # perioder - 1))
- OFFSET-formel för beräkning av dynamisk IRR: = XIRR (1: a FCF: OFFSET (1: a FCF, 0, # perioder - 1), 1: a datum: OFFSET (1: a datum, 0, # perioder - 1))
Andra resurser
Tack för att du läser Finance guide om skuldschema med PMT, IPMT och IF formler. För att fortsätta lära och utveckla din karriär kommer följande finansresurser att vara till hjälp:
- Grundläggande Excel-formler Grundläggande Excel-formler Att behärska grundläggande Excel-formler är avgörande för nybörjare att bli skickliga i ekonomisk analys. Microsoft Excel anses vara branschstandard programvara för dataanalys. Microsofts kalkylprogram är också en av de mest föredragna mjukvarorna av investeringsbanker
- Best Practices för finansiell modellering Best Practices för finansiell modellering Den här artikeln är för att ge läsare information om bästa metoder för ekonomisk modellering och en enkel att följa, steg-för-steg-guide för att bygga en finansiell modell.
- Lista över Excel-funktioner Funktioner Lista över de viktigaste Excel-funktionerna för finansanalytiker. Detta fuskark täcker 100-tal funktioner som är viktiga att känna till som en Excel-analytiker
- Översikt över Excel-genvägar Excel-genvägar Översikt Excel-genvägar är en förbises metod för att öka produktiviteten och hastigheten i Excel. Genvägar i Excel erbjuder finansanalytikern ett kraftfullt verktyg. Dessa genvägar kan utföra många funktioner. så enkelt som att navigera i kalkylbladet för att fylla i formler eller gruppera data.
