Question

Prepare (using a spreadsheet package) an entire duration amortization schedule as follows: Initial amount: Shs 4,000,000/=; rate of interest: 13.5% per annum; period: 10 years payable monthly in arrears. An additional amount of Shs 400,000/= is repaid with the 30th installment, and the monthly installment recomputed to fully pay the loan balance in the initially agreed period. The rate of interest is adjusted to 15.5% per annum with effect from the end of the fourth year. The monthly repayment is recomputed to fully pay the loan balance in the initially agreed period. The borrower enhances his monthly repayments by Shs 12,000/= per month from the end of month 74. The borrower is advanced an additional Shs 400,000/= at the end of month 80 and the monthly repayment is recomputed to fully pay the loan balance in 9 months less than had initially been agreed

Answer 1

	Pv	Initial amount	4,000,000
	Rate	Monthly interest =13.5/12=	1.125%
	Nper	Number of months =10*12	120
	PMT	Monthly payment	60,909.72	(Using PMT function of excel with Rate =1.125%, Nper=120,Pv=-4000000)
		As per Mortgage Schedule for Month 1-30,
	PV1	Loan Balance at end of 30 months	3,036,016.37
	Rate	Monthly interest =13.5/12=	1.125%
	Nper1	Number of months left(120-30)	90
	PMT1	Monthly payment from 31st month to 120th month	53,818.98	(Using PMT function of excel with Rate =1.125%, Nper=90,Pv=-3036016.37)
		As per Mortgage Schedule for Month 31-48,
	Pv2	Loan Balance at end of 48 months	2,646,102.53
	Rate2	Monthly interest rate =15.5/12=	1.292%			1.2916667%
	Nper2	Number of months left(120-48)	72
	PMT2	Monthly payment from 48 month to 120th month	56,672.87	(Using PMT function of excel with Rate =1.292%, Nper=72,Pv=-2646102.53)
	Pv3	Loan Balance at end of 80 months	1,274,363.86
	Rate	Monthly interest rate =15.5/12=	1.292%
	Nper	Number of months left(120-80-9))	31
	PMT3	Monthly payment from 48 month to 111th month	50,148.01	(Using PMT function of excel with Rate =1.292%, Nper=31,Pv=-1274363.86)
										Clipboard Font Alignment Number Styles * v fac =PMT(F4,F5,-F3) G H I J K L M N Pv Rate Initial amount Monthly interest =13.5/12= Number of months =10*12 Monthly payment 4,000,000 1.125% 120 $60,909.72 (Using PMT function of excel with Rate = 1.125%, Nper=120,Pv=-4000000) Nper PMT MORTGAGE SCHEDULE MONTH1-30 Month Beginning Monthly Balance Payment 1 4,000,000 60,909.72 2 3,984,090.28 60,909.72 3 3,968,001.58 60,909.72 4 3,951,731.89 60,909.72 5 3,935,279.15 60,909.72 6 3,918,641.33 60,909.72 7 3,901,816.33 60,909.72 8 3,884,802.05 60,909.72 9 3,867,596.35 60,909.72 10 3,850,197.10 60,909.72 11 3,832,602.10 60,909.72 12 3,814,809.16 60.909.72 13 3,796,816.04 60,909.72 14 3,778,620.51 60,909.72 15 3,760,220.27 60,909.72 16 3,741,613.04 60,909.72 17 3,722,796.47 60,909.72 18 3,703,768.21 60,909.72 19 3,684,525.89 60,909.72 20 3,665,067.09 60,909.72 21 3,645,389.38 60,909.72 22 3,625,490.29 60,909.72 23 3,605,367.34 60,909.72 24 3,585,018.01 60,909.72 25 3,564,439.75 60,909.72 26 3,543,629.98 60,909.72 27 3,522,586.10 60,909.72 28 3,501,305.48 60,909.72 29 3,479,785.45 60,909.72 30 3,458,023.32 60,909.72 C-A 1.125%. D=B-C E=A-D Ending Interest Principal Balance 45,000.00 15,909.72 3,984,090.28 44,821.02 16,088.703,968,001.58 44,640.02 16,269.70 3,951,731.89 44,456.98 16,452.73 3,935,279.15 44,271.89 16,637.83 3,918,641.33 44,084.71 16,825.00 3,901,816.33 43,895.43 17,014.28 3,884,802.05 43,704.02 17,205,69 3,867,596.35 43,510.46 17,399.26 3,850,197.10 43,314.72 17,595.00 3,832,602.10 43,116.77 17,792.94 3,814,809.16 42,916.60 17,993.11 3,796,816.04 42,714.18 18,195.54 3,778,620.51 42,509.48 18,400.23 3.760,220.27 42,302.48 18,607.24 3,741,613.04 42,093.15 18,816.57 3,722,796.47 41,881.46 19,028.26 3,703,768.21 41,667.39 19,242.32 3.684,525.89 41,450.92 19,458.80 3,665,067.09 41,232.00 19,677.71 3,645,389.38 41,010.63 19,899.09 3,625,490.29 40,786.77 20,122.95 3,605,367.34 40,560.38 20,349.33 3,585,018.01 40,331.45 20,578.26 3,564,439.75 40,099.95 20,809.77 3,543,629.98 39,865.84 21,043.88 3,522,586.10 39,629.09 21,280.62 3,501,305.48 39,389.69 21,520.03 3,479,785.45 39,147.59 21,762.13 3,458,023.32 38,902.76 22,006.95 3,436,016.37 400,000.00 3,036,016.37 MORTGAGE SCHEDULE FROM MONTH 31-48 C=A*1.125% D=B-C E=A-D Month from Beginning Month 31 33 А Beginning Monthly Balance Payment 3,036,016.37 53,818.98 2 3,016,352.57 53,818.98 3 2,996,467.56 53,818.98 4 2,976,358.84 53,818.98 5 2,956,023.90 53,818.98 6 2,935,460.19 53,818.98 7 2,914,665.14 53,818.98 8 2,893,636.14 53,818.98 9 2,872,370.57 53,818.98 10 2,850,865.76 53,818.98 11 2,829,119.02 53,818.98 12 2,807,127.63 53,818.98 132,784,888.83 53,818.98 14 2,762,399.85 53,818.98 15 2,739,657.87 53,818.98 16 2,716,660.05 53,818.98 17 2,693,403.49 692 402 49 53,818.98 53.818.98 18 2,669,885.30 53,818.98 Interest 34,155.18 33,933.97 33,710.26 33,484.04 33,255.27 33,023.93 32,789.98 32,553.41 32,314.17 32,072.24 31,827.59 31,580.19 31,330.00 31,077.00 30,821.15 30,562.43 30.300.79 30,036.21 Principal 1 9,663.80 19,885.01 20,108.72 20,334.94 20,563.71 20,795.05 21,029.00 21,265.57 21,504.81 21,746.74 21,991.39 22,238.79 22,488.98 22,741.98 22,997.83 23,256.55 23,518.19 23,782.77 Ending Balance 3,016,352.57 2,996,467.56 2,976,358.84 2,956,023.90 2,935,460.19 2,914,665.14 2,893,636.14 2,872,370.57 2,850,865.76 2,829,119.02 2,807,127.63 2,784,888.83 2,762,399.85 2,739,657.87 2,716,660.05 2,693,403.49 2.669.885.30 2,646,102.53 MORTGAGE SCHEDULE FROM MONTH 49-73 C=A*1.292% D=B-C E=A-D Month from Beginning Month 49 Beginning Balance 1 2,646,102.53 2 2,623,608.49 3 2,600,823.89 4 2,577,745.00 5 2,554,368.00 6 2,530,689.05 7 2,506,704.25 8 2,482,409.64 9 2,457,801.23 10 2,432,874.96 11 2,407,626.73 12 2,382,052.37 13 2,356,147.68 14 2,329,908.38 15 2,303,330.16 16 2,276,408.64 17 2,249,139.39 18 2,221,517.90 192,193,539.64 20 2,165,199.99 21 2,136,494.28 22 2,107,417.80 23 2,077,965.74 24 2,048,133.27 25 2,017,915.45 Monthly Payment 56,672.87 56,672.87 56,672.87 56,672.87 56,672.87 56,672.87 56,672.87 56,672.87 56,672.87 56,672.87 56,672.87 56,672.87 56,672.87 56,672.87 56,672.87 56,672.87 56,672.87 56,672.87 56,672.87 56,672.87 56,672.87 56,672.87 56,672.87 56,672.87 56,672.87 Interest 34,178.83 33,888.28 33,593.98 33,295.87 32,993.92 32,688.07 32,378.26 32,064.46 31,746.60 31,424.64 31,098.51 30,768.18 30,433.57 30,094.65 29,751.35 29,403.61 29,051.38 28,694.61 28,333.22 27,967.17 27,596.39 27,220.81 26,840.39 26,455.06 26,064.74 Principal 22,494.04 22,784.59 23,078.89 23,377.00 23,678.95 23,984.80 24,294.61 24,608.41 24,926.27 25,248.23 25,574.36 25,904.69 26,239.30 26,578.22 26,921.52 27,269.26 27,621.49 27,978.26 28,339.65 28,705.70 29,076.48 29,452.06 29,832.48 30,217.81 30,608.13 Ending Balance 2,623,608.49 2,600,823.89 2,577,745.00 2,554,368.00 2,530,689.05 2,506,704.25 2,482,409.64 2,457,801.23 2,432,874.96 2,407,626.73 2,382,052.37 2,356,147.68 2,329,908.38 2,303,330.16 2,276,408.64 2,249,139.39 2,221,517.90 2,193,539.64 2,165,199.99 2,136,494.28 2,107,417.80 2,077,965.74 2,048,133.27 2,017,915.45 1,987,307.32 25 2,017,915.45 56,672.87 2 6,064.74 3 0,608.13 1,987,307.32 MORTGAGE SCHEDULE FROM MONTH 74-80 26 27 28 29 30 31 32 1,987,307.32 1,944,303.84 1,900,744.89 1,856,623.31 1,811,931.83 1,766,663.08 1,720,809.61 68,672.87 68,672.87 68,672.87 68,672.87 68,672.87 68,672.87 68,672.87 25,669.39 25,113.93 24,551.29 23,981.39 23,404.12 22,819.40 22,227.12 43,003.48 43,558.94 44,121.58 44,691.48 45,268.75 45,853.47 46,445.75 400,000.00 1.944 303 84 1,900,744.89 1,856,623.31 1,811,931.83 1,766,663.08 1,720,809.61 1,674,363.86 1,274,363.86 MORTGAGE SCHEDULE Month 81-111 C=A'1.2922 D=B-C E-A-D Month from Beginning Month Beginning Monthly Balance Payment 1 1,274,363.86 50,148.01 2 1,240,676.39 50,148.01 3 1,206,553.78 50,148.01 4 1,171,990.43 50,148.01 5 1,136,980.63 50,148.01 6 1,101,518.62 50,148.01 7 1,065,598.57 50,148.01 8 1,029,214.54 50,148.01 9 992,360.55 50,148.01 10 955,030.54 50,148.01 11 917,218.34 50,148.01 12 878,917.74 50,148.01 13 840,122.42 50,148.01 14 800,825.99 50,148.01 15 761,021.99 50,148.01 16 720,703.85 50,148.01 17 679,864.93 50,148.01 638,498.51 50,148.01 19 596,597.78 50.148.01 20 554,155.83 50,148.01 21 511,165.66 50,148.01 22 467,620.21 50,148.01 23 423,512.30 50,148.01 378,834.66 50,148.01 25 333,579.93 50,148.01 287,740.67 50,148.01 241,309.31 50,148.01 194,278.22 50,148.01 146,639.64 50,148.01 98,385.72 50.148.01 31 49,508.53 50,148.01 Interest 16,460.53 16,025.40 15,584.65 15,138.21 14,686.00 14,227.95 13,763.98 13,294.02 12,817.99 12,335.81 11,847.40 11,352.69 10,851.58 10,344.00 9,829.87 9,309.09 8,781.59 8,247.27 7.706.05 7.157.85 6,602.56 6,040.09 5,470.37 4,893.28 4,308.74 3,716.65 3,116.91 2,509.43 1,894.10 1,270.82 639.49 Principal Ending Balance 33,687.471,240,676.39 34,122.60 1,206,553.78 34,563.35 1,171,990.43 35,009.80 1.136,980.63 35,462.01 1,101,518.62 35,920.06 1,065,598.57 36,384.03 1,029,214.54 36,853.99 992,360.55 37,330.02 955,030.54 37,812.20 917,218.34 38,300.60 878,917.74 38,795.32 840,122.42 39,296.43 800,825.99 39,804.00 761,021.99 40,318.14 720,703.85 40,838.92 679,864.93 41,366.42 638,498.51 41,900.73 596,597.78 42,441.95 554, 155.83 42,990.16 511,165.66 43,545.45 467,620.21 44.107.91 423,512.30 44,677.64 378,834.66 45,254.73 333,579.93 45,839.27 287,740.67 46,431.36 241,309.31 47,031.10 194,278.22 47,638.58 146,639.64 48,253.91 98,385.72 48,877.19 49,508.53 49,508.52 0.01 26 27 108 109 29 30