Planen Sie Kreditrückzahlungen mit Excel-Formeln

Kreditrückzahlung ist die Rückzahlung von Geld, das zuvor von einem Kreditgeber geliehen wurde, normalerweise durch eine Reihe von regelmäßigen Zahlungen, die Kapital plus Zinsen umfassen. Wussten Sie, dass Sie mit der Software Excel Ihre Kreditrückzahlungen berechnen können?

Dieser Artikel ist eine Schritt-für-Schritt-Anleitung zum Einrichten von Kreditberechnungen.

Ihre Hypothek verstehen

Mit Excel können Sie Ihre Hypothek in drei einfachen Schritten. Im ersten Schritt wird die monatliche Zahlung festgelegt. Im zweiten Schritt wird der Zinssatz berechnet und im dritten Schritt der Kreditplan festgelegt.

Sie können eine Tabelle erstellen in Excel die Ihnen den Zinssatz, die Kreditberechnung für die Laufzeit des Kredits, die Aufgliederung des Kredits, die Amortisation und die monatliche Zahlung anzeigt.

Berechnen Sie die monatliche Zahlung

Hier ist zunächst, wie es geht Berechnen Sie die monatliche Zahlung für eine Hypothek. Anhand des Jahreszinssatzes, des Kapitals und der Laufzeit können wir den monatlich zurückzuzahlenden Betrag bestimmen.

Die Formel, wie im obigen Screenshot gezeigt, lautet wie folgt:

=-PMT(Rate; Länge; gegenwärtiger_wert;[zukünftiger_wert];[typ])

Das Minuszeichen vor PMT ist erforderlich, da die Formel eine negative Zahl zurückgibt. Die ersten drei Argumente sind die Zinssatz des Darlehens, die Dauer des Darlehens (Anzahl der Perioden) und den geliehenen Kapitalbetrag. Die letzten beiden Argumente sind optional, der Restwert ist standardmäßig null; zahlbar im Voraus (für eins) oder am Ende (für null) ist ebenfalls optional.

Die Excel-Formel zur Berechnung der monatlichen Kreditzahlung lautet:

= PMT((1+B2)^(1/12)-1;B4*12;B3)=PMT((1+3,10%)^(1/12)-1;10*12;120000)

Erklärung: Für den Tarif verwenden wir den monatlichen Tarif (Tarifperiode), dann berechnen wir die Anzahl der Perioden (120 für 10 Jahre multipliziert mit 12 Monaten) und geben schließlich die Rektor geliehen. Unsere monatliche Zahlung beträgt $1.161,88 über 10 Jahre.

Berechnen Sie den jährlichen Zinssatz

Wir haben gesehen, wie Sie die Berechnung einer monatlichen Zahlung für eine Hypothek einrichten. Vielleicht möchten wir jedoch eine maximale monatliche Zahlung festlegen, die wir uns leisten können und die auch die Anzahl der Jahre anzeigt, über die wir das Darlehen zurückzahlen müssten. Aus diesem Grund möchten wir den entsprechenden Jahreszinssatz kennen.

Wie im obigen Screenshot gezeigt, berechnen wir zuerst die Periodenrate (in unserem Fall monatlich) und dann die Jahresrate. Die verwendete Formel lautet RATE, wie im obigen Screenshot gezeigt. Es ist wie folgt geschrieben:

=RATE(Nper; pmt; gegenwärtiger_wert;[zukünftiger_wert];[typ])

Die ersten drei Argumente sind die Länge der Darlehen (Anzahl der Perioden), die monatliche Rate zur Rückzahlung des Darlehens und den geliehenen Kapitalbetrag. Die letzten drei Argumente sind optional, und der Restwert ist standardmäßig null; optional ist auch das Begriffsargument für die Steuerung der Fälligkeit im Voraus (bei Eins) oder am Ende (bei Null). Schließlich ist das Argument Schätzwert optional, kann aber eine erste Schätzung der Rate liefern.

