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 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. () 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*$C$1. (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 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 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).

Answer 1

Givens in the question :-

1. Amount required at a future date is $120,000 after 5 years
2. Savings per month for 5 years is $800
3. Rate of Interest on savings is 47% p.a. compounded quarterly or 0.8675% per quarter

Answer – a

Whether Sue will have enough money after 5 years to refurbish her house can be calculated by compounding her monthly savings at every quarters’ rest and shall be compared with the amount required at a future date that is $120,000

Calculation for savings at the end of 5 years

Quarter	Opening Investment (A)	Invested in Quarter (B)	Interest [(A+B)* 0.008675]         ( C )	Closing Investment (A+B+C)
1	-	2,400.00	20.82	2,420.82
2	2,420.82	2,400.00	41.82	4,862.64
3	4,862.64	2,400.00	63.00	7,325.64
4	7,325.64	2,400.00	84.37	9,810.01
5	9,810.01	2,400.00	105.92	12,315.94
6	12,315.94	2,400.00	127.66	14,843.60
7	14,843.60	2,400.00	149.59	17,393.18
8	17,393.18	2,400.00	171.71	19,964.89
9	19,964.89	2,400.00	194.02	22,558.91
10	22,558.91	2,400.00	216.52	25,175.42
11	25,175.42	2,400.00	239.22	27,814.64
12	27,814.64	2,400.00	262.11	30,476.75
13	30,476.75	2,400.00	285.21	33,161.96
14	33,161.96	2,400.00	308.50	35,870.46
15	35,870.46	2,400.00	332.00	38,602.46
16	38,602.46	2,400.00	355.70	41,358.15
17	41,358.15	2,400.00	379.60	44,137.75
18	44,137.75	2,400.00	403.72	46,941.47
19	46,941.47	2,400.00	428.04	49,769.51
20	49,769.51	2,400.00	452.57	52,622.08

Hence form the above calculations we can expect accumulation of $52,622.08 at the end of 5 years by Sue for the refurbishment of her house that is not enough because Sue would need $120,000 for the said purpose, therefore there is a shortage of $67,377.92 ($120,000 - $52,622.08)

Answer – b

The amount falls short for refurbish is $67,377.92 (calculated above) that can be taken up by bank as a loan at 4.35% p.a. compounded biannually and required biannually repayments for 9 years.

We have to calculate minimum biannually repayment for both principal and interest that Sue would have to make on this loan and can be calculated using the below mentioned formula –

Equated Biannually Installment = Loan Amount / Sum of discounting factors for the period

Where –

loan amount is $67,377.92, and

Sum of Discounting factors = ∑ 1/(1 + rate) (for loan period)

Rate = 4.35% p.a. (0.0435 in decimals) or 2.175% per biannually (0.02175 in decimals), period = 9 years or 18 biannual

Hence, Sum of Discounting factors = ∑ 1/(1 + 0.02175) (from 0 to 18)

                                                          = 14.7639

Putting these figures in above formula, we get –

Equated Biannually Installment = $67377.92 / 14.7639

                                                   = $4563.69

Amortization Schedule for the loan

Biannual Period	Opening Principal   (A)	Biannual Installment           (B)	Interest [A*0.02175]     ( C )	Principal (D)	Closing Principal (A+C-B)
1	67,377.92	4,563.69	1,465.47	3,098.22	64,279.70
2	64,279.70	4,563.69	1,398.08	3,165.61	61,114.09
3	61,114.09	4,563.69	1,329.23	3,234.46	57,879.63
4	57,879.63	4,563.69	1,258.88	3,304.81	54,574.83
5	54,574.83	4,563.69	1,187.00	3,376.69	51,198.14
6	51,198.14	4,563.69	1,113.56	3,450.13	47,748.01
7	47,748.01	4,563.69	1,038.52	3,525.17	44,222.84
8	44,222.84	4,563.69	961.85	3,601.84	40,620.99
9	40,620.99	4,563.69	883.51	3,680.18	36,940.81
10	36,940.81	4,563.69	803.46	3,760.23	33,180.58
11	33,180.58	4,563.69	721.68	3,842.01	29,338.57
12	29,338.57	4,563.69	638.11	3,925.58	25,413.00
13	25,413.00	4,563.69	552.73	4,010.96	21,402.04
14	21,402.04	4,563.69	465.49	4,098.20	17,303.84
15	17,303.84	4,563.69	376.36	4,187.33	13,116.51
16	13,116.51	4,563.69	285.28	4,278.41	8,838.11
17	8,838.11	4,563.69	192.23	4,371.46	4,466.64
18	4,466.64	4,563.69	97.15	4,466.54	0