Canadian Personal Finance Blog

Archive for June 5th, 2008

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!