Rate scenario spreadsheets are a handy tool in a mortgage planner’s tool chest. Advisors use them frequently to illustrate hypothetical cost differences between different mortgage options.
One common scenario is the 5-year fixed vs. 5-year variable comparison. We ran a showdown on these two terms when writing Monday’s story. (Click the chart below to enlarge it.)
The comparison above shows a prime – 0.25% variable versus a 3.25% five year fixed. It assumes three (and only three) quarter-point BoC rate hikes, starting in March 2013.
Background: With economists projecting rate hikes by year-end 2012, this scenario’s assumption of higher rates by 2013 is reasonable. The reason we chose only three rate increases for the assumptions is simply to determine the minimum hikes needed to make the fixed mortgage cheaper than the variable.
The purpose of scenarios like this isn’t to forecast rates or express exact borrowing costs, but rather to show what could happen (approximately) if reasonable assumptions pan out.
Note: Many other factors impact total borrowing cost besides rate, including but not limited to prepayments, locking in and penalties (for breaking a mortgage early). Those aspects can also be estimated and factored into scenarios. Here, we’ve assumed the variable-rate borrower has no need to break the mortgage early, and will initially match the fixed-rate payments (for an apples-to-apples comparison of monthly cash flows).
Once you visualize the potential cost difference between a fixed and variable mortgage, you can then decide if the fixed rate is worth the extra upfront premium.
In this particular case, the 3.25% five-year fixed just edges out the variable on paper (it is hypothetically $11 cheaper than the variable over five years). Again, this is based solely on the sample interest rate assumptions, which presuppose a small rate tightening campaign starting in spring 2013. Naturally, any additional rate hikes would increase the advantage of the fixed rate.
The objective here is mostly to reiterate what was said Monday. Today’s five-year fixed rates are surprisingly competitive thanks to lenders getting stingy on variable-rate discounts.
Talk to your mortgage planner and have him/her run some scenarios that match your own circumstances and outlook. You might even find that other terms project out cheaper, such as the 2.99% four-year fixed.
Sidebar: As noted Monday, there’s still an outside possibility of a BoC rate cut by next year. If that happens, variables would likely outperform all other terms, even with today’s mediocre prime – 0.25% average discount.
Analytical types will assign a probability of those rate cuts occurring and then weigh all the different scenarios to reach an “expected value” of the fixed/variable cost difference.
Rob McLister, CMT
Last modified: April 28, 2014
Anyone know of a website that would provide a calculator as the one above? I would be very interested to have a look at the numbers of my current scenario. 3 years into my 5 year variable
Hi David,
I am guessing Rob created it himself and you likely won’t find one like it on the internet.
I don’t believe he would share it with you, but perhaps he would do a scenario or 2 for you.
Reasonably easy to recreate using excel spreadsheet and calculators online (if you know how to use excel!); see some options for base templates googling “mortgage amortization calculator canada excel spreadsheet”. Use two different tabs to create the two scenarios (you can combine as in the chart above if you know what you’re doing, but not necessary).
Yeah, they are really easy to make. There are a few good ones online that you can use as a template that you can modify for your own uses.
This site has some useful information and spreadsheets.
If you use other sites, be cautious of how the payments are calculated. Canadian mortgages are calculated semi-annually, while US mortgages are not, which changes the formulae slightly.
Hey Rob,
Thanks for the scenario – a great tool for analytical clients. Just wondering though, why you didn’t point out the difference in the OSBs?
Julie
There are several amortization calculators available online… some better than others… but I have used them in the past to do roughly the same thing as above.
I thought it was interesting to note that although the fixed won out by $11 on interest, the hypothetical mortgager ended up with $500 less principal by the end of the term with the variable, with payments averaging only $2 higher…
Yes, it is a usefull info; however with the variable rate client will pay more in principal…
This is just a forecast.
Thank you
Hi Julie,
Very good question. This is a total cost comparison so it compares total payments (i.e. all cash outlays including prepayments) to final balances.
You’ll notice the higher outstanding balance of the fixed is offset by the higher total payments of the variable. The difference between the two is $11.45 over five years (in favour of the fixed).
It’s worth noting that this is just a quick comparison so we haven’t got into time value of money, etc. This analysis is primarily meant to show that the variable rate advantage has diminished more than one might think.
Cheers…
Rob
quite the chart rob..thankyou very much….if my bank offered me 3.25 over five i would jump at it…and leave my variable in the rear view mirror……but what bank would ever go that low…none that i know of…none in canada….
Several lenders are offering it. Better yet, if you’re looking for a fixed rate mortgage, why not take a 4-year fixed at 2.99%? There is also a 3-year fixed at 2.59%. The bottom line is rates are at historical lows but consumers still have to understand what they’re getting into and choose a mortgage strategy that fits their objectives.
While I’m a strong advocate of variable, even if we go back to prime – .75% or lower again in the foreseeable future and prime remains at 3%, the 70bp premium to lock the rate for 4 years is quite attractive.
BTW Rob that’s a terrific spreadsheet, I can’t imagine how many hours went into making it. Your clients are quite lucky to have you as their adviser!
Hi NP, If you talk to the right broker you may be pleasantly surprised!
That’s right Shayne. We did create it. We’ll try and get a web version up on our new site by Q1.
Cheers…
Rob
I think it’s because you have to account for the prepayments in addition to the regular payments.
that four year rate is very attractive…time to pull out the mortgage calculator….rob’s chart is great….i’m on a 2.5 variable and it shows 3.25 or perhaps anything close to that fixed rate would be a good rate to jump to….thanx rob…great site
I just got my mortgage done thru ScotiaBank yesterday at prime minus 0.7 close for 5 years. But got this pre-approval 45 days ago. Got a chance to get a 4 years fixed at TD Bank for 2.99%, but what they are offering is a collateral mortgage. You guys think I made the right choice? The variable rate that I got, you think is a good rate at the moment? (I am a first time buyer, this is all new to me)
I just squeaked in at P-.9% with NB the night before everyone started slashing the discount. Thanks to Rob’s wonderful wife Melanie……great job! If I had waited another couple days I would be singing a different tune today and probably lock in for 4 or 5 yr fixed.
Only time will show what was a better choice.
Global debt crisis can’t solve itself …
We have to wait and see
I’ll stick with the variable. Thank you for the analysis. I would like to see a 5 year fixed at 2.99% and only then would I jump from my variable. Europe will support the crisis in Greece because all the loans and austerity measures will not fix the problems in Europe, i.e. No new jobs are created, entrepreneurialism is not supported. The American Super Comittee on the National Debt reports back in Novembe and no new ideas have been put forth. Unemployment in some US cities is at 25%. The US economy needs people to by the many empty homes. Canada is lucky in all of the above areas, but our economy and population is not an economic powerhouse in the global economy. We’re just more stable than our G20 partners. In short, look for a cut after the new year, and a hold and secure at the next BofC meetin
I’m with you Steve all the way….lets see a 2.99 for five fixed and then I would leave my beloved variable…but what are the chances of that happening…keep watching the rates I would think…
There is a big difference between already having a variable rate and getting a NEW variable today. You guys with prime – .9 are lucky dogs. It makes way less sense to go variable at 2.75% today. A 4 year fixed at 2.99% seems like the clear choice…for us anyway.
yup the 2.99 is sure as heck tempting…lets see what carney says on tues…
Very kind; thanks Lior. Creating it took a week or so, but it’s taken a few years of perfecting to allow for different scenarios.
Cheers…
does D+H expert not already have this functionality within tools – scenario builder???
Nope. Expert has nothing remotely close.
Hello Rob,
I tried recreating the table and I am not sure where our differences lie in calculating the payment per period in Excel. May I get your private email so I can send you my formulas ? I do not want to flood this thread with EXCEL discussions. Thank you and this is very helpful ! As always your articleas are a great help ! Keep up the good work !
Victor
Hi Victor, Just sent you an email…
Toady, is 2.99 still available at 4 years? Our broker has said 3.09 is the best rate.
And while these fixed rates are better than p -.25, what about P -.5, especially in light of no upward movement in the interest rates through to the end of 2013?
Street Capital CEO brokers still have 2.99%.