Erstellen Sie einen Kredit-Tilgungsplan in Excel – Eine Schritt-für-Schritt-Anleitung

Die Erstellung eines Tilgungsplans in Excel kann eine wertvolle Fähigkeit sein, die es Ihnen ermöglicht, Ihre Kreditrückzahlungen effektiv zu visualisieren und zu verwalten. Ein Tilgungsplan ist eine Tabelle, die jede regelmäßige Zahlung für einen tilgungsgebundenen Kredit (typischerweise ein Hypothekendarlehen oder Autokredit) im Detail auflistet. Sie zerlegt jede Zahlung in Zins- und Tilgungsanteile und zeigt den verbleibenden Saldo nach jeder Zahlung an. Lassen Sie uns Schritt für Schritt in die Erstellung eines solchen Plans in Excel eintauchen.

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 einen Tilgungsplan (mit zusätzlichen Zahlungen) mithilfe einer Excel-Vorlage

Was ist ein Tilgungsplan?

Ein Tilgungsplan ist eine detaillierte Tabelle, die bei der Berechnung von Krediten verwendet wird und den Prozess des zeitlichen Rückzahlens eines Kredits darstellt. Tilgungspläne werden häufig für Festzinssätze wie Hypotheken, Autokredite und persönliche Kredite verwendet, bei denen die Zahlungshöhe während der Laufzeit konstant bleibt, sich jedoch das Verhältnis zwischen Zins- und Tilgungsanteilen im Laufe der Zeit ändert.

Um einen Kredit-Tilgungsplan in Excel zu erstellen, sind die integrierten Funktionen PMT, PPMT und IPMT tatsächlich entscheidend. Lassen Sie uns verstehen, was jede Funktion bewirkt:

• PMT-Funktion: Diese Funktion wird verwendet, um die Gesamtzahlung pro Periode für einen Kredit basierend auf konstanten Zahlungen und einem konstanten Zinssatz zu berechnen.
• IPMT-Funktion: Diese Funktion berechnet den Zinsanteil einer Zahlung für eine bestimmte Periode.
• PPMT-Funktion: Diese Funktion wird verwendet, um den Tilgungsanteil einer Zahlung für eine bestimmte Periode zu berechnen.

Durch die Verwendung dieser Funktionen in Excel können Sie einen detaillierten Tilgungsplan erstellen, der die Zins- und Tilgungsanteile jeder Zahlung sowie den verbleibenden Saldo des Kredits nach jeder Zahlung anzeigt.

Erstellen Sie einen Tilgungsplan in Excel

In diesem Abschnitt stellen wir zwei unterschiedliche Methoden zur Erstellung eines Tilgungsplans in Excel vor. Diese Methoden richten sich an verschiedene Benutzerpräferenzen und Fähigkeitsniveaus und gewährleisten, dass jeder, unabhängig von seiner Excel-Kompetenz, erfolgreich einen detaillierten und genauen Tilgungsplan für seinen Kredit erstellen kann.

Formeln bieten ein tieferes Verständnis der zugrunde liegenden Berechnungen und bieten Flexibilität, den Plan entsprechend spezifischer Anforderungen anzupassen. Dieser Ansatz ist ideal für diejenigen, die praktische Erfahrung sammeln und klare Einblicke in die Aufteilung jeder Zahlung in Tilgungs- und Zinsanteile erhalten möchten. Nun, lassen Sie uns den Prozess der Erstellung eines Tilgungsplans in Excel Schritt für Schritt durchgehen:

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

1. Geben Sie die relevanten Kreditinformationen wie Jahreszinssatz, Kreditlaufzeit in Jahren, Anzahl der Zahlungen pro Jahr und Kreditbetrag in die Zellen ein, wie im folgenden Screenshot gezeigt:
   
2. Erstellen Sie dann eine Tilgungstabelle in Excel mit den angegebenen Bezeichnungen wie Periode, Zahlung, Zinsen, Tilgung, verbleibender Saldo in den Zellen A7:E7.
3. Tragen Sie in der Spalte „Periode“ die Periodennummern ein. In diesem Beispiel beträgt die Gesamtzahl der Zahlungen 24 Monate (2 Jahre), daher tragen Sie die Zahlen 1 bis 24 in die Spalte „Periode“ ein. Siehe Screenshot:
   
