Microsoft Excel

Das Portal zum Thema Excel-Formeln

Gruppe: Finanzmathematik - BEITRAG: Eine konstante Annuität bei flexiblen Zinsen berechnen


Aufgabe
Ein Darlehen, das während der Laufzeit variierende Zinssätze aufweist, soll mit einer konstanten Annuität getilgt werden.

       A               B               C               D               E               F               G               H               I       
1 Darlehen  20.000        Darlehen  Zinsen  Annuität  Tilgung  
2 Restschuld  5.000      Zinssatz  20.000       
3 Perioden  18      5%  19.117  1.000  1.882,96  883  
4 Zinsphasen  5      5%  18.190  956  1.882,96  927  
5 Annuität  1.882,96      5%  17.216  909  1.882,96  973  
6 Perioden  Zinssatz  Hilfsspalte    7%  16.539  1.205  1.882,96  678  
7 3  5%  11,05980    7%  15.813  1.158  1.882,96  725  
8 3  7%  9,20679    7%  15.037  1.107  1.882,96  776  
9 4  9%  9,27787    9%  14.508  1.353  1.882,96  530  
10 5  10%  7,69067    9%  13.931  1.306  1.882,96  577  
11 3  8%  3,24640    9%  13.301  1.254  1.882,96  629  
12     0,00000    9%  12.615  1.197  1.882,96  686  
13     0,00000    10%  11.994  1.262  1.882,96  621  
14     0,00000    10%  11.311  1.199  1.882,96  684  
15     0,00000    10%  10.559  1.131  1.882,96  752  
16         10%  9.732  1.056  1.882,96  827  
17         10%  8.822  973  1.882,96  910  
18         8%  7.645  706  1.882,96  1.177  
19         8%  6.373  612  1.882,96  1.271  
20         8%  5.000  510  1.882,96  1.373 

Lösung
In B1 steht das Darlehen
In B2 die Restschuld
In B3 die Anzahl an Perioden insgesamt, also =SUMME(A7:A15)
in B4 die Anzahl Phasen mit unterschieldichen Zinssätzen, also =ANZAHL(A7:A15)

In A7:A15 stehen die Perioden je Zinsphase
In B7:B15 stehen die Zinssätze je Zinsphase und je Zinsverrechnungsperiode
(bei monatlicher Verrechnung also Zinssatz/12)

In C7 steht
{=-ZW(B7;A7;1)*PRODUKT((1+B8:B$16)^A8:A$16)}
bis C15 kopieren

Die Annuität in B5 errechnet sich aus
{=(B1*PRODUKT((1+B7:B15)^A7:A15)-B2)/SUMME(C7:C15)}

Im Beispiel können bis A15 Zinsphasen eingegeben werden. Will man dies erweitern, müssen die Bezüge in C7 von B$16;A$16 und in B5 von A15;B15 entsprechend erweitert werden.
Erläuterung
Der Tilgungsplan von E1:I20 ist zur Lösung eigentlich nicht nötig, dient nur als tabellarischer Nachweis.
Formeln:
F2:=+B1
F3:=+F2-I3
G3:=+F2*E3
H3:=+$B$5
I3:=+H3-G3
F3:G3 kopieren bis F20

Das ganze funktioniert auch bei Kapitalansparungen. Dann ist B1=0 und B2 der Kapitalwert am Ende der Laufzeit.