Zins- und Tilgungszahlungen eines Ratenkredits mit Excel ermitteln

Bei der Aufnahme eines Ratenkredits interessieren den Darlehensnehmer neben dem Zinssatz insbesondere die Höhe der regelmäßigen Zahlungen sowie die Höhe der Restschuld nach einer bestimmten Anzahl von Zahlungsperioden.

In unserem Beispiel soll für einen Ratenkredit in Höhe von 10.000 € mit einer Laufzeit von 3 Jahren und einem Zinssatz von 3,5% p. a. errechnet werden, wie hoch die monatliche Belastung aus Zins- und Tilgungszahlung ist und wie hoch die Restschuld nach jedem Jahr ausfällt. Bei diesem Annuitätendarlehen ist das Darlehen nach 3 Jahren getilgt.

Die Ergebnisse der Berechnungen in Excel sehen folgendermaßen aus:

Zins- und Tilgungsraten in Excel

Monatliche Rate

Für die Berechnung der monatlichen Rate aus Zins- und Tilgungszahlung wird die Funktion RMZ verwendet. Diese Funktion gehört zur Kategorie der finanzmathematischen Funktionen. RMZ steht für regelmäßige Zahlung:

Die Formel in Zelle B5 lautet:

=RMZ(B2/B4;B3*B4;B1)

Im Funktionsassistenten sieht die Formel folgendermaßen aus:

Funktion RMZ in Excel

Diese Funktion erwartet als erstes Argument den Zinssatz. Da die Raten monatlich gezahlt werden und sich somit jeden Monat die Restschuld verringert, wird der jährliche Zinssatz in Höhe von 3% durch die Anzahl der zu leistenden 12 Raten pro Jahr in Zelle B4 dividiert: Als zweites Argument geben Sie die Anzahl der Raten über den gesamten Darlehenszeitrum an. Das Argument heißt Zzr und steht für Zahlungszeiträume. In dem Beispiel sind es 3 * 12 = 36 Raten. Das dritte notwendige Argument ist der Darlehensbetrag, der in Zelle B1 steht. Das Argument nennt Sich Bw und steht für Barwert. Es ist der Wert eines Betrags zu Beginn einer Darlehenslaufzeit.

Die RMZ-Funktion kennt noch 2 weitere, optionale Parameter, die Sie in diesem Fall nicht anzugeben brauchen. Der Parameter Zw steht für zukünftiger Wert. Er wird benötigt, wenn am Ende der Laufzeit das Darlehen nicht komplett getilgt ist. In diesem Fall wird die Restschuld angegeben. Mit dem fünften Argument, geben Sie an, ob die Zahlung am Anfang oder am Ende einer Periode fällig wird. Im unserem Beispiel erfolgt die Zahlung am Ende der Periode. Sie brauchen den Parameter deshalb nicht anzugeben, weil dies der Standard ist.

Die monatlich zu zahlende Rate in Zelle B5 wird mit einem negativen Vorzeichen dargestellt, wenn der Darlehensbetrag mit einem positiven Vorzeichen eingegeben worden ist. Die Formatierung der Zelle mit einer roten Schriftfarbe wird durch die RMZ-Funktion automatisch vorgenommen.

In Zelle B6 wird die Summe der über die gesamte Darlehenslaufzeit zu zahlenden Zins- und Tilgungsbeträge durch die Multiplikation der monatlichen Rate mit der Anzahl der zu leistenden Zahlungen ermittelt.

Restschuld

Um die Höhe der Restschuld am Ende eines jeden Darlehensjahres zu errechnen, wird zunächst die Höhe der bis dahin geleisteten Tilgungen ermittelt. Dieser Tilgungsbetrag wird dann vom Darlehensbetrag abgezogen:

Für die Ermittlung der Höhe der kumulierten Tilgungszahlungen für einen vorzugebenden Zeitraum stellt Excel die Funktion KUMKAPITAL zur Verfügung. Die Formel zur Errechnung der im ersten Jahr geleisteten Tilgungen sieht so aus:

=KUMKAPITAL($B$2/$B$4;$B$3*$B$4;$B$1;(A9-1)*$B$4+1;A9*$B$4;0)

Wenn Sie den Funktionsassistenten aufrufen, erscheint folgende Dialogbild:

Funktion KUMKAPITAL in Excel