4. Sobald Sie die Tabelle mit den Bezeichnungen und Periodennummern eingerichtet haben, können Sie fortfahren, Formeln und Werte für die Spalten „Zahlung“, „Zinsen“, „Tilgung“ und „Saldo“ basierend auf den Details Ihres Kredits einzugeben.

⭐️ Schritt 2: Berechnen Sie den Gesamtbetrag der Zahlung mit der PMT-Funktion

Die Syntax der PMT-Funktion lautet:

Geben Sie die folgende Formel in Zelle B7 ein und ziehen Sie dann den Ausfüllknauf nach unten, um diese Formel in andere Zellen zu kopieren, und Sie werden einen konstanten Zahlungsbetrag für alle Perioden sehen. Siehe Screenshot:

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

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

In diesem Schritt berechnen Sie die Zinsen für jede Zahlungsperiode mit der IPMT-Funktion von Excel.

Geben Sie die folgende Formel in Zelle C7 ein und ziehen Sie dann den Ausfüllknauf nach unten, um diese Formel in die Spalte zu kopieren und die Zinsen für jede Periode zu erhalten.

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

⭐️ Schritt 4: Berechnen Sie die Tilgung mit der PPMT-Funktion

Nachdem Sie die Zinsen für jede Periode berechnet haben, ist der nächste Schritt bei der Erstellung eines Tilgungsplans die Berechnung des Tilgungsanteils jeder Zahlung. Dies erfolgt mit der PPMT-Funktion, die entwickelt wurde, um den Tilgungsanteil einer Zahlung für eine bestimmte Periode basierend auf konstanten Zahlungen und einem konstanten Zinssatz zu bestimmen.

Die Syntax der IPMT lautet:

Die Syntax und Parameter der PPMT-Formel sind identisch mit denen der zuvor besprochenen IPMT-Formel.

Geben Sie die folgende Formel in Zelle D7 ein und ziehen Sie dann den Ausfüllknauf nach unten, um die Tilgung für jede Periode einzufügen. Siehe Screenshot:

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

⭐️ Schritt 5: Berechnen Sie den verbleibenden Saldo

Nachdem Sie sowohl die Zinsen als auch die Tilgung jeder Zahlung berechnet haben, ist der nächste Schritt in Ihrem Tilgungsplan die Berechnung des verbleibenden Saldo des Kredits nach jeder Zahlung. Dies ist ein entscheidender Teil des Plans, da er zeigt, wie der Kreditsaldo im Laufe der Zeit abnimmt.

1. Geben Sie in die erste Zelle Ihrer Saldo-Spalte – E7 die folgende Formel ein, was bedeutet, dass der verbleibende Saldo der ursprüngliche Kreditbetrag minus dem Tilgungsanteil der ersten Zahlung ist:

   =B4-D7

   
2. Für die zweite und alle nachfolgenden Perioden berechnen Sie den verbleibenden Saldo, indem Sie die Tilgungszahlung der aktuellen Periode vom Saldo der vorherigen Periode abziehen. Wenden Sie bitte die folgende Formel in Zelle E8 an:

   =E7-D8

    Hinweis: Der Bezug auf die Saldo-Zelle sollte relativ sein, damit er sich aktualisiert, wenn Sie die Formel nach unten ziehen.
   
3. Und ziehen Sie dann den Ausfüllknauf nach unten in die Spalte. Wie Sie sehen können, wird jede Zelle automatisch angepasst, um den verbleibenden Saldo basierend auf den aktualisierten Tilgungszahlungen zu berechnen.
   

⭐️ Schritt 6: Erstellen Sie eine Kreditübersicht

Nachdem Sie Ihren detaillierten Tilgungsplan eingerichtet haben, kann die Erstellung einer Kreditübersicht einen schnellen Überblick über die wichtigsten Aspekte Ihres Kredits bieten. Diese Zusammenfassung enthält normalerweise die Gesamtkosten des Kredits und die insgesamt gezahlten Zinsen.

● Um die Gesamtzahlungen zu berechnen:

=SUM(B7:B30)

● Um die Gesamtzinsen zu berechnen:

=SUM(C7:C30)

⭐️ Ergebnis:

Jetzt wurde ein einfacher, aber umfassender Kredit-Tilgungsplan erfolgreich erstellt. Siehe Screenshot:

