How To: Mortgage Schedule

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 mortgage 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 Schedule

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!


Don’t Pan for Gold in your Cat’s Litter Box

No matter how hard you shake or sift it, you know what you will have, and it isn’t Gold (unless your cat swallowed your Gold Ring or a Krugerrand).

I read that on Postsecret this week and thought it would be a fantastic title. I believe that is an excellent commentary on building up Debt for a better lifestyle, and speaking of interest rates:

Bank of Canada Rates Stay the Same in October 2012

Yes the Bank of Canada decided loose money in Canada is still a really good thing, so they continue on with their existing over night rate of 1.0%, for now. There really wasn’t a big reason to raise rates, since inflation looks under control, however, debt loads are not dropping much either, so there may be a pre-emptive strike by the Central Bank coming in the near future, if you are to believe some of their rhetoric lately.

Core inflation has been lower than expected in recent months, reflecting somewhat softer prices across a wide range of goods and services. Core inflation is expected to increase gradually over coming quarters, reaching 2 per cent by the middle of 2013 as the economy gradually absorbs the current small degree of slack, the growth of labour compensation remains moderate and inflation expectations stay well-anchored. Total CPI inflation has fallen noticeably below the 2 per cent target, as expected, and is projected to return to target by the end of 2013, somewhat later than previously anticipated.

$100 notes/Coupures de 100 $

Get Yer Loose Money, While it Lasts!!! Brown Notes Abound!

Lower than expected? Given the rest of the world’s economies are in the crapper, where would the inflation be coming from? If anything Canada could have a Deflationary period if the strength of the Canadian dollar is finally reflected in consumer goods (why is the Retail Price of all things still more expensive in Canada?).

Policy Changes ?

As a parting threat the Bank of  Canada did say:

Reflecting all of these factors, the Bank has decided to maintain the target for the overnight rate at 1 per cent. Over time, some modest withdrawal of monetary policy stimulus will likely be required, consistent with achieving the 2 per cent inflation target. The timing and degree of any such withdrawal will be weighed carefully against global and domestic developments, including the evolution of imbalances in the household sector.

So be warned, scary monetary policies may be coming soon! The Zombie Bank Rate Apocalypse might be around the corner! Think of the children!!!


CPI at 1.2% for September in Canada 2012

As usual our Friends at Stats Canada published a mixed bag of news with their CPI Report for September 2012, however the CPI (inflation) is back down to low(er) levels at 1.2%, even with food prices and energy prices fluctuating a great deal over the past few months.

Consumer prices rose 1.2% in the 12 months to September, matching the increase in August. Higher energy prices, particularly for gasoline and electricity, led the advance in the Consumer Price Index (CPI) for September. This was tempered by lower year-over-year price increases for the purchase of passenger vehicles and for food purchased from stores.

So inflation is remaining at a stable rate, however, remember that is simply saying that prices are increasing at the same rate (not the prices are not rising).

Energy prices again were the culprit last month for a year over year inflation increase, as Energy prices increased (year over year) by 2.9% which while small(ish) is still twice the inflation level. Gas prices certainly have been all over the place in Ottawa, but they are back down to $1.20 for now (after a big jump around the long weekend, what an interesting coincidence).

CPI Inflation Canada

The CPI for the past little while, with and withOUT energy included in the index

The preceding graph is important to look at and think about. Energy prices also have a nasty effect of rippling into other price increases (since energy rolls into transportation for food and other products). What parts of energy are increasing?

Gasoline prices rose 4.7% in the 12 months to September, following a 2.2% gain the previous month. Compared with August, higher year-over-year price increases for gasoline were recorded in eight provinces, most notably in the Atlantic region.

The cost of electricity rose 6.0% year over year in September after rising 3.4% in August. Increases in Ontario and Alberta continued to lead the rise at the national level.

Yikes, is all I can say to that one. I must admit my electrical bill has been growing steadily for the past few years, and Ontario Hydro promises me that trend will continue, yea?!?

The Actual CPI basket of Goods cost increase over the past little while

This is important to have, as it points out that the CPI measurement is the cost of a basket of goods, that at the start of the index was at 100, and you see the steady increase in cost over time.

Bank of Canada’s core index

These numbers are very important, but of even more significance is how the Bank of Canada measures inflation, as this is the data they will use to decide whether interest rates need to rise or not:

The Bank of Canada’s core index rose 1.3% in the 12 months to September, following a 1.6% increase in August. The slower rate of increase was mostly attributable to smaller price increases for the purchase of passenger vehicles.

So the Bank still has little reason to increase interest rates due to inflation (for now).

The Big Table

Remember that the Stats Canada site has a plethora of really interesting statistics, so go read them and see where all the “talking heads” on TV base their interpretations of where we stand on inflation in Canada.

Consumer Price Index and major components, Canada – Not seasonally adjusted