Die Funktion KUMKAPITAL erwartet insgesamt 6 Argumente. Im Dialogbild oben sehen Sie zunächst nur die ersten 5 Argumente.

Die ersten 3 Argumente der Funktion sind identisch mit der RMZ-Funktion. In unserem Beispiel werden absolute Zellbezüge verwendet, weil die Formel im nächsten Schritt für die beiden anderen Jahre nach unten kopiert werden soll. Um die Tilgungsleistungen des ersten Jahres zu ermitteln, muss im Ergebnis das Argument Zeitraum_Anfang den Wert 1 für die erste Monatsrate und das Argument Zeitraum_Ende den Wert 12 für die 12. Monatsrate haben. Die beiden Werte werden über eine Formel ermittelt, um sie ebenfalls für die beiden anderen Jahre nach unten kopieren zu können. Bei den beiden Formel wird Bezug genommen auf die Zelle A9, in der eine 1 für das erste Jahr steht.

Als sechstes Argument müssen Sie noch angeben, ob die Zahlungen am Anfang oder am Ende der Periode zu leisten sind. Im Gegensatz zur Funktion RMZ können Sie das Argument nicht weglassen. In diesem Beispiel geben Sie eine 0 für Zahlungen am Ende der Periode ein:

Funktion KUMKAPITAL in Excel

Die Höhe der Tilgungsleistungen im ersten Jahr der Darlehenslaufzeit beträgt 3.217,54 €. Wenn Sie diesen negativen Betrag zu dem Darlehensbetrag addieren, erhalten Sie in Zelle D9 die Restschuld nach einem Jahr.

Die Formel in Zelle D9 lautet:

=$B$1+SUMME($C$9:C9)

Zinszahlungen

In der Zelle B9 wird die Summe der im ersten Jahr zu zahlenden Zinsen ermittelt. Die Funktion KUMZINSZ für kumulierte Zinszahlungen erwartet die gleichen Parameter wie die Funktion KUMKAPITAL. Die Formel lautet:

=KUMZINSZ($B$2/$B$4;$B$3*$B$4;$B$1;(A9-1)*$B$4+1;A9*$B$4;0)

Quartalszahlungen

In unteren Schaubild erkennen Sie, wie sich die Beträge ändern, wenn Sie statt 12 Monatsraten nur 4 Quartalsraten bezahlen. Sie brauchen dazu nur die Zahl in der Zelle B4 von 12 auf 4 ändern.
Zins- und Tilgungsraten in Excel

Hinweise

Die oben durchgeführten Berechnungen können Sie ohne Erstellung eines detaillierten Tilgungsplan durchführen. Falls Sie während der Darlehenslaufzeit Sondertilgungen leisten und die Auswirkungen auf die Zins- und Tilgungsraten sehen möchten, sollten Sie einen Tilgungsplan erstellen, der für jede Zahlungsperiode die Zins- und Tilgungsbeträge sowie die Restschuld ermittelt.

Download

Die zugehörige Arbeitsmappe können Sie hier herunterladen.

Michael Richter

Michael Richter

Autor

Ich unterstütze unsere Kunden in erster Linie durch Programmierung von Datenbankanwendungen und Zusatzmodulen von Microsoft Office. In Seminaren und Tipps auf unserer Blogseite gebe ich mein Wissen weiter.

5 Kommentare

  1. Hallo Herr Richter!
    Könnten Sie für uns nicht-studierende Nutzer nicht ihr Beispiel auch zum Download anbieten?
    Vielen Dank und herzliche Grüße, Sebastian Burger

    Antworten
    • Sie können die zugehörige Excel-Arbeitsmappe hier herunterladen.

      Antworten
  2. Hallo Herr Richter,
    leider gelingt es mir nicht, Ihren hilfreichen Beitrag so zu verändern, dass ich die monatliche Rate festlegen und dann die Restschuld nach Ablauf der angegebenen Jahre berechnen lassen kann.
    Können Sie mir dazu einen Tipp geben?
    Viele freundliche Grüße, Hajo Schwietering

    Antworten
    • Hallo Herr Schwietering,

      verwenden Sie für Ihre Berechnung die Funktion ZW().

      Antworten
  3. Vielen Dank

    Antworten

Einen Kommentar abschicken

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

Pin It on Pinterest

Share This