Gjeldsplan med PMT-, IPMT- og IF-formler - Veiledning og eksempler

Vi kan bruke Excels PMT-, IPMT- og IF-formler for å lage en gjeldsplan. Først må vi sette opp modellen ved å legge inn noen gjeldsforutsetninger. I dette eksemplet antar vi at gjelden er $ 5.000.000, betalingsperioden er 5 år, og renten Rente En rente refererer til beløpet som en utlåner belaster en låntaker for enhver gitt gjeld, generelt uttrykt som en prosentandel av rektor. å være 4,5%.

1. Åpningsbalansen i gjeldsplanen er lik lånebeløpet på $ 5 millioner, så i celle E29 skriver vi inn = B25 for å knytte den til antakelsesinngangen. Deretter kan vi bruke PMT-formelen til å beregne den totale betalingen for den første perioden = PMT ($ B $ 27, $ B $ 26, $ B $ 25) . Formelen beregner betalingsbeløpet ved hjelp av lånebeløpet, løpetiden og renten som er angitt i antakelsesdelen.

Gjeldsplan

2. I celle E28 skriver du inn perioden vi er i, som er 1. I celle E29 skriver du inn = E28 + 1 og fyller formelen til høyre. Deretter bruker du IPMT-formelen for å finne ut rentebetalingen for den første perioden = IPMT ($ B $ 27, E28, $ B $ 26, $ B $ 25) .

3. Hovedbetalingen er forskjellen mellom total betaling og rentebetaling, som er = E30-E31 . Utgående saldo er inngående saldo pluss hovedbetalingen som utføres, som er = E29 + E32 . Åpningsbalansen for periode 2 er den utgående balansen for periode 1, som er = E33 .

4. Kopier alle formler fra celle E29 til E33 til neste kolonne, og kopier deretter alt til høyre. Sjekk om utgående saldo for periode 5 = 0 for å sikre at riktige formler og tall blir brukt.

5. Legg merke til at det er noen feilmeldinger som starter fra periode 6 fordi åpningsbalansen er 0. Her kan vi bruke IF-funksjonen til å rense feilene. I celle E30 skriver du = IF (E29> 0, PMT ($ B $ 27, $ B $ 26, $ B $ 25), 0) . Formelen sier at hvis inngangssaldoen er mindre enn 0, vil den totale betalingsverdien vises som 0.

6. I celle 31 skriver du = IF (E29> 0, IPMT ($ B $ 27, E28, $ B $ 26, $ B $ 25), 0) . Denne formelen er lik den forrige, som sier at hvis inngangssaldoen er mindre enn 0, vil rentebetalingen vises som 0.

7. Kopier celle E30 og E31, trykk SKIFT + høyre pil og deretter CTRL + R for å fylle til høyre. Du bør se at alle feilmeldingene nå vises som 0.

XNPV en XIRR med DATE- og IF-funksjoner

Vi kan beregne NPV og IRR basert på spesifikke datoer ved hjelp av Excel-funksjonene XNPV og XIRR med DATE og IF-funksjonene.

8. Gå til celle E6 og skriv inn = DATO (E5,12,31) for å vise datoen. Kopier til høyre. Du vil se #VALUE! melding etter 2021. Vi kan fikse dette ved å bruke IFERROR-funksjonen = IFERROR (DATE (E5,12,31), ””) .

9. Nå kan vi begynne å beregne NPV og IRR. Først må vi legge inn gratis kontantstrømbeløp. Vi antar at FCF-beløpene fra periode 1 til 5 er -1 000, 500, 600, 700, 900. I celle C37 vil vi legge inn en diskonteringsrente på 15%. I celle B37 beregner du NPV ved hjelp av XNPV-formelen = XNPV (C37, E35: I35, E6: I6) .

10. I celle B38 beregner du IRR ved hjelp av XIRR-formel = XIRR (E35: I35, E6: I6) .

Legger til OFFSET til XNPV og XIRR

Vi kan endre til XNPV- og XIRR-formlene for å lage mer dynamiske formler ved hjelp av OFFSET-funksjonen.

11. I celle B42 endrer du formelen til = XNPV (C42, E40: OFFSET (E40,0, $ F $ 3-1), E6: I6) . Formelen er mer dynamisk, for hvis antall perioder øker, vil periodene med fri kontantstrøm også øke. Vi trenger ikke endre NPV-formelen hvis prognoseperioden er lengre. For IRR-funksjonen, endre den til = XIRR (E40: OFFSET (E40,0, $ F $ 3-1), E6: I6) .

12. Etter å ha justert formelen for antall perioder, bør vi forskyve datoene. I celle B42 endrer du formelen til = XNPV (C42, E40: OFFSET (E40,0, $ F $ 3-1), E6: OFFSET (E6,0, $ F $ 3-1)) . Dette gjør at NPV- og IRR-formlene kan hente riktig antall gratis kontantstrømmer med endring i antall perioder.

Sammendrag av viktige gjeldsplanformler

  • PMT-formel for beregning av gjeldsbeløp: = PMT (rentesats, antall vilkår, nåverdi)
  • IPMT-formel for beregning av rentebetaling: = IPMT (rentesats, periode, antall vilkår, nåverdi)
  • XNPV-formel for å finne netto nåverdi: = XNPV (diskonteringsrente, gratis kontantstrømmer, datoer)
  • XIRR-formel for å finne intern avkastningskurs: = XIRR (gratis kontantstrømmer, datoer)
  • OFFSET formel for beregning av dynamisk NPV: = XNPV (diskonteringsrente, 1. FCF: OFFSET (1. FCF, 0, # perioder - 1), 1. dato: OFFSET (1. dato, 0, # perioder - 1))
  • OFFSET formel for beregning av dynamisk IRR: = XIRR (1. FCF: OFFSET (1. FCF, 0, # perioder - 1), 1. dato: OFFSET (1. dato, 0, # perioder - 1))

Andre ressurser

Takk for at du leste Finance-guiden om gjeldsplan med PMT-, IPMT- og IF-formler. For å fortsette å lære og fremme karrieren din, vil følgende finansressurser være nyttige:

  • Grunnleggende Excel-formler Grunnleggende Excel-formler Å mestre grunnleggende Excel-formler er viktig for nybegynnere å bli dyktige i økonomisk analyse. Microsoft Excel regnes som industristandard programvare i dataanalyse. Microsofts regnearkprogram er også en av de mest foretrukne programvarene av investeringsbankfolk
  • Beste praksis for økonomisk modellering Finansiell modellering Beste praksis Denne artikkelen er å gi leserne informasjon om beste praksis for økonomisk modellering og en enkel å følge, trinnvis guide for å bygge en finansiell modell.
  • Liste over funksjoner i Excel Funksjoner Liste over de viktigste Excel-funksjonene for finansanalytikere. Dette juksearket dekker hundrevis av funksjoner som er kritiske å kjenne som en Excel-analytiker
  • Oversikt over Excel-snarveier Excel-snarveier Oversikt Excel-snarveier er en oversett metode for å øke produktiviteten og hastigheten i Excel. Excel-snarveier gir finansanalytikeren et kraftig verktøy. Disse snarveiene kan utføre mange funksjoner. så enkelt som navigering i regnearket for å fylle ut formler eller gruppere data.

Siste innlegg