Das Tutorial zeigt, wie Sie einen Tilgungsplan in Excel erstellen, um regelmäßige Zahlungen für ein Tilgungskredit oder eine Hypothek detailliert zu beschreiben.

Ein Tilgungsdarlehen ist nur eine ausgefallene Möglichkeit, ein Darlehen zu definieren, das während der gesamten Laufzeit des Darlehens in Raten zurückgezahlt wird.

Grundsätzlich amortisieren sich alle Kredite auf die eine oder andere Weise. Zum Beispiel hat ein vollständig amortisierendes Darlehen für 24 Monate 24 gleiche monatliche Zahlungen., Jede Zahlung bezieht einen bestimmten Betrag auf das Kapital und einen Teil auf die Zinsen. Um jede Zahlung für ein Darlehen detailliert zu beschreiben, können Sie einen Tilgungsplan für Kredite erstellen.

Ein Tilgungsplan ist eine Tabelle, die periodische Zahlungen für ein Darlehen oder eine Hypothek im Laufe der Zeit auflistet, jede Zahlung in Kapital und Zinsen aufschlüsselt und den verbleibenden Saldo nach jeder Zahlung anzeigt.,

  • So erstellen Sie einen Tilgungsplan für Kredite in Excel
  • Tilgungsplan für eine variable Anzahl von Perioden
  • Tilgungsplan für Kredite mit zusätzlichen Zahlungen
  • Excel – Tilgungsvorlage

So erstellen Sie einen Tilgungsplan für Kredite in Excel

Um einen Zeitplan für die Tilgung von Darlehen oder Hypotheken in Excel zu erstellen, müssen wir die folgenden Funktionen verwenden:

  • PMT-Funktion-berechnet die gesamtbetrag einer regelmäßigen Zahlung. Dieser Betrag bleibt während der gesamten Laufzeit des Darlehens konstant.,
  • PPMT-Funktion-Ruft den Hauptteil jeder Zahlung ab, die in Richtung des Darlehenskapitals geht, dh den Betrag, den Sie ausgeliehen haben. Dieser Betrag erhöht sich für nachfolgende Zahlungen.
  • IPMT-Funktion-findet den Zinsteil jeder Zahlung, die in Richtung Zinsen geht. Dieser Betrag sinkt mit jeder Zahlung.

Nun gehen wir den Prozess Schritt für Schritt durch.,

Richten Sie die Abschreibungstabelle ein

Definieren Sie zunächst die Eingabezellen, in die Sie die bekannten Komponenten eines Darlehens eingeben:

  • C2 – jährlicher Zinssatz
  • C3 – Darlehenslaufzeit in Jahren
  • C4 – Anzahl der Zahlungen pro Jahr
  • C5 – Darlehensbetrag

Als nächstes erstellen Sie eine Abschreibungstabelle mit den Beschriftungen (Zeitraum, Zahlung, Zinsen, Kapital)., Balance) in A7:E7., Geben Sie in der Spalte Periode eine Reihe von Zahlen ein, die der Gesamtzahl der Zahlungen entsprechen (in diesem Beispiel 1 – 24):

Mit allen bekannten Komponenten kommen wir zu den interessantesten Teil – Kreditabschreibungsformeln.

Gesamtzahlungsbetrag berechnen (PMT-Formel)

Der Zahlungsbetrag wird mit der Funktion PMT(rate, nper, pv, , ) berechnet.

Um verschiedene Zahlungsfrequenzen korrekt zu handhaben (z. B. wöchentlich, monatlich, vierteljährlich usw.).,), Sie sollte im Einklang mit der die angegebenen Werte für rate und nper Argumente:

  • Rate – teilen Sie den jährlichen Zinssatz durch die Anzahl der Zahlungsperioden pro Jahr ($C$2/$C$4).
  • Nper-Multiplizieren Sie die Anzahl der Jahre mit der Anzahl der Zahlungsfristen pro Jahr ($C$3*$C$4).
  • Geben Sie für das Argument pv den Darlehensbetrag ein ($C$5).
  • Die Argumente fv und type können weggelassen werden, da ihre Standardwerte für uns einwandfrei funktionieren (Saldo nach der letzten Zahlung soll 0 sein; Zahlungen werden am Ende jedes Zeitraums geleistet).,

Wenn wir die obigen Argumente zusammenfügen, erhalten wir diese Formel:

