Das Erstellen eines Tilgungsplans für Kredite in Excel kann eine wertvolle Fähigkeit sein, mit der Sie Ihre Kreditrückzahlungen effektiv visualisieren und verwalten können. Ein Tilgungsplan ist eine Tabelle, in der jede regelmäßige Zahlung für ein Tilgungsdarlehen (normalerweise eine Hypothek oder ein Autokredit) aufgeführt ist. Dabei wird jede Zahlung in Zins- und Tilgungsanteile aufgeschlüsselt und der verbleibende Restbetrag nach jeder Zahlung angezeigt. Sehen wir uns eine Schritt-für-Schritt-Anleitung zum Erstellen eines solchen Zeitplans in Excel an.

Was ist ein Tilgungsplan?

Erstellen Sie einen Tilgungsplan in Excel

Erstellen Sie einen Tilgungsplan für eine variable Anzahl von Perioden

Erstellen Sie einen Tilgungsplan mit zusätzlichen Zahlungen

Erstellen Sie mithilfe einer Excel-Vorlage einen Tilgungsplan (mit zusätzlichen Zahlungen).

Was ist ein Tilgungsplan?

Ein Tilgungsplan ist eine detaillierte Tabelle, die in Kreditberechnungen verwendet wird und den Prozess der Tilgung eines Kredits im Laufe der Zeit darstellt. Tilgungspläne werden üblicherweise für festverzinsliche Kredite wie Hypotheken, Autokredite und Privatkredite verwendet, bei denen der Zahlungsbetrag während der gesamten Kreditlaufzeit konstant bleibt, sich jedoch das Verhältnis der Zahlung zu Zinsen und Kapital im Laufe der Zeit ändert.

Um einen Kredittilgungsplan in Excel zu erstellen, sind die integrierten Funktionen PMT, PPMT und IPMT von entscheidender Bedeutung. Lassen Sie uns verstehen, was jede Funktion tut:

• PMT-Funktion: Mit dieser Funktion wird die Gesamtzahlung pro Periode für einen Kredit auf Basis konstanter Zahlungen und eines konstanten Zinssatzes berechnet.
• IPMT-Funktion: Diese Funktion berechnet den Zinsanteil einer Zahlung für einen bestimmten Zeitraum.
• PPMT-Funktion: Mit dieser Funktion wird der Kapitalanteil einer Zahlung für einen bestimmten Zeitraum berechnet.

Mithilfe dieser Funktionen in Excel können Sie einen detaillierten Tilgungsplan erstellen, der die Zins- und Kapitalbestandteile jeder Zahlung sowie den verbleibenden Restbetrag des Darlehens nach jeder Zahlung anzeigt.

Erstellen Sie einen Tilgungsplan in Excel

In diesem Abschnitt stellen wir zwei unterschiedliche Methoden zum Erstellen eines Tilgungsplans in Excel vor. Diese Methoden gehen auf unterschiedliche Benutzerpräferenzen und Kenntnisstände ein und stellen sicher, dass jeder, unabhängig von seinen Excel-Kenntnissen, erfolgreich einen detaillierten und genauen Tilgungsplan für seinen Kredit erstellen kann.

Formeln bieten ein tieferes Verständnis der zugrunde liegenden Berechnungen und bieten die Flexibilität, den Zeitplan an spezifische Anforderungen anzupassen. Dieser Ansatz ist ideal für diejenigen, die eine praktische Erfahrung und einen klaren Einblick in die Aufteilung jeder Zahlung in Kapital- und Zinskomponenten wünschen. Lassen Sie uns nun den Prozess der Erstellung eines Tilgungsplans in Excel Schritt für Schritt aufschlüsseln:

⭐️ Schritt 1: Richten Sie die Kreditinformationen und die Tilgungstabelle ein

1. Geben Sie die entsprechenden Kreditinformationen wie Jahreszins, Kreditlaufzeit in Jahren, Anzahl der Zahlungen pro Jahr und Kreditbetrag in die Zellen ein, wie im folgenden Screenshot gezeigt:
   
