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

Die Erstellung eines Darlehenstilgungsplans in Excel ist eine wertvolle Fähigkeit, mit der Sie Ihre Darlehensrückzahlungen klar visualisieren und effizient verwalten können. Ein Tilgungsplan ist eine Tabelle, die jede periodische Zahlung eines tilgenden Darlehens – typischerweise einer Hypothek oder eines Autokredits – detailliert auflistet. Dabei teilt er jede Zahlung in ihren Zins- und Tilgungsanteil auf und zeigt den jeweiligen Restsaldo nach jeder Zahlung an. Im Folgenden finden Sie eine Schritt-für-Schritt-Anleitung zur Erstellung eines solchen Plans in Excel.

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 Zusatzzahlungen

Erstellen Sie einen Tilgungsplan (mit Zusatzzahlungen) mithilfe einer Excel-Vorlage

Was ist ein Tilgungsplan?

Ein Tilgungsplan ist eine detaillierte Tabelle zur Darlehensberechnung, die den Rückzahlungsprozess eines Darlehens über die gesamte Laufzeit abbildet. Tilgungspläne werden häufig für Festzinsdarlehen wie Hypotheken, Autokredite und Privatkredite verwendet, bei denen die monatliche Rate konstant bleibt, sich jedoch im Laufe der Zeit der Anteil der Zahlung für Zinsen und Tilgung verändert.

Um einen Darlehenstilgungsplan in Excel zu erstellen, sind die integrierten Funktionen PMT, PPMT und IPMT unverzichtbar. Im Folgenden erfahren Sie, welche Aufgabe jede dieser Funktionen übernimmt:

• PMT-Funktion: Mit dieser Funktion berechnen Sie die regelmäßige Zahlung pro Periode für ein Darlehen – basierend auf konstanten Raten und einem festen Zinssatz.
• IPMT-Funktion: Diese Funktion berechnet den Zinsanteil einer Zahlung für eine bestimmte Periode.
• PPMT-Funktion: Mit dieser Funktion berechnen Sie den Tilgungsanteil einer Zahlung für eine bestimmte Periode.

Mit diesen Excel-Funktionen erstellen Sie einen detaillierten Tilgungsplan, der sowohl die Zins- als auch die Tilgungsanteile jeder Zahlung sowie den jeweiligen Restsaldo des Darlehens nach jeder Zahlung klar aufzeigt.

Erstellen Sie einen Tilgungsplan in Excel

In diesem Abschnitt stellen wir zwei unterschiedliche Methoden zur Erstellung eines Tilgungsplans in Excel vor – maßgeschneidert für verschiedene Benutzervorlieben und Kenntnisstände, damit wirklich jeder, unabhängig von seinen Excel-Fähigkeiten, mühelos einen detaillierten und präzisen Tilgungsplan für sein Darlehen erstellen kann.

Formeln vermitteln ein tieferes Verständnis der zugrunde liegenden Berechnungen und bieten die Flexibilität, den Plan individuell an spezifische Anforderungen anzupassen. Dieser Ansatz eignet sich ideal für alle, die praktische Erfahrung sammeln und genau nachvollziehen möchten, wie jede Zahlung in Tilgungs- und Zinsanteile aufgeteilt wird. Im Folgenden wird Schritt für Schritt erläutert, wie Sie in Excel einen Tilgungsplan erstellen:

⭐️ Schritt 1: Einrichten der Darlehensinformationen und der Tilgungstabelle

1. Geben Sie die relevanten Darlehensinformationen wie Jahreszinssatz, Laufzeit des Darlehens in Jahren, Anzahl der Zahlungen pro Jahr und Darlehensbetrag gemäß dem folgenden Screenshot in die Zellen ein:
   
2. Erstellen Sie anschließend in Excel eine Tilgungstabelle mit den Überschriften „Periode“, „Zahlung“, „Zinsen“, „Tilgung“ und „Restsaldo“ in den Zellen A7 bis E7.
3. Geben Sie in der Spalte „Periode“ die Periodennummern ein. Da die Gesamtanzahl der Zahlungen in diesem Beispiel 24 Monate (2 Jahre) beträgt, tragen Sie die Zahlen 1 bis 24 in die Spalte „Periode“ ein. Siehe Screenshot:
   
4. Sobald Sie die Tabelle mit Überschriften und Periodennummern eingerichtet haben, können Sie Formeln und Werte für die Spalten „Zahlung“, „Zinsen“, „Tilgung“ und „Saldo“ gemäß den konkreten Vorgaben Ihres Darlehens eintragen.

