John borrowed $300,000 from a bank for buying an apartment three years ago. He has been paying the monthly mortgage repayments (at month end) with an annual fixed interest rate of 6% for three years, and the mortgage has seven years remaining term. Since the inflation has reverted back to the long-term target, the Reserve Bank decided to pause hiking the benchmark cash rate. As such, major commercial banks started to offer special deals to attract new customers. John is considering two deals listed below.

• Bank BNZ: a 10-year mortgage with a fixed interest rate of 5.4% p.a. The monthly mortgage repayments need to be made at the end of the month. A bonus of $5,000 is offered to customers for transferring an existing mortgage from another bank. (Hint: the bonus can be used to reduce the mortgage balance)

• Bank DBA: a 7-year mortgage with a fixed interest rate of 6.2% p.a. The quarterly mortgage repayments need to be made at the beginning of the quarter (the first repayment is due now). A bonus of $4,000 is offered to customers for transferring an existing mortgage from another bank.

Working as an independent financial planner, you are invited to provide advice about the mortgage decision. In particular, please advise the following

a) For the existing mortgage, what is the monthly mortgage payment? What’s the remaining balance?

b) What will be the monthly mortgage repayment if John switches to Bank BNZ? Please construct a loan amortization table to demonstrate the mortgage account balance over the term of the mortgage.

c) What will be the quarterly mortgage repayment if John switches to Bank DBA? Please construct a loan amortization table to demonstrate the mortgage account balance over the term of the mortgage.

d) Please advise if should John stay with the current mortgage or switch to a new bank. If switching, which bank should John move to? Please justify the reasons. (Hint: considerations should include: total interest payments and impacts of mortgage repayment on living standard)

Construct an Excel file with the formula used (the formula should be shown).