=PMT($C$2/$C$4, $C$3*$C$4, $C$5)

Bitte beachten Sie, dass wir absolute Zellreferenzen verwenden, da diese Formel ohne Änderungen in die folgenden Zellen kopiert werden sollte.,

Geben sie die PMT formel in B8, ziehen sie es unten die spalte, und sie werden sehen eine konstante zahlung menge für alle die perioden:

Berechnen interesse (IPMT formel)

Zu finden die interesse teil von jeder periodischen zahlung, verwenden die IPMT(rate, pro, nper, pv, , ) Funktion:

=IPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5)

Alle Argumente sind dieselben wie in der PMT-Formel, mit Ausnahme des Arguments pro, das den Zahlungszeitraum angibt., Dieses Argument wird als relative Zellreferenz (A8) angegeben, da es sich basierend auf der relativen Position einer Zeile ändern soll, in die die Formel kopiert wird.,

Diese Formel geht zu C8, und dann kopieren Sie es auf so viele Zellen wie nötig:

Find principal (PPMT formula)

Um den Hauptteil jeder periodischen Zahlung zu berechnen, verwenden Sie diese PPMT Formel:

=PPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5)

Die Syntax und Argumente sind genau die gleichen wie in der IPMT formel diskutiert oben:

Diese formel geht zu spalte D, beginnend in D8:

Tipp. Um zu überprüfen, ob Ihre Berechnungen zu diesem Zeitpunkt korrekt sind, addieren Sie die Zahlen in den Spalten Principal und Interest., Die Summe sollte dem Wert in der Zahlungsspalte in derselben Zeile entsprechen.

Holen Sie sich den Restbetrag

Um den Restbetrag für jeden Zeitraum zu berechnen, verwenden wir zwei verschiedene Formeln.

Um den Saldo nach der ersten Zahlung in E8 zu ermitteln, addieren Sie den Darlehensbetrag (C5) und das Kapital der ersten Periode (D8):

=C5+D8

Da der Darlehensbetrag eine positive Zahl ist und das Kapital eine negative Zahl ist, wird letzteres tatsächlich von der ersteren abgezogen.,

Addieren Sie für den zweiten und alle nachfolgenden Perioden den vorherigen Saldo und den Hauptwert dieses Zeitraums:

=E8+D9

Die obige Formel lautet E9 und kopieren Sie sie in die Spalte. Aufgrund der Verwendung relativer Zellreferenzen passt sich die Formel für jede Zeile korrekt an.

Das war ‚ s! Unser monatlicher Tilgungsplan für Kredite ist fertig:

Tipp: Zahlungen als positive Zahlen zurückgeben

Da ein Darlehen von Ihrem Bankkonto ausgezahlt wird, geben Excel-Funktionen die Zahlung, Zinsen und Kapital als negative Zahlen zurück., Standardmäßig werden diese Werte rot hervorgehoben und in Klammern eingeschlossen, wie Sie im Bild oben sehen können.

Wenn Sie es vorziehen, alle Ergebnisse als positive Zahlen zu haben, setzen Sie ein Minuszeichen vor die PMT -, IPMT-und PPMT-Funktionen.

Verwenden Sie für die Saldoformeln Subtraktion anstelle von Addition, wie im folgenden Screenshot gezeigt:

Amortisationsplan für eine variable Anzahl von Perioden

Im obigen Beispiel haben wir einen Tilgungsplan für die vordefinierte Anzahl von Zahlungsfristen erstellt., Diese schnelle einmalige Lösung funktioniert gut für ein bestimmtes Darlehen oder Hypothek.

Wenn Sie einen wiederverwendbaren Amortisationsplan mit einer variablen Anzahl von Perioden erstellen möchten, müssen Sie einen umfassenderen Ansatz verfolgen, der unten beschrieben wird.

Geben Sie die maximale Anzahl von Perioden ein

Fügen Sie in die Spalte Zeitraum die maximale Anzahl von Zahlungen ein, die Sie für ein Darlehen zulassen möchten, z. B. von 1 bis 360. Sie können die AutoFill-Funktion von Excel nutzen, um eine Reihe von Zahlen schneller einzugeben.,

Verwenden Sie IF-Anweisungen in Amortisationsformeln

Da Sie jetzt viele übermäßige Periodennummern haben, müssen Sie die Berechnungen irgendwie auf die tatsächliche Anzahl der Zahlungen für ein bestimmtes Darlehen beschränken. Dies kann durch Einwickeln jeder Formel in eine IF-Anweisung erfolgen. Der logische Test der IF-Anweisung prüft, ob die Periodennummer in der aktuellen Zeile kleiner oder gleich der Gesamtzahl der Zahlungen ist. Wenn der logische Test WAHR ist, wird die entsprechende Funktion berechnet; wenn FALSE, wird eine leere Zeichenfolge zurückgegeben.,

Wenn sich Periode 1 in Zeile 8 befindet, geben Sie die folgenden Formeln in die entsprechenden Zellen ein und kopieren Sie sie dann in die gesamte Tabelle.,

Payment (B8):

=IF(A8<=$C$3*$C$4, PMT($C$2/$C$4, $C$3*$C$4, $C$5), "")

Interest (C8):

=IF(A8<=$C$3*$C$4, IPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5), "")

Principal (D8):

=IF(A8<=$C$3*$C$4,PPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5), "")

Balance:

Für Periode 1 (E8) ist die Formel die gleiche wie im vorherigen Beispiel:

=C5+D8

Für Periode 2 (E9) und alle nachfolgenden Perioden nimmt die Formel diese Form an:

=IF(A9<=$C$3*$C$4, E8+D9, "")

Als Ergebnis haben Sie einen korrekt berechneten Amortisationsplan und eine Reihe leerer Zeilen mit den Periodennummern nach dem darlehen wird ausgezahlt.,

Verstecken Extraperioden Zahlen

Wenn Sie mit einer Reihe von überflüssigen Periodennummern nach der letzten Zahlung angezeigt leben können, können Sie die geleistete Arbeit betrachten und diesen Schritt überspringen. Wenn Sie nach Perfektion streben, blenden Sie alle nicht verwendeten Perioden aus, indem Sie eine bedingte Formatierungsregel festlegen, die die Schriftfarbe für alle Zeilen nach der letzten Zahlung auf Weiß setzt.,

Wählen Sie dazu alle Datenzeilen aus Ihrer Amortisierungstabelle (in unserem Fall A8:E367) aus und klicken Sie auf Registerkarte Start > Bedingte Formatierung > Neue Regel… > Verwenden Sie eine Formel, um zu bestimmen, welche Zellen formatiert werden sollen.

Geben Sie in das entsprechende Feld die folgende Formel ein, die überprüft, ob die Periodennummer in Spalte A größer als die Gesamtzahl der Zahlungen ist:

=$A8>$C$3*$C$4

Wichtiger Hinweis!, Damit die Formel für die bedingte Formatierung korrekt funktioniert, verwenden Sie unbedingt absolute Zellreferenzen für die Zellen Darlehenslaufzeit und Zahlungen pro Jahr, die Sie multiplizieren ($C$3*$C$4). Das Produkt wird mit der Zelle Periode 1 verglichen, für die Sie eine Referenz für gemischte Zellen verwenden – absolute Spalte und relative Zeile ($A8).

Klicken Sie anschließend auf die Schaltfläche Format… und wählen Sie die weiße Schriftfarbe aus. Fertig!,

Machen Sie eine Kreditübersicht

Um die zusammenfassenden Informationen zu Ihrem Darlehen auf einen Blick anzuzeigen, fügen Sie oben in Ihrem Tilgungsplan einige weitere Formeln hinzu.

Zahlungen insgesamt (F2):

=-SUM(B8:B367)

Zinsen insgesamt (F3):

=-SUM(C8:C367)

Wenn Sie Zahlungen als positive Zahlen haben, entfernen Sie das Minuszeichen aus den obigen Formeln.

Das war ‚ s! Unser Kredit Tilgungsplan ist abgeschlossen und gut zu gehen!,

Download loan amortization schedule for Excel

So erstellen Sie einen Tilgungsplan für Kredite mit zusätzlichen Zahlungen in Excel

Die in den vorherigen Beispielen beschriebenen Tilgungspläne sind einfach zu erstellen und zu befolgen (hoffentlich :). Sie lassen jedoch eine nützliche Funktion aus, an der viele Kreditzahler interessiert sind – zusätzliche Zahlungen, um einen Kredit schneller zurückzuzahlen. In diesem Beispiel sehen wir uns an, wie Sie einen Tilgungsplan für Kredite mit zusätzlichen Zahlungen erstellen.

Eingabezellen definieren