⭐️ Schritt 2: Berechnung des Gesamtzahlungsbetrags mithilfe der PMT-Funktion

Die Syntax der PMT-Funktion lautet:

Geben Sie die folgende Formel in Zelle B7 ein und ziehen Sie anschließend den Ausfüllknauf nach unten, um sie auf die darunterliegenden Zellen zu übertragen – so erhalten Sie für alle Perioden einen konstanten Zahlungsbetrag. Siehe Screenshot:

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

⭐️ Schritt 3: Berechnung der Zinsen mithilfe 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 den Ausfüllknauf anschließend nach unten durch die Spalte, um die Zinsen für jede Periode zu berechnen.

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

⭐️ Schritt 4: Berechnung der Tilgung mithilfe der PPMT-Funktion

Nachdem Sie die Zinsen für jede Periode berechnet haben, berechnen Sie im nächsten Schritt bei der Erstellung eines Tilgungsplans den Tilgungsanteil jeder Zahlung – mithilfe der PPMT-Funktion. Diese ist speziell dafür ausgelegt, den Tilgungsanteil einer Zahlung für eine bestimmte Periode auf Basis konstanter Zahlungen und eines festen Zinssatzes zu ermitteln.

Die Syntax der IPMT-Funktion lautet:

Die Syntax und die Parameter der PPMT-Formel entsprechen exakt denen der zuvor beschriebenen IPMT-Formel.

Geben Sie die folgende Formel in Zelle D7 ein und ziehen Sie den Ausfüllknauf anschließend nach unten durch die Spalte, um die Tilgung für jede Periode einzutragen. Siehe Screenshot:

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

⭐️ Schritt 5: Berechnung des Restsaldos

Nachdem Sie sowohl die Zinsen als auch die Tilgung jeder Zahlung berechnet haben, besteht der nächste Schritt Ihres Tilgungsplans darin, den Restsaldo des Darlehens nach jeder Zahlung zu berechnen. Dies ist ein wesentlicher Bestandteil des Plans, da er zeigt, wie sich der Darlehenssaldo im Laufe der Zeit verringert.

1. Geben Sie in die erste Zelle Ihrer Saldo-Spalte – E7 – die folgende Formel ein, was bedeutet, dass der Restsaldo dem ursprünglichen Darlehensbetrag abzüglich des Tilgungsanteils der ersten Zahlung entspricht:

   =B4-D7

   
2. Berechnen Sie für die zweite und alle folgenden Perioden den Restsaldo, indem Sie die Tilgung der aktuellen Periode vom Saldo der vorherigen Periode abziehen. Tragen Sie dazu in Zelle E8 folgende Formel ein:

   =E7-D8

   Hinweis: Der Bezug auf die Saldo-Zelle sollte relativ sein, damit er sich beim Herunterziehen der Formel automatisch anpasst.
   
3. Ziehen Sie anschließend den Ausfüllknauf die Spalte hinunter – jede Zelle passt sich automatisch an und berechnet den Restsaldo basierend auf den aktualisierten Tilgungsbeträgen.
   

⭐️ Schritt 6: Erstellung einer Darlehenszusammenfassung

Nachdem Sie Ihren detaillierten Tilgungsplan eingerichtet haben, bietet die Darlehenszusammenfassung einen schnellen Überblick über die wichtigsten Aspekte Ihres Darlehens – darunter typischerweise die Gesamtkosten sowie die insgesamt gezahlten Zinsen.

● Zur Berechnung der Gesamtzahlungen:

=SUM(B7:B30)

● Zur Berechnung der Gesamtzinsen:

=SUM(C7:C30)

⭐️ Ergebnis:

Nun wurde erfolgreich ein einfacher, aber umfassender Darlehenstilgungsplan erstellt – siehe Screenshot:

Entfesseln Sie die Excel-Magie mit KUTOOLS AI

• Intelligente Ausführung: Führen Sie Zellenoperationen durch, analysieren Sie Daten und erstellen Sie Diagramme – alles ganz einfach per Befehl gesteuert.
• Benutzerdefinierte Formeln: Erstellen Sie maßgeschneiderte Formeln, um Ihre Arbeitsabläufe optimal zu optimieren.
• VBA-Programmierung: Schreiben und implementieren Sie VBA-Code ganz mühelos.
• Formelinterpretation: Verstehen Sie komplexe Formeln mühelos.
• Textübersetzung: Überwinden Sie Sprachbarrieren direkt in Ihren Tabellenkalkulationen.