Die zur Berechnung des Kreditzinses verwendete Excel-Formel lautet:

=RATE(12*B4;-B2;B3) = RATE(12*13;-960;120000)

Hinweis: Die entsprechenden Daten in der monatlichen Zahlung müssen mit einem negativen Vorzeichen versehen werden. Deshalb steht vor der Formel ein Minuszeichen. Die Zinsperiode beträgt 0,294 %.

Wir verwenden die Formel = (1 + B5) ist 12-1 ^ = (1 + 0,294 %) ^ 12-1 um den Jahreszins unseres Darlehens zu erhalten, der 3,58% beträgt. Mit anderen Worten, um 120.000 US-Dollar über 13 Jahre zu leihen, um monatlich 960 US-Dollar zu zahlen, sollten wir einen Kredit mit einem jährlichen Höchstsatz von 3,58% aushandeln.

Bestimmung der Kreditdauer

Wir werden nun sehen, wie Sie die Länge eines Darlehens bestimmen können, wenn Sie den Jahreszins, den aufgenommenen Kapitalbetrag und die monatliche Rückzahlung kennen. Mit anderen Worten, wie lange müssen wir eine Hypothek von 120.000 US-Dollar mit einem Zinssatz von 3,10 % und einer monatlichen Zahlung von 1.100 US-Dollar zurückzahlen?

Die Formel, die wir verwenden werden, ist NPER, wie im obigen Screenshot gezeigt, und sie lautet wie folgt:

=NPER(Rate; pmt; gegenwärtiger_wert;[zukünftiger_wert];[typ])

Die ersten drei Argumente sind der Jahreszins des Darlehens, die monatliche Zahlung, die zur Rückzahlung des Darlehens erforderlich ist, und der geliehene Kapitalbetrag. Die letzten beiden Argumente sind optional, die Restwert standardmäßig auf null. Der Begriff Argument zahlbar im Voraus (für eins) oder am Ende (für null) ist ebenfalls optional.

=NPER((1+B2)^(1/12)-1;-B4;B3) = NPER((1+3,10%)^(1/12)-1;-1100;120000)

Wir verwenden die Formel = B5 / 12 = 127,97 / 12 für die Anzahl der Jahre, um die Kreditrückzahlung abzuschließen. Mit anderen Worten, um 120.000 US-Dollar mit einem Jahreszins von 3,10 % zu leihen und monatlich 1.100 US-Dollar zu zahlen, sollten wir Laufzeiten von 128 Monaten oder 10 Jahren und acht Monaten zurückzahlen.

Zerlegung des Darlehens

Eine Kreditzahlung setzt sich aus Kapital und Zinsen zusammen. Die Zinsen werden für jede Periode berechnet – die monatlichen Rückzahlungen über 10 Jahre ergeben uns beispielsweise 120 Perioden.

Die obige Tabelle zeigt die Aufschlüsselung eines Darlehens (eine Gesamtlaufzeit von 120) unter Verwendung der PPMT- und IPMT-Formeln. Die Argumente der beiden Formeln sind gleich und gliedern sich wie folgt:

=-PPMT(Rate; Anzahl_Periode; Länge; Haupt;[Rest];[Begriff])

Die Argumente sind die gleichen wie für die bereits gesehene PMT-Formel, mit Ausnahme von "num_period", das hinzugefügt wird, um den Zeitraum anzuzeigen, über den das Darlehen unter Berücksichtigung des Kapitals und der Zinsen aufgeschlüsselt werden soll. Hier ist ein Beispiel:

=-PPMT((1+B2)^(1/12)-1;1;B4*12;B3) = PPMT((1+3,10%)^(1/12)-1;1;10* 12;120000)

Das Ergebnis ist im obigen Screenshot "Darlehenszerlegung" über den analysierten Zeitraum dargestellt, der "eins" ist; das heißt, die erste Periode oder der erste Monat. Wir zahlen 1.161,88 USD, aufgeteilt in 856,20 USD Kapital und 305,68 USD Zinsen.

