Didaktische Hinweise für die Excel

Tutorial Excel – Übung 5&6 – Darlehensrückzahlung
-1-
1 Aufgabenstellung
Die Aufgabenstellung ist der folgenden URL zu entnehmen: Übung5&6.
2 Gleichung zur Finanzmathematik
Die Grundformel zur Finanzmathematik in Excel:
Bw * (1 + Zins )
Zzr
æ (1 + Zins )Zzr
+ Rmz (1 + Zins * F ) * çç
Zins
è
ö
÷ + Zw = 0
÷
ø
Bw
Barwert einer Investition
Zins
Zinssatz pro Periode(Zahlungszeitraum)
Zzr
Anzahl der Zahlungszeiträume
Rmz
Betrag (Annuität), der in jeder Periode gezahlt wird
Zw
Zukünftiger Wert (Endwert)
F
Fälligkeit (0 oder 1)
3 Erstellen des Tableaus
3.1
Eintragen der Ausgangswerte
Tutorial Excel – Übung 5&6 – Darlehensrückzahlung
3.2
Tabellenstruktur zur Rückzahlung
Auf Grund der monatlichen Zahlungsweise ergibt sich als Zahlungszeitraum ein
Monat. Die Monate werden laufend nummeriert.
-2-
Tutorial Excel – Übung 5&6 – Darlehensrückzahlung
3.3
-3-
Eintragen der Formeln
3.3.1 Berechnung der monatlichen Rate
Die monatliche konstante Rate beinhaltet den Anteil für die Kapitalrückzahlung und
den Zinsanteil. Dieser Betrag wird mittels der Funktion RMZ berechnet.
Auszug aus der Excel-Hilfe
RMZ
Liefert die konstante Zahlung einer Annuität pro Periode, wobei konstante Zahlungen
und ein konstanter Zinssatz vorausgesetzt werden. (RMZ = Regelmäßige Zahlung)
Syntax
RMZ(Zins;Zzr;Bw;Zw;F)
Der Barwert wurde negativ eingegeben, um der obigen Gleichung (Summe ist gleich
Null) zu entsprechen.
Tutorial Excel – Übung 5&6 – Darlehensrückzahlung
-4-
3.3.2 Berechnung der Tilgung
Die Tilgung umfasst den Anteil an der konstanten monatlichen Rate, der zur Tilgung
des aufgenommenen Kredites benutzt wird. Die Berechnung erfolgt mittels der
Funktion KAPZ.
Auszug aus der Excel-Hilfe
KAPZ
Liefert die Kapitalrückzahlung einer Investition für die angegebene Periode. Es
werden konstante periodische Zahlungen und ein konstanter Zinssatz vorausgesetzt.
(KAPZ = Kapitalrückzahlung)
Syntax
KAPZ(Zins;Zr;Zzr;Bw;Zw;F)
Zr
Jeweilige Periode
Die Tilgung ist für jeden Monat unterschiedlich.
Zum einfachen Kopieren der Formel werden Namen definiert.
Bereich
B16:B75
B6
B7
B8
B9
Name
Monat
BW
ZW
Zins
Zzr
Der Barwert wurde wieder negativ eingegeben. Die Formel wird in die restlichen
Felder kopiert.
Tutorial Excel – Übung 5&6 – Darlehensrückzahlung
3.3.3 Berechnung der Zinsen
Der Zinsanteil berechnet sich aus der Differenz zwischen der regelmäßigen
monatlichen Zahlung RMZ und dem Tilgungsanteil.
Zinsen = Rmz – Tilgung
Zum einfachen Kopieren der Formel werden Namen definiert.
Bereich
B12
D16:D75
Name
Rmz
Tilgung
-5-
Tutorial Excel – Übung 5&6 – Darlehensrückzahlung
3.3.4 Berechnung der Restschuld
Die Restschuld in einem Monat i ergibt sich aus der Restschuld des Monats i-1
reduziert um den Betrag der Tilgung.
Restschuldi = Restschuldi-1 – Tilgung
Für den ersten Monat (i=1) ergibt sich die Restschuld des 0. Monats aus der
Darlehenshöhe.
-6-
Tutorial Excel – Übung 5&6 – Darlehensrückzahlung
3.3.5 Berechnung der Einzahlungssumme
Die Summe der Einzahlungen berechnet sich aus dem Produkt der regelmäßigen
Zahlungen Rmz und dem entsprechenden Monat
Einzahlungssumme = Monat * Rmz
-7-
Tutorial Excel – Übung 5&6 – Darlehensrückzahlung
4 Grafik
Die Entwicklung der Restschuld und der Summe der eingezahlten Beträge sollen
über der Zeit dargestellt werden.
-8-
Tutorial Excel – Übung 5&6 – Darlehensrückzahlung
-9-
Entw icklung von Restschuld und eingezahlten Beträgen
140.000
120.000
100.000
80.000
Rest -schuld
Summe eingezahlt er Bet räge
60.000
40.000
20.000
0
M o nat e