Canadian Personal Finance Blog

This is one way to do this spreadsheet, I used Excel but I am sure other methods are out there that may be more accurate. I use this spreadsheet as an estimate to figure out how much money I still owe on my mortgage and it gives me a warm fuzzy counting the number of days left in my mortgage. Today, I will explain the main function used to estimate your periodical mortgage payments and tomorrow I will show the Mortgage Pay Out Schedule (I’ll include an example template tomorrow as well).

Mortgage Payment Estimation

As with most calculations you will need the following information

1. Total cost of your mortgage
2. Interest rate you will be paying for the mortgage
3. How many years your mortgage is amortized over (25 years?)
4. How may payments you are going to make in a year (25.5 for bi-weekly, 12 for monthly, etc.,)
5. How often the rate is compounded
6. Starting date of the Mortgage

Put each of these values in a cell on a blank spreadsheet. The most important function to use in this worksheet is the PMT() function. In Excel the PMT() function figures out how much your payments are going to be (now the function does this for American Mortgages and really should only be used as a guide, since I have never had a Bank and this function match given the same data.

To use the PMT function is simple it is:

PMT(  X ,  Y , -Z )

Where

X = Interest Rate (yearly) divided by number of pay periods in the year (e.g.  4.75%/25.5 periods)

Y = Total Number of payments which is number of years of the mortgage multiplied by number of payments per year

Z = Total Mortgage to be repaid

Use this and you will get a good estimate of how much your mortgage payments will be (either bi-weekly or monthly).

That alone is very useful if you are trying to figure out how much how you can afford, in either bi-weekly or monthly payments.

Example

Say me and Mrs. C8j decide to really downsize and because our kids ate us out of our savings, we still end up with a $100,000 Mortgage at 5.5% and we want to pay monthly payments on the mortgage but only want a 15 year term.

The cell on the spreadsheet would look like:

=PMT( 5.5%/12, 15*12, -100000)

which would give us a monthly payment of $817.08

This is close to how much we will make in Mortgage payments per month (remember this is an estimate your BANK will tell you what they will charge). This one function can also be used for car loans and many other things, which makes it a very powerful tool.

Tomorrow, we do the mortgage schedule.

Last year, we bought a shredder because I kept taking stuff to shred to work, and felt I had enough stuff that appears in my house that I do not want to leave my house in the garbage where anyone might look at it, and I also enjoying destroying things.

What kind of things do I enjoy running through my shredder?

• Most of the credit cards I have keep sending me “cheques” that I can use to make cash withdrawals and get charge 20% interest from the moment the cheque is cashed. These things have on them my credit card number on it, and thus if someone got a hold of it in tact, I would be in very deep trouble. They shred very nicely.
• Credit Cards that I do not use, I have a bunch of credit cards that I do not use, that I am going to cancel, but I have already shredded them. They shred very nicely indeed.
• I also shred many old financial documents that I don’t need to keep, don’t want to burn and don’t want to leave out in my garbage either.
• Canceled cheques and the like as well.
• Old pay stubs.

I put my shredder in the same class as my safety deposit box as being something important in my Financial Planning, an important aspect of safety and security in finances.

New Housing Prices Still Rising

Just not as fast, and it’s acceleration is dropping. This means if you are buying a new house and you waited from last year it is going to cost more, it’s just not getting as expensive, as quickly.

Year-over-year growth in new housing prices slowed for a second consecutive month in March. This deceleration continues a downward trend that started in September 2006, due mainly to the softening market in Alberta.

Contractors’ selling prices rose 6.1% between March 2007 and March 2008, a slightly slower pace than the 6.2% year-over-year increase posted in February 2008.
(…)
Elsewhere in Ontario, contractors’ selling prices were 4.5% higher in Toronto and 3.1% higher in Ottawa–Gatineau compared with March 2007.

Personal Finance Update

So my attempts at stopping my spending at work begins anew. No spending over the last few days and that is a good beginning. De-Crapification continues as well, with more crap to go very soon.

The topic of what my household spends money on has been discussed before, but here is as a percentage of my entire expenditures, what my family spent it’s money on last month (April 2008)

• Tax 25.24%
• Recreation 19.68%
• Groceries 13.11%
• Insurance 8.88%
• Mortgage Int 5.55%
• Utilities 3.94%
• Auto 3.54%
• RRSP 3.04%
• Commuting 2.70%
• Savings (non RRSP) 2.28%
• Other 12.04%

These numbers are relatively trustworthy since I use Quicken fairly religiously, and my expenses are mostly tracked there (and we don’t really use a lot of cash in our household).

Financial Scary Things

Recreation being 20% of the money I spent things on last month. That is down to my kids recreational activities (basketball). That is one hell of a lot of money, and that is not registration fees or anything like that, that is money spent on going to and coming from and being at tournaments. That one scared the nickels out of my piggy bank.

Why was Insurance there? I pay for my house insurance in one payment every year, and I had to pay it last month. This month it will be big again, because I have to pay my car insurance as well. I also have on going Life Insurance costs that come up for my wife and myself (that was 9% of my spending).

Commuting, is actually the cost of bus passes for my daughters to be able to go to school, so that is not an insignificant amount of capital spent either.

Financial Oddities

The money I am paying in mortgage interest costs is only 6% of my expenditures in a month? Wow, that is really not a whole heck of a lot in comparison to say the amount I spend on Groceries (13%). I honestly don’t know if that is good or bad, it’s mostly confusing, I guess. Anyone care to hazard a guess, I am open to interpretations.

I am also putting 5% away in savings of sorts, in comparison to my total expenditures for the month. Since you don’t know how much I spent last month (no I am not saying what that number is), it’s kind of hard to figure out if this is a good or bad amount (it actually isn’t too bad, and I have to keep remembering that one).

Financial Insights

There are areas where spending can be curtailed, and there will be areas where spending will stop for a while too (specifically recreation), so that is a good thing. My guess is the Auto side of things is going to go up with the cost of gas continuing to go up as well.  I think also this is not reflecting debt repayments either (as this is not a category in Quicken) except for specific mortgage interest as a cost basis point. I’ll need to look at that more as well.

Any comments or insights from my readership appreciated.