Skip to content
Canajun Finances Home » How To Do a Mortgage Schedule

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  
   Periods/Year12  
   Term300  
   Start01-Jun-08  
      
========================================================================== 
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!

Feel Free to Comment

  1. 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

      1. 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?

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

  2. 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.

      1. 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…

Leave a Reply

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

Tags:
Verified by MonsterInsights