2. Erstellen Sie dann in Excel eine Tilgungstabelle mit den angegebenen Bezeichnungen, z. B. Periode, Zahlung, Zinsen, Kapital, Restsaldo in den Zellen A7:E7.
3. Geben Sie in der Spalte „Periode“ die Periodennummern ein. In diesem Beispiel beträgt die Gesamtzahl der Zahlungen 24 Monate (2 Jahre), daher geben Sie die Zahlen 1 bis 24 in die Spalte „Periode“ ein. Siehe Screenshot:
   
4. Sobald Sie die Tabelle mit den Beschriftungen und Periodennummern eingerichtet haben, können Sie basierend auf den Besonderheiten Ihres Darlehens Formeln und Werte für die Spalten „Zahlung“, „Zinsen“, „Kapital“ und „Saldo“ eingeben.

⭐️ Schritt 2: Berechnen Sie den Gesamtzahlungsbetrag mithilfe der PMT-Funktion

Die Syntax des PMT lautet:

Geben Sie die folgende Formel in Zelle B7 ein und ziehen Sie dann den Füllpunkt nach unten, um diese Formel in andere Zellen zu füllen. Sie sehen dann einen konstanten Zahlungsbetrag für alle Zeiträume. Siehe Screenshot:

= -PMT($B$1/$B$3, $B$2*$B$3, $B$4)

⭐️ Schritt 3: Berechnen Sie die Zinsen mithilfe der IPMT-Funktion

In diesem Schritt berechnen Sie die Zinsen für jeden Zahlungszeitraum mithilfe der IPMT-Funktion von Excel.

Geben Sie die folgende Formel in Zelle C7 ein und ziehen Sie dann den Füllpunkt in der Spalte nach unten, um diese Formel auszufüllen und die Zinsen für jeden Zeitraum zu erhalten.

=-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ Schritt 4: Berechnen Sie den Kapitalbetrag mithilfe der PPMT-Funktion

Nach der Berechnung der Zinsen für jede Periode besteht der nächste Schritt bei der Erstellung eines Tilgungsplans darin, den Kapitalanteil jeder Zahlung zu berechnen. Dies geschieht mithilfe der PPMT-Funktion, die darauf ausgelegt ist, den Kapitalanteil einer Zahlung für einen bestimmten Zeitraum auf der Grundlage konstanter Zahlungen und eines konstanten Zinssatzes zu ermitteln.

Die Syntax des IPMT lautet:

Die Syntax und die Parameter für die PPMT-Formel sind identisch mit denen, die in der zuvor besprochenen IPMT-Formel verwendet wurden.

Geben Sie die folgende Formel in Zelle D7 ein und ziehen Sie dann den Füllpunkt in der Spalte nach unten, um den Hauptbetrag für jede Periode einzugeben. Siehe Screenshot:

=-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ Schritt 5: Berechnen Sie den Restbetrag

Nachdem Sie sowohl die Zinsen als auch den Kapitalbetrag für jede Zahlung berechnet haben, besteht der nächste Schritt in Ihrem Tilgungsplan darin, den verbleibenden Restbetrag des Darlehens nach jeder Zahlung zu berechnen. Dies ist ein entscheidender Teil des Zeitplans, da er zeigt, wie der Kreditsaldo im Laufe der Zeit abnimmt.

1. Geben Sie in die erste Zelle Ihrer Saldospalte – E7 – die folgende Formel ein, was bedeutet, dass der Restsaldo dem ursprünglichen Kreditbetrag abzüglich des Kapitalanteils der ersten Zahlung entspricht:

   =B4-D7

   
