Planen Sie Kreditrückzahlungen mit Excel-Formeln
Die Rückzahlung eines Kredits ist die Rückzahlung von Geld, das zuvor von einem Kreditgeber geliehen wurde, in der Regel 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 zur Einrichtung von Kreditberechnungen.
Die zentralen Thesen:
- Verwenden Sie Excel, um Ihre Hypothek in den Griff zu bekommen, indem Sie Ihre monatliche Zahlung, Ihren Zinssatz und Ihren Kreditplan bestimmen.
- Sie können sich mit Excel die Aufschlüsselung eines Kredits genauer ansehen und einen für Sie passenden Tilgungsplan erstellen.
- Für jeden Schritt stehen Berechnungen zur Verfügung, die Sie an Ihre spezifischen Anforderungen anpassen können.
- Wenn Sie Ihr Darlehen schrittweise aufschlüsseln und prüfen, kann sich der Rückzahlungsprozess weniger überwältigend und überschaubarer anfühlen.
Ihre Hypothek verstehen
Mit Excel können Sie Ihre Hypothek in drei einfachen Schritten besser verstehen. 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 in Excel eine Tabelle erstellen, die Ihnen den Zinssatz, die Darlehensberechnung für die Dauer des Darlehens, die Aufgliederung des Darlehens, die Amortisation und die monatliche Zahlung anzeigt.
Berechnen Sie die monatliche Zahlung
Hier erfahren Sie zunächst, wie Sie die monatliche Rate für eine Hypothek berechnen. 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;Gegenwartswert;[Zukunftswert];[Typ])
Das Minuszeichen vor PMT ist erforderlich, da die Formel eine negative Zahl zurückgibt. Die ersten drei Argumente sind der Zinssatz des Darlehens, die Dauer des Darlehens (Anzahl der Perioden) und der geliehene 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)
Erläuterung: Für den Preis, verwenden wir die monatliche Rate (Zeit der Rate), dann berechnen wir die Anzahl der Perioden (120 für 10 Jahre um 12 Monate multipliziert) und schließlich zeigen wir die wichtigsten entlehnt. Unsere monatliche Zahlung beträgt $1.161,88 über 10 Jahre.
Berechnen Sie den Jahreszinssatz
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;aktueller_Wert;[zukünftiger_Wert];[Typ])
Die ersten drei Argumente sind die Länge des Darlehens (Anzahl der Perioden), die monatliche Rückzahlung des Darlehens und der geliehene Kapitalbetrag. Die letzten drei Argumente sind optional, und der Restwert ist standardmäßig null; Der Begriff Argument für die Verwaltung der Laufzeit im Voraus (für eine) oder am Ende (für null) ist ebenfalls optional. 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 Kredits 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.
Die Verwendung von Excel ist eine großartige Möglichkeit, den Überblick über Ihre Schulden zu behalten und einen Rückzahlungsplan zu erstellen, der alle Gebühren minimiert, die Sie am Ende schulden könnten.
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;gegenwartswert;[zukunftswert];[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, der Restwert ist standardmäßig 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)
Minuszeichen vor der Formel
Die entsprechenden Daten in der monatlichen Zahlung müssen mit einem negativen Vorzeichen versehen werden. Deshalb haben wir vor der Formel ein Minuszeichen. Die Erstattungsdauer beträgt 127,97 Perioden (in unserem Fall Monate).
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;num_period;length;principal;[rest];[term])
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 „Kreditzerlegung“ ü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;Prinzipal;Startdatum;Enddatum;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. Für die 40. Periode werden wir beispielsweise 945,51 US-Dollar an unserem monatlichen Gesamtbetrag von 1.161,88 US-Dollar zurückzahlen.
Zur Berechnung des eingelösten Kapitalbetrags 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 Kapitalrückzahlung auf unseren monatlichen Betrag 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.