Erstellen Sie einen Zins- und Tilgungsplan für einen Kredit selbst in Excel - wir erklären wie! Oder laden Sie einfach unsere kostenlosen Loan_Amortisation_Schedules (auf Englisch) mit vier einsatzbereiten Planvarianten herunter.
Wozu brauche ich das?
Wenn Sie ein Annuitäten- oder Tilgungsdarlehen (amortisierendes Darlehen) mit regelmäßigen (meist monatlichen) Zahlungen planen müssen, vielleicht als Teil eines Finanzmodells. Ein solcher Plan kann zur Berechnung von Bilanzwerten für Finanzierungsleasing / IFRS16 verwendet werden oder auch, um verschiedene Finanzierungsalternativen mit unterschiedlichen Laufzeiten und unterschiedlichen Zinssätzen zu vergleichen.
Oder vielleicht wollen Sie einfach ein besseres Verständnis für den Hypothekenzahlungsplan bekommen, den Ihnen Ihre Bank schickt 😊.
Was ist ein amortisierendes Darlehen?
Ein amortisierendes Darlehen muss schrittweise über die Laufzeit zurückbezahlt (getilgt) werden. Es gibt zwei Varianten:
1. Gleiche Raten (Annuität)
Eine Annuität ist ein Darlehen (oder eine Finanzierungsleasing-Verbindlichkeit), bei dem die Raten (Zinsen und Tilgung zusammen) während der gesamten Laufzeit konstant bleiben. Solche Kredite sind sowohl bei Kreditgebern als auch bei Kreditnehmern beliebt, da die monatlichen Zahlungsströme konstant und vorhersehbar sind.
Jede Darlehensrate enthält einen Teil der Gesamtzinsen und einen Teil der Gesamttilgung, wodurch sich der ausstehende Darlehensbetrag verringert. Anfänglich ist der ausstehende Kreditsaldo am höchsten und damit auch die Zinskosten am höchsten. Mit jeder Rückzahlung verringert sich der Kreditsaldo allmählich und damit auch die Höhe der Zinsen, so dass immer mehr von der festen monatlichen Rückzahlung zur Tilgung des Kredits verwendet wird.
2. Gleiche Tilgungsraten
Bei einem Darlehen mit gleichen Tilgungsraten sinkt der Betrag der regelmäßig zu leistenden Zahlung (Zinsen und Tilgung zusammen) im Laufe der Zeit, da sich die Tilgungsraten gleich blieben, die Zinsen für das verbleibende Restkapital aber verringern.
Wie erfasse ich die Eckdaten des Darlehens?
Legen Sie einen Eingabebereich für die Grunddaten wie Kreditlaufzeit etc. an, etwa so:
Wie berechne ich die monatlichen Beträge?
Erstellen Sie einen Rechenbereich wie diesen. Es ist in der Finanzbranche üblich, die Daten und Beträge in den Zeilen zu erfassen. Wenn Sie die Berechnungen jedoch als Input für ein Finanzmodell verwenden, das normalerweise spaltenbasiert aufgebaut ist, kann es sinnvoll sein, den Darlehensplan ebenfalls spaltenbasiert aufzubauen. Achten Sie nur darauf, dass Sie die Zellbezüge richtig setzen.
Sie benötigen für jeden Zeitraum eine Zeile. In jedem Zeitraum müssen Sie die Zinsen, das Kapital und die Gesamtrate plus den Schlusssaldo berechnen. Details zu den Berechnungsmethoden und Formeln finden Sie weiter unten. Eine Summenzeile oben gibt Ihnen einen guten Überblick und ist ideal, um Ihre Ergebnisse einschließlich des Endsaldos zu überprüfen (verwenden Sie eine INDEX/VERGLEICH-Funktion, um Ihren Endsaldo zu finden).
Um es Ihnen leicht zu machen, haben wir eine Datei (auf Englisch) mit vier unterschiedlichen Zins- und Tilgungsplänen erstellt, die Sie kostenlos herunterladen können: Loan_Amortisation_Schedules.
Sie enthalten auch Graphiken, die zeigen, wie sich die Zahlungen und Salden im Laufe der Zeit verändern.
Wie kann ich einen der in den Downloads verfügbaren Zins- und Tilgungspläne verwenden?
Sie können jeden der Pläne so verwenden, wie er ist, indem Sie einfach die Annahmen ändern (graue Eingabezellen am oberen Rand). Alternativ können Sie einen der Pläne in ein bestehendes Modell einbauen. In diesem Fall kopieren Sie die entsprechende Berechnung in Ihr Modell und verknüpfen die Zinskosten mit Ihrer Gewinn- und Verlustrechnung (GuV) und die Endsalden mit Ihrer Bilanz. Für ein Cash-Planungstool verknüpfen Sie die gesamten Raten als Mittelabflüsse mit Ihrem Cashflow-Plan.
Wie genau funktioniert jeder der Zins- und Tilgungspläne?
Jeder Zins- und Tilgungsplan berechnet automatisch Ihre monatlichen Raten (aufgeteilt in Zins und Tilgung) und den Restsaldo. Außerdem wird die Zeile der aktuellen Periode basierend auf dem heutigen Datum grün hervorgehoben.
In Version 4 (Tilgungsdarlehen) können Sie auch einmalige Tilgungen als Eingaben rechts neben jeder Periode eingeben. Die verbleibenden Tilgungsbeträge werden dann automatisch angepasst.
Es gibt vier Versionen - was sind die Unterschiede?
Die vier Versionen unterscheiden sich in der Art und Weise, wie die Zinsen, die Tilgung und die Gesamtrate berechnet werden, wie folgt:
|
Vers. |
Kurz- |
Zins |
+ Tilgung |
= Gesamtrate |
Rest- |
|
1 |
Nutzt Excel- |
=ZINSZ(Zins, |
=KAPZ(Zins, |
= Zins + Tilgung |
= Restvaluta |
|
2 |
Nutzt primär mathematische Operatoren |
=Restvaluta |
= Gesamtrate – berechnete Zinsen |
=RMZ(Zins, |
Analog |
|
3 |
Analog |
Null in der ersten |
Analog |
=RMZ(Zins, |
Analog |
|
4 |
Analog |
Analog |
Gleiche Tilgungsraten |
=Zins + Tilgung |
Analog |
Anmerkungen
- Version 1: Excel-Funktionen erledigen die harte Arbeit für Sie!
Eine Schlusszahlung (falls vereinbart) wird als Restvaluta am Ende der Laufzeit gezeigt.
- Version 2: Einige Modellierer fühlen sich mit diesem Ansatz wohler und er ist flexibler als Version 1, z. B. wenn Sie Ad-hoc-Tilgungen modellieren müssen.
- Versionen 1 und 2: Beide basieren auf nachschüssigen Zahlungen und liefern die gleichen Ergebnisse. Es gibt fast immer verschiedene Wege zum Erfolg in Excel und dies ist ein gutes Beispiel.
- Version 3: Deckt den Spezialfall von Leasingverträgen mit vorschüssigen Zahlungen und einem vom Leasingnehmer garantierten Restwert am Ende der Laufzeit ab (in Deutschland übliche Struktur). Die garantierte Restwert-/Ballonzahlung wird als am Ende des letzten Monats der Laufzeit gezahlt ausgewiesen. Sie wird über diesen Zeitraum weiter verzinst.
- Version 4: Nachschüssige Zahlung mit fester Tilgung, die sich nur bei zusätzlichen einmaligen Tilgungszahlungen ändert. Die Zinsen basieren auf den jeweiligen Restvaluta.
Eine Schlusszahlung (falls vereinbart) wird als Zahlung am Ende des letzten Monats der Vertragslaufzeit gezeigt.
Was bedeuten in den Excel-Finanzfunktionen 'Zins', 'Zr' usw.?
Alle drei verwendeten Finanzfunktionen (ZINSZ, KAPZ und RMZ) verwenden die gleichen Parameter, die nun erklärt werden. Um Ihnen zu helfen, haben wir den entsprechenden Begriff neben jeder der Eingabezellen hinzugefügt.
- Zzr: Die Gesamtzahl der Zahlungsperioden Ihres Darlehens
- Zr: Die Ordnungszahl des Zeitraumes für welchen Sie den Zins oder die Tilgung berechnen möchten. Diese muss sich im Bereich 1 – Zrr bewegen. (Diese Zahl ist nicht in den Annahmen enthalten, sondern in der ersten Spalte des Zins- und Tilgungsplanes unter der Überschrift „Zeitraum“).
- Zins: Der Zinssatz pro Zeitraum, normalerweise die monatliche oder jährliche Rate.
- Bw: Der Barwert (Ausgangswert) des Darlehens oder des Leases.
- Zw (optional): Der Zukunftswert oder die Restvaluta nachdem die letzte Zahlung geleistet wurde. Wenn dieser Wert in der Formel ausgelassen wird, ist die Annahme, dass er 0 beträgt. Der Zukunftswert eines Annuitätendarlehens z.B. ist oft 0.
- F (optional): Nutzen Sie 0 für nachschüssige Zahlungen, die am Ende jedes Zeitraums fällig werden und 1 für vorschüssige Zahlungen am Anfang der Zeiträume. Wenn F ausgelassen wird, ist die Annahme, dass nachschüssige Zahlungen vereinbart sind (also 0).
Achtung! Achten Sie bei der Verwendung dieser Funktionen darauf, dass die Parameter Zins, Zr und Zzr übereinstimmen, d.h. wenn Sie monatliche Zeiträume verwenden (wie hier), dann muss auch der Zinssatz monatlich und nicht jährlich sein. Wenn Sie diese mischen, erhalten Sie falsche Ergebnisse!
Stellen Sie außerdem sicher, dass Sie die richtigen Vorzeichen in den Formeln verwenden, d. h., wenn Bw positiv ist, muss Zw negativ sein und umgekehrt, um die richtigen Werte zu erhalten. Wenn Sie Bw als positive Zahl verwenden, werden Ihre Formelergebnisse negative Zahlen sein (die Sie eventuell umrechnen müssen, je nachdem, wofür Sie sie verwenden wollen).
Welche Version ist am besten zu verwenden?
Für Standard-Darlehensstrukturen (nachschüssige Zahlung) können Sie Ihre bevorzugte Version zwischen den Versionen 1 und 2 frei wählen. Für Leasingstrukturen mit Vorauszahlung ist Version 3 Ihre Vorlage der Wahl. Für Darlehen mit gleichen Tilgungsraten in jeder Periode (mit oder ohne zusätzliche / einmalige Tilgungen) verwenden Sie bitte Version 4.
Welche Besonderheiten haben die Zins- und Tilgungspläne?
- Datenüberprüfung für die Laufzeiteingabezelle (D6): Einträge müssen eine ganze Zahl zwischen eins und 360 sein, damit die Formeln korrekt funktionieren.
Wie? Bitte markieren Sie die Zelle D6, verwenden Sie das Menü Daten , Datenüberprüfung, und überprüfen Sie die Registerkarten 'Einstellungen' und 'Fehlermeldung'.
- Bedingte Formatierung für die Tabelle: Die Daten für den aktuellen Monat werden automatisch grün hinterlegt.
Wie? Bitte markieren Sie eine beliebige Zelle, verwenden Sie das Menü Start, Bedingte Formatierung, Regeln verwalten und überprüfen Sie die Einstellungen.
