Planen Sie Kreditrückzahlungen mit Excel-Formeln

Die Rückzahlung eines Kredits ist die Rückzahlung von Geldern, die zuvor von einem Kreditgeber geliehen wurden, in der Regel über eine Reihe von regelmäßigen Zahlungen, die Kapital plus Zinsen umfassen. Wussten Sie, dass Sie das Softwareprogramm Excel zur Berechnung Ihrer Kreditrückzahlungen verwenden können?

Dieser Artikel ist eine schrittweise Anleitung zum Einrichten 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 die Aufteilung eines Kredits mit Excel genauer untersuchen und einen Tilgungsplan erstellen, der für Sie funktioniert.
• Für jeden Schritt stehen Berechnungen zur Verfügung, die Sie an Ihre spezifischen Anforderungen anpassen können.
• Eine schrittweise Aufschlüsselung und Überprüfung Ihres Kredits kann dazu führen, dass sich der Rückzahlungsprozess weniger überwältigend und leichter handhabbar anfühlt.

Verstehen Sie Ihre Hypothek

Mit Excel erhalten Sie in drei einfachen Schritten ein besseres Verständnis Ihrer Hypothek. Der erste Schritt bestimmt die monatliche Zahlung. Der zweite Schritt berechnet den Zinssatz und der dritte Schritt bestimmt den Kreditplan.

Sie können in Excel eine Tabelle erstellen, in der der Zinssatz, die Kreditberechnung für die Laufzeit des Kredits, die Zerlegung des Kredits, die Tilgung und die monatliche Zahlung aufgeführt sind.

Berechnen Sie die monatliche Zahlung

Zunächst erfahren Sie, wie Sie die monatliche Zahlung für eine Hypothek berechnen. Anhand des jährlichen Zinssatzes, des Kapitals und der Laufzeit können wir den monatlich zurückzuzahlenden Betrag bestimmen.

Die im obigen Screenshot gezeigte Formel lautet wie folgt:

= -PMT (rate; length; present_value; [future_value]; [type])

Das Minuszeichen vor PMT ist erforderlich, da die Formel eine negative Zahl zurückgibt. Die ersten drei Argumente sind der Zinssatz des Darlehens, die Laufzeit des Darlehens (Anzahl der Perioden) und der geliehene Kapitalbetrag. Die letzten beiden Argumente sind optional. Der Restwert wird standardmäßig auf Null gesetzt. zahlbar im voraus (für eins) oder am ende (für null), ist ebenfalls optional.

Die Excel-Formel zur Berechnung der monatlichen Auszahlung des Darlehens lautet:

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

Erläuterung: Für die Rate verwenden wir die monatliche Rate (Zinsperiode), berechnen dann die Anzahl der Perioden (120 für 10 Jahre multipliziert mit 12 Monaten) und geben schließlich den geliehenen Kapitalbetrag an. Unsere monatliche Zahlung beträgt über einen Zeitraum von 10 Jahren 1.161, 88 USD.

Berechnen Sie den jährlichen Zinssatz

Wir haben gesehen, wie die Berechnung einer monatlichen Zahlung für eine Hypothek eingerichtet wird. Möglicherweise möchten wir jedoch eine maximale monatliche Zahlung festlegen, die wir uns leisten können und die auch die Anzahl der Jahre angibt, über die wir den Kredit zurückzahlen müssten. Aus diesem Grund möchten wir den entsprechenden jährlichen Zinssatz kennen.

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

= RATE (Nper; pmt; present_value; [future_value]; [type])

Die ersten drei Argumente sind die Laufzeit des Kredits (Anzahl der Perioden), die monatliche Rückzahlung des Kredits und der geliehene Kapitalbetrag. Die letzten drei Argumente sind optional und der Restwert ist standardmäßig Null. Optional ist auch das Termargument für die Verwaltung der Fälligkeit im Voraus (für eins) oder am Ende (für null). Schließlich ist das Schätzungsargument optional, kann jedoch eine anfängliche Schätzung der Rate geben.

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. Der Zinszeitraum beträgt 0, 294%.

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

Die Verwendung von Excel ist eine hervorragende Methode, um Ihre Schulden im Auge zu behalten und einen Zeitplan für die Rückzahlung zu erstellen, mit dem die möglicherweise anfallenden Gebühren minimiert werden.

Bestimmen der Laufzeit eines Kredits

Wir werden nun sehen, wie Sie die Laufzeit eines Kredits bestimmen können, wenn Sie den Jahreszinssatz, den geliehenen Kapitalbetrag und die monatliche Rückzahlung kennen. Mit anderen Worten, wie lange müssen wir eine Hypothek in Höhe von 120.000 USD mit einem Zinssatz von 3, 10% und einer monatlichen Zahlung von 1.100 USD zurückzahlen?

