Question

Question 1 -Interest & loan Sue will need $120,000 to refurbish her house at the corner of a main road into a cake shop in 5 years. She has a saving account which carn 3.47 % p.a. compounding quarterly and she is able to deposit $800 into that account at the end of each month for 5 years. a) Will Sue have enough money after 5 years? If not, how much is in short? Show all calculations. (4 marks) b) Even if Sue may not have enough money, she will consider taking a 9-year loan for the required amount because she is keen to have her own business. She talked to the lenders from her local Bank and Credit Union regarding loan options (i) repayments. Calculate the minimum biannually repayment for both principal and interes: that Sue would have to make on this loan. If you wish, use EXCEL to calculate the biannually repayment. (4 marks) The local bank charges 4.35% per year compounding biannually and requires biannually EXCEL Instructions: Refer to Topic 4 in the EXCEL bookilet for instructions on how to use financial functions to make annuity calculations. You need to show formulas used, do not simply type in values Therefore, you need to show the formulas in your spreadsheet in this form B4=B3*$C$1 Use EXCEL to set up an Amortisation Schedule for the loan. Include your completed EXCEL (ii) amortisation schedule. The schedule should include the amount of principal and the amount of interest that comprise each payment until the loan is paid off at the end of its term. (7 marks) EXCEL Instructions: Refer to your amortisation notes from Week 3 and the corresponding EXCEL spreadsheet available on the course website, which you can modify to help you create the amortisation schedule for this question. Be sure to add rour initials to all column names An example for the EXCEL file configurations is provided below. Failure to meet this requirement will result in a penalty (marks deduction). List all variables provided Amount borowed 2 Interest due per month 3 Loan term (months) $5,000.00 $15.00 12 Monthly repayment can be find using EXCEL function: PMT 4 Tetal interest charges S Monthly repayment S900.00 $49167 6 APR 1000% 7 MK is the initial, use your own not mine Month MK Payment MK 1 S PV MK 8(15856/12)-A9) 491.67 487.60 483.57 810-5856/12)-A30) 10 2 491.67 11 3 S 491.67 12 4 S 491.67 Show EXCEL formula used Sass 13 491.67 for the calculations. This is an 491.67 14 EXCEL versicn of the formula SBSS 7 S 491.67 15 P=Mx(1+i 16 491.67 17 491.67 491.67 491.67 10 S 11 S 18 19 20 12 491.67 Total 5,592.47 21 SUMICc0) 22 Figure 1 Determination of APR Hint: When you prepare the amortisation schedule, do not round the calculation results to the nearest cent. Otherwise the Outstanding Balance may not balance to 0 at the end of the term. You should use the format cells' in EXCEL to change the appearance of a number without changing the number itself. 2

