Salwid-Mort, a mortgage broker, has hired you to develop a spreadsheet they will use with their clients. They’ve noticed that thirty-year loans with an interest offset facility are popular with their clients. They want a spreadsheet that demonstrates some of the characteristics of this type of loan to their clients. Broadly, they’d like to show their clients a graph with three curves on it. 1. The declining outstanding balance of a traditional principle and interest loan over a year of loan’s operation. 2. As above, but with an interest offset facility, tracking fortnightly savings of $800. 3. As above, but the client puts a stream of fortnightly savings of $1 600 (i.e., double the above) in their interest offset account. To achieve these objectives, you have been asked to develop the following. Develop a spreadsheet with at least three sheets. One sheet, titled ‘Inputs’ consists of the following. 1. The Salwid-Mort logo. 2. CY22, the Australian 10-year government bond yield for calendar year 2022 (i.e., the 10-year government bond yield on 31 December 2022, a j2 rate).∗ 3. The rate of interest for the 30-year mortgage, given by 205 basis points over CY22. 4. The initial loan amount, $1 000 000. 5. The fortnightly cash deposit amount of $800. 6. A second, higher ($1 600), fortnightly cash deposit amount. One sheet, titled ‘Graphs’ consists of the following a line graph, with three curves, in different colours, each pertaining to the eleventh year of operation of the loan (i.e., from t = 10 to t = 11). The curves are as follows. 1. The outstanding balance of the loan, with no offset (i.e., a traditional principle and interest loan). ∗You will need to use FactSet to find this value. 1 ACST2001 Financial Modelling Group spreadsheet project S2 2023 [15 marks] Version 1.03 © 2023 Department of Actuarial Studies and Business Analytics, Macquarie University (All rights reserved) 2. The outstanding balance of the loan with an offset facility (described below) generated by saving $800 a fortnight. 3. The outstanding balance of the loan with an offset facility generated by saving $1 600 a fortnight. The axes should be labelled: ‘Loan balance’ (vertical axis) and ‘Time elapsed’ (horizontal axis, in years). The graph should include a legend, indicating which curve is which. Again, only one year of the loan’s progress is to be graphed: the eleventh year. One sheet, titled ‘Models’, consists of the following: two cash flow diagrams, drawn from the perspective of the client, and, following the principles developed in the lectures, models the following. 1. The progress of the outstanding balance of the traditional principle and interest loan in the eleventh year. 2. The progress of the outstanding balance of a loan with an interest offset facility in the eleventh year. Use the following notation in your diagrams. i. Xs, the outstanding loan balance at time s. ii. R, the loan repayment paid at time s. iii. Bs, the savings account balance at time s. iv. D, the deposit made to the savings account at time s. v. i, the (constant) assumed rate of interest In doing your calculations, note the following. 1. Treat the loan as running for 24 fortnightly (evenly spaced) periods each year over 30 years. 2. Deposits to the savings account are made at the end of each fortnightly period. They can be either $800 or $1 600. 3. The initial balance of the savings account is zero. 2 ACST2001 Financial Modelling Group spreadsheet project S2 2023 [15 marks] Version 1.03 © 2023 Department of Actuarial Studies and Business Analytics, Macquarie University (All rights reserved) 4. The savings account earns compound interest at an effective annual rate equivalent to the mortgage rate (given above). 5. The interest offset facility is modelled as follows: the interest earnings at the end of each fortnight are credited towards the loan—and so the savings account accumulates without interest. 6. The loan starts with an outstanding balance of $1 000 000. Interest is charged at the end of each fortnight, when a loan repayment is also made. The repayment is at a level to extinguish the loan after thirty years at the given mortgage rate (ignoring any interest offset facility). 7. If the interest offset facility is operating, then, at the end of each fortnight, the interest from the savings account is credited towards the loan. End of task The submission deadline for the spreadsheet project is 9.00 a.m. on 3 October 2023. You will need to submit your solutions (in one functional .xlsx or .xls file) to the link on iLearn prior to this time. Your spreadsheet should be clearly labelled and easy to understand. Make sure you identify what the “inputs” and “outputs” are. Include necessary information (e.g., title, axis titles, etc.) in your plot. Document and describe the steps in the development of each tab of your spreadsheet. Please note that uploading a file can take up to 15 minutes. You need to submit your file at least 20 minutes before the deadline to ensure a successful submission. 3 ACST2001 Financial Modelling Group spreadsheet project S2 2023 [15 marks] Version 1.03 © 2023 Department of Actuarial Studies and Business Analytics, Macquarie University (All rights reserved)