Mortgage Rates

How To: Mortgage Schedule

by bigcajunman on May 23, 2013

A while ago I showed how the simple PMT() function in Excel can be used to estimate your periodic mortgage payments, if you have all the pertinent information. Today we can use that information to build a schedule to show your mortgage payments and how they change your Mortgage Principle over time.

First a clarification, as was pointed out by one of my commenters, the PMT() function gives you the wrong periodic payment, due to Canadian Mortgages having their interest compounded semi-annually (twice a year) because in the U.S. it is compounded annually. The fix for this is to change the first entry in your PMT function from:

Annual Interest Rate/12 months: which assumes only an annual compounding
( (Annual Interest Rate/2 + 1) ^ (2/12) -1 ) which compensates for the semi-annual compounding

Mortgage Table

I like a Mortgage Schedule Table, just because it gives you the ability to understand where you stand in terms of paying off your loans.

Each row of the table will show a payment, how it breaks down in terms of interest and principle payment and how much principle remains after each payment (sounds simple doesn’t it, well it is):

Let’s try this example and show some of the sheet. Assume a $100,000 Mortgage, with an interest rate of 5.95% compounded semi-annually, with a 25 year pay back with monthly payments. We use the PMT() function to find out that our monthly payment will be $636.84.

The sheet will be laid out in the following way:

Column 1 is the date of each payment
You can do this for bi-weekly, but I did monthly to make it easy to increment from month to month you simple do =DATE(YEAR(A12),MONTH(A12)+1,DAY(A12))
Column 2 shows the real monthly payment
We already calculated that using the PMT() function
Column 3 calculation of how much each payment goes towards the Interest
This is = Previous balance * Interest rate for that payment period
Column 4 calculation of how much each payment goes to the principle
Total payment - Interest Payment = Principle payment
Column 5 the remaining balance on the loan
Previous Balance - Principle Payment
Column 6 total interest paid on the loan so far.
Previous Interest total + Interest Paid on this payment

Mortgage Schedule


Mortgage Amount	$100,000.00		Interest	5.950%
Monthly Payment	$636.84		Years	25
			Periods/Year	12
			Term	300
			Start	01-Jun-08

==========================================================================
Date	Payment	Interest	Principal	Balance	Total Interest
01-Jun-08	$0.00			$100,000.00	$0.00
1-Jul-08	$636.84	$489.80	$147.04	$99,852.96	$489.80

Simple isn’t it?

You just keep going line by line for 25 * 12 times for all the payments and you will see the loan drop to zero. Hopefully tomorrow I will have a link to the example worksheets I set up, for both US and Canadian Mortgages.

More interesting versions of this is if you add an OVERPAYMENT column and then start seeing what happens when you add extra payments early on and how much faster your mortgage gets paid off!

{ 10 comments }

The Free Market and Mortgages (in Canada)

by bigcajunman on March 21, 2013

Recently we have had two different lenders come up with “cut-rate” Mortgage (BMO and Manulife) “deals” and both have now withdrawn those “deals” after urging from the Government (specifically the Department of Finance (and in the case of Manulife the Finance Minister himself)).

Finance Minister’s New Tool for Dealing with Rogue Banks?

The direct quotes (from the CBC web site) from those involved were:

“My expectation is that banks will engage in prudent lending — not the type of ‘race to the bottom’ practices that led to a mortgage crisis in the United States,” Finance Minister Jim Flaherty said of BMO’s “cut rate” mortgage deal.

and a further quote from the finance minister:

“I had one of my staff call them and indicate my displeasure, which is the same thing I did with BMO, except I called myself,” the finance minister said.

So there you have it my fellow Canadians, evidently there is now plenty of room for the government in our bedrooms (and homes) (to paraphrase Pierre Trudeau).

Now I could not find enough information on what kind of “deal” both BMO and Manulife were offering for these alleged 5 year mortgages, however offering rates of 2.99% and then 2.79% makes these sound like great deals (however, I am willing to hear from folks who are better informed on the entire deal on whether over payments were possible, and whether there might have been hidden penalties as well), but that is not the real point, it is the fact that the “Harper Government” now feels that market forces cannot be trusted.