Relative importance Sept 2011 August 2012 September 2012 August to Sept 2012 Sept 2011 to Sept 2012
% (2002=100) % change
All-items Consumer Price Index (CPI) 100.002 120.6 121.8 122.0 0.2 1.2
Food 15.99 128.2 131.7 130.3 -1.1 1.6
Shelter 27.49 125.7 127.4 127.2 -0.2 1.2
Household operations, furnishings and equipment 11.55 111.6 112.7 113.2 0.4 1.4
Clothing and footwear 5.31 95.0 89.5 93.3 4.2 -1.8
Transportation 20.60 126.3 127.5 128.3 0.6 1.6
Health and personal care 4.95 117.5 119.8 118.5 -1.1 0.9
Recreation, education and reading 11.20 106.9 107.6 107.7 0.1 0.7
Alcoholic beverages and tobacco products 2.91 135.9 137.8 137.7 -0.1 1.3
Special aggregates
Core CPI3 82.15 118.4 119.7 119.9 0.2 1.3
All-items CPI excluding energy 89.92 117.8 118.8 118.9 0.1 0.9
Energy4 10.08 156.0 159.2 160.6 0.9 2.9
Gasoline 5.80 180.4 184.9 188.8 2.1 4.7
All-items CPI excluding food and energy 73.93 115.5 116.0 116.4 0.3 0.8
Goods 47.80 113.5 114.0 114.2 0.2 0.6
Services 52.20 127.8 129.6 129.8 0.2 1.6
1. 2009 CPI basket weights at April 2011 prices, Canada, effective May 2011. Detailed weights are available under the Documentation section of survey 2301 (
2. Figures may not add to 100% as a result of rounding.
3. The Bank of Canada’s core index excludes eight of the Consumer Price Index’s most volatile components (fruit, fruit preparations and nuts; vegetables and vegetable preparations; mortgage interest cost; natural gas; fuel oil and other fuels; gasoline; inter-city transportation; and tobacco products and smokers’ supplies) as well as the effects of changes in indirect taxes on the remaining components. For additional information on the core CPI, please consult the Bank of Canada website (
4. The special aggregate “Energy” includes: electricity; natural gas; fuel oil and other fuels; gasoline; and fuel, parts and supplies for recreational vehicles.

{ 1 comment }

Rates Still at 1% Overnight Rate for Bank of Canada, September 2012

The Bank of Canada decided to keep their key overnight rate 1%, blah, blah, blah… you know the drill.

Things really don’t seem to be changing much this past little while do they?

Why are the rates staying the same? Here are some very good quotes from yesterday’s publication from the Bank of Canada:

“… The economic expansion in the United States continues at a gradual pace. Europe is in recession and its crisis, while contained, remains acute. In China and other major emerging economies, growth is decelerating somewhat more quickly than expected…”

OK, so the major markets of the world are not in very good shape, thus the danger of a massive spending spree (other than in China, where spending is slowing down), is not high.

“…In Canada, while global headwinds continue to restrain economic activity, underlying momentum remains at a pace roughly in line with the economy’s production potential. Economic growth is expected to pick up through 2013, with consumption and business investment continuing to be its principal drivers, reflecting very stimulative financial conditions. Business investment remains solid. There are tentative signs of slowing in household spending, although the household debt burden continues to rise….”

Global headwinds? I really love that turn of phrase, but more interestingly is the increase in household debt burden is now being spoken about by the central bank? Must be pretty bad, I guess?

Does this mean rates are going to stay down for now? I think so, but if there is an actual economic up turn in North America, all bets may be off.

Bank Rate (overnight) Since 2002, Pretty Low eh? And the Beat Goes On….

The graph stops at May, however, it is still the same as it was then, so you get the gist of things.


Haven’t You Changed Banks Yet?

Have you at least threatened your current bank that you might leave if they don’t give you a better deal? Remember I have always said Don’t be Afraid to Make a Change, especially when it comes to your bank.

I must admit that I have not changed banks for a good long time (and given how much I have bitched about TD, you would almost think they might have asked me to go somewhere else), but I also have done my part to get better deals or to complain to get better service.

Right now, you have to work harder to get better deals from your bank, in the area of interest rates, because they are being a little more tight-fisted with credit, but if you don’t ask for a better deal I can give you a 100% guarantee that you will not get a better deal (OK, 99.9% maybe there is a bank that actually treats their current customers better but it isn’t very likely).

I also stand by the statement that if you are going to bargain with your current bank, you had better be willing to vote with your feet and move on, or your bank may not take much notice of you.

Here is an even easier scenario:

  1. Find a new bank that is willing to give you a better deal on your: Mortgage, Debt, Investments, or Services (hopefully more than one of those)
  2. Get them to put it all in writing for you (say you want it for your records).
  3. Go to your old bank with the document and say, “I want this, can you give it to me?”

You shouldn’t do this very often, but if you are fed up with your current bank, it is a good tactic, and if they refuse, simply walk back to the bank that offered you a better deal and say, “Make it So!”.

Michael James also pointed out that most of the time when you do a change to a different bank or financial institution most of the time the new bank will be willing to do most of the work for you (i.e. do the transfer leg work), all you need to do is remember which bills come out of which account auto-magically and you are laughing.

Sometimes a change is what you need, keep that in mind.


%d bloggers like this: