Personal Finance & Money Stack Exchange is a question and answer site for people who want to be financially literate. It only takes a minute to sign up.
Sign up to join this community
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)
Concrete example: what is the weekly payment on a $500,000 mortgage at 3% amortized over 25 years assuming prime rate does not change during that time
According to this calculator
Weekly payment $544.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 $544.72, close enough for me.
Upon running a calculation on the RBC calculator page at
https://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:-
http://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 $546.81
The calculation formula is restated here with further explanation :-
Calculating the Present Value of an Ordinary Annuity http://www.investopedia.com/articles/03/101503.asp
Note
The RBC calculator quotes a weekly payment of $547.17 but its amortisation table counts 1297 weeks which doesn't seem right.
monthly_payment = PMT(rate/12, amortization_in_years*12, principal)
weekly_payment = monthly_payment * 12 / 52
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 $547.17, BMO $544.77 and TD $545.02. As suggested by @brian, this formula gives us a rough answer by just dividing the total year's payments into weeks.
