How To Do a Mortgage Schedule

in Bank Rates, Monthly Payments, Mortgage Rates

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.

The sheet layout is the following:

  • 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 real 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
Mortgage Schedule
Mortgage Amount $100,000.00 Interest 5.950%
Monthly Payment $636.84 Years 25
Periods/Year 12
Term 300
Start 01-Jun-08
==========================================================================
Date Payment Interest Principal Balance Total 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!

{ 8 comments }

  • Denis May 24, 2013, 7:46 PM

    From 79k and up in various places along the coast, but here in Vic you are looking at 500+. Oh, and don’t forget about mooring and sanitation fees! Could be 600+ / month. Lotus Land North is Stupid expensive for some kind of shelter. That is why I suggested that cardboard box as a starter home! lol

    Reply
    • bigcajunman May 24, 2013, 8:34 PM

      You need to get a job that gives you a living expense? Either that or get appointed as a Senator, then you write it all off?

      Reply
      • Denis May 25, 2013, 12:30 AM

        Although I once met the Prime Minister and shook his hand, he and I are not “buds”, and although Canada has a senator being caught doing the shady deal thing, I doubt I have a chance to replace Senator Duffy. I would be happy to have the job though! You could be darned sure I would do the job to the best of my abilities too! At least I don’t have the sense of entitlement that he has!

        Shaking the hand of a Prime Minister of Canada was something on my bucket list, and I managed to cross that off the list. How fun is it to cross stuff off your bucket list!!!

        So, how DO I become a senator?

        Reply
        • bigcajunman May 25, 2013, 6:10 AM

          There is no criteria, you must be somehow recognized by the sitting Government AND there must be a seat vacant.

          Did you contribute to the Conservative party lately? That’s a start (I hear).

  • Denis May 24, 2013, 12:49 PM

    I would LOVE to find a place where I can afford this low of a mortgage. 100k here on Vancouver Island will buy you a box, typically the one that your new washer came in. Houses usually start at 350k and up. When I was military, I got transferred and sold my duplex here in Victoria. Bad idea, but hindsight is 20/20.

    Now that I am back I cannot afford to get into a house as my income is far too low to meet the bank requirements. Now, I either get to save for retirement or save for a down payment for a house that I will never be able to afford a mortgage on. I can’t afford to do both.

    Reply
    • bigcajunman May 24, 2013, 12:54 PM

      Well, you are also saving on property taxes as well (looking on the bright side). Can you commute from Dryden Ontario to Vancouver? How about Perth?

      Reply
      • Denis May 24, 2013, 1:08 PM

        Grin… Commute? From FROSTBITE HEAVEN aka Dryden, ON?

        I live on the wet/west coast for the mild temperatures as I am a complete wuss when it comes to being cold! I can find a house for under 50k in Sask. but actually live there??? EEEP!!!!!

        I lived in Northern Alberta as well as northern Quebec. HATED the winters. 14.5 measured feet of snow piled up in front of my house during the winter in Quebec, having to climb onto the snow bank beside the driveway to shovel it back far enough for the snow blower to be able to throw the latest batch of snow onto that now shortened snow bank! Ya, that really is not for me.

        Now, Perth Australia would be okay temperature wise… But I don’t think you meant Australia! (grin) That one would be a heck of a commute to Victoria! Vancouver is on the mainland, so getting to that airport would not be too bad I guess…

        Reply
        • bigcajunman May 24, 2013, 1:27 PM

          What about a house-boat in the harbor? Bet those aren’t cheap in Lotus-Land (North) either.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: