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
- How to create a mortgage schedule sheet
Open an Excel (or whatever spreadsheet software you like) document.
- 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)) - Real Monthly Payment
Column 2 shows the real monthly payment
We already calculated that using the PMT() function - Interest Payments
Column 3 calculation of how much each payment goes towards the Interest
This is = Previous balance * Interest rate for that payment period - Principle Payment
Column 4 calculation of how much each payment goes to the principle
Total payment – Interest Payment = Principle payment - Remaining Balance
Column 5 the remaining balance on the loan
Previous Balance – Principle Payment - 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 | 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!
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
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?
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?
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).
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.
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?
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…
What about a house-boat in the harbor? Bet those aren’t cheap in Lotus-Land (North) either.