: Formula for variable rate Canadian mortgage What is the formula for calculating payments on a variable rate mortgage in Canada? (This source says variable rate mortgages are compounded differently than fixed rate mortgages)

Vote Up Vote Down   0 Reactions

Login to give Reaction!

Upon running a calculation on the RBC calculator page at
www.rbcroyalbank.com/cgi-bin/mortgage/mpc/start.cgi/start
it states :

(ii) interest is compounded semi-annually for fixed interest rates and
each payment period for variable interest rates

So, taking the quoted rate as nominal 3% compounded weekly, the weekly rate is

r = 0.03/52 = 0.000576923 = 0.0576923 %

with an effective yield of

(1 + r)^52 - 1 = 0.0304456 = 3.04456 % p.a.

To calculate the weekly payment the following loan formula can be used:-
www.financeformulas.net/Loan_Payment_Formula.html
p = r*pv/(1 - (1 + r)^-n)

where

p = periodic payment
pv = present (initial) value of loan
r = rate per period
n = number of periods

So with

pv = 500000
n = 52*25 = 1300

p = r*pv/(1 - (1 + r)^-n) = 546.814

The weekly payment is approximately 6.81

The calculation formula is restated here with further explanation :-

Calculating the Present Value of an Ordinary Annuity www.investopedia.com/articles/03/101503.asp
Note

The RBC calculator quotes a weekly payment of 7.17 but its amortisation table counts 1297 weeks which doesn't seem right.

Vote Up Vote Down   0 Reactions

Login to give Reaction!

According to this calculator

Weekly payment 4.77

The math is complicated. Variable mortgages are usually, or always AFAIK, compounded monthly. The rate quoted is an APR specified by law using a formula found here

APR = (C/(T×P)) × 100

where

APR is the annual percentage rate cost of borrowing; C is an amount
that represents the cost of borrowing within the meaning of section 5
over the term of the loan; P is the average of the principal of the
loan outstanding at the end of each period for the calculation of
interest under the credit agreement, before subtracting any payment
that is due at that time; and T is the term of the loan in years,
expressed to at least two decimal points of significance.

It's even harder in your case as you want to pay weekly. Basically it's the same as if you paid everything on the day before it compounds, ie. the first of the month. So what you can do is figure out the monthly payment and divide by 4.33 wks/month to get a weekly payment.

Now you have to figure the interest rate used for monthly compounding. That's going to be the 12th root of (1+0.03) - 1, so about 0.2466% every month.

So now you can plug this into excel using the PMT function like this (12/52 is the inverse of 4.33 wks/yr and a bit more accurate)

=PMT(0.002466,25*12,-500000)*12/52

And you get 4.72, close enough for me.

Vote Up Vote Down   0 Reactions

Login to give Reaction!

Short answer

monthly_payment = PMT(rate/12, amortization_in_years*12, principal)
weekly_payment = monthly_payment * 12 / 52

Long answer

Variable rate mortgages in Canada are often compounded monthly, but sometimes semi-annually according to this post on RedFlagDeals from 2009:

As per law the fixed rates mortgages in Canada are compounded semi
annually while in variable rate mortgages banks can chose the way they
compound the interest. Most banks compound the interest for their
variable rate mortgages as monthly while some banks such as National
Bank, ING Direct and Scotiabank on one of its variable mortgage
compound the interest semi annually.

For monthly payments compounded monthly:

monthly_payment = PMT(rate/12, amortization_in_years*12, principal)
= PMT(0.03/12, 25*12, 500000)
= -2371.06

This result matches the mortgage calculators at RBC, BMO and TD.

For monthly payments compounded semi-annually:

monthly_rate = (1+(rate/2))^(2/12)-1
= (1+(0.03/2))^(2/12)-1
= 0.002484516

monthly_payment = PMT(monthly_rate, amortization_in_years*12, principal)
= PMT(0.002484516, 25*12, 500000)
= -2366.23

This result matches the mortgage calculator at National Bank.

For weekly payments:

weekly_payment = monthly_payment * 12 / 52
= -2371.05 * 12 / 52
= -547.17

Each bank seems to calculate weekly payments slightly differently: RBC 7.17, BMO 4.77 and TD 5.02. As suggested by @brian , this formula gives us a rough answer by just dividing the total year's payments into weeks.