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.

20 Kommentare

  1. eine solide Erklärung.
    Ich muß noch herausfinden, wie man den Funktionsassistenten öffnet.
    Ich habe jedoch anhand der Beispiele die Zell-Beziehungen eingegeben und alles hat geklappt.
    Prima, und herzlichen Dank, Günter Ziepa

    Antworten
    • Hallo,
      in der Bearbeitungsleiste auf das fx klicken. Dann öffnet sich der Funktionsassistent.

      Antworten
  2. Herzlichen Dank Ihnen, hat mir super schnell geholfen!

    Antworten
  3. HAllo,

    ich suche etwas anderes, ich suche etwas, wo ich oben eine MAske habe, dann den Betrag eingebe, die Zinsen. ggf noch einen bearbeitungskosten betrag
    dann möchte ich ähnlich wie bei einer bank bei dem Kontoauszug,
    die Zinsen ausgewissen bekommen und monatlich zu wechselnden Tage eine Rückzahlung oder eine weitere darlehnsauszahlung eintragen können.

    habt Ihr so etwas. bitte anschreiben:

    Antworten
    • Hallo Frank,

      eine fertige Lösung können wir Ihnen nicht anbieten.

      Antworten
  4. Der Download funktionierte leider nicht.

    Antworten
    • Hallo,
      mit Edge und Firefox klappt der Download.

      Antworten
  5. Super Beitrag und toll erklärt.
    Vielen Dank!

    Antworten
  6. Wie binde ich in Ihrem Beispiel eine jähliche Sondertilgung ein ?

    Antworten
    • Hallo Ingo,
      mit den vorgestellten Funktion RMZ, KUMKAPITAL und KUMZINSZ ist eine Berücksichtigung von jährlichen Sondertilgungen nicht möglich.

      Antworten
      • Hallo Herr Richter,

        die Berücksichtigung von Sondertilgungen wäre schon sehr hilfreich, da bei meinem Hypothekendarlehen als Option möglich und genutzt.
        Ansonsten großes Dankeschön für Ihre hilfreichen und gut nachvollziehbaren Erklärungen.

        Mit besten Grüßen
        Thomas

        Antworten
        • Hallo Thomas,
          erweitere die Tabelle um eine Spalte Sondertilgung und passe die Formel für die Restschuld so an, dass die Sondertilgung mit einfließt.

          Antworten
  7. Danke für die verständliche Erklärung. Genau das hatte ich gesucht!

    Antworten
  8. Hallo Herr Richter,
    danke! Die Ecxel-Tabelle ist genau das, was ich (heute lange) gesucht habe!
    Viele Grüße
    Sabine

    Antworten
  9. Ich danke Ihnen von Herzen für die Erklärung!
    Hat mir sehr geholfen und ist wirklich SEHR GUT erklärt!

    Antworten
  10. Vielen Dank

    Antworten
  11. 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
  12. 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

Antworten auf Thomas Antworten abbrechen

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

Pin It on Pinterest

Share This