Bill has bought a new home in Canberra. He borrowed $600000 at a rate calculated to be 150 basis points over the Australian 10-year government bond yield for 2018*. The loan is to be repaid in annual instalments over a thirty year period. The first instalment was due on 19 March 2020.

a. In your spreadsheet, in your first tab (labelled ‘A’), draw up the loan repayment schedule for Bill. Calculate the annual loan repayment amount using Goal Seek.

Like Bill, in question 3 above, Scott has bought a house in Canberra, borrowing the same amount, and on the the same terms. Scott’s bank, however, offers an ‘interest offset’ account facility with the loan. Like Bill, Scott’s first payment is on 19 March 2020.

On the day Scott takes the loan of $600000 out (19 March 2019), Malcolm gives Scott $100000. Scott immediately puts the money into his interest offset account. This account also earns the same rate compound interest charged by the bank. Over the term of the loan Scott does not put any more money into the interest offset account. The interest offset account pays interest annually, and its first payment will be on 19 March 2020.

Scott’s interest offset account pays its interest payments to Scott’s loan.

b. In your spreadsheet, in your second tab (labelled ‘B’), follow the approach we used in THQ1 to draw up the loan repayment schedule for Scott. When is the last (reduced) payment made? How much is the last (reduced) payment?

c. Alternatively, rather than viewing the Scott’s payments as consisting of his loan repayment plus the interest credit from his offset account, we can view his capital outstanding as being reduced by the amount of his mortgage offset account, with interest charges being calculated on this reduced balance—to which only his loan repayment amount (the same as Bill’s) is applied.

Follow this approach in your third tab (labelled ‘C’) and construct a modified loan repayment schedule for Scott to show you arrive at the same answer as above.

d. Peter has a home much like Scott’s, with loan repayments equal to Bill’s—and also received a $100000 gift from Malcolm, which was put straight into Peter’s interest offset account. However, Peter’s bank only credits 80% of the balance of his interest offset account against his outstanding capital.

In your fourth tab (labelled ‘D’) draw up the loan repayment schedule for Peter. When will his last (reduced) payment occur? How much is his last (reduced) payment?

e. In your fifth tab (labelled ‘E’) plot Bill’s, Scott’s and Peter’s outstanding loan balance at the beginning of each year of the loan against each other.

*Bond rate has been taken 2.84%.

Click on Buy Solution and make payment. All prices shown above are in USD. Payment supported in all currencies. Price shown above includes the solution of all questions mentioned on this page. Please note that our prices are fixed (do not bargain).

After making payment, solution is available instantly.Solution is available either in Word or Excel format unless otherwise specified.

If your question is slightly different from the above question, please contact us at info@myassignmentguru.com with your version of question.