Wussten Sie, dass Sie Excel zur Berechnung Ihrer Kreditrückzahlungen verwenden können? Dieser Artikel führt Sie durch alle notwendigen Schritte. (Siehe auch: Hypothekenrechner: Wie sie funktionieren .)

Mit Excel können Sie Ihre Hypothek in drei einfachen Schritten besser verstehen. Der erste Schritt besteht darin, die monatliche Zahlung zu bestimmen. Das zweite ist, den Zinssatz zu entdecken, und der dritte ist, den Kreditplan zu finden. Um dies zu tun, können Sie eine Tabelle in Excel erstellen, die Ihnen Folgendes mitteilt: Die lnterestsätze; die Darlehenskalkulation für die Dauer; Zerlegung eines Darlehens sowie Amortisation und Berechnung für monatliche Miete.

Darlehensberechnung für monatliche Miete

Sehen wir uns zunächst an, wie Sie die Berechnung einer monatlichen Zahlung für eine Hypothek durchführen. Mit anderen Worten, wir können anhand des jährlichen Zinssatzes, des Kapitals und der Laufzeit den Betrag bestimmen, der monatlich zurückgezahlt wird.

Die Formel, wie im obigen Screenshot gezeigt, ist wie folgt geschrieben:

= - PMT (Rate; Länge; present_value; [future_value]; [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 Länge des Darlehens (Anzahl der Perioden) und der entliehene Schuldner. Die letzten beiden Argumente sind optional, der Restwert ist standardmäßig 0 und wird im Voraus (für 1) oder am Ende (für 0) bezahlt und ist optional.

Die Excel-Formel zur Berechnung der monatlichen Zahlung 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 Periode der Rate, die die monatliche Rate ist, dann berechnen wir die Anzahl der Perioden (Monate hier 120 für 10 Jahre multipliziert mit 12 Monaten) und schließlich geben wir die Hauptschuld. Unsere monatliche Zahlung beträgt $ 1, 161. 88 über 10 Jahre.

Hypothekenberechnung für Zinssätze

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

Berechnen des Zinssatzes für ein Darlehen

Wie in dem obigen Screenshot gezeigt, berechnen wir zunächst die Periodenrate (in unserem Fall monatlich) und dann die Jahresrate. Die verwendete Formel ist RATE, wie im obigen Screenshot gezeigt, sie lautet wie folgt:

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

Die ersten drei Argumente sind die Länge von das Darlehen (Anzahl der Perioden) und die monatliche Zahlung zur Rückzahlung des entliehenen Kapitals. Die letzten drei Argumente sind optional und der Residualwert ist standardmäßig 0, das Termargument für die Verwaltung der Laufzeit im Voraus (für 1) oder am Ende (für 0) ist ebenfalls optional, und schließlich ist das Schätzargument optional, kann aber geben Sie eine erste Schätzung der Rate.

Die Excel-Formel zur Berechnung der Kreditzinssätze lautet:

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

Hinweis: Die entsprechenden Daten in die monatliche Zahlung muss ein negatives Vorzeichen erhalten. Darum ein Minuszeichen vor der Formel. Unsere Zinsperiode ist 0. 294%.

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

Hypothekenberechnung für die Länge eines Darlehens

Wir werden nun sehen, wie man die Laufzeit eines Darlehens erhält, wenn man die Jahresrate, den aufgenommenen Kapitalbetrag und die monatliche Zahlung kennt, die zurückgezahlt werden muss. Mit anderen Worten, wie lange werden wir brauchen, um eine $ 120, 000 Hypothek mit einer Rate von 3. 10% und monatliche Zahlung von $ 1, 100 zurückzahlen?

Anzahl der Rückzahlungen für ein Darlehen

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

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

Die ersten drei Argumente sind die Jahresrate des Darlehens, die monatliche Zahlung, die zur Rückzahlung des Darlehens benötigt wird, und der entliehene Kapitalbetrag. Die letzten beiden Argumente sind optional, der Residualwert ist standardmäßig 0, das Termargument, das im Voraus (für 1) oder am Ende (für 0) zu zahlen ist, 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 haben wir vor der Formel ein Minuszeichen. Die Erstattungsdauer beträgt 127. 97 Zeiträume (in unserem Fall Monate).

Wir verwenden die Formel = B5 / 12 = 127. 97/12 für die Anzahl der Jahre, um die Kreditrückzahlung zu vervollständigen. Mit anderen Worten, um 120.000 $ mit einer jährlichen Rate von 3. 10% zu leihen und monatlich 100 $ zu zahlen, sollten wir die Fälligkeiten für 128 Monate oder 10 Jahre und 8 Monate zurückzahlen.

Zerlegen des Darlehens

Eine Kreditzahlung besteht aus zwei Dingen, dem Kapital und dem Zins. Die Zinsen werden für jede Periode berechnet, zum Beispiel die monatlichen Rückzahlungen über 10 Jahre, wir erhalten 120 Perioden.

Der obige Screenshot zeigt die Aufteilung eines Darlehens (eine Gesamtperiode von 120) unter Verwendung der PPMT- und IPMT-Formeln. Die Argumente der beiden Formeln sind gleich und werden wie folgt aufgegliedert:

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

= - INTPER (rate; num_period; length; principal; [residual]; [terme])

Die Argumente sind die gleichen wie für die PMT-Formel im ersten Teil, mit Ausnahme von num_period, das hinzugefügt wird, um den Zeitraum anzuzeigen, über den das Darlehen abgebrochen werden soll. das Prinzip und Interesse dafür. Nehmen wir ein Beispiel:

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

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

Das Ergebnis ist das im Screenshot "Loan Decomposition" angezeigte Ergebnis im analysierten Zeitraum von "1", also der erste Zeitraum oder der erste Monat.Für dieses bezahlen wir 1161 Dollar. 88, aufgeteilt in $ 856, 20 Principal und $ 305. 68 Zinsen.

Excel-Loan-Berechnung

Es ist jetzt auch möglich, die Tilgungs- und Zinsrückzahlung für mehrere Perioden, wie die ersten 12 Monate oder die ersten 15 Monate, zu berechnen.

= - CUMPRINC (Rate; Länge; Prinzipal; Startdatum; Enddatum; Typ)

= - CUMIPMT (Rate; Länge; Prinzipal; Startdatum; Enddatum; Typ)

Wir finden die Argumente, Rate, Länge, Principal und 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. Der erste zeigt den Beginn des zu analysierenden Zeitraums an und der zweite das Ende. Nehmen wir ein Beispiel:

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

= - CUMPRINC ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000; 1; 12; 0)

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

= - CUMIPMT ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000; 1; 12; 0)

