We kunnen de PMT-, IPMT- en IF-formules van Excel gebruiken om een schuldenregeling te maken. Ten eerste moeten we het model opzetten door een aantal schuldaannames in te voeren. In dit voorbeeld gaan we ervan uit dat de schuld $ 5.000.000 is, de betalingstermijn 5 jaar en het rentetarief Rentetarief Een rentetarief verwijst naar het bedrag dat een geldschieter aan een lener in rekening brengt voor elke vorm van schuld, doorgaans uitgedrukt als een percentage van de hoofdsom. 4,5%.
1. Het beginsaldo in ons schuldoverzicht is gelijk aan het geleende bedrag van $ 5 miljoen, dus in cel E29 voeren we = B25 in om het te koppelen aan de aanname-input. Vervolgens kunnen we de PMT-formule gebruiken om de totale betaling voor de eerste periode = PMT ($ B $ 27, $ B $ 26, $ B $ 25) te berekenen . De formule berekent het betalingsbedrag met behulp van het geleende bedrag, de looptijd en het rentetarief zoals vermeld in het gedeelte over aannames.
2. Voer in cel E28 de periode in waarin we ons bevinden, namelijk 1. Voer in cel E29 = E28 + 1 in en vul de formule rechts in. Gebruik vervolgens de IPMT-formule om de rentebetaling voor de eerste periode = IPMT ($ B $ 27, E28, $ B $ 26, $ B $ 25) te achterhalen .
3. De hoofdsom is het verschil tussen de totale betaling en de rentebetaling, namelijk = E30-E31 . Het eindsaldo is het beginsaldo plus de betaling van de hoofdsom, namelijk = E29 + E32 . De beginsaldo voor periode 2 is de eindsaldo voor periode 1, dit is = E33 .
4. Kopieer alle formules van cel E29 naar E33 naar de volgende kolom en kopieer dan alles naar rechts. Controleer of de eindsaldo voor periode 5 = 0 om er zeker van te zijn dat de juiste formules en cijfers worden gebruikt.
5. Merk op dat er enkele foutmeldingen zijn vanaf periode 6 omdat het beginsaldo 0 is. Hier kunnen we de ALS-functie gebruiken om de fouten op te schonen. Typ in cel E30 = IF (E29> 0, PMT ($ B $ 27, $ B $ 26, $ B $ 25), 0) . De formule stelt dat als het beginsaldo lager is dan 0, de totale betalingswaarde wordt weergegeven als 0.
6. Typ in cel 31 = IF (E29> 0, IPMT ($ B $ 27, E28, $ B $ 26, $ B $ 25), 0) . Deze formule is vergelijkbaar met de vorige, waarin staat dat als het beginsaldo kleiner is dan 0, de rentebetaling wordt weergegeven als 0.
7. Kopieer cel E30 en E31, druk op SHIFT + pijl naar rechts en vervolgens op CTRL + R om rechts te vullen. U zou moeten zien dat alle foutmeldingen nu worden weergegeven als 0.
XNPV en XIRR met DATUM- en ALS-functies
We kunnen de NPV en IRR berekenen op basis van specifieke datums met behulp van de Excel-functies XNPV en XIRR met de DATE- en IF-functies.
8. Ga naar cel E6 en voer = DATE (E5,12,31) in om de datum weer te geven. Kopieer naar rechts. U ziet de #VALUE! bericht na 2021. We kunnen dit oplossen door de IFERROR-functie = IFERROR (DATE (E5,12,31), ””) te gebruiken .
9. Nu kunnen we beginnen met het berekenen van de NPV en IRR. Eerst moeten we de bedragen van de vrije kasstroom invoeren. We nemen aan dat de FCF-bedragen van periode 1 tot en met 5 -1.000, 500, 600, 700, 900 zijn. In cel C37 voeren we een discontovoet in van 15%. Bereken in cel B37 de NPV met behulp van de XNPV-formule = XNPV (C37, E35: I35, E6: I6) .
10. Bereken in cel B38 de IRR met de XIRR-formule = XIRR (E35: I35, E6: I6) .
OFFSET toevoegen aan XNPV en XIRR
We kunnen overschakelen naar de XNPV- en XIRR-formules om meer dynamische formules te maken met behulp van de OFFSET-functie.
11. Wijzig in cel B42 de formule in = XNPV (C42, E40: OFFSET (E40,0, $ F $ 3-1), E6: I6) . De formule is dynamischer, want als het aantal periodes toeneemt, dan zullen ook de periodes van vrije cashflow toenemen. We hoeven de NPV-formule niet te wijzigen als de prognoseperiode langer is. Wijzig voor de IRR-functie deze in = XIRR (E40: OFFSET (E40,0, $ F $ 3-1), E6: I6) .
12. Nadat we de formule voor het aantal perioden hebben aangepast, moeten we de datums verschuiven. Wijzig in cel B42 de formule in = XNPV (C42, E40: OFFSET (E40,0, $ F $ 3-1), E6: OFFSET (E6,0, $ F $ 3-1)) . Hierdoor kunnen de NPV- en IRR-formules het juiste aantal vrije cashflows oppikken met de verandering in het aantal perioden.
Samenvatting van de formules voor belangrijke schulden
- PMT-formule voor het berekenen van het bedrag van de schuldbetaling: = PMT (rentepercentage, aantal termijnen, contante waarde)
- IPMT-formule voor het berekenen van rentebetaling: = IPMT (rentepercentage, periode, aantal termijnen, contante waarde)
- XNPV-formule voor het vinden van de netto contante waarde: = XNPV (disconteringsvoet, vrije kasstromen, datums)
- XIRR-formule voor het vinden van het interne rendement: = XIRR (vrije kasstromen, datums)
- OFFSET-formule voor het berekenen van dynamische NPV: = XNPV (disconteringsvoet, 1e FCF: OFFSET (1e FCF, 0, # perioden - 1), 1e datum: OFFSET (1e datum, 0, # perioden - 1))
- OFFSET-formule voor het berekenen van dynamische IRR: = XIRR (1st FCF: OFFSET (1st FCF, 0, # periodes - 1), 1st date: OFFSET (1st date, 0, # periodes - 1))
Andere bronnen
Bedankt voor het lezen van de Finance-gids over het schuldschema met PMT-, IPMT- en IF-formules. Om te blijven leren en uw carrière vooruit te helpen, zijn de volgende financiële bronnen nuttig:
- Basis Excel-formules Basis Excel-formules Het beheersen van basis Excel-formules is van cruciaal belang voor beginners om bedreven te worden in financiële analyse. Microsoft Excel wordt beschouwd als het industriestandaard stuk software voor gegevensanalyse. Het spreadsheetprogramma van Microsoft is toevallig ook een van de meest geprefereerde software van investeringsbankiers
- Best practices voor financiële modellering Best practices voor financiële modellering Dit artikel is bedoeld om lezers informatie te geven over best practices voor financiële modellen en een eenvoudig te volgen, stapsgewijze handleiding voor het bouwen van een financieel model.
- Lijst met Excel-functies Functies Lijst met de belangrijkste Excel-functies voor financiële analisten. Dit spiekbriefje bevat honderden functies die essentieel zijn om te kennen als Excel-analist
- Overzicht van Excel-snelkoppelingen Excel-snelkoppelingen Overzicht Excel-snelkoppelingen zijn een over het hoofd geziene methode om de productiviteit en snelheid binnen Excel te verhogen. Excel-snelkoppelingen bieden de financiële analist een krachtig hulpmiddel. Deze sneltoetsen kunnen veel functies uitvoeren. zo eenvoudig als navigatie in de spreadsheet tot het invullen van formules of het groeperen van gegevens.
