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

Theyve 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, theyd like to show their clients a graph with three curves on it.

The declining outstanding balance of a traditional principle and interest loan over a year of loans operation.

As above, but with an interest offset facility, tracking fortnightly savings of $800.

As above, but the client puts a stream of fortnightly savings of $1 600 (i.e., double the above) in their interest offset account.

-----------------------------------------------------------------------------------------------------------------------------------------------------------

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.

-----------------------------------------------------------------------------------------------------------------------------------------------------------

In doing your calculations, note the following.

Treat the loan as running for 24 fortnightly (evenly spaced) periods each year over 30 years.

Deposits to the savings account are made at the end of each fortnightly period. They can be either $800 or $1 600.

The initial balance of the savings account is zero.

The savings account earns compound interest at an effective annual rate equivalent to the mortgage rate (given above).

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.

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

If the interest offset facility is operating, then, at the end of each fort- night, the interest from the savings account is credited towards the loan.