Das Ergebnis ist ein im Screenshot gezeigtes "Cumul 1. Jahr", also die analysierten Perioden reichen von 1 bis 12, von der ersten Periode (erster Monat) bis zum zwölften (12. Monat). Über ein Jahr würden wir 10 419, 55 Principal und 3 522. 99 Zinsen zahlen.

Amortisation des Darlehens

Die früheren Formeln erlauben es uns, unseren Zeitplan periodisch zu erstellen, wie viel wir monatlich an Kapital und Zinsen zahlen und wie viel noch zu zahlen ist.

Erstellen eines Darlehensplans in Excel

Um einen Darlehensplan zu erstellen, verwenden wir verschiedene oben beschriebene Formeln und erweitern diese um die Anzahl der Perioden.

Geben Sie in der ersten Periodenspalte einfach "1" als ersten Punkt ein und ziehen Sie die Zelle dann nach unten. In unserem Fall benötigen wir 120 Perioden seit einer 10-jährigen Darlehenszahlung multipliziert mit 12 Monaten = 120.

Die zweite Spalte ist der monatliche Betrag, den wir jeden Monat zahlen müssen und der über den gesamten Kreditplan konstant ist. Um es zu berechnen, fügen 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 Auftraggeber, der monatlich zurückgezahlt wird. Zum Beispiel werden wir für die 40. Periode 945 $ zurückzahlen. 51 auf unseren monatlichen Gesamtbetrag von $ 1, 161. 88. 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 ist der Zinssatz, für den wir den auf unseren monatlichen Betrag zurückgezahlten Kapitalbetrag berechnen, um herauszufinden, wie Viel Zinsen sind zu zahlen unter Verwendung der Formel:

= - 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 ist wie folgt:

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

= 120000 + CUMPRINC ((1 + 3, 10%) ^ (1/12); 10 * 12; 120000; 1; 1; 0)

Die Formel verwendet eine Kombination von Kapital unter einem Zeitraum voraus mit der Zelle, die das geborgte Haupt enthält. Diese Periode beginnt sich zu ändern, wenn wir die Zelle kopieren und nach unten ziehen.Der Screenshot unten zeigt, dass am Ende von 120 Perioden unser Darlehen zurückgezahlt wird.