Canadian Personal Finance Blog

Yesterday 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) whereas 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 compouding
• ( (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 actual 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

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!

My major tool in my Financial Planning activities is my computer. I use it to track my spending, I use it to make up financial plans for the coming time, and I use it to analyze my spending habits, all in all a very powerful tool for me.

My wife prefers to use pen and paper because she likes to see the spending and such, and if that is the way you work, I have no problem with that either. It takes a little longer, but maybe when it takes longer you might notice and absorb more information from the data entry side of things.

Financial Tools

My major tools that I use (I am not endorsing these computer tools, I am simply pointing this out to be complete) are:

1. Quicken, first and foremost this is the tool I use to keep track of spending and attempt to report trends and such. A very powerful tool, that I still don’t think I am using completely correctly.
2. Tax software, in this case Quicktax, but only because it is so simple when I do it this way and I can E-file my taxes which is quicker and easier for me. I also use that tool to infuriate myself figuring out what might happen if the Government supported Income Splitting.
3. Excel or whatever spreadsheet you like. I like to extract data from Quicken and then use it for some elementary calculations and forecasts in Excel. Excel has some very powerful financial functions, but make sure you are using the Canadian versions for Interest calculations and such.
4. Powerpoint, to present information easily to my wife or to my banker, if I am going in to try to get Free Banking
5. Internet Explorer or a browser for On line banking, and thanks to my PC I no longer walk into my local branch weekly to get my banking updates, I get them daily on line. This is an amazing capability that we take for granted that didn’t exist 20 years ago. I pay bills that I don’t have to mail in any more either.
6. Firefox or Internet explorer as a research tool, by looking up on Government sites I learn about Tax rules, by reading company’s web pages I learn about good investments and by reading some amazing Blogs, I learn about Finances in general (see my right bar for some excellent financial bloggers).

These tools make Personal Finance for me a little easier to deal with.

Take Care of your PC

As with all tools, you must maintain your PC. Yesterday my PC was taken away, because it was doing suspicious “Virusy” things at work and now I sit at my kids’ computer attempting to get anything done. I am lucky because I have an entire I.T. group to take care of my PC (for now), most folks do not, so here is my views on the minimal I.T. tasks you should be performing on your PC (this advice I do actually stand behind, because this is an area I think I have some expertise):

1. Backups, backups, backups! If there is any data on your computer that is important, you must make sure it is backed up in some fashion. There are many ways to do backups
   1. Back data up to CD’s or DVD’s if you have a CD writer. If you don’t have a CD or DVD burner, go get one NOW.
   2. Norton and other services are now offering network based back up services that you can subscribe to. I would read their agreement closely to see what re-courses you have if your secret data is compromised by their backup system, but this is still another way to go.
   3. If you have TWO computers at home, make a BACKUP directory on each, and back up important data on the other machine. At least if one fails you still have your data.
   4. Floppies? Well, if you think that is the way to go, knock your socks off, but I do not recommend it.
2. Restore! Yes this is just as important, you must test that you can restore data from your backup system! Backups are useless if you cannot retrieve your data. If you have important data already backed up on floppies, transfer it to DVDs or Cd’s or something. Also check the state of your old important backups, because the Media it is on, does degrade.
3. Anti-Virus software, get something, ANYTHING, but do not think that your Internet Access provider or your good ideas are going to stop viruses from getting on your computer. Use Norton, McAfee, NOD or others, but use something or your machine will become compromised.
4. Anti-Spyware software too, most anti-virus systems now come with Spyware checks as well, important to get this kind of software, or you are going to end up selling Herbal Viagra from your PC.
5. Clean your computer. Vacuum out the fans at least, or take it to a reputable local computer shop to have it cleaned (ask for references from the local shop, if you don’t know the owner). I had a fan break on my CPU, due to dust and my machine was gone for a few days.

There are many other tricks of the trade you should think about, but this is my minimum list. If anyone else cares to comment on other important tasks, please feel free, as I don’t think this list is exhaustive, just a good starting point.

If you are carrying debt with a bank, you were just given a gift (a huge gift if you live in the U.S.), because the banks have lowered their interest rates yet again on loans. What will you do? Will you simply lower your monthly payment to match what the banks ask you and use the extra money elsewhere, or will you exert financial intelligence on this debt and attempt to pay it off faster?

I propose a very pedagogical example for your reading pleasure.

Scenario 1: Original Debt

If you were say a Financial blogger and you had say, $13000 on your line of credit with your banking institution, your pay off schedule for that debt might look like this:

Great easy to understand and see, but wait, the bank just dropped my interest by 1/4 %!!!

New Debt Schedule with Lower Interest Rate

Well how would that look then if I was only paying 6.25%?

That’s kind of cool, I have saved $60 in interest over the length of the 3 year loan, that is nice.

The twist

The twist would be continuing to make the payment I would have made at 6.5%?

So what, I hear you say? Well I have saved about $5 in interest payments and my final principal payment is about $46 cheaper, so what? Um, that isn’t a good thing, you kept you spent $53.48  and you saved over $60 (or so) over 3 years, but you are paying ths debt down faster! If you had put that money in a bank account, would you have got that kind of payback?

Don’t just play with the cards dealt you, if you have budgeted to pay off a loan with a specific payment amount and interest rates drop, then keep up the original payment amount, you will speed up your Debt Pay down!