Erweitern Sie Ihre Excel-Fähigkeiten mit KI-gestützten Tools.Jetzt herunterladenund erleben Sie Effizienz wie nie zuvor!

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

Im vorherigen Beispiel haben wir einen Darlehenstilgungsplan für eine feste Anzahl von Zahlungen erstellt – ideal, um ein konkretes Darlehen oder eine Hypothek mit unveränderten Konditionen zu bearbeiten.

Möchten Sie jedoch einen flexiblen Tilgungsplan erstellen, der sich wiederholt für Darlehen mit unterschiedlichen Laufzeiten nutzen lässt und Ihnen je nach Darlehensszenario eine Anpassung der Zahlungsanzahl ermöglicht, müssen Sie eine detailliertere Methode anwenden.

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

1. Geben Sie die relevanten Darlehensinformationen wie Jahreszinssatz, Laufzeit des Darlehens in Jahren, Anzahl der Zahlungen pro Jahr und Darlehensbetrag gemäß dem folgenden Screenshot in die Zellen ein:
   
2. Erstellen Sie anschließend in Excel eine Tilgungstabelle mit den Überschriften „Periode“, „Zahlung“, „Zinsen“, „Tilgung“ und „Restsaldo“ in den Zellen A7 bis E7.
3. Geben Sie in der Spalte „Periode“ die höchste Anzahl an Zahlungen ein, die Sie für ein beliebiges Darlehen in Betracht ziehen – beispielsweise Werte von 1 bis 360. Damit decken Sie etwa ein standardmäßiges 30-Jahres-Darlehen ab, sofern monatliche Zahlungen vereinbart sind.
   

⭐️ Schritt 2: Passen Sie die Formeln für Zahlung, Zinsen und Tilgung mithilfe der WENN-Funktion an

Geben Sie die folgenden Formeln in die entsprechenden Zellen ein und ziehen Sie anschließend am Ausfüllkästchen, um sie bis zur von Ihnen festgelegten maximalen Anzahl der Zahlungsperioden zu erweitern.

● Zahlungsformel:

Normalerweise nutzen Sie die RMZ-Funktion zur Berechnung der Zahlung. Um eine WENN-Anweisung einzubinden, lautet die Syntax der Formel:

Die Formel lautet daher wie folgt:

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

● Zinsformel:

Die Syntaxformel lautet:

Die Formel lautet daher wie folgt:

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

● Tilgungsformel:

Die Syntaxformel lautet:

Die Formel lautet daher wie folgt:

=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 Restsaldo an

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

● Erste Saldenzelle: (E7)

=B4-D7

● Zweite Saldenzelle: (E8)

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

⭐️ Schritt 4: Erstellen Sie eine Darlehensübersicht

Nachdem Sie den Tilgungsplan mit den angepassten Formeln eingerichtet haben, erstellen Sie als Nächstes eine übersichtliche Darlehenszusammenfassung.

● Zur Berechnung der Gesamtzahlungen:

=SUM(B7:B366)

● Zur Berechnung der Gesamtzinsen:

=SUM(C7:C366)

⭐️ Ergebnis:

Sie verfügen nun über einen umfassenden und dynamischen Tilgungsplan in Excel mit einer detaillierten Darlehensübersicht. Sobald Sie die Laufzeit des Zahlungsplans anpassen, aktualisiert sich der gesamte Tilgungsplan automatisch, um diese Änderungen unmittelbar widerzuspiegeln. Sehen Sie sich dazu die folgende Demo an:

Erstellen Sie einen Tilgungsplan mit Zusatzzahlungen

Durch zusätzliche Zahlungen über die vereinbarten Raten hinaus lässt sich ein Darlehen schneller tilgen. Ein in Excel erstellter Tilgungsplan, der Sondertilgungen berücksichtigt, zeigt anschaulich, wie diese Extrazahlungen die Rückzahlung beschleunigen und die Gesamtzinsbelastung senken. So richten Sie ihn ein:

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

1. Geben Sie die relevanten Darlehensinformationen wie Jahreszinssatz, Laufzeit des Darlehens in Jahren, Anzahl der Zahlungen pro Jahr, Darlehensbetrag und Zusatzzahlung gemäß dem folgenden Screenshot in die Zellen ein:
   