2. Berechnen Sie für die zweite und alle folgenden Perioden den Restsaldo, indem Sie die Hauptzahlung der aktuellen Periode vom Saldo der vorherigen Periode abziehen. Bitte wenden Sie die folgende Formel in Zelle E8 an:

   =E7-D8

    Note: Der Verweis auf die Saldenzelle sollte relativ sein, sodass er aktualisiert wird, wenn Sie die Formel nach unten ziehen.
   
3. Ziehen Sie dann den Füllpunkt nach unten zur Spalte. Wie Sie sehen, wird jede Zelle automatisch angepasst, um den Restsaldo basierend auf den aktualisierten Hauptzahlungen zu berechnen.
   

⭐️ Schritt 6: Erstellen Sie eine Kreditzusammenfassung

Nachdem Sie Ihren detaillierten Tilgungsplan erstellt haben, können Sie durch die Erstellung einer Kreditzusammenfassung einen schnellen Überblick über die wichtigsten Aspekte Ihres Kredits erhalten. Diese Zusammenfassung enthält in der Regel die Gesamtkosten des Darlehens und die insgesamt gezahlten Zinsen.

● So berechnen Sie die Gesamtzahlungen:

=SUM(B7:B30)

● So berechnen Sie die Gesamtzinsen:

=SUM(C7:C30)

⭐️ Ergebnis:

Jetzt wurde erfolgreich ein einfacher, aber umfassender Tilgungsplan für Kredite erstellt. siehe Screenshot:

Erstellen Sie einen Tilgungsplan für eine variable Anzahl von Perioden

Im vorherigen Beispiel erstellen wir einen Kreditrückzahlungsplan für eine feste Anzahl von Zahlungen. Dieser Ansatz eignet sich perfekt für die Abwicklung eines bestimmten Darlehens oder einer Hypothek, bei der sich die Bedingungen nicht ändern.

Wenn Sie jedoch einen flexiblen Tilgungsplan erstellen möchten, der wiederholt für Kredite mit unterschiedlichen Laufzeiten verwendet werden kann und es Ihnen ermöglicht, die Anzahl der Zahlungen je nach Bedarf für verschiedene Kreditszenarien zu ändern, müssen Sie einer detaillierteren Methode folgen.

⭐️ Schritt 1: Richten Sie die Kreditinformationen und die Tilgungstabelle ein

1. Geben Sie die entsprechenden Kreditinformationen wie Jahreszins, Kreditlaufzeit in Jahren, Anzahl der Zahlungen pro Jahr und Kreditbetrag in die Zellen ein, wie im folgenden Screenshot gezeigt:
   
2. Erstellen Sie dann in Excel eine Tilgungstabelle mit den angegebenen Bezeichnungen, z. B. Periode, Zahlung, Zinsen, Kapital, Restsaldo in den Zellen A7:E7.
3. Geben Sie in der Spalte „Zeitraum“ die höchste Anzahl an Zahlungen ein, die Sie für einen Kredit in Betracht ziehen könnten. Geben Sie beispielsweise Zahlen im Bereich von 1 bis 360 ein. Dies kann einen Standardkredit mit einer Laufzeit von 30 Jahren abdecken, wenn Sie monatliche Zahlungen leisten.
   

⭐️ Schritt 2: Ändern Sie die Zahlungs-, Zins- und Kapitalformeln mit der IF-Funktion

Geben Sie die folgenden Formeln in die entsprechenden Zellen ein und ziehen Sie dann den Füllpunkt, um diese Formeln auf die von Ihnen festgelegte maximale Anzahl von Zahlungsperioden zu erweitern.

● Zahlungsformel:

Normalerweise verwenden Sie die PMT-Funktion zur Berechnung der Zahlung. Um eine IF-Anweisung einzubinden, lautet die Syntaxformel:

Die Formel lautet also:

=IF(A7<=$B$2*$B$3, -PMT($B$1/$B$3, $B$2*$B$3, $B$4), "")

● Zinsformel:

Die Syntaxformel lautet:

Die Formel lautet also:

