Question

Problem 1 (Required, 25 marks) A borrower has borrowed $2000000 from the bank. It is given that the loan charges interest at an annual effective interest rate 16.0755% and compound interest is assumed. (a) Suppose that the borrower decides to repay the loan by 180 monthly payments made at the end of every month, (i) Using retrospective method, calculate the outstanding balance at 60th repayment date. (ii) Calculate the interest due and principal repaid in 120th repayment. (b) Suppose that the borrower decides to repay the loan by m monthly repayment of amount $30000 each plus a drop payment made one month after the man repayment, calculate the amount of drop payment and determine the time taken to repay the loan completely (*In this problem, we assume that the first repayment will be made 1 month after today.)

Answer 1

	(a)
	(i)	Retrospective method
	Pv	Amount of loan	$2,000,000
		Effective annual Interest rate	16.0755%
		Effective monthly rate =r
		(1+r)^12=(1+0.160755)=1.160755
		1+r=1.160755^(1/12)=	1.012500035
		r=Monthly interest rate=	0.0125000
	Rate	Monthly interest rate	1.25%
	Nper	Number of months of payment	180
	PMT	Monthly payment	$27,991.80	(Using PMT function of excel with rate =1.25%, Nper=180,Pv=-2000000)
	FV	Future Value of payments at the end of 60 months	$2,479,362.76	(Using FV function of excel with Rate =1.25%, Nper=60,Pmt=-27991.80)
	FV1	Future Value of Loan at the end of 60 months	$4,214,371.45	(Using FV function of excel with Rate =1.25%, Nper=60,Pv=-2000000)
	FV1-FV	Outstanding Balance at 60th repayment date	$1,735,008.69
	(ii)
	FV2	Future Value of payments at the end of 119 payments	$7,581,084.93	(Using FV function of excel with Rate =1.25%, Nper=119,Pmt=-27991.80)
	FV3	Future Value of Loan at the end of 119 payments	$8,770,827.70	(Using FV function of excel with Rate =1.25%, Nper=119,Pv=-2000000)
	FV3-FV2	Outstanding Balance at the end of 119 th repayment	$1,189,742.77
		Interest for 120th payment =1189742.77*1.25%=	$14,871.83
		Principal paid in 120th payment =27991.80-14871.83=	$13,119.97
	(b)	TIME TAKEN TO REPAY THE LOAN COMPLETELY
	Pmt	Monthly repayment	$30,000
	Rate	Interest rate	1.25%
	Pv	Loan amount	$2,000,000
	NPER	Time taken to repay the loan completely(Months)	144.2355	(Using NPER function of excel with Rate =1.25%, Pmt=30000,Pv=-2000000)
		Time taken to repay the loan completely	144.2355	months
		Amount of Drop Payment=Loan Balance at the end of m months *1.0125
		Loan Balance at the end of m months =Present Value of future payments at the rate of $30000 per month for (144.2355-m) months
		Interest rate =1.25%=0.0125
		Present Worth Factor (PWF)=(P/A,I,N)=(((1+i)^N)-1)/(i*((1+i)^N))
		i=1.25%, N=144.2355-m
		PWF=(P/A,1.25%,(144.2355-m))
		((1.0125^(144.2355-m))-1)/(0.0125*(1.0125^(144.2355-m)))
		Amount of Drop Payment =30000*PWF*1.0125=30000*1.0125*((1.0125^(144.2355-m))-1)/(0.0125*(1.0125^(144.2355-m)))
		For examply, if m=120
		Amount of Drop Payment =30000*PWF*1.0125=30000*1.0125*((1.0125^(144.2355-120))-1)/(0.0125*(1.0125^(144.2355-120)))							$631,729.67
										113 =PMT(H11, H12,-H5) 2 A B C D E F G H I J K L M N OL $2,000,000 16.0755% Retrospective method Amount of loan Effective annual Interest rate Effective monthly rate =r (1+r)^12=(1+0.160755)=1.160755 1+r=1.160755^(1/12) = r=Monthly interest rate= Monthly interest rate Number of months of payment Monthly payment Rate Nper PMT 1.01250004 0.0125000 1.25% 180 $27,991.80 (Using PMT function of excel with rate =1.25%, Nper=180, Pv=-2000000) 15 X f =FV(H11,60,-H13) B C D E I J K L M N O PV $2,000,000 16.0755% Retrospective method Amount of loan Effective annual Interest rate Effective monthly rate =r (1+r)^12=(1+0.160755)=1.160755 1+r=1.160755^(1/12)= r=Monthly interest rate= Monthly interest rate Number of months of payment Monthly payment Rate Nper PMT 1.012500035 0.0125000 1.25% 180 $27,991.80 (Using PMT function of excel with rate =1.25%, Nper=180,Pv=-2000000) FV Future Value of payments at the end of 60 months $2,479,362.76 (Using FV function of excel with Rate =1.25%, Nper=60, Pmt=-27991.80) H16 : for =FV(H11,60,-H5) A B C D E H I J K L M N O (i) Pv $2,000,000 16.0755% Retrospective method Amount of loan Effective annual Interest rate Effective monthly rate =r (1+r)^12=(1+0.160755)=1.160755 1+r=1.160755^(1/12)= r=Monthly interest rate= Monthly interest rate Number of months of payment Monthly payment Rate Nper PMT 1.012500035 0.0125000 1.25% 180 $27,991.80 (Using PMT function of excel with rate = 1.25%, Nper=180, Pv=-2000000) FV FV1 Future Value of payments at the end of 60 months $2,479,362.76 Future Value of Loan at the end of 60 months $4,214,371.45 (Using FV function of excel with Rate =1.25%, Nper=60, Pmt=-27991.80) (Using FV function of excel with Rate =1.25%, Nper=60,Pv=-2000000) H19 : * fic =FV(H11,119,-413) A B C D E F I J K L M N O H $2,000,000 16.0755% Amount of loan Effective annual Interest rate Effective monthly rate =r (1+r)^12=(1+0.160755)=1.160755 1+r=1.160755^(1/12)= r=Monthly interest rate= Monthly interest rate Number of months of payment Monthly payment Rate Nper PMT 1.012500035 0.0125000 1.25% 180 $27,991.80 (Using PMT function of excel with rate =1.25%, Nper=180, Pv=-2000000) FV Future Value of payments at the end of 60 months $2,479,362.76 Future Value of Loan at the end of 60 months $4,214,371.45 Outstanding Balance at 60th repayment date $1,735,008.69 (Using FV function of excel with Rate = 1.25%, Nper=60, Pmt=-27991.80) (Using FV function of excel with Rate = 1.25%, Nper=60, Pv=-2000000) FV1 FV1-FV FV2 Future Value of payments at the end of 60 months $7,581,084.93 (Using FV function of excel with Rate =1.25%, Nper=119, Pmt=-27991.80) 2 . Cupboard Foht Allahment Numberly Styles Editih H20 : * f =FV(H11,119,,-45) A B C D F E Pv I J K L M N O H $2,000,000 16.0755% Amount of loan Effective annual Interest rate Effective monthly rate =r (1+r)^12=(1+0.160755)=1.160755 1+r=1.160755^(1/12)= r=Monthly interest rate= Monthly interest rate Number of months of payment Monthly payment Rate Nper PMT 1.012500035 0.0125000 1.25% 180 $27,991.80 (Using PMT function of excel with rate =1.25%, Nper=180,Pv=-2000000) FV Future Value of payments at the end of 60 months $2,479,362.76 Future Value of Loan at the end of 60 months $4,214,371.45 Outstanding Balance at 60th repayment date $1,735,008.69 (Using FV function of excel with Rate =1.25%, Nper=60, Pmt=-27991.80) (Using FV function of excel with Rate = 1.25%, Nper=60,Pv=-2000000) FV1 FV1-FV FV2 FV3 Future Value of payments at the end of 60 months $7,581,084.93 (Using FV function of excel with Rate =1.25%, Nper=119, Pmt=-27991.80) Future Value of Loan at the end of 60 months $8,770,827.70 (Using FV function of excel with Rate =1.25%, Nper=119,Pv=-2000000) H29 X fx =NPER(H27,H26,-H28) в с D E H $4,214,371.45 $1,735,008.69 I J K L M N O (Using FV function of excel with Rate = 1.25%, Nper=60,Pv=-2000000) F FV1 Future Value of Loan at the end of 60 months FV1-FV Outstanding Balance at 60th repayment date (ii) FV2 Future Value of payments at the end of 119 payments FV3 Future Value of Loan at the end of 119 payments FV3-FV2 Outstanding Balance at the end of 119 th repayment Interest for 120th payment =1189742.77*1.25%= Principal paid in 120th payment =27991.80-14871.83= (Using FV function of excel with Rate =1.25%, Nper=119, Pmt=-27991.80) (Using FV function of excel with Rate = 1.25%, Nper=119,Pv=-2000000) Nooo-minoo $7,581,084.93 $8,770,827.70 $1,189,742.77 $14,871.83 $13,119.97 (b) Pmt Rate Pv NPER TIME TAKEN TO REPAY THE LOAN COMPLETELY Monthly repayment Interest rate Loan amount Time taken to repay the loan completely(Months) $30,000 1.25% $2,000,000 144.2355) (Using NPER function of excel with Rate =1.25%, Pmt=30000, Pv=-2000000)