Entfesseln Sie die Magie von Excel mit Kutools AI

• Intelligente Ausführung: Führen Sie Zellenoperationen durch, analysieren Sie Daten und erstellen Sie Diagramme – alles angetrieben durch einfache Befehle.
• Benutzerdefinierte Formeln: Erstellen Sie maßgeschneiderte Formeln, um Ihre Arbeitsabläufe zu optimieren.
• VBA-Codierung: Schreiben und implementieren Sie VBA-Code mühelos.
• Formelinterpretation: Verstehen Sie komplexe Formeln mit Leichtigkeit.
• Textübersetzung: Überwinden Sie Sprachbarrieren in Ihren Tabellen.

Erweitern Sie Ihre Excel-Fähigkeiten mit KI-gestützten Tools. Laden Sie jetzt herunter und erleben Sie eine Effizienz wie nie zuvor!

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

Im vorherigen Beispiel haben wir einen Tilgungsplan für eine feste Anzahl von Zahlungen erstellt. Dieser Ansatz ist perfekt für die Handhabung eines bestimmten Kredits oder einer Hypothek, bei denen sich die Konditionen nicht ändern.

Wenn Sie jedoch einen flexiblen Tilgungsplan erstellen möchten, der wiederholt für Kredite mit variablen Perioden verwendet werden kann und es Ihnen ermöglicht, die Anzahl der Zahlungen je nach verschiedenen Kreditszenarien anzupassen, müssen Sie einer detaillierteren Methode folgen.

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

1. Geben Sie die relevanten Kreditinformationen wie Jahreszinssatz, Kreditlaufzeit in Jahren, Anzahl der Zahlungen pro Jahr und Kreditbetrag in die Zellen ein, wie im folgenden Screenshot gezeigt:
   
2. Erstellen Sie dann eine Tilgungstabelle in Excel mit den angegebenen Bezeichnungen wie Periode, Zahlung, Zinsen, Tilgung, verbleibender Saldo in den Zellen A7:E7.
3. Tragen Sie in der Spalte „Periode“ die höchste Anzahl von Zahlungen ein, die Sie für einen Kredit in Betracht ziehen könnten, zum Beispiel die Zahlen von 1 bis 360. Dies deckt einen Standard-30-Jahres-Kredit ab, wenn Sie monatliche Zahlungen leisten.
   

⭐️ Schritt 2: Ändern Sie die Formeln für Zahlung, Zinsen und Tilgung mit der WENN-Funktion

Geben Sie die folgenden Formeln in die entsprechenden Zellen ein und ziehen Sie dann den Ausfüllknauf, um diese Formeln bis zur maximalen Anzahl von Zahlungsperioden, die Sie festgelegt haben, nach unten zu erweitern.

● Zahlungsformel:

Normalerweise verwenden Sie die PMT-Funktion, um die Zahlung zu berechnen. Um eine WENN-Anweisung einzubinden, lautet die Syntaxformel:

Also lautet die Formel:

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

● Zinsformel:

Die Syntaxformel lautet:

Also lautet die Formel:

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

● Tilgungsformel:

Die Syntaxformel lautet:

Also lautet die Formel:

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

⭐️ Schritt 3: Passen Sie die Formel für den verbleibenden Saldo an

Für den verbleibenden Saldo ziehen Sie normalerweise die Tilgung vom vorherigen Saldo ab. Mit einer WENN-Anweisung ändern Sie dies wie folgt:

● Die erste Saldo-Zelle:(E7)

=B4-D7

● Die zweite Saldo-Zelle:(E8)

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

⭐️ Schritt 4: Erstellen Sie eine Kreditübersicht

Nachdem Sie den Tilgungsplan mit den geänderten Formeln eingerichtet haben, besteht der nächste Schritt darin, eine Kreditübersicht zu erstellen.

● Um die Gesamtzahlungen zu berechnen:

=SUM(B7:B366)

● Um die Gesamtzinsen zu berechnen:

=SUM(C7:C366)

⭐️ Ergebnis:

Jetzt haben Sie einen umfassenden und dynamischen Tilgungsplan in Excel, komplett mit einer detaillierten Kreditübersicht. Wann immer Sie die Laufzeit der Zahlungsperiode anpassen, wird der gesamte Tilgungsplan automatisch aktualisiert, um diese Änderungen widerzuspiegeln. Siehe die Demo unten:

