Question

Consider a typical $1,500,000 Canadian mortgage. Suppose that the current nominal interest rate is 6% and the maturity is set at 20 years. The rollover period is 2 years

The lender would like to know the effective yield at t=0 under the assumption that the mortgage interest rate will drop to 4% 2 years from its origination and will remain at 4% until the end of the mortgage’s life. Formulate the equation for computing the effective annual yield.

Answer 1

Consider a typical $1,500,000 Canadian mortgage. Suppose that the current nominal interest rate is 6% and the maturity is set at 20 years.

Payment frequency = Monthly

Period will be month.

We can find the monthly payment under this mortgage for the first two years using PMT function of excel.

Monthly payment = PMT(Rate, Period, PV)

Rate = interest rate per period = interest rate per month = 6% / 12 = 0.5%

Period = Nos. of months in 20 years = 12 x 20 = 240

PV = - Value of loan today = - $ 1,500,000

Hence, Monthly payment, P₁ = PMT(Rate, Period, PV) = PMT(0.5%, 240, -1500000) = $10,746.47

By the end of 2 years, total amount of principal paid can be calculated using the CUMPRINC function of excel.

Principal paid in 2 years = Principal paid in 24 months = -CUMPRINC(Rate, Period, PV, start period, end period, Type) = -CUMPRINC(0.5%, 240, 1500000, 1, 24, 0) =  $ 82,563.97

Hence, Loan balance outstanding at the end of 24 months = 1,500,000 -  82,563.97 = $  1,417,436.03

At this stage loan will be reset to interest rate of 4% per annum = 4% / 12 = 0.333% per month.

New monthly payment can be calculated using the PM function with inputs as

Rate = 0.333%, Period = 18 years = 12 x 18 = 216 months, PV = - Value of loan outstanding = - 1,417,436.03

Hence, monthly payment, P₂ = PMT(Rate, Period, PV) = PMT(0.333%, 216, -1417436.03) = $9,216.14

This payment will now continue from period 25 till end of the loan i.e. period 240.

Let's assume the effective annual yield be Y.

Then monthly yield will be Y / 12.

Hence the equation to solve the yield will be as follows:

Present value of all the future payments should be equal to the loan amount today.

The first term can be simplified as value of annuity over 24 periods.

The second term can be simplified as = Value of annuity over 240 periods - Value of annuity over 24 periods

which can be simplified as:

Hence, the equation for computing the effective annual yield is: