Das Portal zum Thema Excel-Formeln

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.