Have they overstepped their bounds? No, both of the firms could have said, “Sod off!” (however imprudent that might have been), this was not an act of parliament, but it does open an interesting discussion about How Big a Nurse Maid Must the Government Be?

Would there have been a run on “cut-rate” mortgages? There might have been, can you trust banks to not cut their own throats? Rhetorical question now, I suppose, but currently I am fairly certain that I could walk into my bank and after a lot of threatening to leave, I might get a fixed rate of somewhere near 3.5% (without too much work, maybe even lower, who knows). Would we end up with a Bunch of Stupid Mortgages causing a US-like Sub-prime debacle? Guess we shall never know.

Does the government think Canadians are Financially Stupid? Actions speak louder than words in this case.

Also, I find it hilarious to then hear Tom Mulcair (I am a former Quebecois so that is what we call him there) complaining about the Conservatives interfering with the free market, the irony of hearing a staunch Socialist complaining that the Free Market should decide is not lost on me.

No word on the heir apparent for the Liberals (Mini Trudeau) about whether the Government should be stepping in here.

What do you folks think? Should the government stopped this foolishness or let the marketplace decide?

{ 12 comments }

Stupid Mortgages in Canada

by bigcajunman on April 17, 2012

The CBC had an interesting article on how Canada is Tiptoeing Towards a Subprime debacle similar to the United States (but not exactly the same).

I was skeptical, but I was also unaware that there are so many “alternate lenders” for mortgages in Canada (and that these Mortgages are not CMHC insured).

The banks seem to have come to their senses and are now rejecting more than 20% of Mortgage applications these days, because they cannot be CMHC insured, according to the CBC and while I think this is a good idea, folks are simply trying to contravene the system and get a loan that is not insured by the CMHC, and thus are a lot more risky.

The weird thing is that the president of one of these “alternate loan providers” claims their industry takes up 20% of the mortgage market, which is one hell of a big whack of money.

To be clear (at least to my understanding) this is not a SubPrime type situation, the loans being given out are not at low rates (which then ratchet up quickly (at least I hope not)), but it is loans that are being given to folks that banks don’t want to loan money, and that was one of the faulty pillars of the subprime debacle (i.e. people who couldn’t rent a home, were buying them instead).

Are we That Dumb?

These kind of loans worry me, because people are doing stupid things like tying themselves up with debts that would make the Marquis de Sade proud. If you can’t afford a house, maybe the bank is right? Owning a home is not a RIGHT it is a PRIVILEGE, and folks need to figure that one out.

There is a difference between what you want in life and what you can afford. I guess this goes back to whether Canadians are Financially Stupid, but I am willing to discuss if folks think these alternate lenders are simply offering a service (much like pay-day loan companies and such).

{ 14 comments }

Cheaper Mortgage Rate or Free Banking?

by bigcajunman on October 25, 2011

That was the interesting quandary I was in last week when I went in to talk with my bank about RESPs and such.

Normally when I make an appointment at the bank, I bring a list of things I need to get done and then a few Christmas wishes too. Typically my main wish is to get Free Banking (i.e. no bank fees, or a vacation from fees (yes I already have a PC account, as well)), however this time a co-worker gave me a different idea.

As background normally every time I ask for Free Banking and then the bank representative acts as if I am asking for one of their kidneys (and possibly their spleen), and they say it’s impossible to get that, and such, but will eventually relent and give me 6 months or a year of banking for free (i.e. no monthly service charge). It all plays out as a very badly written soap opera, so I decided this time I’d try for something with more impact, which is the interest rate on my Line of Credit.

Currently most secured lines of credit are pegged at Bank Prime (not the bank of Canada prime) plus 1% or something close to that, so I decided to ask, can that rate be lowered (guessing it wasn’t likely, but again, you never know unless you ask, and the worst thing they can say is No).

