Tilgungsplan in Excel: Annuitätendarlehen, Restschuld nach Zinsbindung und Sondertilgungen berechnen
Tilgungsplan in Excel erstellen: Monatsrate aus Sollzins und anfänglicher Tilgung, Zins- und Tilgungsanteil je Rate, Restschuld am Ende der Zinsbindung, Wirkung von Sondertilgungen, Anschlussfinanzierung und die passenden Excel-Funktionen RMZ, ZINSZ, KAPZ und ZW, mit einem Beispiel über 300.000 Euro.
Kurz gesagt: Ein Tilgungsplan zeigt für jede Rate eines Darlehens, wie viel davon Zinsen und wie viel Tilgung ist und wie hoch die Restschuld danach ist. Bei der in Deutschland üblichen Baufinanzierung, dem Annuitätendarlehen, wird die Monatsrate meist so festgelegt:
Monatsrate = Darlehensbetrag × (Sollzins + anfängliche Tilgung) ÷ 12
Beispiel: 300.000 € mit 3,8 % Sollzins und 2 % anfänglicher Tilgung ergeben 1.450 € pro Monat. Nach 10 Jahren Zinsbindung beträgt die Restschuld noch rund 227.146 €. Mit jährlichen Sondertilgungen von 5.000 € sinkt sie auf etwa 167.485 €. Alle Zahlen dienen der Veranschaulichung und sind kein Finanzierungsangebot.
Schritt 1: Eingaben
| Zelle | Name | Beispiel |
|---|---|---|
| B2 | Darlehen | 300.000 € |
| B3 | Sollzins | 3,80 % |
| B4 | Tilgung (anfänglich) | 2,00 % |
| B5 | Zinsbindung (Jahre) | 10 |
| B6 | Sondertilgung pro Jahr | 0 bzw. 5.000 € |
| B7 | Erste Rate | Datum |
| B9 | Rate | =RUNDEN(Darlehen*(Sollzins+Tilgung)/12;2) |
Mit dem Sollzins werden die Zinsen berechnet. Der effektive Jahreszins berücksichtigt zusätzlich bestimmte Kosten und die unterjährige Zahlung; er dient dem Vergleich von Angeboten.
Alternativ lässt sich die Rate für eine gewünschte Laufzeit mit =RMZ(Sollzins/12;Monate;-Darlehen) berechnen, etwa wenn das Darlehen in genau 25 Jahren getilgt sein soll.
Schritt 2: Die Zeilen des Tilgungsplans
| Spalte | Formel (Zeile 12) |
|---|---|
| A Monat | 1, darunter =WENN(H12>0,005;A12+1;"") |
| B Datum | =ErsteRate, darunter =WENN(A13="";"";EDATUM(B12;1)) |
| C Restschuld Anfang | =Darlehen, darunter =H12 |
| D Zinsen | =RUNDEN(C12*Sollzins/12;2) |
| E Tilgung | =MIN(Rate-D12;C12) |
| F Sondertilgung | =WENN(REST(A12;12)=0;MIN(Sondertilgung;C12-E12);0) |
| G Zahlung gesamt | =D12+E12+F12 |
| H Restschuld Ende | =C12-E12-F12 |
Ziehen Sie die Formeln über ausreichend viele Zeilen (z. B. 420) und umschließen Sie sie mit WENN($A13="";"";…), damit der Plan bei Restschuld null von selbst endet. Die Sondertilgung wird hier jeweils mit der zwölften Rate eines Jahres gebucht; passen Sie das an den Termin in Ihrem Vertrag an.
Beispiel: 300.000 € zu 3,8 % Sollzins und 2 % Tilgung
| Monat | Restschuld Anfang | Zinsen | Tilgung | Restschuld Ende |
|---|---|---|---|---|
| 1 | 300.000,00 | 950,00 | 500,00 | 299.500,00 |
| 2 | 299.500,00 | 948,42 | 501,58 | 298.998,42 |
| … | … | … | … | … |
| 120 | — | — | — | ≈ 227.146 |
In den ersten zehn Jahren zahlen Sie 174.000 € (120 × 1.450 €), davon rund 101.146 € Zinsen und nur etwa 72.854 € Tilgung. Weil die Rate gleich bleibt und die Zinsen mit der Restschuld sinken, wächst der Tilgungsanteil jeden Monat ein wenig. Bliebe der Sollzins dauerhaft bei 3,8 %, wäre das Darlehen nach 337 Monaten (rund 28 Jahren) getilgt, mit Gesamtzinsen von etwa 188.297 €.
Schritt 3: Restschuld nach der Zinsbindung
Die wichtigste Zahl für Ihre Planung ist die Restschuld am Ende der Zinsbindung, denn für diesen Betrag brauchen Sie eine Anschlussfinanzierung. Ohne Tabelle berechnet Excel sie direkt:
=-ZW(Sollzins/12;Zinsbindung*12;-Rate;Darlehen)
Weitere nützliche Funktionen:
- Zinsanteil in Monat 24:
=ZINSZ(Sollzins/12;24;Laufzeitmonate;-Darlehen) - Tilgungsanteil in Monat 24:
=KAPZ(Sollzins/12;24;Laufzeitmonate;-Darlehen) - Anzahl Monate bis zur Volltilgung:
=ZZR(Sollzins/12;-Rate;Darlehen)
ZINSZ und KAPZ setzen eine feste Gesamtlaufzeit voraus; bei der Logik „Sollzins + anfängliche Tilgung“ setzen Sie dafür das Ergebnis von ZZR ein. Die Zeile-für-Zeile-Tabelle bleibt genauer, sobald Sondertilgungen oder Zinsänderungen hinzukommen.
Schritt 4: Wirkung von Sondertilgungen
| Szenario | Zinsen in 10 Jahren | Restschuld nach 10 Jahren |
|---|---|---|
| Ohne Sondertilgung | ≈ 101.146 € | ≈ 227.146 € |
| 5.000 € Sondertilgung pro Jahr | ≈ 91.485 € | ≈ 167.485 € |
| Unterschied | ≈ 9.661 € weniger Zinsen | ≈ 59.661 € weniger Restschuld |
Die Restschuld sinkt um mehr als die 50.000 € Sondertilgungen, weil jede Sondertilgung auch die Zinsen der Folgemonate senkt, die Rate aber gleich bleibt. Ob und in welcher Höhe Sondertilgungen kostenfrei möglich sind (häufig vereinbart: ein bestimmter Prozentsatz pro Jahr), steht in Ihrem Darlehensvertrag.
Schritt 5: Anschlussfinanzierung durchspielen
Für die Zeit nach der Zinsbindung lohnt ein einfacher Stresstest. Bleibt die Rate bei 1.450 €, dauert die Rückzahlung der Restschuld von rund 227.146 €:
- bei 3,8 % Anschlusszins: etwa 217 Monate (gut 18 Jahre),
- bei 5,0 % Anschlusszins: etwa 254 Monate (gut 21 Jahre).
Formel: =ZZR(Anschlusszins/12;-Rate;Restschuld). Alternativ berechnen Sie mit =RMZ(Anschlusszins/12;Restmonate;-Restschuld), welche Rate nötig wäre, um zum geplanten Zeitpunkt schuldenfrei zu sein. Bei Darlehen mit gebundenem Sollzins besteht nach § 489 BGB in der Regel ein Kündigungsrecht zehn Jahre nach vollständigem Empfang des Darlehens; Details und Fristen klären Sie mit Ihrer Bank oder Beratung.
Monatsrate und Haushaltsbudget
Die Rate sollte dauerhaft ins Budget passen, inklusive Rücklagen für Instandhaltung und Nebenkosten. Tragen Sie sie als festen Posten in Ihr Haushaltsbuch in Excel ein und prüfen Sie, wie viel Puffer bleibt.
Häufige Fehler
- Effektivzins statt Sollzins für die Zinsberechnung verwenden.
- Jahreszins nicht durch 12 teilen.
- Restschuld nach Zinsbindung ignorieren und nur auf die Rate schauen.
- Zu niedrige Tilgung: Sie senkt die Rate, verlängert aber die Laufzeit und erhöht die Restschuld deutlich.
- Vorzeichen: Ohne Minuszeichen vor dem Darlehen liefern RMZ und ZW negative Werte.
Häufige Fragen
Warum weicht mein Plan leicht vom Plan der Bank ab?
Banken rechnen teils mit tagegenauer Zinsberechnung, anderen Rundungen oder einem abweichenden ersten Zahlungstermin. Kleine Differenzen sind normal; große Abweichungen deuten auf andere Konditionen hin.
Wie bilde ich ein tilgungsfreies Anfangsjahr ab?
Setzen Sie in den betreffenden Monaten die Tilgung auf null (Rate = Zinsen) und beginnen Sie danach mit der regulären Rate.
Funktioniert der Tilgungsplan in Google Tabellen?
Ja. RMZ, ZINSZ, KAPZ, ZW, ZZR und EDATUM sind in der deutschen Version verfügbar.
Weitere Hilfen finden Sie unter Schulden & Sparen.