How To Do a Mortgage Schedule

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 mortgage 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 Schedule

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.

How to create the sheet?

How to create a mortgage schedule using Spreadsheet software

  1. How to create a mortgage schedule sheet

    Open an Excel (or whatever spreadsheet software you like) document.

  2. Date

    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))

  3. Real Monthly Payment

    Column 2 shows the real monthly payment
    We already calculated that using the PMT() function

  4. Interest Payments

    Column 3 calculation of how much each payment goes towards the Interest
    This is = Previous balance * Interest rate for that payment period

  5. Principle Payment

    Column 4 calculation of how much each payment goes to the principle
    Total payment – Interest Payment = Principle payment

  6. Remaining Balance

    Column 5 the remaining balance on the loan
    Previous Balance – Principle Payment

  7. Total Interest Paid on Loan

    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  Interest5.950% 
Monthly Payment$636.84  Years25  
DatePaymentInterestPrincipalBalanceTotal 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!


We did it! Let’s Splurge!

Don’t get me wrong it is important to reward yourself if you have achieved a goal in life (even though the Protestant Work ethic, which I grew up with suggests the statement, “What do you want? A Medal? You did what you were supposed to!” is more apropos), but remember that the reward should be tempered with the nature of the goal that you have achieved. Let’s splurge is not the sentiment you should have.

What do I mean? If you have managed to battle a major illness, and are healthy again, going out to dinner with a loved one to celebrate is certainly fair payment for that achievement, however there are countless examples which I will now elaborate on to show that sometimes the reward is more of a punishment:

  • You have not bought lunch at work for the week, so you decide to celebrate with your wife on Friday evening by going out to a nice restaurant for dinner. That is much like an AA member celebrating their 100 day chip with a bottle of champagne, makes no sense to me.
  • You have paid off your credit cards completely, so you decide to celebrate you will take your family on a well deserved vacation. Unless you have been saving for this vacation, and are planning on paying for it with cash, this is the same as picking the scab off a healing wound. Find something cheaper to celebrate
  • I have finished my Christmas shopping and have come in under budget so I’ll take the left over money and buy myself a nice gift. Why not take that money and pay off debt? Maybe donate the money to charity, at least you get a tax write off with it?
  • You have paid off your car finally, and decide it is time to trade it in and get a new one. OK, this really does happen a lot folks, why not keep the car and enjoy not having payments? If the car is a lemon then I could see some reason to the idea, but if the car is working fine, why do you need to get back into debt for an asset that loses value?

Let’s Splurge Isn’t a Bad Thing ?

I guess I am sounding a little curmudgeonly on this list, but please keep in mind that spending money freely for some of us is like a drug, the more you spend, the more you want to spend and this is where a lot of us get into our financial dilemmas.

Congratulations if you reach your goal, but celebrate frugally and you will feel even better later as well.


Pay Yourself First : AutoMagic Bill Payments

How do You Pay Yourself First ?

I was reading another site and saw that someone has written a book or is charging folks to learn how to make their personal finances automatic so in response to that, here are some simple ideas (for free (and thus worth every penny you paid for it)) to automate bill payment for your personal finances. I offer these ideas as a possible solution, your mileage may vary, and with all personal finance you should make sure this fits into how you deal with your money (and as always pay yourself first ).

Yesterday I outlined a simple way to make sure we follow one of the pillars of personal finance Pay Yourself First. This methodology is automatic, and hopefully helps folks to hide money away for a rainy day.

Automating Bill Payments

Pay yourself first

Hocus Pocus for Finances
Image courtesy of Photo by digitalart at freedigital.net

How is this possible? Well you may ask that good reader, this heuristic is simplistic in it’s details, so please make sure you understand what the methodology outlines before trying to implement it.

First, find all of those monthly bills that you pay, excluding your credit card bills (given those bills can have wildly different balances each month, I like to exclude those from this system). Some examples of these bills would be:

  • Electric Bill (or Hydro as we would say in Quebec)
  • Water & Sewers Bill
  • Heating Bill (if you don’t heat with electrical
  • Home Phone Bill
  • Cell Phone Bill
  • Mortgage and/or Rent

This list of bills, you can figure out what your average monthly outlay is, assuming all these bills are close to the same amount every month (if any of these bills vacillate wildly over the year, you might want to exclude it from this method, or make provisions for this happening).

Bank Accounts

We had two bank accounts from yesterday Working and Savings First, we now create a third account which we can call Bill Payment. This account is effectively a working capital account, so it should not have penalties for number of transactions, and such, because money will go in and out of it a great deal.

Once this account is set up, you need to set up another per pay cheque automatic transfer to this account, because this is where your bills are going to be paid from.

Some Simple Arithmetic

How do we figure out how much to be transferred? Look at how much you spent on the bills over the past year, take into account any increases in service fees, and then take that amount and divide it by the number of pay cheques you will have over a year (i.e. if you are paid twice a month, divide by 24, if you are paid bi-weekly, divide by 25.5, etc.,). This amount is what you should transfer to the Bill Payment per pay cheque (at a minimum). I added at a minimum, because it is best to have a float in this account, in case there are unexpected charges or increases in your bills every year. It would even be better to start this account with a float of at least 1 pay cheque worth of payments, in case of problems.

So now you have solved half the issues with monthly bill payments, you have taken that money away automatically, before it is in your Working account.

How do I pay these bills monthly? That can be dealt with in a few ways, and can be automated as well:

  • You can set up with each company a direct withdrawal of money from your Bill Payment account. I really hate this idea, but include it for completeness. I never like anyone taking things directly from my bank accounts, but this is one way to make your bill payments, automagic.
  • Set up payments from the Bill Payment account to every one of your bills that pay 2 days before your bills are normally due every month. This is a little tricky because you have to cheque with each bill, to see when they are normally due, and then set up a recurring bill payment for that bill using your On Line Banking or Phone Banking system (the same way as we did for automatic money transfers). Doing it two days before means you are less likely to be tripped up by weekends and holidays.
  • You can try to pay your monthly bills bi-weekly (this one is tricky, some firms really get pissed off when you do this). Simply pay a portion of every bill on each payday. You need to calculate what 1/2 of the bill is normally and simply pay it every pay cheque. This will mean that eventually you will get ahead of your bill payments (if you are paid bi-weekly). I tried this for a while and had two companies phone me saying they didn’t like this, and told me to stop doing it (very interesting).

This heuristic can work, but I would strongly suggest you do it manually for a month or two, to see if it can work for you, and if it seems to be OK, you can automate it after you are done.

If you automate your saving (i.e. pay yourself first) , and automate most (if not all) of your bills, all you need to do then is control your day to day spending, which might be a little easier if you only get a smaller amount of money.


You Need Only Ask

With my house filling up for the summer, and my Internet access about to be overrun again, I decided it would be a good idea to upgrade my service to have a higher bandwidth ceiling (given how high the penalties are for going over that ceiling). I also decided it was time to be my own Ellen Roseman, and stand up for my rights as a consumer, so I was going to ask for a discount too.

Customer Retention

No, but many firms think this IS Customer Retention

I called Rogers, who is my Internet (and Cable) provider, and I spoke to a service representative and took care of the upgrade of my package to a higher bandwidth ceiling, and that all rolled along quite simply.

I then got up the courage to ask, “Can I please talk to a customer retention agent?”. The customer rep I spoke to asked why I wanted to, and I pointed out that I had been a customer in good standing with Rogers and wanted to see if there was anything they could do to lower my bill. The Customer rep agreed that it never hurts to ask so she connected me to the Rogers Customer Retention line (it’s not called that, but I forgot what the exact title was).

I was ready with my story, which was that Bell has been bombarding me with weekly letters outlining their new fibre to the home product and how cheap it was going to be ($40/month for the first year), and what would Rogers do to keep me as a customer. I was very polite with the young lady and said I really liked the Rogers product, but I would like it even more if it was cheaper.

To my utter surprise the young lady said, “Sure we can lower your Internet rate by 30% for the next year if you would like?”. I was flabbergasted, because I expected a little more argument, but no, I simply asked in a polite and calm manner and was given a large discount. I had to agree to sign a “contract” for a year, and there are penalties if I cancel the service early, but given I wasn’t really planning on changing in the first place, one phone call saving me 30% a month was just fine by me.

Ask and Ye Shall Receive

The moral of the story, if you think you are paying too much, simply ask for a discount and see what happens. It seems to work for banking and now it seems to work for Internet access too.


Gosh Darn CPP and EI 2010

Given that we start a new year, all folks who receive paycheques (I believe the Japanese term is Salary-man) get to start paying CPP and EI premiums¬†again. For a lot of folks, they are just deductions that appear on every pay stub, but for folks who make over a certain amount, this deduction occurs sometime in the year, and after that, they get a “virtual raise” given they do not have to pay these deductions for the rest of the year.

Michael James is a lover of numbers (but not a numerologist, luckily) and pointed out one day how easy it is to approximate how much someone makes by when they stop paying EI premiums (and you’d be surprised how many people talk openly about the fact that they have stopped paying the premium (in fact I had just told Michael James that very fact)).

It’s a pretty simple game to play and well worth a couple of minutes to create a miniature model to figure this thing out.

Meet Jack

Jack gets paid bi-weekly and works as an employee of XYYZZ. He gets paid a regular salary (assume no bonuses and such), so if we list the month, Jack tells us, “I stopped paying EI premiums this month,” we can then approximate how much Jack makes in salary. We know from the EI website that your premium is 1.73% of your insurable earnings (the maximum insurable earnings were $42,300 in 2010).

EI Premium1.73% 
MonthEffective PaysApprox Gross Income

Just remember, what you tell folks can sometimes have more meaning than you might think.

Past CPP & EI

Yes, it is a topic I write about, as it is essential to me. Here are a few from the past years to compare and contrast (hint see how much CPP has gone up).


%d bloggers like this: