Questions are below:

Salwid-Mort, a mortgage broker, has hired you to develop aspreadsheet they will use with their clients.

Theyve noticed that thirty-year loans with an interest offset facility are popular with their clients. They want aspreadsheet thatdemonstrates some of thecharacteristics of this type of loan to their clients. Broadly, theyd 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 loans 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).

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 loans 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.

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 loanand 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.