Das Portal zum Thema Excel-Formeln

Es soll ein Tilgungsplan mit Zins, Tilgung, Belastung und Entwicklung des Darlehensrestes entwickelt werden.

A               B               C               D               E        1 100.000          2 10.000          3 7,50%          4 12          5 10          6 0          7           8           9           10 Periode  Zins  Tilgung  Annuität  Restdarlehen   11           12         100.000,00   13 1  625,00  505,82  1.130,82  99.494,18   14 2  621,84  508,98  1.130,82  98.985,21   15 3  618,66  512,16  1.130,82  98.473,05   16 4  615,46  515,36  1.130,82  97.957,69   17 5  612,24  518,58  1.130,82  97.439,11   18 6  608,99  521,82  1.130,82  96.917,29   19 7  605,73  525,08  1.130,82  96.392,20   20 8  602,45  528,36  1.130,82  95.863,84

Lösung

in A1 steht die Darlehenssumme (als Beispiel 100.000)
in A2 steht der Finanzierungsrestwert zum Ende der Laufzeit (als Beispiel 10.000)
in A3 steht der Zinssatz (dezimal bzw in % als Beispiel: 7,5 %)
in A4 stehen die Verrechnungen p.a. (als Beispiel: 12 - monatlich)
in A5 steht die Gesamt-Laufzeit in Jahren (als Beispiel 10)
in A6 steht entweder 0 (nachschüssige) oder 1 (vorschüssige) Verrechnung

Überschriften: in A10 steht Periode in B10: Zins in C10: Tilgung in D10: Annuität in E10: Restdarlehen

in A13 steht 1
in A14 steht =WENN(ODER(A13="";A13>=A$4*A$5);"";A13+1)
ca. 300 (25 Jahre * 12 Monate) Zellen runterkopieren

in B13 steht =WENN(A13="";"";(E12-WENN(A$6=1;D13;0))*A$3/A$4)
und runterkopieren

in C13 steht =WENN(A13="";"";D13-B13)
und runterkopieren

in D13 steht die Annuitätenformel

=((A1-A2)*A3/(1-(A4/(A4+A3))^(A4*A5))+A2*A3)/A4*A4/(A4+(A6=1)*A3)

bei nachschüssiger Verrechnung ergeben sich für obiges Beispiel 1.130,82
in D14 steht =WENN(A14="";"";D13)
und runterkopieren

in E12 steht =A1
in E13 steht =WENN(A13="";"";E12-C13)
und runterkopieren

Einen fertigen Tilgungsplan findet man in unserem Tip Nr. 139 und folgende.

Erläuterung