![Tilgungsplan für Annuitätendarlehen berechnen - Vorlage [Excel, Beispiel, Erklärung, Darlehen]](https://i.ytimg.com/vi/JTSt4hn7PpI/hqdefault.jpg)
Inhalt
- Erstellen des Formats für den Hypothekenrechner
- Erläuterung der im Hypothekenrechner verwendeten Finanzfunktionen
- Hinzufügen der Anfangsformeln zum Hypothekenrechner
- Hinzufügen der endgültigen Formeln zum Hypothekenrechner
- Hinzufügen zusätzlicher Zahlungen zum Hypothekenrechner
Ich bin ein professioneller Computeranalyst für eine große Wirtschaftsprüfungsgesellschaft. In meiner Freizeit schreibe ich über Videospiele und verschiedene andere Themen.
Die PMT-Funktion in Excel ist eine Finanzfunktion, mit der Kreditzahlungen berechnet werden (sie kann auch zur Berechnung des Werts von Investitionen im Zeitverlauf verwendet werden). Mit den zugehörigen IPMT- und PPMT-Funktionen ist es einfach, ein Arbeitsblatt zu erstellen, das einen Tilgungsplan für eine Hypothek oder ein anderes Darlehen berechnet.
Mit diesen Funktionen können Sie verschiedene Kreditsituationen modellieren, einschließlich zusätzlicher Kapitalzahlungen, unterschiedlicher Kreditbeträge, unterschiedlicher Zinssätze usw., und monatliche Gesamtzahlungen sowie Zahlungen über die Laufzeit der Hypothek berechnen.
Dieser Artikel enthält eine schrittweise Anleitung zum Erstellen eines Hypothekendarlehensrechners mit Microsoft Excel. Während das Arbeitsblatt in Microsoft Excel 2007 erstellt wurde, ist die Funktionalität in Microsoft Excel 2003 dieselbe.
Erstellen des Formats für den Hypothekenrechner
Einrichten des Anfangsformats des Hypothekenrechners
Erläuterung der im Hypothekenrechner verwendeten Finanzfunktionen
PMT-, IPMT- und PPMT-Funktionen - für ein Darlehen mit einem konstanten Zinssatz und Zahlung:
- PMT berechnet die Gesamtzahlung.
- IPMT berechnet die Zinszahlung für einen bestimmten Zeitraum.
- PPMT berechnet die Hauptzahlung für einen bestimmten Zeitraum.
Syntax dieser Funktionen:
- = PMT (Rate, Anzahl der Zahlungen, Barwert, [zukünftiger Wert], [Typ])
- = IPMT (Rate, Zeitraum, Anzahl der Zahlungen, Barwert, [zukünftiger Wert], [Typ])
- = PPMT (Rate, Zeitraum, Anzahl der Zahlungen, Barwert, [zukünftiger Wert], [Typ])
Wo:
- Bewertung: der monatliche Zinssatz für das Darlehen.
- Zeitraum: Der Zeitraum, für den Sie die Zinsen oder das Kapital berechnen möchten.
- Anzahl der Zahlungen: die Gesamtzahl der Zahlungen des Darlehens.
- Vorhandener Wert: der aktuelle Wert, den die zukünftigen Zahlungen jetzt wert sind.
- Zukünftiger Wert: Der zukünftige Wert, den Sie nach der letzten Zahlung erreichen möchten. Wenn Sie dies weglassen, wird davon ausgegangen, dass der letzte Wert Null ist.
- Art: Gibt an, wann die Zahlungen fällig sind. (0 ist das Ende der Periode, 1 ist der Beginn der Periode.)
Das Erstellen eines Hypothekenplans in Microsoft Excel ist relativ einfach, obwohl einige komplexe Finanzfunktionen verwendet werden.
Beginnen Sie mit der Erstellung eines einfachen Arbeitsblatts, in das die Daten eingegeben und die Amortisation berechnet werden. Fügen Sie in diesem Beispiel die grundlegenden Variablen wie Darlehensbetrag, Anzahl der Monate, Zinssatz usw. und die Überschriften für den tatsächlichen Tilgungsplan hinzu. Um dies zu tun:
- Erstellen Sie einen Bereich mit den wichtigsten Kreditdetails: Gesamtkredit, Zinssatz, Monate. Dies sind die Variablen, mit denen verschiedene Darlehensbedingungen modelliert werden können.
- Fügen Sie diesem Bereich die monatliche Gesamtzahlung, die Gesamtzahlungen und die insgesamt gezahlten Zinsen hinzu - diese werden später berechnet.
- Erstellen Sie den tatsächlichen Zeitplan, indem Sie die Überschrift hinzufügen: Monat, Eröffnungssaldo, Kapital, Zinsen, zusätzliche Zahlung, Schlusssaldo und Gesamtzahlung. Sobald Formeln hinzugefügt wurden, berechnet dieser Abschnitt automatisch den Tilgungsplan basierend auf den Variablen, die in den Hauptkreditbereich eingegeben wurden.
- Fügen Sie dem Bereich einen Darlehensbetrag, einen Zinssatz und die Anzahl der Monate hinzu. Dies geschieht, um sicherzustellen, dass die Formeln funktionieren und einen beliebigen Wert haben können.
- Formatieren Sie die Überschriften und den Bereich.
- Fügen Sie die Formel = -PMT (C3 / 12, C4, C2) zu Zelle C5 hinzu (siehe Erläuterung der PMT rechts). WICHTIGER HINWEIS: Das Interesse am Hauptbereich ist ein jährliches Interesse. Teilen Sie diesen Wert durch 12, um den in der Formel verwendeten monatlichen Zinssatz zu erhalten.
Das Grundformat ist vollständig. Andere Elemente, die hinzugefügt werden könnten (siehe obigen Screenshot für ein Beispiel eines komplexeren endgültigen Arbeitsblatts):
- PMI-Zahlung
- Monatliche Versicherungszahlung
- Grundsteuern
Hinzufügen der Formel = SUMME (H10: H500) zum Feld Total Interest Paid in der Zusammenfassung.
Hinzufügen der Anfangsformeln zum Hypothekenrechner
Nachdem das anfängliche Format festgelegt wurde, können die verbleibenden Formeln im Zusammenfassungsbereich und die oberste Zeile im Tilgungsplan erstellt werden. Geben Sie in jede Zelle, auf die unten verwiesen wird, die entsprechende Formel ein.
| Zelle | Formel | Kommentar |
|---|---|---|
C6 | = SUMME (H10: H500) | Summiert alle Zeilen "Gesamtzahlung", um eine Gesamtzahlung (Kapital und Zinsen) zu erhalten. |
C7 | = SUMME (E10: E500) | Summiert alle 'Zins'-Zeilen, um eine Gesamtzinszahlung zu erhalten. |
B10 | 1 | Da dies die erste Zahlung ist, wird dies auf 1 gesetzt. |
C10 | = + C2 | Da dies die erste Zahlung ist, wird dies als Darlehenswert festgelegt. |
D10 | = -PPMT ($ C $ 3/12, B10, $ C $ 4, $ C $ 2) | Berechnet die Hauptzahlung nur für diesen Zeitraum. |
E10 | = -IPMT ($ C $ 3/12, B10, $ C $ 4, $ C $ 2) | Berechnet die Zinszahlung nur für diesen Zeitraum. |
F10 |
| Noch kein Wert zugewiesen. |
G10 | = + C10-D10-F10 | Berechnet den verbleibenden Restbetrag des Darlehens, indem die Eröffnungssalden genommen und nur um die Hauptzahlungen reduziert werden. |
H10 | = + D10 + E10 + F10 | Berechnet die monatliche Gesamtzahlung (Kapital plus Zinsen). |
Hinzufügen der Formel = + IF (G10 1, IF (B10 = "", "", B10 + 1), "") zur zweiten Zeile der Spalte "Monat".
Hinzufügen der endgültigen Formeln zum Hypothekenrechner
Die erste Reihe von Formeln wurde erstellt. Jetzt können die restlichen Formeln erstellt werden. Sie sind im Wesentlichen gleich, prüfen jedoch, ob der Monat leer ist (der Monat prüft, ob der vorherige Schlusssaldo Null ist, und gibt leer zurück, wenn dies der Fall ist, andernfalls wird der Wert des Vormonats plus Eins zurückgegeben. In jeder referenzierten Zelle Geben Sie unten die entsprechende Formel ein.
| Zelle | Formel | Kommentar |
|---|---|---|
B11 | = + IF (G10> 1, IF (B10 = "", "", B10 + 1), "") | Dies prüft, ob zuerst ein Schlusssaldo vorhanden ist. dann prüft es, ob der Monat zuvor einen Wert hat; Wenn beide Werte oder true sind, wird der Wert des Vormonats um eins erhöht. |
C11 | = + IF (B11 = "", "", G10) | Wenn der Wert des Monats nicht leer ist, wird der Wert aus dem Schlusssaldo des Vormonats verwendet, andernfalls bleibt die Zelle leer. |
D11 | = IF (B11 = "", 0, -PPMT ($ C $ 3/12, B11, $ C $ 4, $ C $ 2)) | Wenn der Wert des Monats nicht leer ist, wird der Kapitalwert nur für diesen Zeitraum berechnet, andernfalls wird in der Zelle Null platziert. |
E11 | = IF (B11 = "", 0, -IPMT ($ C $ 3/12, B11, $ C $ 4, $ C $ 2)) | Wenn der Wert des Monats nicht leer ist, werden nur die Zinsen für diesen Zeitraum berechnet, andernfalls wird in der Zelle Null platziert. |
F11 | Noch kein Wert zugewiesen | |
G11 | = IF (B11 = "", 0, + C11-D11-F11) | Wenn der Wert des Monats nicht leer ist, berechnet dies den verbleibenden Restbetrag des Darlehens, indem die Eröffnungssalden genommen und nur durch Hauptzahlungen reduziert werden, andernfalls wird Null in die Zelle gesetzt. |
H11 | = IF (B11 = "", 0, + D11 + E11 + F11) | Wenn der Wert des Monats nicht leer ist, wird die monatliche Gesamtzahlung berechnet (Kapital plus Zinsen, andernfalls wird in der Zelle Null platziert. |
Kopieren Sie diese Formeln in Zeile 500. Der grundlegende Hypothekenrechner ist jetzt vollständig. Der Abschreibungsplan wird automatisch neu berechnet, wenn die Werte in den Zellen C2, C3 und C4 geändert werden.
Hinzufügen zusätzlicher Zahlungen zum Hypothekenrechner
Sobald der Entwurf abgeschlossen ist, können Sie alle Kreditdetails ändern, um die Amortisation zu modellieren. Zusätzlich können Sie während des gesamten Darlehens zusätzliche Zahlungen hinzufügen. Im Beispiel wurde jeden Monat eine zusätzliche Zahlung auf das Darlehen angewendet. Diese zusätzliche Zahlung reduziert die Laufzeit des Darlehens, den Gesamtbetrag und die gezahlten Zinsen. Diese zusätzliche Zahlung kann jederzeit während des Darlehens erfolgen und muss nicht monatlich erfolgen.
Dieser Artikel ist genau und nach bestem Wissen des Autors. Der Inhalt dient nur zu Informations- oder Unterhaltungszwecken und ersetzt nicht die persönliche Beratung oder professionelle Beratung in geschäftlichen, finanziellen, rechtlichen oder technischen Angelegenheiten.