The meeting was going very well and I had reached the end of the tasks I needed to get done, so I then figured I’d broach the interest rate subject and asked, “Is there any way to get a lower rate on my line of credit?”. The bank rep did not jump out of her chair screeching obscenities, she didn’t even seem remotely perturbed by the request (which made me concerned that it was an easy NO), she simply typed a few things on her computer and said, “Yes, we can see if we can cut your rate to 0.5% over our prime”.

I believe my mouth dropped open and I blinked a couple of times, but regained my composure enough to say, “Yes that would be very good”. Some forms were filled in, but I believe I have been approved for this rate (no fuss, no muss, no complaining about me wanting an internal organ, all very civilized).

This good news got me scratching my head a little, since free banking costs the bank about $14 a month, whereas a rate cut by 0.5% on my Line of Credit is more than that, so what gives? My guess is that the Service Fee is actual bank income, and is very important to the bank, whereas income from Line of Credit interest (while important) is a more variable value, so losing 0.5% is not as important? I may be out in left field on this one, and if anyone has a better idea, I am open to suggestions.

At the end of it all, I am proven correct, if you don’t ask, the answer is always no.

{ 13 comments }

Interest Rates Stay the Same But Mortgages Rules Get Tighter

by bigcajunman on January 19, 2011

Here Comes Da Recovery!

Bank of Canada announced yesterday that they will not be raising their key overnight rate this month, which is always good news for those who are carrying debt (and bad news for those supplying the money to finance the debts).

The reason given is optimistic globally, and not so much on the Canadian side of things:

The recovery in Canada is proceeding broadly as anticipated, with a period of more modest growth and the beginning of the expected rebalancing of demand. The contribution of government spending is expected to wind down this year, consistent with announced fiscal plans. Stretched household balance sheets are expected to restrain the pace of consumption growth and residential investment. In contrast, business investment will likely continue to rebound strongly, owing to stimulative financial conditions and competitive imperatives. Net exports are projected to contribute more to growth going forward, supported by stronger U.S. activity and global demand for commodities. However, the cumulative effects of the persistent strength in the Canadian dollar and Canada’s poor relative productivity performance are restraining this recovery in net exports and contributing to a widening of Canada’s current account deficit to a 20-year high.

That is an interesting analysis of how Canada may proceed, and some interesting commentary about Canada becoming a consumer country instead of a producer (which it traditionally has been).

A cautionary comment at the end of the release makes you wonder too:

Reflecting all of these factors, the Bank has decided to maintain the target for the overnight rate at 1 per cent. This leaves considerable monetary stimulus in place, consistent with achieving the 2 per cent inflation target in an environment of significant excess supply in Canada. Any further reduction in monetary policy stimulus would need to be carefully considered.

So we still need the stimulus of low interest rates, for now. Good to know, but I suspect rates may start creeping up this summer, so now is the time to start lowering your debt loads, while the rates are lower.

New Mortgage Rules too

Many fin bloggers have been talking about the new Mortgage rules that were introduced in legislation this week as well. The quick overview is (to quote the official release):

* Reduce the maximum amortization period to 30 years from 35 years for new government-backed insured mortgages with loan-to-value ratios of more than 80 per cent. This will significantly reduce the total interest payments Canadian families make on their mortgages, allow Canadian families to build up equity in their homes more quickly, and help Canadians pay off their mortgages before they retire.
* Lower the maximum amount Canadians can borrow in refinancing their mortgages to 85 per cent from 90 per cent of the value of their homes. This will promote saving through home ownership and limit the repackaging of consumer debt into mortgages guaranteed by taxpayers.
* Withdraw government insurance backing on lines of credit secured by homes, such as home equity lines of credit, or HELOCs. This will ensure that risks associated with consumer debt products used to borrow funds unrelated to house purchases are managed by the financial institutions and not borne by taxpayers.

The HELOC one is interesting as well. Is this the same as a Secured Line of Credit (secured against your home)? Any fin bloggers caring to enlighten me, please feel free to comment!

{ 9 comments }

Previous entries