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.

This week I took a short trip for my company, and I was astounded at how much it ended up costing, just in flight charges.

My company used to have a travel department, where people booked trips for you, found the best deals, but also discussed with you about how you wanted your travel to work. This task was then outsourced and is now being run by Amex Travel, which has a Web Portal that all travel booking must be done through. The booking of the trip is chocked full of questions and then it gives you a staggering array of choices and decisions about cheaper flights and such, which I usually ignore, and then get a nasty note saying I am not playing nicely, but that is for another posting.

This week’s one day travel’s costs were:

• Air Fare alone was $356  which isn’t too bad given I booked fairly late, and I was only doing a day trip.
• Add on a $10 Air Transport Tax
• Add $20 in GST
• Add $35 Airport Improvement Fee
• And you end up with $420.94 total cost
• Whoops forgot there is an $8 service fee that Amex travel throws on top of this, that you only see on your credit card later.

Fine, I had to travel and I had to pay, so I booked it. Away we went, our meetings went well and we finished early, so then we tried come home on an earlier flight. There was room on an earlier flight, but due to the type of the flight booked, I had to pay a $55.00 re-booking fee on top of all of this.

I am in the wrong business, airline travel has more hidden fees and tricks than the Mutual Fund business (OK maybe not, but it feels that way). Luckily I didn’t pay for this out of my own pocket, but now I know why I don’t fly places any more.

I grow weary of losing money $2 at a time on all the service fees thrown on top of the original price of things. Can’t someone just quote me a price and then not kill me adding more to it?

Back in 2005 just when I was starting to blog, I never really knew what I was going to write about (nothing much has changed), so I wrote about the system I put in place to ensure that my kids got their allowances.

As a follow on to the story my oldest child is now 18, so I no longer will be allowed to directly access her bank account any more (something to keep in mind).

Real World Example: Kids Allowances

OK, so back to what this blog is about, real world financial ranting.
For the longest time my wife and I tried to get the kids on an allowance, so that they could learn what money is, how it works and some responsibility, but inevitably, we’d forget for a couple of weeks, try to catch up and eventually just gave up (much to the kids chagrin). Interesting, we were trying to teach the kids responsibility and all it did was show how irresponsible their parents were (now THAT is ironic).

About 6 years ago I was in the TD on one of my yearly visits, getting my bank fees waived for a year, and get them to fix something they had screwed up (I think it was my mortgage that year), when I asked about kids’ bank accounts. My brother sends the girls money every year, and we had got to the point where we didn’t want to just buy them toys with it. The poor woman who’s life I was ruining for the day, said the accounts could be opened then (since the kids had SIN numbers), and the accounts would show up “under” my account on my on line banking.

A day or two later, a light went on in my head. I called the bank on the phone lady (who I now call once a year, because I do most of my banking on line, but couldn’t figure out how to do what I wanted). I asked her to set up weekly transfers from my account to my kids accounts, thus assuring that the money was paid every week (whether I remembered or not).

Well, it has worked, the kids get their weekly allowances AND they actually do things like:

• Buy clothes that they really want
• Have somewhere to put their uncle’s money and can then buy what they want
• Buy presents for their friends birthdays (that one shocked me the first time it happened).

So it seems this experiment has worked, chalk one up for me.