Bank of Canada

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 }

Inflation at 0.4 % For April in Canada

by bigcajunman on May 21, 2013

Just before the long weekend our friends at Stats Canada published their monthly Consumer Price Index review and they claimed that Inflation for the last 12 months (ending in April) Inflation ran at 0.4%, to which I call, Poppycock! (apologize for the potty mouth).

Gas prices are again the reason that prices are allegedly not going up that fast, however, we have also seen this past weekend, Gas prices jumping 7 cents a litre for no real reason except a need to gouge the hell out of consumers on a long weekend. Oil prices have not moved in the past few months, yet somehow there is a Gas shortage? We are desecrating the environment in Alberta that would make the Leaders of the Industrial Revolution wince, yet gas prices are going up? Wow…

OK, enough of a rant on that, here is a graph to show inflation with and without Gas included, which suggests our reliance on fossil fuels is not dwindling as much as the Granola Crunching Generation would hope:

Consumer Price Index Changes with and without Gasoline

So as we can see Gasoline is still the most important part of the Consumer Price Index. Want to see just how wacky Gasoline prices have been over the past little while?

Up to 25% swings in price changes? Who is Making these Changes? The Market?

Food prices continue to track at about 1.5% year over year price increase, while clothing continues to drop (for all you lovers of clothes produced in 3rd world sweat shops (as opposed to the 20th century where it was North American sweat shops)).

If you look at the seasonally adjust CPI, we in fact had a drop in CPI in April:

CPI Dropped (if you include a Seasonal Adjustment)

Bank of Canada’s core index

As we know the Bank of Canada has their own way of measuring the CPI, but with their “slide rules” they have calculated inflation to be well below their threshold of excitement:

The Bank of Canada’s core index rose 1.1% in the 12 months to April, following a 1.4% increase in March.

On a monthly basis, the seasonally adjusted core index posted no change in April, after rising 0.2% in March.

The Big Table

As usual I include one of the big tables from Stats Canada to help you comprehend where you are spending more:

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

	Relative importance¹	April 2012	March 2013	April 2013	March to April 2013	April 2012 to April 2013
	%	(2002=100)			% change
All-items Consumer Price Index (CPI)	100.00²	122.2	122.9	122.7	-0.2	0.4
Food	16.60	130.1	132.4	132.1	-0.2	1.5
Shelter	26.26	126.6	128.0	128.2	0.2	1.3
Household operations, furnishings and equipment	12.66	112.6	114.7	114.3	-0.3	1.5
Clothing and footwear	5.82	95.3	95.3	94.7	-0.6	-0.6
Transportation	19.98	131.3	129.5	128.6	-0.7	-2.1
Health and personal care	4.93	118.9	118.3	118.6	0.3	-0.3
Recreation, education and reading	10.96	105.4	105.2	105.2	0.0	-0.2
Alcoholic beverages and tobacco products	2.79	137.7	139.8	140.1	0.2	1.7
Special aggregates
Core CPI³	84.91	119.7	120.9	121.0	0.1	1.1
All-items CPI excluding energy	91.44	119.1	120.0	119.9	-0.1	0.7
Energy⁴	8.56	161.0	159.9	158.0	-1.2	-1.9
Gasoline	4.62	192.9	186.5	181.3	-2.8	-6.0
All-items CPI excluding food and energy	74.85	116.6	117.2	117.2	0.0	0.5
Goods	48.18	115.3	115.4	114.9	-0.4	-0.3
Services	51.82	129.1	130.3	130.3	0.0	0.9

1.2011 CPI basket weights at January 2013 prices, Canada, effective February 2013. Detailed weights are available under the Documentation section of survey 2301 (www.statcan.gc.ca/imdb-bmdi/2301-eng.htm).

2.Figures may not add up to 100% as a result of rounding.

3.The Bank of Canada’s core index excludes eight of the CPI’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 (www.bankofcanada.ca/rates/indicators/key-variables/inflation-control-target/).

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 }

Inflation Back to 1.0% for March in Canada

by bigcajunman on April 22, 2013

Our friends at Stats Canada published their monthly Consumer Price Index (for March 2013) Report, and with Gasoline prices moderating, we are back to inflation of 1.0% for the year ending in March 2013.

CPI with and Without Gasoline Over the Past Little While

Our friends at Stats Canada said:

The slower increase in the CPI was mainly the result of gasoline prices, which fell 0.3% on a year-over-year basis in March, after rising 3.9% in February. Provincially, gasoline prices declined year over year in seven provinces.

Never ceases to amaze me that gasoline prices are so random across Canada.

A nasty spike in Fresh Fruit ( 8.7% up) and Fresh Vegetables ( 7.2% up) too, which suggests we should all keep eating junk food ? (NO!)

As we can see the increase in the CPI over the past little while:

Seasonally Adjusted Inflation over the Past Little While

Bank of Canada’s core index