2. Berechnen Sie anschließend die planmäßige Zahlung.
   Neben den Eingabezellen benötigen wir für unsere weiteren Berechnungen eine weitere vordefinierte Zelle: den planmäßigen Zahlungsbetrag. Dabei handelt es sich um den regulären Zahlungsbetrag eines Darlehens unter der Annahme, dass keine zusätzlichen Zahlungen geleistet werden. Tragen Sie dazu folgende Formel in Zelle B6 ein:

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

   
   
3. Erstellen Sie anschließend eine Tilgungstabelle in Excel:
   • Legen Sie die angegebenen Beschriftungen wie Periode, Planmäßige Zahlung, Zusatzzahlung, Gesamtzahlung, Zinsen, Tilgung und Restsaldo in den Zellen A8:G8 fest;
   • Geben Sie in der Spalte „Periode“ die höchste Anzahl an Zahlungen ein, die Sie für einen Kredit in Betracht ziehen würden – beispielsweise die Zahlen von 0 bis 360. Damit decken Sie einen Standardkredit über 30 Jahre ab, sofern Sie monatlich zahlen.
   • Für Periode 0 (Zeile 9 in unserem Fall) rufen Sie den Saldowert mit dieser Formel ab: =B4, was dem ursprünglichen Darlehensbetrag entspricht. Alle anderen Zellen in dieser Zeile sollten leer bleiben.
   • 

⭐️ Schritt 2: Erstellen Sie die Formeln für den Tilgungsplan mit Sonderzahlungen

Geben Sie die folgenden Formeln nacheinander in die entsprechenden Zellen ein. Um die Fehlerbehandlung zu verbessern, umschließen wir diese sowie alle zukünftigen Formeln mit der WENNFEHLER-Funktion – so vermeiden Sie zuverlässig mehrere potenzielle Fehler, die auftreten könnten, wenn Eingabezellen leer bleiben oder falsche Werte enthalten.

● Berechnen Sie die planmäßige 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 Sonderzahlung:

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 Restsaldo

Geben Sie die folgende Formel in Zelle G10 ein:

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

Sobald Sie alle Formeln eingegeben haben, markieren Sie den Zellbereich B10:G10 und ziehen Sie am Ausfüllkästchen, um die Formeln über die gesamte Laufzeit aller Zahlungsperioden zu kopieren. In nicht genutzten Perioden zeigen die Zellen den Wert 0 an. Siehe Screenshot:

⭐️ Schritt 3: Erstellen Sie eine Darlehensübersicht

● Ermitteln Sie die geplante Anzahl der Zahlungen:

=B2:B3

● Ermitteln Sie die tatsächliche Anzahl der Zahlungen:

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

● Ermitteln Sie die Summe der Sonderzahlungen:

=SUM(C10:C369)

● Ermitteln Sie die Gesamtzinsen:

=SUM(F10:F369)

⭐️ Ergebnis:

Indem Sie diese Schritte befolgen, erstellen Sie in Excel einen dynamischen Tilgungsplan, der Sonderzahlungen berücksichtigt.

Erstellen Sie einen Tilgungsplan mithilfe einer Excel-Vorlage

Die Erstellung eines Tilgungsplans in Excel mithilfe einer Vorlage ist eine unkomplizierte und zeitsparende Lösung. Excel bietet integrierte Vorlagen, die Zinsen, Tilgung und Restsaldo jeder Zahlung automatisch berechnen. So erstellen Sie ganz einfach einen Tilgungsplan mit einer Excel-Vorlage:

1. Klicken Sie auf Datei > Neu, geben Sie im Suchfeld Tilgungsplan ein und drücken Sie die Enter-Taste. Wählen Sie dann die Vorlage, die Ihren Anforderungen am besten entspricht, indem Sie darauf klicken. In diesem Beispiel wählen wir beispielsweise die Vorlage „Einfacher Darlehensrechner“. Siehe Screenshot:
   
2. Nachdem Sie eine Vorlage ausgewählt haben, klicken Sie auf die Schaltfläche Erstellen, um sie als neues Arbeitsblatt zu öffnen.
3. Geben Sie anschließend Ihre individuellen Darlehensdaten ein – die Vorlage berechnet den Tilgungsplan automatisch und füllt ihn basierend auf Ihren Angaben aus.
4. Speichern Sie abschließend Ihre neue Tilgungsplan-Arbeitsmappe.