2

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

3 Answers 3

2

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 $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.

3
  • +1 You appear to be correct. "Variable rate mortgage interest is compounded monthly, unlike in a fixed rate mortgage (semi-annual)." - link Jul 9, 2014 at 15:18
  • I stared at that (1+(rate/2))^(2/12)-1 for a little bit. Mathematically, X^(1/n) says to me "the rate which when compounded n times, produces rate X. (where X is > 1.0) But I'm still trying to wrap my head around the idea of dividing an advertised rate by the number of periods (e.g. annual rate by 12)... i.e. The fact that a bank advertises an annual 3%, but if you were to borrow 100 dollars, you'd owe $103.04 at the end of that year. That's baffling to me. It seems like an arbitrary deviation from common sense, just to add some fuzz.
    – pf_init_js
    Jun 2, 2022 at 8:04
  • The interest Act of Canada, section 6 applies to fixed interest rates on mortgages, and it is where it is stipulated that the interests be recalculated annually or semi annually. laws-lois.justice.gc.ca/eng/acts/i-15/FullText.html . All banks prefer semi-annually, so that's what they all seem to be doing for the fixed rates.
    – pf_init_js
    Jun 4, 2022 at 6:55
1

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.

8
  • So would the general formula be PMT((1+quoted_rate)^(1/12)-1, years*12, -principal)*12/payments_per_year) ?
    – Brian Low
    Jul 7, 2014 at 23:47
  • that's right, assuming the variable rate is compounded monthly, which I think is always.
    – brian
    Jul 8, 2014 at 0:02
  • It looks like each bank handles weekly payments slightly differently (BMO $544.77, RBC $547.17, TD $545.02). All banks handle monthly the same: $2,371.06 but our formula gives $2,360.54 when payments_per_year=12. Why the difference?
    – Brian Low
    Jul 8, 2014 at 0:14
  • My guess is the banks count on you paying on the 1st of the month (or same day every month) where this formula uses 12 exactly spaced payments. Without knowing their algorithms, it's just speculation. Keep in mind a difference of $11 per month is the same scale as $2.50 per week, similar to the differences in bank calculators.
    – brian
    Jul 8, 2014 at 0:33
  • I think your monthly interest rate may be off a bit. I think we need to convert 3% APR to 3.0416% Effective Annual Rate using (1+(rate/12))^12-1. Then calculate the effective monthly rate with your formula (1+rate)^(1/12)-1 to get 0.25%. This gives a weekly payment of $547.17 (matching RBC) and a monthly payment of $2,371.06 (matching all banks).
    – Brian Low
    Jul 8, 2014 at 0:58
1

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.

3
  • I got $546.81 as well, but it seems incorrect as I used the regular equations, 52 payments per year thus weekly compounding. Ooops - I see you edited, deleted a prior answer. So this is correct? Compounding in line with payments? Jul 9, 2014 at 21:10
  • @JoeTaxpayer - It seems the most straightforward method, provided the bank really quotes a weekly compounded nominal rate. Jul 9, 2014 at 22:46
  • According to the rules posted above a week , for the purpose of defining a period, is to be considered 1/52 of a year. Could the discrepancy between 52*25 = 1300 and the 1297 have to do with the fact that for the first three weeks (of month 0) you haven't accrued interests yet? wild guess.
    – pf_init_js
    Jun 2, 2022 at 7:05

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .