So erstellst du einen Amortisationsplan in Excel: Die Schritte werden erklärt

Ein Tilgungsplan bietet einen klaren finanziellen Fahrplan, der zeigt, wie sich jede Darlehenszahlung auf Zinsen und Kapital aufteilt, bis deine Schulden vollständig getilgt sind.

Mit den in Excel integrierten Funktionen kannst du detaillierte Tilgungspläne erstellen, um den Lebenszyklus deines Kredits zu visualisieren, fundierte finanzielle Entscheidungen zu treffen und sogar Optionen für eine vorzeitige Rückzahlung zu prüfen.

In diesem Artikel erfährst du, wie du einen umfassenden Tilgungsplan in Excel von Grund auf erstellen kannst. Wir behandeln wichtige Funktionen wie PMT(), IPMT() und PPMT(), führen dich Schritt für Schritt durch den Prozess und erkunden praktische Szenarien, um dein Finanzplanungsinstrumentarium zu erweitern.

Was genau ist ein Amortisationsplan?

Ein Tilgungsplan ist eine vollständige Tabelle mit den regelmäßigen Darlehenszahlungen, in der die Höhe des Kapitals und der Zinsen angegeben ist, die sich aus jeder Zahlung zusammensetzen, bis das Darlehen am Ende der Laufzeit abbezahlt ist.

Wenn du einen Kredit aufnimmst, gibt es mehrere wichtige Faktoren, die bestimmen, wie deine Zahlungen funktionieren:

• Principal: Der ursprünglich geliehene Betrag, den du zurückzahlen musst
• Interesse: Die Kosten für das Ausleihen des Geldes, berechnet als Prozentsatz des Kapitals
• Zahlungsfrist: Wie oft du Zahlungen leistest (monatlich, zweiwöchentlich, etc.)
• Laufzeit desDarlehens: Der Gesamtzeitraum, über den du das Darlehen zurückzahlen wirst

Bei den meisten Standardkrediten - wie Hypotheken, Autokrediten und Privatkrediten - bleiben die Zahlungen während der gesamten Laufzeit gleich. Was sich jedoch mit jeder Zahlung ändert, ist der Anteil, der auf die Zinsen und das Kapital entfällt. In der Anfangsphase deckt ein größerer Anteil jeder Zahlung die Zinsen ab. Je länger die Laufzeit des Kredits andauert, desto mehr von jeder Zahlung verringert den Kapitalsaldo.

Der Hauptzweck der Erstellung eines Tilgungsplans ist es, Transparenz in dein Darlehen zu bringen. So kannst du genau sehen, wie viel Zinsen du im Laufe der Zeit zahlst, wie schnell du Eigenkapital aufbaust und wie sich zusätzliche Zahlungen auf deine Kreditlaufzeit auswirken könnten. Für Unternehmen dienen diese Listen auch der Buchhaltung und Steuerplanung.

Die Excel-Funktionen und -Formeln, die du kennen musst

Excel verfügt nicht über eine einzige eingebaute "Amortisationsfunktion". Stattdessen erstellst du in der Regel einen Tilgungsplan mit bestimmten Finanzfunktionen, die zusammenarbeiten, um verschiedene Aspekte deiner Darlehenszahlungen zu berechnen. Hier sind die wichtigsten Funktionen, die du brauchst:

PMT()-Funktion

Die Funktion PMT() berechnet die Zahlung für ein Darlehen auf der Grundlage konstanter Zahlungen und eines konstanten Zinssatzes:

=PMT(rate, nper, pv, [fv], [type])

• rate: Der Zinssatz pro Periode (Jahreszins geteilt durch die Zahlungsfrequenz)

• nper: Die Gesamtzahl der Zahlungsperioden

• pv: Der Barwert (Kreditbetrag)

• [fv]: Optionaler Zukunftswert (in der Regel 0 für vollständig ausgezahlte Darlehen)

• [type]: Optional; 0 für Zahlungen am Ende des Zeitraums, 1 für Zahlungen am Anfang des Zeitraums

So berechnest du die monatliche Rate für eine Hypothek von 250.000 USD bei einem jährlichen Zinssatz von 4,5% für 30 Jahre: 

=PMT(4.5%/12, 30*12, 250000)

Dies ergibt -$1.266,71, was den Mittelabfluss für den Kreditnehmer darstellt.

