Question

Sue will need $120,000 to refurbish her house at the comer of a main road into a cake shop in 5 years. She has a saving account which earn 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 interest that Sue would have to make on this loan. If you wish, use EXXCEL 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 booklet 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*SCS1 (i) 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) Use EXCEL to set up an Amortisation Schedule for the loan. Include your completed EXCEL

Answer 1

a)
FV = R * ((1+r/n)^nt-1)/(r/n)
where, FV = Maturity Value
P = Monthly amount deposited
r = Rate of interest
n = No. of times the interest is compounded in a year
t = Tenure
Values given
P = $ 800
r = 3.47%
n = 4
t = 5 years
putting the values in the formula provided above
FV = ($ 800 *(1+(.0347/4))^(4*5))-1)/(.0347/4)
FV = ($ 800 *(1+0.008675)^(20))-1)/(0.008675)
FV = $ 800 *((1.008675)^(20)-1)/(0.008675)
FV = $ 800 *((1.008675)^(20)-1)/(.008675)
FV = $( 800 *(1.188571)-1)/(0.008675)
FV = $( 950.8568-1)/(0.008675)
FV = $( 949.8568)/(0.008675)
FV = $ 109493.58
FV = $ 109,494
Sue will not have enough money at the end of 5 years. She will be short by
$ 120,000 - $ 109.494
$10,506
b(i)
We will use the formula for Future Value to calculate the principle portion of the
first installment that Sue will pay to the Bank
FV = R * ((1+r/n)^nt-1)/(r/n)
Values given
FV = $ 120,000
r = 4.35%
n = 2
t = 9 years
$ 120000 = (R *(1+(.0435/2))^(2*9))-1)/(.0435/2)
$ 120000 = (R *(1+(.02175))^(18))-1)/(.02175)
$ 120000 = (R *(1.02175))^(18))-1)/(.02175)
$ 120000 = (R *(1.473003))-1)/(.02175)
$ 120000 = (R *0.473003)/(.02175)
R = $120000*02175/.473003
R = $ 5517.94
Interest paid in the first repayment on $ 120000
=120000*4.35%/2
= $ 2,610
Minumum biannual repayment = $ 5517.94+$ 2610
= $ 8,127.94
(ii)
Amortization Schedule
Period	Beginning Balance	Total Payment	Interest	Principle Total Payment - Interest	Ending Balance (Beginning Balance - Interest)
1	$120,000.00	$8,127.94	$2,610.00	$5,517.94	$114,482.06
2	$114,482.06	$8,127.94	$2,489.98	$5,637.96	$108,844.10
3	$108,844.10	$8,127.94	$2,367.36	$5,760.58	$103,083.52
4	$103,083.52	$8,127.94	$2,242.07	$5,885.87	$97,197.65
5	$97,197.65	$8,127.94	$2,114.05	$6,013.89	$91,183.76
6	$91,183.76	$8,127.94	$1,983.25	$6,144.69	$85,039.07
7	$85,039.07	$8,127.94	$1,849.60	$6,278.34	$78,760.73
8	$78,760.73	$8,127.94	$1,713.05	$6,414.89	$72,345.84
9	$72,345.84	$8,127.94	$1,573.52	$6,554.42	$65,791.42
10	$65,791.42	$8,127.94	$1,430.96	$6,696.98	$59,094.44
11	$59,094.44	$8,127.94	$1,285.30	$6,842.64	$52,251.80
12	$52,251.80	$8,127.94	$1,136.48	$6,991.46	$45,260.34
13	$45,260.34	$8,127.94	$984.41	$7,143.53	$38,116.81
14	$38,116.81	$8,127.94	$829.04	$7,298.90	$30,817.91
15	$30,817.91	$8,127.94	$670.29	$7,457.65	$23,360.26
16	$23,360.26	$8,127.94	$508.09	$7,619.85	$15,740.41
17	$15,740.41	$8,127.94	$342.35	$7,785.59	$7,954.82
18	$7,954.82	$8,127.94	$173.02	$7,954.92	($0.10)
Total			$26,302.82	$120,000.10
c.(i)
We will use the formula for Future Value to calculate the principle portion of the
first installment that Sue will pay to the Bank
FV = R * ((1+r/n)^nt-1)/(r/n)
Values given
FV = $ 120,000
r =3.95%
n = 12
t = 7 years( since repayment is made in 7 years out of total 9 years)
$ 120000 = (R *(1+(.0395/12))^(12*7))-1)/(.0395/2)
$ 120000 = (R *(1+(.003292))^(84))-1)/(.003292)
$ 120000 = (R *(1.003292))^(84))-1)/(.003292)
$ 120000 = (R *(1.317908))-1)/(.003292)
$ 120000 = (R *0.317908)/(.003292)
R = $120000*0.003292/0.317908
R = $ 1242.50
Interest for the first repayment on $ 120000
=120000*3.95%/12
= $ 395
Minumum monthly repayment = $ 1242.50+$ 395
= $ 1,637.50
(ii)
Total Interest paid by Sue
= Interest paid in first 2 years + Interest paid during repayments
Interest paid for the first two years = $ 120,000 * 3.95%*2
= $ 9,480
Interest paid during the next seven years
Total amount paid = $ 1,637.50 *7*12
= $ 137,550
Interest = Total amount paid - Loan amount
= $ 137550 - $ 120000
= $ 17,550
Total Interest = $ 9,480+$ 17,550
= $ 27,030
d.
Total interest paid on Bank Loan				$26,302.82
Total interest paid on Credit Union Loan				$27,030.00
Since, the interest paid on bank loan is lower by $ 727.18 if bank loan is
taken,Sue should choose the option of taking the Bank loan