Beginnen Sie wie gewohnt mit dem Einrichten der Eingabezellen., In diesem Fall nennen wir diese Zellen wie unten beschrieben, um das Lesen unserer Formeln zu erleichtern:

  • interestRate – C2 (jährlicher Zinssatz)
  • LoanTerm – C3 (Kreditlaufzeit in Jahren)
  • PaymentsPerYear – C4 (Anzahl der Zahlungen pro Jahr)
  • LoanAmount – C5 (Gesamtkreditbetrag)
  • ExtraPayment – C6 (zusätzliche Zahlung pro Zeitraum)

Berechnen Sie eine geplante Zahlung

Abgesehen von den Eingabezellen ist für unsere weiteren Berechnungen eine weitere vordefinierte Zelle erforderlich – der geplante Zahlungsbetrag, d.h., der Betrag, der für ein Darlehen zu zahlen ist, wenn keine zusätzlichen Zahlungen geleistet werden. Dieser Betrag wird mit folgender Formel berechnet:

=IFERROR(-PMT(InterestRate/PaymentsPerYear, LoanTerm*PaymentsPerYear, LoanAmount), "")

Bitte achten Sie darauf, dass wir vor der PMT-Funktion ein Minuszeichen setzen, um das Ergebnis als positive Zahl zu erhalten. Um Fehler zu vermeiden, falls einige der Eingabezellen leer sind, schließen wir die PMT-Formel in die IFERROR-Funktion ein.

Geben Sie diese Formel in eine Zelle ein (in unserem Fall G2) und benennen Sie diese Zelle ScheduledPayment.,

Richten Sie die Tilgungstabelle ein

Erstellen Sie eine Tilgungstabelle für Kredite mit den im folgenden Screenshot gezeigten Überschriften. Geben Sie in der Spalte Periode eine Reihe von Zahlen ein, die mit Null beginnen (Sie können die Zeile Periode 0 bei Bedarf später ausblenden).

Wenn Sie einen wiederverwendbaren Tilgungsplan erstellen möchten, geben Sie die maximal mögliche Anzahl von Zahlungsfristen ein (in diesem Beispiel 0 bis 360).

Ziehen Sie für den Zeitraum 0 (in unserem Fall Zeile 9) den Saldowert, der dem ursprünglichen Darlehensbetrag entspricht., Alle anderen Zellen in dieser Zeile bleibt leer:

Formel im G9:

=LoanAmount

4. Erstellen Sie Formeln für den Amortisationsplan mit zusätzlichen Zahlungen

Dies ist ein wichtiger Teil unserer Arbeit. Da die integrierten Funktionen von Excel keine zusätzlichen Zahlungen vorsehen, müssen wir die gesamte Mathematik selbst erledigen.

Hinweis:. In diesem Beispiel befindet sich Periode 0 in Zeile 9 und Periode 1 in Zeile 10. Wenn Ihre Amortisierungstabelle in einer anderen Zeile beginnt, passen Sie die Zellreferenzen entsprechend an.,

Geben Sie die folgenden Formeln in Zeile 10 (Periode 1) ein und kopieren Sie sie dann für alle verbleibenden Perioden nach unten.

Geplante Zahlung (B10):

Wenn der geplante Zahlungsbetrag (Zelle G2) kleiner oder gleich dem Restbetrag (G9) ist, verwenden Sie die geplante Zahlung. Andernfalls fügen Sie den Restbetrag und die Zinsen für den Vormonat hinzu.

=IFERROR(IF(ScheduledPayment<=G9, ScheduledPayment, G9+G9*InterestRate/PaymentsPerYear), "")

Als zusätzliche Vorsichtsmaßnahme wickeln wir diese und alle nachfolgenden Formeln in die IFERROR Funktion ein., Dies verhindert eine Reihe verschiedener Fehler, wenn einige der Eingabezellen leer sind oder ungültige Werte enthalten.

Extrazahlung (C10):

Verwenden Sie eine IF-Formel mit der folgenden Logik:

Wenn der Extrazahlungsbetrag (Zelle C6 genannt) kleiner ist als die Differenz zwischen dem verbleibenden Saldo und dem Kapital dieser Periode (G9-E10), geben Sie ExtraZahlung zurück; Andernfalls verwenden Sie die Differenz.

=IFERROR(IF(ExtraPayment<G9-E10, ExtraPayment, G9-E10), "")

