Now, Loan Amount (PV)=108000*(1-20%)=$86400,
tenure(nper)=5*12=60,
Say, equivalent monthly interest rate is r
Hence (1+r)^12=(1+3.4%/2)^2
or, r= 0.281%
Now, below is the amortization schedule:
Now, if they repaid lump sum amount of $10000 at the end of 3rd
year i.e. 36th month and increase their periodic payment by 30%
i.e. 1566.98*(1+30%)=$2037.07 after 40the payment i.e 41th payment
onwards, the below would be the amortization schedule:
Hence, you would repay the loan after 51st payment i.e. loan
tenure would be shortened by 60-51=9 months.
А В C D 86400 1 PV 2 nper 60 3 rate 0.281% ЕРМТ(В3, В2, В1,0,0) 4 PMT PMT(rate, nper, pv, [fv], [type]) 5 6
А В 1 PV 2 nper 86400 60 3 rate 0.281% ($1,566.98) 4 PMT
К M N MonthLOriginal Loan Interest(K*0.281%) Repayment Outstanding Loan (K+L-M] 86400 85076.10402 86400 O O 1 86400 243.0838002 $1,566.98 $1,566.98 $1.566.98 $1,566.98 $1,566.98 $1.566.98 $1.566.98 $1.566.98 $1,566.98 $1,566.98 $1,566.98 $1,566.98 $1,566.98 $1,566.98 $1,566.98 $1.566.98 $1.566.98 $1.566.98 $1,566.98 $1,566.98 $1.566.98 $1,566.98 $1,566.98 $1.566.98 $1,566.98 2 85076.10402 239.3590587 83748.4833 3 83748.4833 235.6238378 82417.12737 81082.0257 4 82417.12737 231.8781079 5 81082.0257 228.1218395 79743.16776 6 79743.16776 224.355003 78400.54298 78400.54298 220.5775686 77054.14078 216.7895065 77054.14078 75703.9505 74349,96151 9 75703.9505 212.9907868 10 74349.96151 209.1813795 72992.16312 11 72992.16312 205.3612546 71630.54459 71630.54459 12 201.5303818 70265.0952 13 70265.0952 197.688731 68895.80415 14 68895.80415 193.8362719 67522.66065 67522.66065 15 189.972974 66145.65384 186.0988068 16 66145.65384 64764.77287 17 64764.77287 182.2137397 63380.00684 18 63380,00684 178.3177421 61991.3448 60598.77581 19 61991.3448 174.4107833 60598.77581 20 170.4928323 59202.28886 21 59202.28886 166.5638583 57801.87294 56397.517 22 5780187294 162.6238303 23 56397.517 158.6727171 54989.20994 54989.20994 53576.94065 52160.69798 154.7104875 24 53576.94065 25 150.7371104 52160.69798 26 146.7525542 $1,566.98 $1,566.98 $1,566.98 $1,566.98 $1,566.98 $1,566.98 $1,566.98 $1,566.98 $1.566.98 $1,566.98 $1,566.98 50740.47076 27 50740.47076 142.7567877 49316.24777 138,7497792 28 49316.24777 47888.01777 29 47888.01777 134.731497 46455.76949 45019,49162 30 46455.76949 130.7019096 31 45019.49162 126.660985 43579.17283 32 43579.17283 122.6086914 42134.80174 33 42134,80174 118.5449969 40686.36696 34 40686.36696 114.4698692 39233.85706 35 39233.85706 110.3832763 37777.26056 37777.26056 36316 56596 36 106.2851858 36316.56596 37 102 1755656 $1566 98. 3485176175
K L M N Repayment Outstanding Loan (K+L-M MonthLOriginal Loan Interest[K*0.281%) 31 45019.49162 126.660985 $1,566.98 $1,566.98 $1,566.98 $1,566.98 $1.566.98 $1,566.98 $1,566.98 $1,566.98 $1,566.98 $1,566.98 43579.17283 32 43579.17283 122.6086914 42134.80174 40686.36696 39233.85706 33 42134.80174 118.5449969 40686.36696 39233.85706 34 114.4698692 35 110.3832763 37777.26056 106.2851858 36 37777.26056 36316.56596 37 36316.56596 102.1755656 34851.76175 98.05438299 38 34851.76175 33382.83636 39 33382.83636 93.9216056 31909.77819 40 31909.77819 89.77720076 30432.57561 85.62113576 $1,566.98 $1,566.98 $1.566.98 28951,21697 41 30432.57561 42 28951.21697 81.45337779 27465.69057 27465,69057 43 77.27389397 25975.98469 73.08265129 44 25975.98469 $1,566.98 $1.566.98 $1,566.98 $1,566.98 $1,566.98 $1,566.98 $1,566.98 $1,566.98 24482.08756 22983.9874 45 24482.08756 68.87961668 46 22983.9874 64.66475696 21481.67238 19975.13064 47 21481.67238 60.43803885 48 19975.13064 56.199429 18464.35029 49 18464.35029 51.94889395 16949.31941 50 16949.31941 47.68640015 15430,02603 51 15430.02603 43.41191395 13906.45817 12378.60379 52 13906.45817 39.12540161 $1,566.98 $1.566.98 $1,566.98 $1,566.98 $1,566.98 $1,566.98 $1,566.98 $1,566.98 $1,566.98 53 12378.60379 34.8268293 10846.45085 54 10846.45085 30.51616309 9309.987233 26.19336894 7769.200825 55 9309.987233 56 7769.200825 21.85841274 6224.07946 57 6224.07946 17.51126028 4674.610943 13.15187723 58 4674.610943 3120.783043 8.780229188 59 3120.783043 1562.583496 60 1562.583496 4.396281646 4.84306E-11
К L M N Month Original Loan Interest(K*0.281%) Outstanding Loan (K+L-M) Repayment 0 86400 0 86400 $1,566.98 $1,566.98 $1,566.98 $1,566.98 $1,566.98 $1,566.98 $1,566.98 $1,566.98 $1,566.98 $1,566.98 $1,566.98 $1,566.98 $1,566.98 $1,566.98 $1,566.98 $1,566.98 $1,566.98 $1,566.98 $1,566.98 $1,566.98 $1,566.98 $1,566.98 86400 1 243.0838002 85076.10402 83748.4833 2 85076.10402 239.3590587 3 83748.4833 235.6238378 82417.12737 4 82417.12737 231.8781079 81082.0257 5 81082.0257 228.1218395 79743.16776 79743.16776 224.355003 78400.54298 6 7 78400.54298 220.5775686 77054.14078 77054.14078 216.7895065 8 75703.9505 74349.96151 9 75703.9505 212.9907868 10 74349.96151 209.1813795 72992.16312 71630.54459 11 72992.16312 205.3612546 70265.0952 12 71630.54459 201.5303818 13 70265.0952 197.688731 68895.80415 14 8895.80415 193.8362719 67522.66065 67522.66065 15 189.972974 66145.65384 66145.65384 16 186.0988068 64764.77287 64764.77287 17 182.2137397 63380.00684 63380.00684 61991.3448 18 178.3177421 61991.3448 19 174.4107833 60598.77581 20 60598.77581 170.4928323 59202.28886 59202.28886 21 166.5638583 57801.87294 162.6238303 22 57801.87294 56397.517 $1.566.98 23 56397.517 158.6727171 54989.20994
J K L M N Interest(K 0.281% Month Original Loan Repayment $1,566.98 Outstanding Loan (K+L-M) 19 61991.3448 174.4107833 60598.77581 $1,566.98 20 60598.77581 170.4928323 59202.28886 166.5638583 $1,566.98 21 59202.28886 57801.87294 $1,566.98 22 57801.87294 162.6238303 56397.517 56397.517 158.6727171 $1,566.98 23 54989.20994 $1,566.98 24 54989.20994 154.7104875 53576.94065 53576.94065 $1,566.98 52160.69798 25 150.7371104 146.7525542 $1,566.98 50740.47076 26 52160.69798 142.7567877 $1,566.98 49316.24777 27 50740.47076 $1,566.98 28 49316.24777 138.7497792 47888.01777 $1,566.98 29 47888.01777 134.731497 46455.76949 $1,566.98 30 46455.76949 130.7019096 45019.49162 45019.49162 126.660985 $1,566.98 43579.17283 31 43579.17283 122.6086914 $1,566.98 32 42134.80174 $1,566.98 118.5449969 33 42134.80174 40686.36696 $1,566.98 34 40686.36696 114.4698692 39233.85706 39233.85706 $1,566.98 106.2851858 $11,566.98 35 110.3832763 37777.26056 37777.26056 36 26316.56596 $1,566.98 37 26316.56596 74.04086647 24823.62705 $1,566.98 38 24823.62705 69.84052777 23326.4878 $1,566.98 39 23326.4878 65.62837154 21825.1364 61.40436453 $1,566.98 20319.56099 40 21825.1364 20319.56099 $2,037.07 41 57.16847341 18339.65575 $2,037.07 42 18339.65575 51.5980696 16354.18011 $2,037.07 43 16354.18011 46.01199363 14363.11839 $2,037.07 $2,037.07 44 14363.11839 40.41020141 12366.45488 12366.45488 45 34.7926487 10364.17382 29.15929119 $2,037.07 8356.259402 46 10364.17382 $2,037.07 47 8356.259402 23.51008439 6342.695776 $2,037.07 48 6342.695776 17.84498371 4323.46705 $2,037.07 49 4323.46705 12.16394445 2298.557284 6.466921754 $2,037.07 $2,037.07 50 2298.557284 267.9504953 51 267.9504953 0.753870656 -1768.369344