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
- ( (AnnualInterest 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