Credit 1000000 i p. a. 0.12 "Create a plan of redemption that relates to a loan of 1,000,000.00. The interest rate is 12% p. a. and the bank calculates the interest each month. You repay the debt in 10 years with regular monthly payment. What will bee the interest payment after 5 years? How much do you pay on interest in total over this period? What is the current level of debt in 5 years?" i p. m. 0.01 No of years: 10 Calculation - excel: Payment Annuity Payment of Interest Amortization of debt Current Debt Redemption in 5 years Payment of interest in 5 years Current debt Annuity: 14347.09484 0 1000000 7897.352845 6449.741996 637076.8467 1 14347.09484 10000 4347.09484 995652.9052 2 14347.09484 9956.529052 4390.565789 991262.3394 3 14347.09484 9912.623394 4434.471447 986827.8679 Calculation by hand: 4 14347.09484 9868.278679 4478.816161 982349.0518 AM(r)=a*v^(n-r) 5 14347.09484 9823.490518 4523.604323 977825.4474 "Let AM(r) ....... is the amortization in 5th year, ie 60th installments (12 installments per year, 5 years)." 6 14347.09484 9778.254474 4568.840366 973256.6071 Then: AM(r+1)= 7897.352845 ie redemption in the 61st installment. 7 14347.09484 9732.566071 4614.52877 968642.0783 8 14347.09484 9686.420783 4660.674057 963981.4042 It holds true: a=IP+AM 9 14347.09484 9639.814042 4707.280798 959274.1235 Then: IM(r+1)=a -AM(r+1) 6449.741996 10 14347.09484 9592.741235 4754.353606 954519.7698 11 14347.09484 9545.197698 4801.897142 949717.8727 D(r) ……………… is the state of debt in 5th year. 12 14347.09484 9497.178727 4849.916113 944867.9566 It holds true: U(r+1)=D(r)*i 13 14347.09484 9448.679566 4898.415274 939969.5413 Then: D(r)=U(r+1)/i 644974.1996 14 14347.09484 9399.695413 4947.399427 935022.1419 15 14347.09484 9350.221419 4996.873421 930025.2685 16 14347.09484 9300.252685 5046.842156 924978.4263 17 14347.09484 9249.784263 5097.310577 919881.1157 Payment Anuita Interest payment Amortization Current Debt 18 14347.09484 9198.811157 5148.283683 914732.8321 60 14347.09484 644974.1996 19 14347.09484 9147.328321 5199.76652 909533.0655 61 14347.09484 6449.741996 7897.352845 637076.8467 20 14347.09484 9095.330655 5251.764185 904281.3013 21 14347.09484 9042.813013 5304.281827 898977.0195 Sum of interest payment 22 14347.09484 8989.770195 5357.324645 893619.6949 It holds true: a1=a2=…an 23 14347.09484 8936.196949 5410.897892 888208.797 24 14347.09484 8882.08797 5465.00687 882743.7901 Then the following must apply: sum of IP=a*r-sum_AM 25 14347.09484 8827.437901 5519.656939 877224.1332 Where the sum of AM must be = D(0)-D(r) 26 14347.09484 8772.241332 5574.853509 871649.2797 Then the sum of IP= 512249.632 27 14347.09484 8716.492797 5630.602044 866018.6776 28 14347.09484 8660.186776 5686.908064 860331.7696 Calculation - excel: 512249.632 29 14347.09484 8603.317696 5743.777145 854587.9924 30 14347.09484 8545.879924 5801.214916 848786.7775 31 14347.09484 8487.867775 5859.227065 842927.5504 32 14347.09484 8429.275504 5917.819336 837009.7311 33 14347.09484 8370.097311 5976.997529 831032.7336 34 14347.09484 8310.327336 6036.767505 824995.9661 35 14347.09484 8249.959661 6097.13518 818898.8309 36 14347.09484 8188.988309 6158.106531 812740.7244 37 14347.09484 8127.407244 6219.687597 806521.0368 38 14347.09484 8065.210368 6281.884473 800239.1523 39 14347.09484 8002.391523 6344.703317 793894.449 40 14347.09484 7938.94449 6408.150351 787486.2986 41 14347.09484 7874.862986 6472.231854 781014.0668 42 14347.09484 7810.140668 6536.954173 774477.1126 43 14347.09484 7744.771126 6602.323714 767874.7889 44 14347.09484 7678.747889 6668.346952 761206.4419 45 14347.09484 7612.064419 6735.030421 754471.4115 46 14347.09484 7544.714115 6802.380725 747669.0308 47 14347.09484 7476.690308 6870.404533 740798.6262 48 14347.09484 7407.986262 6939.108578 733859.5177 49 14347.09484 7338.595177 7008.499664 726851.018 50 14347.09484 7268.51018 7078.58466 719772.4333 51 14347.09484 7197.724333 7149.370507 712623.0628 52 14347.09484 7126.230628 7220.864212 705402.1986 53 14347.09484 7054.021986 7293.072854 698109.1258 54 14347.09484 6981.091258 7366.003583 690743.1222 55 14347.09484 6907.431222 7439.663618 683303.4586 56 14347.09484 6833.034586 7514.060255 675789.3983 57 14347.09484 6757.893983 7589.200857 668200.1975 58 14347.09484 6682.001975 7665.092866 660535.1046 355025.8004 59 14347.09484 6605.351046 7741.743794 652793.3608 60 14347.09484 6527.933608 7819.161232 644974.1996 The amortization in 5 years Interest payment in 5th yeary Debt 61 14347.09484 6449.741996 7897.352845 637076.8467 7897.352845 6449.741996 637076.8467 62 14347.09484 6370.768467 7976.326373 629100.5203 63 14347.09484 6291.005203 8056.089637 621044.4307 64 14347.09484 6210.444307 8136.650533 612907.7802 The sum of payed interest The sum of all redemptions in 5 years 65 14347.09484 6129.077802 8218.017039 604689.7631 512249.632 355025.8004 66 14347.09484 6046.897631 8300.197209 596389.5659 67 14347.09484 5963.895659 8383.199181 588006.3667 68 14347.09484 5880.063667 8467.031173 579539.3356 69 14347.09484 5795.393356 8551.701485 570987.6341 70 14347.09484 5709.876341 8637.218499 562350.4156 71 14347.09484 5623.504156 8723.590684 553626.8249 72 14347.09484 5536.268249 8810.826591 544815.9983 73 14347.09484 5448.159983 8898.934857 535917.0635 74 14347.09484 5359.170635 8987.924206 526929.1393 75 14347.09484 5269.291393 9077.803448 517851.3358 76 14347.09484 5178.513358 9168.581482 508682.7543 77 14347.09484 5086.827543 9260.267297 499422.487 78 14347.09484 4994.22487 9352.86997 490069.6171 79 14347.09484 4900.696171 9446.39867 480623.2184 80 14347.09484 4806.232184 9540.862656 471082.3557 81 14347.09484 4710.823557 9636.271283 461446.0844 82 14347.09484 4614.460844 9732.633996 451713.4504 83 14347.09484 4517.134504 9829.960336 441883.4901 84 14347.09484 4418.834901 9928.259939 431955.2302 85 14347.09484 4319.552302 10027.54254 421927.6876 86 14347.09484 4219.276876 10127.81796 411799.8697 87 14347.09484 4117.998697 10229.09614 401570.7735 88 14347.09484 4015.707735 10331.3871 391239.3864 89 14347.09484 3912.393864 10434.70098 380804.6854 90 14347.09484 3808.046854 10539.04799 370265.6375 91 14347.09484 3702.656375 10644.43847 359621.199 92 14347.09484 3596.21199 10750.88285 348870.3161 93 14347.09484 3488.703161 10858.39168 338011.9245 94 14347.09484 3380.119245 10966.9756 327044.9489 95 14347.09484 3270.449489 11076.64535 315968.3035 96 14347.09484 3159.683035 11187.41181 304780.8917 97 14347.09484 3047.808917 11299.28592 293481.6058 98 14347.09484 2934.816058 11412.27878 282069.327 99 14347.09484 2820.69327 11526.40157 270542.9254 100 14347.09484 2705.429254 11641.66559 258901.2599 101 14347.09484 2589.012599 11758.08224 247143.1776 102 14347.09484 2471.431776 11875.66306 235267.5145 103 14347.09484 2352.675145 11994.41969 223273.0949 104 14347.09484 2232.730949 12114.36389 211158.731 105 14347.09484 2111.58731 12235.50753 198923.2234 106 14347.09484 1989.232234 12357.86261 186565.3608 107 14347.09484 1865.653608 12481.44123 174083.9196 108 14347.09484 1740.839196 12606.25564 161477.6639 109 14347.09484 1614.776639 12732.3182 148745.3457 110 14347.09484 1487.453457 12859.64138 135885.7044 111 14347.09484 1358.857044 12988.2378 122897.4666 112 14347.09484 1228.974666 13118.12017 109779.3464 113 14347.09484 1097.793464 13249.30138 96530.04502 114 14347.09484 965.3004502 13381.79439 83148.25063 115 14347.09484 831.4825063 13515.61233 69632.63829 116 14347.09484 696.3263829 13650.76846 55981.86983 117 14347.09484 559.8186983 13787.27614 42194.59369 118 14347.09484 421.9459369 13925.1489 28269.44479 119 14347.09484 282.6944479 14064.40039 14205.0444 120 14347.09484 142.050444 14205.0444 0.00