Definition Tilgungsplan

Ein Tilgungsplan ist eine Tabelle, die periodische Zahlungen für ein Darlehen oder eine Hypothek im Laufe der Zeit auflistet, jede Zahlung in Kapital und Zinsen aufschlüsselt und den Restbetrag nach jeder Zahlung anzeigt.

Um einen Tilgungsplan für Darlehen oder Hypotheken in Excel zu erstellen, müssen wir die folgenden Funktionen verwenden:

• RMZ-Funktion – berechnet den Gesamtbetrag einer regelmäßigen Zahlung. Dieser Betrag bleibt für die gesamte Laufzeit des Darlehens konstant.

• KAPZ-Funktion – erhält den Hauptteil jeder Zahlung, die in Richtung des Darlehenskapitals geht, d.h. den geliehenen Betrag. Bei Folgezahlungen erhöht sich dieser Betrag.

• ZINSZ-Funktion – findet den Zinsanteil jeder Zahlung, der für Zinsen verwendet wird. Dieser Betrag verringert sich mit jeder Zahlung.

Lassen Sie uns nun Schritt für Schritt durch den Prozess gehen.

Richten Sie die Amortisationstabelle ein

Definieren Sie zunächst die Eingabezellen, in die Sie die bekannten Komponenten eines Darlehens eingeben:

• D4 – Darlehensbetrag
• D5 – Darlehenslaufzeit in Jahren
• D6 – jährlicher Zinssatz
• D7 – Zahl der Zahlungen pro Jahr

Als nächstes erstellen Sie eine Tilgungstabelle mit den Titeln (Periode, Zahlung, Zinsen, Kapital, Saldo) in L4:P4. Geben Sie in der Spalte Zeitraum eine Reihe von Zahlen ein, die der Gesamtzahl der Zahlungen entspricht (in diesem Beispiel 1-24):

Gesamtzahlungsbetrag berechnen (RMZ-Formel)

Der Zahlungsbetrag wird mit der Funktion RMZ(zins, zzr, bw, [zw], [F]) berechnet.

Um unterschiedliche Zahlungshäufigkeiten korrekt zu handhaben (z. B. wöchentlich, monatlich, vierteljährlich usw.), sollten Sie konsistent mit den Werten sein, die für die Argumente zins und zzr bereitgestellt werden:

• zins – teilen Sie den jährlichen Zinssatz durch die Anzahl der Zahlungsperioden pro Jahr ($D$6/$D$7).
• zzr – Multiplizieren Sie die Anzahl der Jahre mit der Anzahl der Zahlungsperioden pro Jahr ($D$6/$D$7).
• Geben Sie für das bw-Argument den Darlehensbetrag ein ($D$4).
• Die zw– und F-Argumente können weggelassen werden, da ihre Standardwerte für uns gut funktionieren (Saldo nach der letzten Zahlung sollte 0 sein; Zahlungen werden am Ende jeder Periode vorgenommen).

Wenn wir die obigen Argumente zusammenfügen, erhalten wir diese Formel:

=RMZ($D$6/$D$7;$D$5*$D$7;$D$4)

Bitte beachten Sie, dass wir absolute Zellbezüge verwenden, da diese Formel ohne Änderungen in die unten stehenden Zellen kopiert werden soll.

Geben Sie die RMZ-Formel in M5 ein, ziehen Sie sie in der Spalte nach unten, und Sie sehen einen konstanten Zahlungsbetrag für alle Zeiträume:

Zinsen berechnen (KAPZ-Formel)

Um den Zinsanteil jeder periodischen Zahlung zu ermitteln, verwenden Sie die Funktion KAPZ(zins, zr, zzr, bw, [zw], [F]):

=KAPZ($D$6/$D$7;L5;$D$5*$D$7;$D$4)

 

Alle Argumente sind dieselben wie in der RMZ-Formel, mit Ausnahme des zr-Arguments, das den Zahlungszeitraum angibt. Dieses Argument wird als relativer Zellbezug (L5) bereitgestellt, da es sich basierend auf der relativen Position einer Zeile ändern soll, in die die Formel kopiert wird.

Diese Formel geht zu N5, und dann kopieren Sie sie in so viele Zellen wie nötig:

Zinszahlung (ZINSZ-Formel)

Um den Kapitalanteil jeder periodischen Zahlung zu berechnen, verwenden Sie diese ZINSZ-Formel:

=ZINSZ($D$6/$D$7;L5;$D$5*$D$7;$D$4)

Syntax und Argumente sind genau die gleichen wie in der oben besprochenen KAPZ-Formel:
Diese Formel geht in Spalte O, beginnend in O5:

Holen Sie sich das Restguthaben

Um das verbleibende Guthaben für jeden Zeitraum zu berechnen, verwenden wir zwei verschiedene Formeln.

Um den Restbetrag nach der ersten Zahlung in P5 zu ermitteln, addieren Sie den Darlehensbetrag (D4) und den Kapitalbetrag der ersten Periode (O5):

= D4+O5

Da der Darlehensbetrag eine positive Zahl und der Kapitalbetrag eine negative Zahl ist, wird letztere tatsächlich von ersterer abgezogen.

Addieren Sie für die zweite und alle folgenden Perioden den vorherigen Saldo und die Kapitalsumme dieser Periode:

= P5+O6

Die obige Formel geht zu P6, und dann kopieren Sie sie in die Spalte. Durch die Verwendung von relativen Zellbezügen passt sich die Formel für jede Zeile korrekt an.

Das ist es! Unser monatlicher Tilgungsplan für Kredite ist fertig:

Da ein Darlehen von Ihrem Bankkonto ausgezahlt wird, geben Excel-Funktionen die Zahlung, die Zinsen und den Kapitalbetrag als negative Zahlen zurück. Standardmäßig sind diese Werte rot hervorgehoben und haben ein negatives Vorzeichen, wie Sie im obigen Bild sehen können.

Wenn Sie es vorziehen, alle Ergebnisse als positive Zahlen anzuzeigen, setzen Sie ein Minuszeichen vor die Funktionen.

Verwenden Sie für die Bilanzformeln die Subtraktion anstelle der Addition, wie im folgenden Screenshot gezeigt:

So erstellen Sie einen Kredittilgungsplan für die vordefinierte Anzahl von Zahlungsperioden. Diese schnelle einmalige Lösung eignet sich gut für ein bestimmtes Darlehen oder eine Hypothek.

Benötigen Sie Excel-Hilfe?

Haben Sie ein Problem mit Ihrer Excel-Datei? Für nur 49 Fr. analysieren wir Ihre Datei und erstellen einen konkreten Vorschlag. Weitere Infos dazu auf: www.exact-construct.ch