Schuldenplan mit PMT, IPMT und IF

Wir können die PMT-, IPMT- und IF-Formeln von Excel verwenden, um einen Schuldenplan zu erstellen. Zunächst müssen wir das Modell einrichten, indem wir einige Schuldenannahmen eingeben. In diesem Beispiel gehen wir von einer Verschuldung von 5.000.000 USD, einer Zahlungsdauer von 5 Jahren und einem Zinssatz aus. Zinssatz Ein Zinssatz bezieht sich auf den Betrag, den ein Kreditgeber einem Kreditnehmer für jede gegebene Form von Schulden berechnet, im Allgemeinen ausgedrückt als ein Prozentsatz des Kapitals. 4,5% betragen.

1. Der Eröffnungssaldo in unserem Schuldenplan entspricht dem Darlehensbetrag von 5 Mio. USD. In Zelle E29 geben wir also = B25 ein , um ihn mit der Annahme zu verknüpfen. Dann können wir die PMT-Formel verwenden, um die Gesamtzahlung für den ersten Zeitraum zu berechnen = PMT (27 B $, 26 B $, 25 B $) . Die Formel berechnet den Zahlungsbetrag anhand des Darlehensbetrags, der Laufzeit und des Zinssatzes, die im Abschnitt zur Annahme angegeben sind.

Schuldenplan

2. Geben Sie in Zelle E28 die Periode ein, in der wir uns befinden, nämlich 1. Geben Sie in Zelle E29 = E28 + 1 ein und füllen Sie die Formel rechts aus. Verwenden Sie als Nächstes die IPMT-Formel, um die Zinszahlung für den ersten Zeitraum = IPMT (27 B $, E28, 26 B $, 25 B $) zu ermitteln .

3. Die Hauptzahlung ist die Differenz zwischen Gesamtzahlung und Zinszahlung, die = E30-E31 beträgt . Der Schlusssaldo ist der Eröffnungssaldo zuzüglich der geleisteten Hauptzahlung = E29 + E32 . Der Eröffnungssaldo für Periode 2 ist der Schlusssaldo für Periode 1, der = E33 ist .

4. Kopieren Sie alle Formeln von Zelle E29 bis E33 in die nächste Spalte und kopieren Sie dann alles nach rechts. Überprüfen Sie, ob der Schlusssaldo für Periode 5 = 0 ist, um sicherzustellen, dass korrekte Formeln und Zahlen verwendet werden.

5. Beachten Sie, dass es ab Periode 6 einige Fehlermeldungen gibt, da der Eröffnungssaldo 0 ist. Hier können wir die IF-Funktion verwenden, um die Fehler zu bereinigen. Geben Sie in Zelle E30 = IF (E29> 0, PMT ($ B $ 27, $ B $ 26, $ B $ 25), 0) ein . Die Formel besagt, dass wenn der Eröffnungssaldo kleiner als 0 ist, der gesamte Zahlungswert als 0 angezeigt wird.

6. Geben Sie in Zelle 31 = IF (E29> 0, IPMT ($ B $ 27, E28, $ B $ 26, $ B $ 25), 0) ein . Diese Formel ähnelt der vorherigen, die besagt, dass die Zinszahlung als 0 angezeigt wird, wenn der Eröffnungssaldo kleiner als 0 ist.

7. Kopieren Sie die Zellen E30 und E31, drücken Sie UMSCHALT + Pfeil nach rechts und dann STRG + R, um nach rechts zu füllen. Sie sollten sehen, dass alle Fehlermeldungen jetzt als 0 angezeigt werden.

XNPV ein XIRR mit DATE- und IF-Funktionen

Mit den Excel-Funktionen XNPV und XIRR mit den Funktionen DATE und IF können wir den Kapitalwert und den IRR anhand bestimmter Daten berechnen.

8. Gehen Sie zu Zelle E6 und geben Sie = DATE (E5,12,31) ein , um das Datum anzuzeigen. Nach rechts kopieren. Sie werden den #WERT sehen! Nachricht nach 2021. Wir können dies beheben, indem wir die IFERROR-Funktion = IFERROR (DATE (E5,12,31), ”” verwenden .

9. Jetzt können wir mit der Berechnung des Kapitalwerts und des IRR beginnen. Zunächst müssen wir die Free Cashflow-Beträge eingeben. Wir gehen davon aus, dass die FCF-Beträge von Periode 1 bis 5 -1.000, 500, 600, 700, 900 betragen. In Zelle C37 geben wir einen Abzinsungssatz von 15% ein. Berechnen Sie in Zelle B37 den Kapitalwert mit der XNPV-Formel = XNPV (C37, E35: I35, E6: I6) .