Kreditberechnung in Excel

Es ist auch möglich, die Tilgungs- und Zinsrückzahlung für mehrere Zeiträume zu berechnen, beispielsweise für die ersten 12 Monate oder die ersten 15 Monate.

=-CUMPRINC(Rate; Länge; Rektor; Startdatum; Endtermin; Typ)

Wir finden die Argumente Rate, Länge, Prinzipal und Laufzeit (die obligatorisch sind), die wir bereits im ersten Teil mit der Formel PMT gesehen haben. Aber auch hier brauchen wir die Argumente "start_date" und "end_date". Das "start_date" gibt den Beginn des zu analysierenden Zeitraums und das "end_date" das Ende des zu analysierenden Zeitraums an.

Hier ist ein Beispiel:

=-CUMPRINC((1+B2)^(1/12)-1;B4*12;B3;1;12;0)

Das Ergebnis ist im Screenshot "Cumul 1st year" dargestellt, die analysierten Perioden reichen also von eins bis 12 der ersten Periode (erster Monat) bis zum zwölften (12. Monat). Über ein Jahr würden wir 10.419,55 USD an Kapital und 3.522,99 USD an Zinsen zahlen.

Tilgung des Darlehens

Die vorherigen Formeln ermöglichen es uns, unseren Zeitplan Zeitraum für Zeitraum zu erstellen, um zu wissen, wie viel wir monatlich an Kapital und Zinsen zahlen werden und wie viel noch zu zahlen ist.

Erstellen eines Kreditplans

Um einen Kreditplan zu erstellen, verwenden wir die oben besprochenen verschiedenen Formeln und erweitern sie über die Anzahl der Perioden.

Geben Sie in der Spalte für den ersten Punkt "1" als ersten Punkt ein und ziehen Sie dann die Zelle nach unten. In unserem Fall benötigen wir 120 Perioden, da eine 10-jährige Kreditzahlung multipliziert mit 12 Monaten 120 ergibt.

Die zweite Spalte ist der monatliche Betrag, den wir jeden Monat zahlen müssen – der über den gesamten Kreditplan hinweg konstant ist. Um den Betrag zu berechnen, fügen Sie die folgende Formel in die Zelle unserer ersten Periode ein:

=-PMT(TP; B4*12;B3) =-PMT((1+3,10%)^(1/12)-1;10*12;120000)

Die dritte Spalte ist das Kapital, das monatlich zurückgezahlt wird. Zum Beispiel werden wir für die 40. Periode einen Kapitalbetrag von 945,51 US-Dollar auf unseren monatlichen Gesamtbetrag von 1.161,88 US-Dollar zurückzahlen.

Um den eingelösten Kapitalbetrag zu berechnen, verwenden wir die folgende Formel:

=-PPMT(TP; A18;$B$4*12;$B$3) =-PPMT((1+3,10%)^(1/12);1;10*12;120000)

Die vierte Spalte sind die Zinsen, für die wir die Formel verwenden, um die auf unseren monatlichen Betrag zurückgezahlte Kapitalsumme zu berechnen, um herauszufinden, wie viel Zinsen zu zahlen sind:

=-INTPER(TP; A18;$B$4*12;$B$3) =-INTPER((1+3,10%)^(1/12);1;10*12;120000)

Die fünfte Spalte enthält den noch zu zahlenden Betrag. Zum Beispiel müssen wir nach der 40. Zahlung 83.994,69 $ auf 120.000 $ zahlen.

Die Formel lautet wie folgt:

=$B$3+CUMPRINC(TP;$B$4*12;$B$3;1;A18;0)

Die Formel verwendet eine Kombination von Kapital unter einer Periode vor der Zelle, die das entliehene Kapital enthält. Dieser Zeitraum beginnt sich zu ändern, wenn wir die Zelle kopieren und nach unten ziehen. Die folgende Tabelle zeigt, dass unser Darlehen nach 120 Perioden zurückgezahlt wird.