| A | B | C | D | E | F | G | H | I | |
| 1 | Tilgungsplan für das Darlehen | ||||||||
| 2 | |||||||||
| 3 | Anleihebetrag | 4.000.000,00 | = Kontostand: 01.02.2011 | ||||||
| 4 | Zinssatz | '0.01 | |||||||
| 5 | Laufzeit (in Jahren) | 10 | |||||||
| 6 | Zahlungen pro Jahr | 12 | |||||||
| 7 | Kreditbeginn | 01.02.2011 | |||||||
| 8 | Monatliche Belastung | 6.666,67 | 6666,67 | in D8 immer den angezeigten Wert aus E 8 einfügen | |||||
| 9 | Anzahl der Raten | 120 | |||||||
| 10 | Tilgung | '0.01 | |||||||
| 11 | |||||||||
| 12 | |||||||||
| 13 | Rate Nr. | Zahlungstermine | Anfangsstand | Zins | Tilgung | Endstand | Kumulativer Zins | Kumulative Tilgung | |
| 14 | 1 | 01.02.2011 | 4.000.000,00 | 3.333,33 | 3.333,34 | 3.996.666,66 | 3.333,33 | 3.333,33 | |
| 15 | 2 | 01.03.2011 | 3.996.666,66 | 3.330,56 | 3.336,11 | 3.993.330,55 | 6.663,89 | 6.669,45 | |
| 16 | 3 | 01.04.2011 | 3.993.330,55 | 3.327,78 | 3.338,89 | 3.989.991,65 | 9.991,66 | 10.008,35 | |
| 17 | 4 | 01.05.2011 | 3.989.991,65 | 3.324,99 | 3.341,68 | 3.986.649,98 | 13.316,66 | 13.350,02 | |
| 18 | 5 | 01.06.2011 | 3.986.649,98 | 3.322,21 | 3.344,46 | 3.983.305,52 | 16.638,87 | 16.694,48 | |
| 19 | 6 | 01.07.2011 | 3.983.305,52 | 3.319,42 | 3.347,25 | 3.979.958,27 | 19.958,29 | 20.041,73 | |
| 20 | 7 | 01.08.2011 | 3.979.958,27 | 3.316,63 | 3.350,04 | 3.976.608,23 | 23.274,92 | 23.391,77 | |
| 21 | 8 | 01.09.2011 | 3.976.608,23 | 3.313,84 | 3.352,83 | 3.973.255,40 | 26.588,76 | 26.744,60 | |
| 22 | 9 | 01.10.2011 | 3.973.255,40 | 3.311,05 | 3.355,62 | 3.969.899,78 | 29.899,81 | 30.100,22 | |
| 23 | 10 | 01.11.2011 | 3.969.899,78 | 3.308,25 | 3.358,42 | 3.966.541,36 | 33.208,06 | 33.458,64 | |
| 24 | 11 | 01.12.2011 | 3.966.541,36 | 3.305,45 | 3.361,22 | 3.963.180,14 | 36.513,51 | 36.819,86 | |
| 25 | 12 | 01.01.2012 | 3.963.180,14 | 3.302,65 | 3.364,02 | 3.959.816,12 | 39.816,16 | 40.183,88 | |
| 26 | 13 | 01.02.2012 | 3.959.816,12 | 3.299,85 | 3.366,82 | 3.956.449,29 | 43.116,00 | 40.217,37 | |
| 27 | 14 | 01.03.2012 | 3.956.449,29 | 3.297,04 | 3.369,63 | 3.953.079,66 | 46.413,04 | 40.250,88 | |
| 28 | 15 | 01.04.2012 | 3.953.079,66 | 3.294,23 | 3.372,44 | 3.949.707,23 | 49.707,28 | 40.284,43 | |
| 29 | 16 | 01.05.2012 | 3.949.707,23 | 3.291,42 | 3.375,25 | 3.946.331,98 | 52.998,70 | 40.318,00 | |
| 30 | 17 | 01.06.2012 | 3.946.331,98 | 3.288,61 | 3.378,06 | 3.942.953,92 | 56.287,31 | 40.351,60 | |
| 31 | 18 | 01.07.2012 | 3.942.953,92 | 3.285,79 | 3.380,88 | 3.939.573,04 | 59.573,10 | 40.385,22 | |
| 32 | 19 | 01.08.2012 | 3.939.573,04 | 3.282,98 | 3.383,69 | 3.936.189,35 | 62.856,08 | 40.418,88 | |
| verbundene Zellen |
| E3:F3 |
| verwendete Formeln | ||
| Zelle | Formel | Bereich |
| E3 | =+First_payment_due | |
| E8 | =SUMME(E14+I14) | |
| D9 | =D5*D6 | |
| B14 | =WENN(First_payment_no<Total_payments;First_payment_no;"") | |
| C14 | =D7 | |
| D14 | =WENN(B14<>"";WENN(Table_beg_bal<0;0;Table_beg_bal);"") | |
| E14 | =Interest | |
| F14 | =Principal | |
| G14 | =Ending.Balance | |
| H14 | =WENN(B14<>"";E14+Table_prior_interest;"") | |
| I14 | =SUMME(D3*D10/12) | |
| B15 | =payment.Num | |
| D15 | =Beg.Bal | |
| E15 | =Interest | |
| F15 | =Principal | |
| G15 | =Ending.Balance | |
| H15 | =Cum.Interest | |
| I15 | =SUMME(F14:F15) | |
| B16 | =payment.Num | |
| D16 | =Beg.Bal | |
| E16 | =Interest | |
| F16 | =Principal | |
| G16 | =Ending.Balance | |
| H16 | =Cum.Interest | |
| I16 | =SUMME(F14:F16) | |
| B17 | =payment.Num | |
| D17 | =Beg.Bal | |
| E17 | =Interest | |
| F17 | =Principal | |
| G17 | =Ending.Balance | |
| H17 | =Cum.Interest | |
| I17 | =SUMME(F14:F17) | |
| B18 | =payment.Num | |
| D18 | =Beg.Bal | |
| E18 | =Interest | |
| F18 | =Principal | |
| G18 | =Ending.Balance | |
| H18 | =Cum.Interest | |
| I18 | =SUMME(F14:F18) | |
| B19 | =payment.Num | |
| D19 | =Beg.Bal | |
| E19 | =Interest | |
| F19 | =Principal | |
| G19 | =Ending.Balance | |
| H19 | =Cum.Interest | |
| I19 | =SUMME(F14:F19) | |
| B20 | =payment.Num | |
| D20 | =Beg.Bal | |
| E20 | =Interest | |
| F20 | =Principal | |
| G20 | =Ending.Balance | |
| H20 | =Cum.Interest | |
| I20 | =SUMME(F14:F20) | |
| B21 | =payment.Num | |
| D21 | =Beg.Bal | |
| E21 | =Interest | |
| F21 | =Principal | |
| G21 | =Ending.Balance | |
| H21 | =Cum.Interest | |
| I21 | =SUMME(F14:F21) | |
| B22 | =payment.Num | |
| D22 | =Beg.Bal | |
| E22 | =Interest | |
| F22 | =Principal | |
| G22 | =Ending.Balance | |
| H22 | =Cum.Interest | |
| I22 | =SUMME(F14:F22) | |
| B23 | =payment.Num | |
| D23 | =Beg.Bal | |
| E23 | =Interest | |
| F23 | =Principal | |
| G23 | =Ending.Balance | |
| H23 | =Cum.Interest | |
| I23 | =SUMME(F14:F23) | |
| B24 | =payment.Num | |
| D24 | =Beg.Bal | |
| E24 | =Interest | |
| F24 | =Principal | |
| G24 | =Ending.Balance | |
| H24 | =Cum.Interest | |
| I24 | =SUMME(F14:F24) | |
| B25 | =payment.Num | |
| D25 | =Beg.Bal | |
| E25 | =Interest | |
| F25 | =Principal | |
| G25 | =Ending.Balance | |
| H25 | =Cum.Interest | |
| I25 | =SUMME(F14:F25) | |
| B26 | =payment.Num | |
| D26 | =Beg.Bal | |
| E26 | =Interest | |
| F26 | =Principal | |
| G26 | =Ending.Balance | |
| H26 | =Cum.Interest | |
| I26 | =SUMME(F15:F26) | |
| B27 | =payment.Num | |
| D27 | =Beg.Bal | |
| E27 | =Interest | |
| F27 | =Principal | |
| G27 | =Ending.Balance | |
| H27 | =Cum.Interest | |
| I27 | =SUMME(F16:F27) | |
| B28 | =payment.Num | |
| D28 | =Beg.Bal | |
| E28 | =Interest | |
| F28 | =Principal | |
| G28 | =Ending.Balance | |
| H28 | =Cum.Interest | |
| I28 | =SUMME(F17:F28) | |
| B29 | =payment.Num | |
| D29 | =Beg.Bal | |
| E29 | =Interest | |
| F29 | =Principal | |
| G29 | =Ending.Balance | |
| H29 | =Cum.Interest | |
| I29 | =SUMME(F18:F29) | |
| B30 | =payment.Num | |
| D30 | =Beg.Bal | |
| E30 | =Interest | |
| F30 | =Principal | |
| G30 | =Ending.Balance | |
| H30 | =Cum.Interest | |
| I30 | =SUMME(F19:F30) | |
| B31 | =payment.Num | |
| D31 | =Beg.Bal | |
| E31 | =Interest | |
| F31 | =Principal | |
| G31 | =Ending.Balance | |
| H31 | =Cum.Interest | |
| I31 | =SUMME(F20:F31) | |
| B32 | =payment.Num | |
| D32 | =Beg.Bal | |
| E32 | =Interest | |
| F32 | =Principal | |
| G32 | =Ending.Balance | |
| H32 | =Cum.Interest | |
| I32 | =SUMME(F21:F32) | |
| definierte Namen | |||
| Name | Bezieht sich auf | Tabelle | Z1S1-Formel |
| Annual_interest_rate | =Hypothek!$D$4 | =Hypothek!Z4S4 | |
| Beg.Bal | =WENN(Hypothek!IU1"";Hypothek!D65536;"") | =WENN(Hypothek!ZS(254)"";Hypothek!Z(65535)S(3);"") | |
| Calculated_payment | =Hypothek!$D$8 | =Hypothek!Z8S4 | |
| Cum.Interest | =WENN(Hypothek!IQ1"";Hypothek!A65536+Hypothek!IT1;"") | =WENN(Hypothek!ZS(250)"";Hypothek!Z(65535)S+Hypothek!ZS(253);"") | |
| Ending.Balance | =WENN(Hypothek!IR1"";Hypothek!IT1-Hypothek!IV1;"") | =WENN(Hypothek!ZS(251)"";Hypothek!ZS(253)-Hypothek!ZS(255);"") | |
| Entered_payment | =Hypothek!$D$11 | =Hypothek!Z11S4 | |
| First_payment_due | =Hypothek!$D$7 | =Hypothek!Z7S4 | |
| First_payment_no | =Hypothek!$C$207 | =Hypothek!Z207S3 | |
| Interest | =WENN(Hypothek!IT1"";Hypothek!IV1*Annuitätendarlehn.xls!Periodic_rate;"") | =WENN(Hypothek!ZS(253)"";Hypothek!ZS(255)*Annuitätendarlehn.xls!Periodic_rate;"") | |
| Loan_amount | =Hypothek!$D$3 | =Hypothek!Z3S4 | |
| payment.Num | =WENN(ODER(Hypothek!A65536="";Hypothek!A65536=Annuitätendarlehn.xls!Total_payments);"";Hypothek!A65536+1) | =WENN(ODER(Hypothek!Z(65535)S="";Hypothek!Z(65535)S=Annuitätendarlehn.xls!Total_payments);"";Hypothek!Z(65535)S+1) | |
| Payments_per_year | =Hypothek!$D$6 | =Hypothek!Z6S4 | |
| Principal | =WENN(Hypothek!IS1"";MIN(Hypothek!IU1;Annuitätendarlehn.xls!Pmt_to_use-Hypothek!IV1);"") | =WENN(Hypothek!ZS(252)"";MIN(Hypothek!ZS(254);Annuitätendarlehn.xls!Pmt_to_use-Hypothek!ZS(255));"") | |
| Table_beg_bal | =Hypothek!$G$206 | =Hypothek!Z206S7 | |
| Table_prior_interest | =Hypothek!$G$207 | =Hypothek!Z207S7 | |
| Term_in_years | =Hypothek!$D$5 | =Hypothek!Z5S4 | |
| Total_payments | =Annuitätendarlehn.xls!Payments_per_year*Annuitätendarlehn.xls!Term_in_years | =Annuitätendarlehn.xls!Payments_per_year*Annuitätendarlehn.xls!Term_in_years | |
| Print_Area | =Hypothek!$A$1:$I$203 | Hypothek | =Hypothek!Z1S1:Z203S9 |
| Print_Titles | =Hypothek!$1:$13 | Hypothek | =Hypothek!Z1:Z13 |