Remember that the Bank of Canada measures inflation is just a little bit different way, however, the good news is, it continues to be low (so one less reason to raise bank interest rates too):

The Bank of Canada’s core index rose 1.4% in the 12 months to March, matching the increase in February.

On a monthly basis, the seasonally adjusted core index increased 0.2% in March, after increasing 0.4% in February.

The Big Table

What are the big tables saying where are money is worse less? Just have a look at this one:

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

	Relative importance¹	March 2012	February 2013	March 2013	February to March 2013	March 2012 to March 2013
	%	(2002=100)			% change
All-items Consumer Price Index (CPI)	100.00²	121.7	122.7	122.9	0.2	1.0
Food	16.60	130.0	132.9	132.4	-0.4	1.8
Shelter	26.26	126.6	127.9	128.0	0.1	1.1
Household operations, furnishings and equipment	12.66	112.7	114.3	114.7	0.3	1.8
Clothing and footwear	5.82	94.8	91.4	95.3	4.3	0.5
Transportation	19.98	129.5	130.3	129.5	-0.6	0.0
Health and personal care	4.93	118.3	118.6	118.3	-0.3	0.0
Recreation, education and reading	10.96	104.9	104.7	105.2	0.5	0.3
Alcoholic beverages and tobacco products	2.79	137.5	139.4	139.8	0.3	1.7
Special aggregates
Core CPI³	84.91	119.2	120.6	120.9	0.2	1.4
All-items CPI excluding energy	91.44	118.7	119.7	120.0	0.3	1.1
Energy⁴	8.56	159.5	160.1	159.9	-0.1	0.3
Gasoline	4.62	187.0	186.1	186.5	0.2	-0.3
All-items CPI excluding food and energy	74.85	116.1	116.9	117.2	0.3	0.9
Goods	48.18	114.8	115.2	115.4	0.2	0.5
Services	51.82	128.6	130.1	130.3	0.2	1.3

1. 2011 CPI basket weights at January 2013 prices, Canada, effective February 2013. Detailed weights are available under the Documentation section of survey 2301 (www.statcan.gc.ca/imdb-bmdi/2301-eng.htm).

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 (www.bankofcanada.ca/rates/indicators/key-variables/inflation-control-target/).

4. The special aggregate “Energy” includes: electricity; natural gas; fuel oil and other fuels; gasoline; and fuel, parts and supplies for recreational vehicles.

{ 2 comments }

Loose Money, Trudeau-mania II, Nut Bags and Friday #ShoutOuts

by bigcajunman on April 19, 2013

If you missed it on Wednesday the Bank of Canada continued to keep their key over night rate at 1%. I have given up writing about rates until they actually change, and that doesn’t look like it might happen any time in the future. To quote the B of C:

With continued slack in the Canadian economy, the muted outlook for inflation, and the constructive evolution of imbalances in the household sector, the considerable monetary policy stimulus currently in place will likely remain appropriate for a period of time, after which some modest withdrawal will likely be required, consistent with achieving the 2 per cent inflation target.

In other words, we don’t dare screw around with this economy or it will fall over like a badly built house of cards. Given the latest employment numbers, I think I agree.

Trudeau-mania II (this time he’s a stripper) was launched this past weekend. I must ask, when did Canadian Politics become like a 4th grade playground? It used to be there would be a grace period before there were too many barbs aimed at the new leader, the Tories had attack ads up as Trudeau was making his acceptance speech? Come on guys, give him a chance to screw up before you start calling him unqualified!

We lost a voice of my youth with the passing of George “Pat” Summerall a football announcer who fought his demons and managed to battle them to a draw. He and John Madden are the voices of football for me (and John Facenda), games are just missing a little of his magic these days.

The 800 lb. gorilla in the room this week is the Bombings in Boston and now Ricin-laced Letters in Washington, I guess the nut bags thought life was going a little too smoothly and they decided to make life that much more interesting? I think Craig Ferguson summed it up best when he said, “… is anybody else sick of this sh*t?…”, yes Mr. Ferguson, I am. Makes you wan to back to Medieval remedies like drawing and quartering, however, that would make us as bad as the Nut Bags.

My Weekly Recap

A week that more likely than not will live into infamy but some other topics as well:

My Writings for Week Ending April 19th

Spring has Sprung, and the Grass is Growing, and so is my Lawn

Best of: The Safe Deposit Box, Our Friend

However, remember that safe deposit box fees won’t be tax exempt next year!

Lifestyles with Boomerang Children

What happens when your kids go away and come back? Please tell me!!!

The Marathon

I did go off topic with this one, but it’s my soap box and I feel writing about someone like John is important.

You Aren’t Poor

Mrs. C8j likes to point out that she suggested this topic, if you were curious (so if you don’t like it blame her).

How Do You Eat an Elephant?

No I am not talking figuratively, simply a hackneyed metaphor used by financial planners these days.

