Question

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.1 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, draw up the loan repayment schedule for Bill. Calculate the annual loan repayment amount using Goal Seek.

Answer 1

Australian 10-year government bond yield rate is not given so we assume rate is 3.25 %

So loan rate will be =3.25+1.50=4.75 ( 100 basis point = 1 percentage)

Using time value of time concepts we can find annual installment

Loan amount = Present value of all future installment

Loan amount = Installment amount × PVAF (4.75%,30 YEARS)

SO Installment amount = loan amount /PVAF (4.75% ,30 YEARS)

installment amount = 6,00,000/ 15.8204 (PVAF taken form annuity table)

= 37926 (round off)