Answer 1

	a)	Quarterly interest rate =(3.47/4)	0.8675%	0.008675
		Monthly effective interest =r
		(1+r)^3=1+0.008675=	1.008675
		Monthly effective interest =r=	0.0028833	(1.008675^(1/3))-1
	Rate	Monthly effective interest =r=	0.2883345%
	Nper	Number of months of savings	60	(5*12)
	Pmt	Monthly savings	$800
	FV	Accumulated Savings at end of 5 years	$52,320.07	(Using FV function of excel with Rate =0.2883345%, Nper=60, Pmt=-800)
		Sue will not have the required amount
		The shortage will be =120000-52320.07=	$67,679.93
	b)
		i. Required bi annual payment
	Pv	Amount of Loan required	$67,679.93
	Rate	Bi annual interest rate =(4.35/2)	2.1750%
	Nper	Number of bi annual payments=9*2	18
	PMT	Bi annual Repayment	$4,584.15	(Using PMT function of excel with Rate =2.175%, Nper=18, Pv=-67679.93)
		ii. Amortisation Schedule
			A	B	C=A*2.175%	D=B-C	E=A-D
		Bi annual Period	Beginning Loan Balance	Amount of Repayment	Interest	Principal	Ending Loan Balance
		1	$67,679.93	$4,584.15	$1,472.04	$3,112.11	$64,567.81
		2	$64,567.81	$4,584.15	$1,404.35	$3,179.80	$61,388.01
		3	$61,388.01	$4,584.15	$1,335.19	$3,248.96	$58,139.05
		4	$58,139.05	$4,584.15	$1,264.52	$3,319.63	$54,819.43
		5	$54,819.43	$4,584.15	$1,192.32	$3,391.83	$51,427.60
		6	$51,427.60	$4,584.15	$1,118.55	$3,465.60	$47,962.00
		7	$47,962.00	$4,584.15	$1,043.17	$3,540.98	$44,421.02
		8	$44,421.02	$4,584.15	$966.16	$3,617.99	$40,803.03
		9	$40,803.03	$4,584.15	$887.47	$3,696.68	$37,106.34
		10	$37,106.34	$4,584.15	$807.06	$3,777.09	$33,329.26
		11	$33,329.26	$4,584.15	$724.91	$3,859.24	$29,470.02
		12	$29,470.02	$4,584.15	$640.97	$3,943.18	$25,526.84
		13	$25,526.84	$4,584.15	$555.21	$4,028.94	$21,497.90
		14	$21,497.90	$4,584.15	$467.58	$4,116.57	$17,381.33
		15	$17,381.33	$4,584.15	$378.04	$4,206.11	$13,175.22
		16	$13,175.22	$4,584.15	$286.56	$4,297.59	$8,877.63
		17	$8,877.63	$4,584.15	$193.09	$4,391.06	$4,486.57
		18	$4,486.57	$4,584.15	$97.58	$4,486.57	$0.00
			TOTAL	$82,514.71	$14,834.78
	iii)	Total amount of Repayment	$82,514.71
		Total interest payment	$14,834.78
	c)	Monthly interest rate =(3.95/12)	0.32917%
		First 2 years payment =67679.93*0.32917%	$222.78
		Total Payment in first two years	$5,346.71	(222.78*24)
		Monthly Payment during 7 years:
	Rate	Monthly interest Rate	0.32917%
	Nper	Number of months =7*12	84
	Pv	Loan balance at the end of year2	$67,679.93
	PMT	Monthly Payment during 7 years:	$923.55	(Using PMT function of excel with Rate =0.32917%, Nper=84, Pv=-67679.93)
		Total Payment in 7 years =923.55*84	$77,577.94
		Payment in first 2 years	$5,346.71
		Total Payment in 9 years =77577.94+5346.71	$82,924.66
		Total Interest Payment =82924.66-67679.93	$15,244.73
	d)	Sue should take Bi annual payment option at 4.35%
				D10 FV(D7,08,-09) | D E G H I J K 0.8675% 0.008675 Quarterly interest rate =(3.47/4) Monthly effective interest =r (1+r)^3=1+0.008675= Monthly effective interest =r= Monthly effective interest =r= Number of months of savings Monthly savings Accumulated Savings at end of 5 years Rate Nper Pmt FV 1.008675 0.0028833 (1.008675^(1/3))-1 0.2883345% 60 (5*12) $800 $52,320.07 | (Using FV function of excel with Rate =0.2883345%, Nper=60, Pmt=-800) * fic =PMT(D17,018,-D16) D19 | : в А D E F G H J K L a) 0.8675% 0.008675 Rate Nper Pmt FV Quarterly interest rate =(3.47/4) Monthly effective interest=r (1+r)^3=1+0.008675= Monthly effective interest =r= Monthly effective interest =r= Number of months of savings Monthly savings Accumulated Savings at end of 5 years Sue will not have the required amount The shortage will be = 120000-52320.07= 1.008675 0.0028833 (1.008675^(1/3))-1 0.2883345% 60 (5*12) $800 $52,320.07 (Using FV function of excel with Rate =0.2883345%, Nper=60, Pmt=-800) $67,679.93 Pv Rate i. Required bi annual payment Amount of Loan required Bi annual interest rate =(4.35/2) Number of bi annual payments=9*2 Bi annual Repayment $67,679.93 2.175% PMT $4,584.15 | (Using PMT function of excel with Rate =2.175%, Nper=18, Pv=-67679.93) D54 =PMT(D51,052,-053) I J D 12 $29,470.02 13 $25,526.84 14 $21,497.90 15 $17,381.33 16 $13,175.22 17 $8,877.63 18 $4,486.57 TOTAL $82,514.71 $14,834.78 E F G H $4,584.15 $640.97 $3,943.18 $25,526.84 $4,584.15 $555.21 $4,028.94 $21,497.90 $4,584.15 $467.58 $4,116.57 $17,381.33 $4,584.15 $378.04 $4,206.11 $13,175.22 $4,584.15 $286.56 $4,297.59 $8,877.63 $4,584.15 $193.09 $4,391.06 $4,486.57 $4,584.15 $97.58 $4,486.57 $0.00 $82,514.71 $14,834.78 43 ii) Total amount of Repayment Total interest payment 45 46 C) Monthly interest rate =(3.95/12) First 2 years payment =67679.93*0.32917% Total Payment in first two years 0.32917% $222.78 $5,346.71 (222.78*24) 0.32917% Monthly Payment during 7 years: Monthly interest Rate Number of months =7*12 Loan balance at the end of year2 Monthly Payment during 7 years: 51 Rate 52 Nper 53 Pv 54 PMT 84 $67,679.93 (Using PMT function of excel with Rate =0.32917%, Nper=84, Pv=-67679.93)