=IF(A7<=$B$2*$B$3,-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

● Prinzipielle Formel:

Die Syntaxformel lautet:

Die Formel lautet also:

=IF(A7<=$B$2*$B$3,-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

⭐️ Schritt 3: Passen Sie die Restsaldoformel an

Für den Restbetrag können Sie in der Regel den Kapitalbetrag vom vorherigen Betrag abziehen. Ändern Sie eine IF-Anweisung wie folgt:

● Die erste Balancezelle:(E7)

=B4-D7

● Die zweite Ausgleichszelle:(E8)

=IF(A8<=$B$2*$B$3, E7-D8, "") 

⭐️ Schritt 4: Erstellen Sie eine Kreditzusammenfassung

Nachdem Sie den Tilgungsplan mit den geänderten Formeln erstellt haben, besteht der nächste Schritt darin, eine Kreditzusammenfassung zu erstellen.

● So berechnen Sie die Gesamtzahlungen:

=SUM(B7:B366)

● So berechnen Sie die Gesamtzinsen:

=SUM(C7:C366)

⭐️ Ergebnis:

Jetzt verfügen Sie über einen umfassenden und dynamischen Tilgungsplan in Excel, komplett mit einer detaillierten Kreditübersicht. Wenn Sie die Zahlungsfrist anpassen, wird der gesamte Tilgungsplan automatisch aktualisiert, um diese Änderungen widerzuspiegeln. Sehen Sie sich die Demo unten an:

Erstellen Sie einen Tilgungsplan mit zusätzlichen Zahlungen

Durch die Leistung zusätzlicher Zahlungen über die geplanten hinaus kann ein Kredit schneller abbezahlt werden. Ein in Excel erstellter Tilgungsplan, der zusätzliche Zahlungen einbezieht, zeigt, wie diese zusätzlichen Zahlungen die Tilgung des Kredits beschleunigen und die insgesamt gezahlten Zinsen senken können. So können Sie es einrichten:

⭐️ Schritt 1: Richten Sie die Kreditinformationen und die Tilgungstabelle ein

1. Geben Sie die entsprechenden Kreditinformationen wie Jahreszins, Kreditlaufzeit in Jahren, Anzahl der Zahlungen pro Jahr, Kreditbetrag und Zusatzzahlung in die Zellen ein, wie im folgenden Screenshot gezeigt:
   
2. Berechnen Sie dann die geplante Zahlung.
   Zusätzlich zu den Eingabezellen wird für unsere nachfolgenden Berechnungen eine weitere vordefinierte Zelle benötigt – der geplante Zahlungsbetrag. Dies ist der reguläre Rückzahlungsbetrag für einen Kredit, sofern keine zusätzlichen Zahlungen geleistet werden. Bitte wenden Sie die folgende Formel in Zelle B6 an:

   =IFERROR(-PMT($B$1/$B$3, $B$2*$B$3, $B$4),"")

   
   
3. Erstellen Sie dann eine Tilgungstabelle in Excel:
   • Legen Sie in den Zellen A8:G8 die angegebenen Beschriftungen fest, z. B. Periode, Zahlung planen, Zusätzliche Zahlung, Gesamtzahlung, Zinsen, Kapital, Restsaldo;
   • Geben Sie in der Spalte „Periode“ die höchste Anzahl an Zahlungen ein, die Sie für einen Kredit in Betracht ziehen könnten. Geben Sie beispielsweise die Zahlen von 0 bis 360 ein. Dies kann einen Standardkredit mit einer Laufzeit von 30 Jahren abdecken, wenn Sie monatliche Zahlungen leisten;
   • Rufen Sie für Periode 0 (in unserem Fall Zeile 9) den Saldowert mit dieser Formel ab = B4, was der ursprünglichen Kreditsumme entspricht. Alle anderen Zellen in dieser Zeile sollten leer bleiben.
   • 

⭐️ Schritt 2: Erstellen Sie die Formeln für den Tilgungsplan mit zusätzlichen Zahlungen

Geben Sie die folgenden Formeln nacheinander in die entsprechenden Zellen ein. Um die Fehlerbehandlung zu verbessern, schließen wir diese und alle zukünftigen Formeln in die IFERROR-Funktion ein. Dieser Ansatz trägt dazu bei, mehrere potenzielle Fehler zu vermeiden, die auftreten könnten, wenn eine der Eingabezellen leer bleibt oder falsche Werte enthält.

● Berechnen Sie die geplante Zahlung:

Geben Sie die folgende Formel in Zelle B10 ein:

=IFERROR(IF($B$6<=G9, $B$6, G9+G9*$B$1/$B$3), "")

● Berechnen Sie die Zuzahlung:

Geben Sie die folgende Formel in Zelle C10 ein:

=IFERROR(IF($B$5<G9-E10,$B$5, G9-E10), "")

● Berechnen Sie die Gesamtzahlung:

Geben Sie die folgende Formel in Zelle D10 ein:

=IFERROR(B10+C10, "")

● Berechnen Sie den Kapitalbetrag:

Geben Sie die folgende Formel in Zelle E10 ein:

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

● Berechnen Sie die Zinsen:

Geben Sie die folgende Formel in Zelle F10 ein:

=IFERROR(IF(B10>0, $B$1/$B$3*G9, 0), "")

● Berechnen Sie den Restbetrag

Geben Sie die folgende Formel in Zelle G10 ein:

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

Sobald Sie die einzelnen Formeln ausgefüllt haben, wählen Sie den Zellbereich B10:G10 aus und verwenden Sie den Ausfüllpunkt, um diese Formeln nach unten über den gesamten Satz von Zahlungsperioden zu ziehen und zu erweitern. Für nicht genutzte Zeiträume werden in den Zellen Nullen angezeigt. Siehe Screenshot:

⭐️ Schritt 3: Erstellen Sie eine Kreditzusammenfassung

● Erhalten Sie die geplante Anzahl an Zahlungen:

=B2:B3

● Erhalten Sie die tatsächliche Anzahl der Zahlungen:

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

● Erhalten Sie zusätzliche Gesamtzahlungen:

=SUM(C10:C369)

● Erhalten Sie Gesamtzinsen:

=SUM(F10:F369)

⭐️ Ergebnis:

Indem Sie diese Schritte ausführen, erstellen Sie in Excel einen dynamischen Tilgungsplan, der zusätzliche Zahlungen berücksichtigt.

Erstellen Sie mithilfe einer Excel-Vorlage einen Tilgungsplan

Das Erstellen eines Tilgungsplans in Excel mithilfe einer Vorlage ist eine unkomplizierte und zeiteffiziente Methode. Excel bietet integrierte Vorlagen, die automatisch die Zinsen, den Kapitalbetrag und den Restbetrag jeder Zahlung berechnen. So erstellen Sie einen Tilgungsplan mithilfe einer Excel-Vorlage:

1. Klicken Sie auf Kostenlos erhalten und dann auf Installieren. Reichen Sie das > NeuGeben Sie im Suchfeld Folgendes ein: Amortisationsplan, und drücke Enter Schlüssel. Wählen Sie dann die Vorlage aus, die Ihren Anforderungen am besten entspricht, indem Sie darauf klicken. Hier wähle ich zum Beispiel die Vorlage „Einfacher Kreditrechner“ aus. Siehe Screenshot:
   
2. Wenn Sie eine Vorlage ausgewählt haben, klicken Sie auf Erstellen Klicken Sie auf die Schaltfläche, um sie als neue Arbeitsmappe zu öffnen.
3. Geben Sie dann Ihre eigenen Kreditdetails ein. Die Vorlage sollte den Zeitplan basierend auf Ihren Eingaben automatisch berechnen und ausfüllen.
4. Speichern Sie abschließend Ihre neue Tilgungsplan-Arbeitsmappe.