Microsoft Excel

Das Portal zum Thema Excel-Formeln

Gruppe: Finanzmathematik

Beitrag: Eckdaten eines einfachen Tilgungsplans bestimmen

Aufgabe
In einem vereinfachten Tilgungsplan (nachschüssige Verrechnung und vorgegebene Eingangstilgung) sollen Restschuld, Tilgungs- und Zinsanteil in einer bestimmten Periode berechnet werden. Außerdem soll die Teillaufzeit zu einer vorgegebenen Restschuld ermittelt werden.

       A               B               C               D               E               F               G       
1 100.000,00  Darlehen  Per.  Zins  Tilgung  Annuität  Restwert  
2 7,00%  Zins  0        100.000,00  
3 4,00%  Eingangstilgung  1  7.000,00  4.000,00  11.000,00  96.000,00  
4 1  Verr. p.a.  2  6.720,00  4.280,00  11.000,00  91.720,00  
5 11.000,00  = Annuität  3  6.420,40  4.579,60  11.000,00  87.140,40  
6 10  Jahre Laufzeit  4  6.099,83  4.900,17  11.000,00  82.240,23  
7 14,952  Per. Gesamtlfz.  5  5.756,82  5.243,18  11.000,00  76.997,04  
8 82.000,00  Restschuld  6  5.389,79  5.610,21  11.000,00  71.386,84  
9 4,047  nach Perioden  7  4.997,08  6.002,92  11.000,00  65.383,92  
10     8  4.576,87  6.423,13  11.000,00  58.960,79  
11     9  4.127,26  6.872,74  11.000,00  52.088,05  
12     10  3.646,16  7.353,84  11.000,00  44.734,21  
13     11  3.131,39  7.868,61  11.000,00  36.865,60  
14     12  2.580,59  8.419,41  11.000,00  28.446,19  
15     13  1.991,23  9.008,77  11.000,00  19.437,43  
16     14  1.360,62  9.639,38  11.000,00  9.798,05  
17     15  685,86  10.314,14  11.000,00  -516,09 

Lösung
in A1 steht das Darlehen (als Beispiel 100.000)
In A2 steht der Zinssatz p.a. in Prozent (7 %)
In A3 steht die Anfangstilgung in Prozent (4 %)
In A4 stehen die Verrechnungen p.a. (1)
in A5 ergibt sich die bis auf die Schlußzahlung gleichbleibende Annuität
=A1*(A2+A3)/A4
in A6 steht die Periode (Jahr t) (10)

Restwert im Jahr t:
=A1*(1-A1*(A3/A4)*((1+A2/A4)^(A6*A4)-1)/(A1*A2/A4))

Zinsanteil im Jahr t:
=A1*(A2+A3)/A4-A1*A3/A4*(1+A2/A4)^(A6*A4-1)

Tilgungsanteil im Jahr t
=A1*A3/A4*(1+A2/A4)^(A6*A4-1)

Die Gesamtlaufzeit:
=LN(1-(A2/A4)/A5*A1)/LN(1/(1+A2/A4))

Teillaufzeit einer vorgegebenen Restschuld in A8:
=RUNDEN(LN((A2/A4*(A8-A1)-A1*A3/A4)/(-A1*A3/A4))/LN(1+A2/A4);3)

Für den Tilgungsplan:
in G2 steht =A1
in D3: =G2*A$2/A$4 | E3: =F3-D3 | F3: =A$5 | G3: =G2-E3
D3 bis G3 runterkopieren