This is one way to do a mortgage estimator (for payments), I used Excel but I am sure other methods are out there that may be more accurate. I use this spreadsheet as an estimate to figure out how much money I still owe on my mortgage and it gives me a warm fuzzy counting the number of days left in my mortgage. Today, I will explain the main function used to estimate your periodical mortgage payments and tomorrow I will show the Mortgage Pay Out Schedule (I’ll include an example template tomorrow as well).
Mortgage Estimator (how to)
As with most mortgage estimator you will need the following information
- Total cost of your mortgage
- Interest rate you will be paying for the mortgage
- How many years your mortgage is amortized over (25 years?)
- How may payments you are going to make in a year (25.5 for bi-weekly, 12 for monthly, etc.,)
- How often the rate is compounded
- Starting date of the Mortgage
Put each of these values in a cell on a blank spreadsheet. The most important function to use in this worksheet is the PMT() function. In Excel the PMT() function figures out how much your payments are going to be (now the function does this for American Mortgages and really should only be used as a guide, since I have never had a Bank and this function match given the same data.
To use the PMT function is simple it is:
PMT( X , Y , -Z )
Where
X = Interest Rate (yearly) divided by number of pay periods in the year (e.g. 4.75%/25.5 periods)
Y = Total Number of payments which is number of years of the mortgage multiplied by number of payments per year
Z = Total Mortgage to be repaid
Use this and you will get a good estimate of how much your mortgage payments will be (either bi-weekly or monthly).
That alone is very useful if you are trying to figure out how much how you can afford, in either bi-weekly or monthly payments.
Example
Say me and Mrs. C8j decide to really downsize and because our kids ate us out of our savings, we still end up with a $100,000 Mortgage at 5.5% and we want to pay monthly payments on the mortgage but only want a 15 year term.
The cell on the spreadsheet would look like:
=PMT( 5.5%/12, 15*12, -100000)
which would give us a monthly payment of $817.08
This is close to how much we will make in Mortgage payments per month (remember this is an estimate your BANK will tell you what they will charge). This one function can also be used for car loans and many other things, which makes it a very powerful tool.
Tomorrow, we do the mortgage schedule.
Waterloo’s might. However Marshal’s is returned on the first page of a search!
DAvid
Waterloo needs to work on their SEO strategies then 🙂
I took some Act Sci courses at Waterloo, this is where some of the ideas for this came from.
–C8j
I am sure the University of Waterloo’s Actuarial profs also have some cool s/s too.
Professor Marshal of York University offers a number of spreadsheet-based calculators at this site: http://www.yorku.ca/amarshal/mortgage.htm
Have a look for the formulas used there.
DAvid
Using the PMT funtion will give you the mayment to calculate a loan. It’s a good estimator but for Canadian mortgages it will never be correct. This is because Canadian mortgages may have monthy, weekly, or bi-weekly payments but the interest only ever compounds twice a year.
The really makes your interest calculation in the PMT function funky. Do a web search for “Canadian mortgage excel formula” and you’ll find sites showing how you monkey with the formula.