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        100.000,00  
3 4,00%  Eingangstilgung  7.000,00  4.000,00  11.000,00  96.000,00  
4 Verr. p.a.  6.720,00  4.280,00  11.000,00  91.720,00  
5 11.000,00  = Annuität  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.756,82  5.243,18  11.000,00  76.997,04  
8 82.000,00  Restschuld  5.389,79  5.610,21  11.000,00  71.386,84  
9 4,047  nach Perioden  4.997,08  6.002,92  11.000,00  65.383,92  
10     4.576,87  6.423,13  11.000,00  58.960,79  
11     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