Die Formel, die wir verwenden werden, lautet NPER (siehe Abbildung oben) und lautet wie folgt:

= NPER (rate; pmt; present_value; [future_value]; [type])

Die ersten drei Argumente sind der jährliche Zinssatz des Kredits, die monatliche Zahlung für die Rückzahlung des Kredits und der geliehene Kapitalbetrag. Die letzten beiden Argumente sind optional, der Restwert wird standardmäßig auf Null gesetzt. Das im Voraus (für eins) oder am Ende (für null) zu zahlende Termargument ist ebenfalls optional.

= NPER ((1 + B2) ^ (1/12) -1; -B4; B3) = NPER ((1 + 3, 10%) ^ (1/12) -1; -1100; 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 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 Rückzahlung des Darlehens abzuschließen. Mit anderen Worten, um 120.000 USD mit einem Jahreszins von 3, 10% zu leihen und 1.100 USD monatlich zu zahlen, müssen wir die Fälligkeiten für 128 Monate oder 10 Jahre und 8 Monate zurückzahlen.

Zerlegung des Kredits

Eine Darlehenszahlung setzt sich aus Kapital und Zinsen zusammen. Die Zinsen werden für jede Periode berechnet - zum Beispiel ergeben die monatlichen Rückzahlungen über 10 Jahre 120 Perioden.

Die obige Tabelle zeigt die Aufteilung eines Kredits (eine Gesamtlaufzeit von 120) nach den Formeln PPMT und IPMT. Die Argumente der beiden Formeln sind gleich und setzen sich wie folgt zusammen:

= -PPMT (rate; num_period; length; principal; [residual]; [term])

Die Argumente sind dieselben wie für die bereits angezeigte PMT-Formel, mit Ausnahme von "num_period", die hinzugefügt wird, um den Zeitraum anzuzeigen, über den das Darlehen unter Berücksichtigung des Kapitals und der Zinsen aufgelöst werden muss. 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 wird im Screenshot über "Darlehenszerlegung" über den analysierten Zeitraum angezeigt, der "Eins" ist. das heißt, der erste Zeitraum 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 Kapital- und Zinsrückzahlung für mehrere Zeiträume wie die ersten 12 Monate oder die ersten 15 Monate zu berechnen.

= -CUMPRINC (rate; length; principal; start_date; end_date; type)

Wir finden die Argumente, die Rate, die Länge, das Prinzip und den Term (die obligatorisch sind), die wir bereits im ersten Teil mit der Formel PMT gesehen haben. Aber hier brauchen wir auch die Argumente "start_date" und "end_date". Das "Startdatum" gibt den Beginn des zu analysierenden Zeitraums an, und das "Enddatum" gibt 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 wird im Screenshot "Cumul 1st year" angezeigt. Die analysierten Zeiträume reichen von einem bis 12 des ersten Zeitraums (ersten Monats) 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 von Periode zu Periode zu erstellen, zu wissen, wie viel Kapital und Zinsen wir monatlich zahlen werden, und zu wissen, wie viel noch zu zahlen ist.

Erstellen eines Kreditplans

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

Geben Sie in der ersten Periodenspalte "1" als ersten Zeitraum ein und ziehen Sie die Zelle nach unten. In unserem Fall benötigen wir 120 Perioden, da eine 10-jährige Darlehenszahlung multipliziert mit 12 Monaten 120 entspricht.

Die zweite Spalte gibt den monatlichen Betrag an, den wir jeden Monat zahlen müssen. Dieser Betrag ist über den gesamten Kreditplan hinweg konstant. Um den Betrag zu berechnen, geben Sie die folgende Formel in die Zelle unserer ersten Periode ein:

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

Die dritte Spalte ist der Kapitalbetrag, der monatlich zurückgezahlt wird. Zum Beispiel werden wir für den 40. Zeitraum 945, 51 USD an unserem monatlichen Gesamtbetrag von 1.161, 88 USD 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 enthält die Zinsen, für die wir anhand der Formel den zurückgezahlten Kapitalbetrag für unseren monatlichen Betrag berechnen, um festzustellen, 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. Nach der 40. Zahlung müssen wir beispielsweise 83.994, 69 USD für 120.000 USD bezahlen.

Die Formel lautet wie folgt:

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

Die Formel verwendet eine Kombination von Principal mit einem Punkt vor der Zelle, die den ausgeliehenen Principal 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 Ablauf von 120 Perioden zurückgezahlt wird.