Eine ausführliche Erklärung der Funktion PMT(), einschließlich häufiger Fehler und fortgeschrittener Anwendungsbeispiele, findest du in unserem umfassenden PMT()-Excel-Leitfaden.

IPMT()-Funktion

Die Funktion IPMT() berechnet den Zinsanteil einer bestimmten Zahlung:

=IPMT(rate, per, nper, pv, [fv], [type])

• per: Der spezifische Zahlungszeitraum, den du berechnest

Der Parameter per ist wichtig, da er festlegt, für welchen Zahlungszeitraum du den Zinsbetrag berechnest - im Gegensatz zur Funktion PMT(), die für alle Zeiträume den gleichen Zahlungsbetrag liefert, variieren die Ergebnisse von IPMT() erheblich, je nachdem, welchen Zeitraum du angibst, da die Zinsanteile während der Laufzeit des Darlehens abnehmen.

Das zeigt genau, wie viel von einer bestimmten Zahlung auf die Zinsen entfällt.

PPMT()-Funktion

Die Funktion PPMT() berechnet den Hauptanteil einer bestimmten Zahlung:

=PPMT(rate, per, nper, pv, [fv], [type])

Ähnlich wie bei IPMT() variiert der Kapitalbetrag je nach Periode, wobei frühere Zahlungen einen kleineren und spätere Zahlungen einen größeren Kapitalanteil haben. Sie zeigt an, um wie viel eine bestimmte Zahlung deinen Kreditsaldo reduziert.

Benutzerdefinierte Formeln

Für jeden Zahlungszeitraum solltest du einen Lernpfad erstellen:

Verbleibender Saldo: Der ausstehende Kreditbetrag nach jeder Zahlung

Remaining balance = Previous balance - Current principal payment

Kumuliertes Kapital: Bislang gezahltes Kapital insgesamt

Cumulative principal = Previous cumulative principal + current principal payment

Kumulierte Zinsen: Bislang gezahlte Zinsen insgesamt

Formula: Previous cumulative interest + current interest payment

Funktion Round()

Selbst kleine Rundungsfehler können sich im Laufe der Zeit in einem Tilgungsplan summieren:

=ROUND(value, 2)

So wird sichergestellt, dass alle Geldwerte auf den nächsten Cent gerundet werden.

Absolute vs. relative Referenzen

Wenn du deine Tabelle erstellst, musst du Folgendes beachten:

• Verwende absolute Referenzen ($) für feste Werte wie Zinssatz und Kreditbetrag

• Verwende relative Referenzen für Werte, die sich jede Periode ändern

Die folgende Formel verwendet zum Beispiel absolute Referenzen für den Zinssatz, die Kreditlaufzeit und den Betrag, aber eine relative Referenz für den Zahlungszeitraum:

=PPMT($B$2/12, A10, $B$3*12, $B$1)

Schritt-für-Schritt-Anleitung zur Erstellung des Tilgungsplans

Nachdem du nun die wichtigsten Funktionen verstanden hast, wollen wir einen Tilgungsplan von Grund auf erstellen. Mit dieser Anleitung erstellst du eine vollständige Tabelle, die jede Zahlung während der gesamten Laufzeit deines Kredits verfolgt.

1. Eingabe von Darlehensdaten

Gib deine Darlehensdaten in die entsprechenden Eingabefelder ein, wie im Beispiel unten gezeigt:

• Darlehensbetrag: 250.000 $ (Zelle B1)
• Jährlicher Zinssatz: 4,5% (Zelle B2)
• Laufzeit des Darlehens: 30 Jahre (Zelle B3)
• Zahlungsfrequenz: Monatlich (Zelle B4)

Einrichten der Eingabeparameter für den Kredit. Bild vom Autor. 

2. Berechnung der monatlichen Zahlung

Berechne in Zelle B5 die monatliche Zahlung mit der PMT-Funktion: =PMT(B2/12, B3*12, B1)

Diese Formel ergibt -$1.266,71, das ist deine monatliche Zahlung. Das negative Vorzeichen bedeutet, dass es sich um eine Zahlung (Mittelabfluss) handelt.

Einrichten der Spaltenüberschriften und Formeln. Bild vom Autor.

3. Erstellen der ersten Zeile des Zeitplans

Als Nächstes können wir die folgenden Überschriften erstellen und die erste Datenzeile (Zeile 7 und 8) abarbeiten:  

• Zeitraum: Gib 1 in Zelle A8 ein

• Zahlungsbetrag=$B$5 in Zelle B8 (mit Verweis auf die berechnete monatliche Zahlung)

• Zinszahlung: =IPMT($B$2/12, A8, $B$3*12, $B$1) in Zelle C8

• Hauptzahlung: =PPMT($B$2/12, A8, $B$3*12, $B$1) in Zelle D8

• Verbleibender Saldo=$B$1+D8 in Zelle E8 (ursprüngliches Darlehen plus Tilgungszahlung, was funktioniert, weil D8 einen negativen Wert liefert und somit die Tilgungssumme vom Darlehen abgezogen wird)

• Kumulative Hauptsumme=D8 in Zelle F8 (entspricht der Kapitalzahlung für die erste Zeile)

• Kumulierte Zinsen: =C8 in Zelle G8 (entspricht der Zinszahlung für die erste Zeile)

Zeigt die Aufschlüsselung der ersten Zahlung. Bild vom Autor.

4. Den Zeitplan vervollständigen

Für die zweite Reihe (Reihe 9):

• Zeitraum=A8+1 in Zelle A9 (inkrementiert um 1)

• Zahlungsbetrag: Kopiere die Formel von B8 nach B9

• Zinszahlung: =IPMT($B$2/12, A9, $B$3*12, $B$1) in Zelle C9

• Hauptzahlung: =PPMT($B$2/12, A9, $B$3*12, $B$1) in Zelle D9

• Verbleibender Saldo: =E8+D9 in Zelle E9 (vorheriger Saldo plus aktueller Kapitalbetrag)

• Kumulierte Hauptsumme=F8+D9 in Zelle F9 (vorherige Gesamtsumme plus aktuelle Hauptsumme)

• Kumulierte Zinsen=G8+C9 in Zelle G9 (vorherige Summe plus aktuelle Zinsen)

Beachte, dass sowohl in der Funktion IPMT() als auch in der Funktion PPMT() A9 als relative Referenz (ohne Dollarzeichen) verwendet wird. Das heißt, wenn du die Formel nach unten kopierst, wird sie automatisch auf A10, A11 usw. aktualisiert, wobei jedes Mal der richtige Zeitraum berechnet wird.

Formeln aufstellen, die kopiert werden können. Bild vom Autor.

Die ersten paar Zahlungen im Tilgungsplan. Bild vom Autor.

Wähle alle Zellen in Zeile 9 aus und ziehe sie nach unten bis Zeile 367 (für ein 30-jähriges Darlehen mit 360 monatlichen Zahlungen).

Je länger die Laufzeit des Kredits läuft, desto mehr von jeder Zahlung fließt in die Tilgung und nicht in die Zinsen. Bild vom Autor.

5. Überprüfung der Abschlusszahlung

In einem richtig konstruierten Tilgungsplan sollte die letzte Zahlung den Restsaldo auf genau null bringen und die kumulierte Kreditsumme sollte dem ursprünglichen Kreditbetrag entsprechen.

Die letzte Zahlung bringt den Restsaldo auf genau null. Bild vom Autor.

Wie du in der letzten Zeile sehen kannst, beträgt der Restsaldo 0,00 $ (hervorgehoben) und die kumulierte Kreditsumme 250.000 $, was unserem ursprünglichen Kreditbetrag entspricht. Das bestätigt, dass unser Abschreibungsplan korrekt ist.

Bei der Überprüfung des Endsaldos kann es hilfreich sein, die Funktion ROUND() auf wichtige Berechnungen anzuwenden. Kleine Abweichungen im Dezimalbereich, die auf die Rechengenauigkeit von Excel zurückzuführen sind, können dazu führen, dass der endgültige Saldo einen winzigen Restbetrag anstelle von genau Null anzeigt. Die Verwendung von =ROUND(value, 2) stellt sicher, dass alle monetären Werte mit den üblichen Finanzberichterstattungsstandards übereinstimmen.

Zusätzliche Dinge, die du vielleicht berücksichtigen solltest

Ein Standardzeitplan ist der Ausgangspunkt. Berücksichtige diese Dinge, um dein Modell flexibler zu machen:

Verwaltung von Zahlungsschwankungen

