Question

Cajun Jack needs $45,000 for a boat used to hunt crocodiles that are used both for their leather and meat. Cajun is considering taking a 9-year loan for the required amount and he talked to the lenders from his local Bank and Credit Union regarding loan options. The local bank charges 6.25% per year compounding quarterly and requires quarterly repayments. (a) Calculate the quarterly repayment for both principal and interest that Cajun would have to make on this loan. If you wish, use EXCEL to calculate the quarterly repayment. EXCEL Instructions: Refer to Topic 4 in the EXCEL booklet for instructions on how to use financial functions to make annuity calculations. (b) 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. 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. You need to use formulas, do not simply type in values. Therefore you need to show the formulas in your spreadsheet in this form B4=B3*$C$1. Failure to meet this requirement will result in a penalty (marks deduction). Hint: When you prepare the amortisation schedule, do not round the repayment 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. (c) Use your amortisation schedule from part (b) to calculate the total interest and the total amount paid over the life of the loan. The Credit Union charges Cajun 5.89 % per year compounding monthly and requires monthly repayments. Also, The Credit Union offers a two-year interest-only option with his 8-year loan. This means that for the first two- years, every month Cajun would pay only interest on the amount borrowed. Loan repayments consisting of both interest and principal would then commence in year three and continue for 5 years. (d) Calculate the monthly repayment that Cajun would have to make starting in year three if he wants to pay off the loan in 8 years. If you wish, use EXCEL to calculate the monthly repayment. EXCEL Instructions: Refer to your own notes taken in class and Topic 4 in the EXCEL booklet for instructions on how to use financial functions to make annuity calculations. (e) Calculate the total interest paid on the loan with the two-year interest-only option. (f) Which option should Cajun Jack take? As part of your response you must explain why the option you select is the better of the two alternatives.

Answer 1

Quarterly payment: 1643.84, Total interest: 14178.19

Loan repayment schedule is as below: (Interest starts decreasing over time and Principal Starts Increasing)

Sl.	Interest	Principal	Balance
1	703.13	940.71	44059.29
2	688.43	955.41	43103.88
3	673.5	970.34	42133.54
4	658.34	985.5	41148.05
5	642.94	1000.9	40147.15
6	627.3	1016.54	39130.61
7	611.42	1032.42	38098.19
8	595.28	1048.56	37049.63
9	578.9	1064.94	35984.69
10	562.26	1081.58	34903.12
11	545.36	1098.48	33804.64
12	528.2	1115.64	32689
13	510.77	1133.07	31555.93
14	493.06	1150.78	30405.15
15	475.08	1168.76	29236.39
16	456.82	1187.02	28049.37
17	438.27	1205.57	26843.81
18	419.43	1224.41	25619.4
19	400.3	1243.54	24375.86
20	380.87	1262.97	23112.89
21	361.14	1282.7	21830.19
22	341.1	1302.74	20527.45
23	320.74	1323.1	19204.36
24	300.07	1343.77	17860.59
25	279.07	1364.77	16495.82
26	257.75	1386.09	15109.73
27	236.09	1407.75	13701.98
28	214.09	1429.75	12272.23
29	191.75	1452.09	10820.14
30	169.06	1474.78	9345.37
31	146.02	1497.82	7847.55
32	122.62	1521.22	6326.33
33	98.85	1544.99	4781.34
34	74.71	1569.13	3212.21
35	50.19	1593.65	1618.56
36	25.29	1618.55	0

Totals

$ 59,178.20 (P+I)

$ 45,000.00 (Principal)

$ 14,178.20 (Interest)