10. Berechnen Sie in Zelle B38 die IRR mit der XIRR-Formel = XIRR (E35: I35, E6: I6) .

Hinzufügen von OFFSET zu XNPV und XIRR

Wir können zu den XNPV- und XIRR-Formeln wechseln, um mithilfe der OFFSET-Funktion dynamischere Formeln zu erstellen.

11. Ändern Sie in Zelle B42 die Formel in = XNPV (C42, E40: OFFSET (E40,0, $ F $ 3-1), E6: I6) . Die Formel ist dynamischer, denn wenn sich die Anzahl der Perioden erhöht, erhöhen sich auch die Perioden des freien Cashflows. Wir müssen die Barwertformel nicht ändern, wenn der Prognosezeitraum länger ist. Ändern Sie die IRR-Funktion in = XIRR (E40: OFFSET (E40,0, $ F $ 3-1), E6: I6) .

12. Nachdem Sie die Formel für die Anzahl der Perioden angepasst haben, sollten Sie die Daten ausgleichen. Ändern Sie in Zelle B42 die Formel in = XNPV (C42, E40: OFFSET (E40,0, $ F $ 3-1), E6: OFFSET (E6,0, $ F $ 3-1)) . Dies ermöglicht es den NPV- und IRR-Formeln, die richtige Anzahl von Free Cashflows mit der Änderung der Anzahl von Perioden zu erfassen.

Zusammenfassung der wichtigsten Schuldenplanformeln

  • PMT-Formel zur Berechnung des Schuldenzahlungsbetrags: = PMT (Zinssatz, Anzahl der Laufzeiten, Barwert)
  • IPMT-Formel zur Berechnung der Zinszahlung: = IPMT (Zinssatz, Zeitraum, Anzahl der Laufzeiten, Barwert)
  • XNPV-Formel zur Ermittlung des Barwerts: = XNPV (Abzinsungssatz, Free Cashflow, Datum)
  • XIRR-Formel zur Ermittlung der internen Rendite: = XIRR (Free Cashflows, Daten)
  • OFFSET-Formel zur Berechnung des dynamischen Kapitalwerts : = XNPV (Abzinsungssatz, 1. FCF: OFFSET (1. FCF, 0, # Perioden - 1), 1. Datum: OFFSET (1. Datum, 0, # Perioden - 1))
  • OFFSET-Formel zur Berechnung des dynamischen IRR: = XIRR (1. FCF: OFFSET (1. FCF, 0, # Perioden - 1), 1. Datum: OFFSET (1. Datum, 0, # Perioden - 1))

Andere Ressourcen

Vielen Dank, dass Sie den Finanzleitfaden zum Schuldenplan mit PMT-, IPMT- und IF-Formeln gelesen haben. Die folgenden Finanzressourcen sind hilfreich, um weiter zu lernen und Ihre Karriere voranzutreiben:

  • Grundlegende Excel-Formeln Grundlegende Excel-Formeln Das Beherrschen grundlegender Excel-Formeln ist für Anfänger von entscheidender Bedeutung, um sich mit Finanzanalysen vertraut zu machen. Microsoft Excel gilt als branchenübliche Software für die Datenanalyse. Das Tabellenkalkulationsprogramm von Microsoft ist auch eine der von Investmentbankern am meisten bevorzugten Software
  • Best Practices für die Finanzmodellierung Best Practices für die Finanzmodellierung Dieser Artikel bietet Lesern Informationen zu Best Practices für die Finanzmodellierung und eine leicht zu befolgende, schrittweise Anleitung zum Erstellen eines Finanzmodells.
  • Liste der Excel-Funktionen Funktionen Liste der wichtigsten Excel-Funktionen für Finanzanalysten. Dieser Spickzettel enthält Hunderte von Funktionen, die für Excel-Analysten von entscheidender Bedeutung sind
  • Übersicht über Excel-Verknüpfungen Excel-Verknüpfungen Übersicht Excel-Verknüpfungen sind eine übersehene Methode zur Steigerung der Produktivität und Geschwindigkeit in Excel. Excel-Verknüpfungen bieten dem Finanzanalysten ein leistungsstarkes Tool. Diese Verknüpfungen können viele Funktionen ausführen. So einfach wie die Navigation innerhalb der Tabelle zum Ausfüllen von Formeln oder Gruppieren von Daten.

Empfohlen

Wurde Crackstreams geschlossen?
2022
Ist die MC-Kommandozentrale sicher?
2022
Verlässt Taliesin die kritische Rolle?
2022