Annuitätendarlehen mit Excel berechnen - so geht's

Die Bestandteile der monatlichen Raten des Annuitätendarlehens

• Die monatlich zu zahlende Rate des Annuitätendarlehens - die sogenannte Annuität - besteht aus zwei Teilen, nämlich dem Zins- und dem Tilgungsanteil.
• Die Höhe des Zinsanteils wird durch die Konditionen des Kreditvertrags vorgegeben. Ein Darlehen über eine Gesamtsumme von 100.000 Euro zu einem effektiven Jahreszins von 3,6 % ergibt eine Zinsschuld von 3.600 Euro pro Jahr bzw. 300 Euro pro Monat.
• Die Höhe des Tilgungsanteils ergibt sich aus der Differenz der monatlichen Annuität und des Zinsanteils. In unserem Beispiel verbleibt bei einer monatlichen Annuität von 1.000 Euro nach Abzug der Zinszahlungen ein Tilgungsanteil in Höhe von 700 Euro im ersten Monat.
• Jeden Monat verschiebt sich das Verhältnis zwischen Zins und Tilgung, da sich kontinuierlich die Restschuld um die geleisteten Tilgungszahlungen verringert. Der Zinsanteil nimmt somit stetig ab, während sich der Tilgungsanteil im selben Maße erhöht.

Berechnen Sie Ihre Raten mit Excel

1. Erstellen Sie zur Berechnung Ihres Annuitätendarlehens eine neue Excel-Datei.
2. Formatieren Sie die Zelle B1 mit dem Format "Prozent", und tragen Sie hier den effektiven Jahreszins gemäß Ihres Kreditvertrags ein (z.B. 3,6 %).
3. Die Zellen B2 und B3 formatieren Sie mit dem Format "Währung". Tragen Sie in B2 den Darlehensbetrag (in unserem Beispiel 100.000 €) und in B3 die gewünschte monatliche Annuität (in unserem Beispiel 1.000 €;) ein.
4. Erstellen Sie nun in derselben Excel-Arbeitsmappe eine fortlaufende Matrix zur Darstellung des Verlaufs des Annuitätendarlehens. Tragen Sie hierfür zunächst die Überschriften "Monat", "Zins", "Tilgung" und "Restschuld" in die Zellen A5-D5 in dieser Reihenfolge ein.
5. Nun kann der erste Monat der Rückzahlungsperiode des Annuitätendarlehens modelliert werden. Tragen Sie in die Zelle A6 den Wert "1" ein. In Zelle B6 steht die Formel "=$B$2*$B$1/12". In Zelle C6 ist die Formel "=$B$3-B6" einzugeben. Nachdem in der Zelle D6 nun noch die Formel "=$B$2-C6" eingegeben worden ist, ist die erste Zeile der Matrix komplett.
6. In der zweiten Zeile der Matrix müssen die Excel-Formeln etwas angepasst werden. In der Zelle A7 ist der Wert "2" zu erfassen, in der Zelle B7 ist die Formel "=D6*$B$1/12" einzugeben. Die Zelle C7 ist mit der Formel "=$B$3-B7", und die Zelle D7 mit der Formel "=D6-C7" zu versehen.
7. Sie erkennen anhand der berechneten Werte, dass sich bereits im zweiten Monat die Höhe der Zinszahlungen zugunsten einer höheren Tilgung verringert hat.
8. Markieren Sie nun mit der Maus die Zellen A7-D7 und vervollständigen Sie die Tabelle mit gehaltener linker Maustaste genau so weit nach unten, bis der Wert in der Zelle "Restschuld" bei 0 angekommen ist.

Im oberen Beispiel beträgt die Restschuld nach 119 Monaten noch exakt 69,78 Euro, welche im 120. Monat dann zu zahlen und das Annuitätendarlehen somit nach genau 10 Jahren Laufzeit getilgt ist. Die Summe der insgesamt geleisteten Zins- und Tilgungszahlungen können Sie in derselben Excel-Arbeitsmappe durch Addition der entsprechenden Spalten der Matrix ermitteln, im oberen Beispiel die Zellen B6-B124 bzw. C6-C124.