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

Improve this question
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.

Improve this answer
  • So would the general formula be PMT((1+quoted_rate)^(1/12)-1, years*12, -principal)*12/payments_per_year) ? – Brian Low Jul 7 '14 at 23:47
  • that's right, assuming the variable rate is compounded monthly, which I think is always. – brian Jul 8 '14 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 '14 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 '14 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 '14 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.

Improve this answer
  • 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? – JTP - Apologise to Monica♦ Jul 9 '14 at 21:10
  • @JoeTaxpayer - It seems the most straightforward method, provided the bank really quotes a weekly compounded nominal rate. – Chris Degnen Jul 9 '14 at 22:46
1

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.

Improve this answer
  • +1 You appear to be correct. "Variable rate mortgage interest is compounded monthly, unlike in a fixed rate mortgage (semi-annual)." - link – Chris Degnen Jul 9 '14 at 15:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.