Question

Question 1 - Interest & loan 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 cam 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. (1) The local bank charges 4.35% per year compounding biannually and requires biannually repayments. Calculate the minimum biannually repayment for both principal and interest that Sue would have to make on this loan. If you wish, use EXCEL to calculate the biannually repayment. (4 marks) 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 SCSI. (ii) Use EXCEL to set up an Amortisation Schedule for the loan. Include your completed EXCEL amortisation schedule. The schedule should include the amount of principal and the amount of interest that comprise cach 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 your 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 1 Amet borrowed $5.000.00 2 Interest due per month $15.00 Loan term (months Monthly repayment can be finding 4 Total interest charges EXCEL function: PMT 5 Monthly repayment SA91.67 10.30 MK is the initial use your own not mine Month MK Payment MK PVMK 1$ 491675 1750 11 /2149 2 $ 491.67 $ 157 8101 6 /124-A10) 3 $ 491.67 Show EXCEL for used for the calculations. This is a &$ 491.67 EXCEL is of the formula 7 $ 49167 P=Mx11 .$ 49167 $900.00 10 $ 11 $ 1916 49167 Total $ 5.512.07 SUMCN.C20 Fo t o Ang 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.

Answer 1

a).

Annuity occurring at the end of the period

(1+r)n-1 Future Value A« r

Where

A = Annuity Amount

r = Rate of Interest

n = Annuity Period

Future Value = (800*3) * [((1+ (0.0347/4)) ^(5*4)-1)/ (0.0347/4)]

Future Value = 2400* [(1.008675)²⁰-1/ 0.008675]

Future Value = 52,560

Sue will have $52,560 after 5 years. That is Sue will be in short of $ 67,440

b).

i) In case of taking loan from local bank

where

rl+r) 2 A P (1+r)"- 2

• A = payment Amount per period
• P = initial Principal (loan amount)
• r = interest rate per period
• n = total number of payments or periods

A=67440*[(0.0435/2)(1+0.0435/2)¹⁸ / (1+0.0435/2)¹⁸-1]

A=4567.92

So, Biannual Repayment Installment will be $4,567.92 and Principal and Ineterst Calculation will be as given in below table

Repayment No.	Opening Outstanding	Interest for Period	Installment	Principal Repayment	Closing Outstanding
Formula —>		C2=B2*$I$5		E2= D2-C2	F2= B2-E2
1	$ 67,440.00	$      1,466.82	$     4,567.92	$   3,101.10	$     64,338.90
2	$ 64,338.90	$      1,399.37	$     4,567.92	$   3,168.55	$     61,170.35
3	$ 61,170.35	$      1,330.46	$     4,567.92	$   3,237.46	$     57,932.89
4	$ 57,932.89	$      1,260.04	$     4,567.92	$   3,307.88	$     54,625.01
5	$ 54,625.01	$      1,188.09	$     4,567.92	$   3,379.83	$     51,245.18
6	$ 51,245.18	$      1,114.58	$     4,567.92	$   3,453.34	$     47,791.84
7	$ 47,791.84	$      1,039.47	$     4,567.92	$   3,528.45	$     44,263.40
8	$ 44,263.40	$         962.73	$     4,567.92	$   3,605.19	$     40,658.20
9	$ 40,658.20	$         884.32	$     4,567.92	$   3,683.60	$     36,974.60
10	$ 36,974.60	$         804.20	$     4,567.92	$   3,763.72	$     33,210.88
11	$ 33,210.88	$         722.34	$     4,567.92	$   3,845.58	$     29,365.29
12	$ 29,365.29	$         638.70	$     4,567.92	$   3,929.22	$     25,436.07
13	$ 25,436.07	$         553.23	$     4,567.92	$   4,014.69	$     21,421.38
14	$ 21,421.38	$         465.92	$     4,567.92	$   4,102.00	$     17,319.38
15	$ 17,319.38	$         376.70	$     4,567.92	$   4,191.22	$     13,128.16
16	$ 13,128.16	$         285.54	$     4,567.92	$   4,282.38	$       8,845.77
17	$    8,845.77	$         192.40	$     4,567.92	$   4,375.52	$       4,470.25
18	$    4,470.25	$            97.67	$     4,567.92	$   4,470.25	$                    -

ii) Amortization Schedule

Installment No.	Opening Outstanding	Interest for Period	Installment	Principal Repayment	Closing Outstanding
1	$ 67,440.00	$      1,466.82	$     4,567.92	$   3,101.10	$     64,338.90
2	$ 64,338.90	$      1,399.37	$     4,567.92	$   3,168.55	$     61,170.35
3	$ 61,170.35	$      1,330.46	$     4,567.92	$   3,237.46	$     57,932.89
4	$ 57,932.89	$      1,260.04	$     4,567.92	$   3,307.88	$     54,625.01
5	$ 54,625.01	$      1,188.09	$     4,567.92	$   3,379.83	$     51,245.18
6	$ 51,245.18	$      1,114.58	$     4,567.92	$   3,453.34	$     47,791.84
7	$ 47,791.84	$      1,039.47	$     4,567.92	$   3,528.45	$     44,263.40
8	$ 44,263.40	$         962.73	$     4,567.92	$   3,605.19	$     40,658.20
9	$ 40,658.20	$         884.32	$     4,567.92	$   3,683.60	$     36,974.60
10	$ 36,974.60	$         804.20	$     4,567.92	$   3,763.72	$     33,210.88
11	$ 33,210.88	$         722.34	$     4,567.92	$   3,845.58	$     29,365.29
12	$ 29,365.29	$         638.70	$     4,567.92	$   3,929.22	$     25,436.07
13	$ 25,436.07	$         553.23	$     4,567.92	$   4,014.69	$     21,421.38
14	$ 21,421.38	$         465.92	$     4,567.92	$   4,102.00	$     17,319.38
15	$ 17,319.38	$         376.70	$     4,567.92	$   4,191.22	$     13,128.16
16	$ 13,128.16	$         285.54	$     4,567.92	$   4,282.38	$       8,845.77
17	$    8,845.77	$         192.40	$     4,567.92	$   4,375.52	$       4,470.25
18	$    4,470.25	$            97.67	$     4,567.92	$   4,470.25	$                    -