Unlock Your Potential

Latest Questions

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: one, the declining outstanding balance of a traditional principle and interest loan, two, as above, but with an interest offset facility, tracking fortnightly savings of $1000, and three, as above, but the client gives a fortnightly savings amount greater than $1 000. To achieve these objectives, you have been asked to develop the following. Develop a spreadsheet with at least two sheets. One sheet, titled Inputs consists of the following.1. The Salwid-Mort logo 2. CY21, the Australian 10-year government bond yield for calendar year 2021 (i.e., the 10-year government bond yield on 31 December 2021, a j2 rate)* 3. The rate of interest for the 30-year mortgage, given by 130 basispoints over CY21 4. The initial loan amount, $1 000 000 5. The fortnightly cash deposit amount of $1 000 6. A second, higher (than $1 000), fortnightly cash deposit amount. One sheet, titled Graphs consists of the following a line graph, with three curves. 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 $1 000 a fortnight. 3. The outstanding balance of the loan with an offset facility generated by saving more than $1 000 a fortnight. * [15 marks] 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. 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 $1 000 or a higher amount. 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 no longer accumulates with 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 fort- night, the interest from the savings account is credited towards the loan.