Die Tilgungspläne werden noch wertvoller, wenn sie angepasst werden, um verschiedene Zahlungsszenarien zu untersuchen. Um zusätzliche Zahlungen zu modellieren, erstelle eine zusätzliche Eingabezelle für zusätzliche Zahlungen und ändere die Formel für die Tilgungszahlung, um diesen Betrag einzubeziehen. Dieser Ansatz macht deutlich, wie zusätzliche Zahlungen sowohl die Laufzeit des Kredits als auch die Gesamtzinszahlungen verringern. Bei Darlehen mit Ballonzahlungen oder variablen Zinssätzen solltest du überlegen, ob du in deinem Zeitplan separate Abschnitte einfügst, die Zinsänderungen zu bestimmten Zeitpunkten widerspiegeln. Mit dem Szenario-Manager von Excel kannst du verschiedene Tarifumgebungen und ihre Auswirkungen auf deine Gesamtkosten vergleichen.

Verbesserung der Lesbarkeit und Analyse

Die richtige Formatierung verwandelt deinen Tilgungsplan von einer einfachen Tabelle in ein aufschlussreiches Finanzinstrument. Wende Währungsformatierungen mit einheitlichen Dezimalstellen für alle Geldwerte an und verwende bedingte Formatierungen, um wichtige Meilensteine hervorzuheben, z. B. wenn du die Hälfte des Kapitals abbezahlt oder bestimmte Eigenkapitalschwellen erreicht hast. Es ist auch wichtig zu wissen, wie sich die verschiedenen Methoden der Zinsabgrenzung auf deine Zahlungen auswirken. Bei der täglichen Abgrenzung (die bei Studentenkrediten üblich ist) werden die Zinsen auf der Grundlage eines 365-Tage-Jahres berechnet, während viele Hypotheken eine monatliche Abgrenzung verwenden. Wenn du dokumentierst, welche Methode du für deinen Zeitplan verwendest, ist die Genauigkeit beim Vergleich mit den tatsächlichen Kreditauszügen gewährleistet.

Anpassung für bestimmte Darlehensarten

Unterschiedliche Darlehen haben einzigartige Eigenschaften, die eine Anpassung deines grundlegenden Tilgungsschemas erfordern können. Füge bei Hypotheken mit Treuhandkonten Spalten hinzu, um neben Kapital und Zinsen auch Versicherungs- und Steuerkomponenten zu erfassen. Bei Krediten mit negativer Tilgung (bei denen die Zahlungen die aufgelaufenen Zinsen nicht abdecken), musst du die Berechnung des Restsaldos so ändern, dass die unbezahlten Zinsen zum Kapital hinzukommen. Mit der Option "Zahlungszeitpunkt" in den Excel-Finanzfunktionen (Parametertype ) kannst du Zahlungen am Anfang oder am Ende von Perioden modellieren, was besonders bei Leasingverträgen und einigen gewerblichen Krediten nützlich ist, bei denen sich die Zeitkonventionen von den üblichen Verbraucherkrediten unterscheiden.

Fazit

Wenn du einen Tilgungsplan in Excel erstellst, erhältst du einen wertvollen Einblick in deine Kredite, der über das hinausgeht, was die meisten Kreditgeber anbieten. Indem du deinen eigenen Zeitplan erstellst, kannst du genau sehen, wie sich jede Zahlung auf deinen Kontostand auswirkt, die wahren Kosten der Kreditaufnahme im Laufe der Zeit verstehen und fundierte Entscheidungen über Vorauszahlungsoptionen treffen. Die dabei erworbenen Fähigkeiten - der Umgang mit den Finanzfunktionen von Excel, das Erstellen dynamischer Formeln und das Entwerfen informativer Tabellen - gehen weit über die Kreditanalyse hinaus und können auf viele Aspekte der persönlichen und geschäftlichen Finanzplanung angewendet werden.

Wenn du über die Abschreibung hinausgehen und umfassendere Finanzmodelle erstellen möchtest - einschließlich Einkommensprognosen, Bewertung von Investitionen und Was-wäre-wenn-Szenarien -, dann schau dir unseren Kurs Finanzmodellierung in Excel an. Es ist ein großartiger nächster Schritt, um deine Tabellenkalkulationskenntnisse für die Finanzanalyse zu stärken. Und denk daran: Je besser du dich mit diesen Finanzberechnungen auskennst, desto besser bist du in der Lage, deine finanzielle Zukunft selbst in die Hand zu nehmen, egal ob du private Schulden verwaltest, Investitionen für dein Unternehmen planst oder Kunden über ihre finanziellen Möglichkeiten berätst.