Erstellen Sie einen Tilgungsplan mit zusätzlichen Zahlungen

Durch zusätzliche Zahlungen über die geplanten hinaus kann ein Kredit schneller abbezahlt werden. Ein Tilgungsplan, der zusätzliche Zahlungen berücksichtigt, zeigt, wie diese zusätzlichen Zahlungen die Rückzahlung des Kredits beschleunigen und die insgesamt gezahlten Zinsen verringern können. Hier erfahren Sie, wie Sie diesen Plan einrichten:

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

1. Geben Sie die relevanten Kreditinformationen wie Jahreszinssatz, Kreditlaufzeit in Jahren, Anzahl der Zahlungen pro Jahr, Kreditbetrag und zusätzliche Zahlung in die Zellen ein, wie im folgenden Screenshot gezeigt:
   
2. Berechnen Sie dann die geplante Zahlung.
   Neben den Eingabezellen wird eine weitere vordefinierte Zelle für unsere nachfolgenden Berechnungen benötigt – die Höhe der geplanten Zahlung. Dies ist der reguläre Zahlungsbetrag für einen Kredit unter der Annahme, dass 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:
   • Setzen Sie die angegebenen Bezeichnungen wie Periode, Geplante Zahlung, Zusätzliche Zahlung, Gesamtzahlung, Zinsen, Tilgung, verbleibender Saldo in den Zellen A8:G8;
   • Tragen Sie in der Spalte „Periode“ die höchste Anzahl von Zahlungen ein, die Sie für einen Kredit in Betracht ziehen könnten. Zum Beispiel füllen Sie die Zahlen von 0 bis 360 aus. Dies deckt einen Standard-30-Jahres-Kredit ab, wenn Sie monatliche Zahlungen leisten;
   • Für Periode 0 (Zeile 9 in unserem Fall) rufen Sie den Saldo-Wert mit dieser Formel =B4 ab, was dem ursprünglichen Kreditbetrag 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 Fehlerbehandlung zu verbessern, schließen wir diese und alle zukünftigen Formeln in die WENNFEHLER-Funktion ein. Dieser Ansatz hilft, 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 zusätzliche Zahlung:

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 die Tilgung:

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 verbleibenden Saldo

Geben Sie die folgende Formel in Zelle G10 ein:

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

Sobald Sie jede der Formeln abgeschlossen haben, wählen Sie den Zellbereich B10:G10 aus und verwenden Sie den Ausfüllknauf, um diese Formeln nach unten durch die gesamte Reihe von Zahlungsperioden zu ziehen und zu erweitern. Für nicht genutzte Perioden zeigen die Zellen 0 an. Siehe Screenshot:

⭐️ Schritt 3: Erstellen Sie eine Kreditübersicht

● Erhalten Sie die geplante Anzahl der Zahlungen:

=B2:B3

● Erhalten Sie die tatsächliche Anzahl der Zahlungen:

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

● Erhalten Sie die Gesamtzahl der zusätzlichen Zahlungen:

=SUM(C10:C369)

● Erhalten Sie die Gesamtzinsen:

=SUM(F10:F369)

⭐️ Ergebnis:

Indem Sie diesen Schritten folgen, erstellen Sie einen dynamischen Tilgungsplan in Excel, der zusätzliche Zahlungen berücksichtigt.

Erstellen Sie einen Tilgungsplan mithilfe einer Excel-Vorlage

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

1. Klicken Sie auf Datei > Neu, geben Sie im Suchfeld Tilgungsplanein und drücken Sie die Eingabetaste . Wählen Sie dann die Vorlage aus, die am besten zu Ihren Bedürfnissen passt, indem Sie darauf klicken. Zum Beispiel werde ich hier die einfache Kreditrechner-Vorlage auswählen. Siehe Screenshot:
   
2. Sobald Sie eine Vorlage ausgewählt haben, klicken Sie auf die Schaltfläche Erstellen, um sie als neue Arbeitsmappe zu öffnen.
3. Geben Sie dann Ihre eigenen Kreditdetails ein, und die Vorlage sollte automatisch den Plan basierend auf Ihren Eingaben berechnen und ausfüllen.
4. Speichern Sie abschließend Ihre neue Tilgungsplan-Arbeitsmappe.