Tip. Wenn Sie variable zusätzliche Zahlungen haben, geben Sie einfach die einzelnen Beträge direkt in die Spalte Zusätzliche Zahlung ein.,

Gesamtzahlung (D10)

Fügen Sie einfach die geplante Zahlung (B10) und die zusätzliche Zahlung (C10) für den aktuellen Zeitraum hinzu:

=IFERROR(B10+C10, "")

Principal (E10)

Wenn die geplante Zahlung für einen bestimmten Zeitraum größer als Null ist, geben Sie einen kleineren der beiden Werte zurück: geplante Zahlung abzüglich Zinsen (B10-F10) oder den verbleibenden Restbetrag (G9); andernfalls Null zurückgeben.

=IFERROR(IF(B10>0, MIN(B10-F10, G9), 0), "")

Bitte beachten Sie, dass der Auftraggeber nur den Teil der geplanten Zahlung enthält (nicht die zusätzliche Zahlung!) das geht in Richtung des Darlehensgebers.,

Zinsen (F10)

Wenn die geplante Zahlung für einen bestimmten Zeitraum größer als Null ist, teilen Sie den jährlichen Zinssatz (Zelle C2 genannt) durch die Anzahl der Zahlungen pro Jahr (Zelle C4 genannt) und multiplizieren Sie das Ergebnis mit dem nach der vorherigen Periode verbleibenden Saldo; Andernfalls geben Sie 0 zurück.

=IFERROR(IF(B10>0, InterestRate/PaymentsPerYear*G9, 0), "")

Saldo (G10)

Wenn der Restbetrag (G9) größer als Null ist, subtrahieren Sie den Hauptteil der Zahlung (E10) und die zusätzliche Zahlung (C10) vom Restbetrag nach der vorherigen Periode (G9); Andernfalls geben Sie 0 zurück.,

=IFERROR(IF(G9 >0, G9-E10-C10, 0), "")

Hinweis:. Weil einige der Formeln aufeinander verweisen (keine zirkuläre Referenz!), Sie zeigen möglicherweise falsche Ergebnisse in den Prozess. Beginnen Sie also erst mit der Fehlerbehebung, wenn Sie die allerletzte Formel in Ihre Amortisationstabelle eingegeben haben.

Wenn alles richtig gemacht, Ihr Darlehen Tilgungsplan an dieser Stelle sollte etwa so aussehen:

Verstecken extra Perioden

Richten Sie eine bedingte Formatierungsregel, um die Werte in nicht verwendeten Perioden zu verstecken, wie in diesem Tipp erläutert.,zahlungen pro Jahr:

=LoanTerm*PaymentsPerYear

Tatsächliche Anzahl der Zahlungen:

Zählen Sie Zellen in der gesamten Zahlungsspalte, die größer als Null sind, beginnend mit Periode 1:

=COUNTIF(D10:D369,">"&0)

Zusätzliche Zahlungen insgesamt:

Addieren Sie Zellen in der zusätzlichen Zahlungsspalte, beginnend mit Periode 1:

=SUM(C10:C369)

Gesamtzinsen:

Addieren Sie Zellen in der Spalte Zinsen, beginnend mit Periode 1:

=SUM(F10:F369)

Optional können Sie die Zeile Periode 0 ausblenden, und Ihr Tilgungsplan für Kredite mit zusätzlichen Zahlungen ist fertig!, Der Screenshot unten zeigt das Endergebnis:

Download loan amortization schedule with extra payments

Amortization schedule Excel template

Um in kürzester Zeit einen erstklassigen Tilgungsplan für Kredite zu erstellen, verwenden Sie die integrierten Vorlagen von Excel. Gehen Sie einfach zur Datei > New, geben Sie“ Amortisierungsplan“in das Suchfeld ein und wählen Sie die gewünschte Vorlage aus, z. B. diese mit zusätzlichen Zahlungen:

So erstellen Sie einen Kredit-oder Hypothekenabschreibungsplan in Excel., Ich danke Ihnen für das Lesen und hoffe, Sie nächste Woche auf unserem Blog zu sehen!

Verfügbare Downloads

Tilgungsplan Beispielarbeitsmappe

  • So berechnen Sie Zinseszinsen in Excel
  • So finden Sie CAGR (Compound annual Growth rate) in Excel
  • Berechnen des Prozentsatzes in Excel mit Formelbeispielen

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.