Links for the Week

Given it was Tax week in the US this week, and it will be tax time real soon in Canada, maybe it’s time to think about your taxes :

Other Bookkeeping

Very Loose Money

Remember my RSS feed is available too, and I have added an RSS Comment Feed as well. Have a look at my micro-blog on Twitter, where you can see a whole plethora of good articles and pithy comments by me as well. Twitter feed where I re-tweet many great articles by some of my featured writers (and make the occasional odd or off colour commentary on life (in 140 characters or less)). I am also on reddit, Tumblr, Pinterest and other Social Media sites (look for the BigCajunMan userid) as well. If you have social media accounts, don’t forget to vote for my posts (see the nifty dashboard on the bottom of each article, where you can cast your votes). As they say in Quebec, vote early and vote often! This site is iPhone Friendly (and Android , iPod Touch and iPad Friendly), enjoy it on the go, in a readable format for the device. If you are reading with an iPhone or Android device, drop me a comment and tell me if this needs any improvements. This site is also in the Kindle Blog list, if you are interested.

{ 6 comments }

Inflation Up a Little in February in Canada

by bigcajunman on April 2, 2013

Back on Wednesday last week (hey I’ve taken some time off, cut me some slack), our friends at Stats Canada published their Consumer Price Index Report for February 2013 and it’s news continued to be encouraging, in that inflation continues to run at a relatively low 1.2% (year over year).

Last month the CPI rate was at a petit 0.5% year over year so this is a 140% jump over last month’s rate, however it is still well below the traditional 2.0% HOLY CRAP INFLATION Line.

Inflation in Canada for Past Little While

CPI Yearly Rate for the Past Little While

That big jump at the end of the graph actually does look quite dramatic, doesn’t it?

Who was a big culprit in this steep jump? You guessed it gasoline, to quote our friends at Stats Canada:

Gasoline prices advanced 3.9% year over year in February following a 1.8% decrease in January. On a monthly basis, gasoline prices rose 8.4% in February, the largest monthly increase since May 2008.

I think I could have guessed that one without reading the report, given the gouging I have seen in the Ottawa area for Gas. The dramatic differences by area is interesting too (in Ottawa Gas yesterday was $1.24 per liter, in Carleton Place it was $1.15 and in Peterborough it was $1.27).

If you want a much more depressing picture, have a look at this one:

Seasonally Adjusted CPI for the Past Little While

Sad eh? Remember the rate started a while ago, but it sums up nicely how the price of everything has gone up!

Bank of Canada’s core index

Remember our amigos at the Bank of Canada do their inflationary calculations in a different way:

The Bank of Canada’s core index rose 1.4% in the 12 months to February, following a 1.0% advance in January.

On a monthly basis, the seasonally adjusted core index rose 0.4% in February, after increasing 0.1% in January.

So the advance in this index’s increase is only 40% higher, doubt this will be the reason the B of C might tighten their purse strings (but I have been wrong before).

The Really Big Table

The Big Tables are where you really see why things are so much more expensive, so check them all out at Stats Canada, but here is a good one to whet your appetite:

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

	Relative import¹	Feb 2012	January 2013	February 2013	January to February 2013	February 2012 to February 2013
	%	(2002=100)			% change
All-items Consumer Price Index (CPI)	100.00²	121.2	121.3	122.7	1.2	1.2
Food	16.60	130.4	131.6	132.9	1.0	1.9
Shelter	26.26	126.9	127.8	127.9	0.1	0.8
Household operations, furnishings and equipment	12.66	112.8	113.5	114.3	0.7	1.3
Clothing and footwear	5.82	91.9	87.9	91.4	4.0	-0.5
Transportation	19.98	127.8	126.7	130.3	2.8	2.0
Health and personal care	4.93	118.4	118.5	118.6	0.1	0.2
Recreation, education and reading	10.96	103.7	103.7	104.7	1.0	1.0
Alcoholic beverages and tobacco products	2.79	136.6	138.9	139.4	0.4	2.0
Special aggregates
Core CPI³	84.91	118.9	119.6	120.6	0.8	1.4
All-items CPI excluding energy	91.44	118.3	118.7	119.7	0.8	1.2
Energy⁴	8.56	156.9	152.8	160.1	4.8	2.0
Gasoline	4.62	179.2	171.6	186.1	8.4	3.9
All-items CPI excluding food and energy	74.85	115.6	115.9	116.9	0.9	1.1
Goods	48.18	114.1	112.9	115.2	2.0	1.0
Services	51.82	128.2	129.6	130.1	0.4	1.5

1.2011 CPI basket weights at January 2013 prices, Canada, effective February 2013. Detailed weights are available under the Documentation section of survey 2301 (www.statcan.gc.ca/imdb-bmdi/2301-eng.htm).

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 (www.bankofcanada.ca/rates/indicators/key-variables/inflation-control-target/).

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 }