Everyone wants to Understand their Mortgage Payment
Although there are no shortage of Mortgage Rate Calculators, Payment Calculators, and tables online that will calculate payment rates for a given interest and term, I decided there was a need for a simple Home Loan payment spreadsheet that anyone can set up and use on their computer.
Why Another Mortgage Calculator?
The mortgage calculators that are available online are great if you want to know how much your payment will be for a given interest rate for x years, but they lack the flexibility of "what if?".
By now, everyone has heard of the Bi-Weekly payment programs that people are selling for hundreds of dollars, convincing you can save thousands over the life of your mortgage. These programs work but why pay someone else to show you the value of this when you can do it yourself?
By now, everyone has heard of the Bi-Weekly payment programs that people are selling for hundreds of dollars, convincing you can save thousands over the life of your mortgage. These programs work but why pay someone else to show you the value of this when you can do it yourself?
The miracle of Compound Interest in Reverse
or why you pay for your house twice in thirty years!
I'll give you an example. Let's say you have a $100,000 mortgage loan payable for 30 years @ 5.75 fixed interest. The payment will be $583.57. Thirty years later when you break out the champagne at the note burning party, you might want to have two bottles. You have paid $110,087.94 in interest! That $100,000 mortgage cost you $210,087.94!
Now that I have your attention.....
Let's get on to the spreadsheet
First of all, I'm assuming you're familiar with your spreadsheet program. This works equally well with Microsoft Excel, Open Office Calc, or even most every freeware spreadsheet available.
All you have to know how to do is create simple formulas that reference another cell value, how to anchor an 'absolute' cell value, and how to copy a value into a column of cells.
Actually, I'm going to show you all of that so you should have no problem.
All you have to know how to do is create simple formulas that reference another cell value, how to anchor an 'absolute' cell value, and how to copy a value into a column of cells.
Actually, I'm going to show you all of that so you should have no problem.
First, lets do our titles
Type in these titles in the cells indicated....
B2=Mortgage
B3=Interest %
B4=Term(yrs)
B5=Payment
B6=Prepayment
B8=Balance
C8=Interest
D8=Principal
E8=Prepayment
If you want to get fancy, now would be the time to center all these in their cells if you know how and want to do that.
B3=Interest %
B4=Term(yrs)
B5=Payment
B6=Prepayment
B8=Balance
C8=Interest
D8=Principal
E8=Prepayment
If you want to get fancy, now would be the time to center all these in their cells if you know how and want to do that.
And now for the formulas
Actual cell contents are enclosed in brackets [ ]
A10-[1] (just the number 1)
B10-[=$C$2] (some spreadsheets use a + sign instead of the equals to indicate a cell reference. The $ characters make the cell reference absolute instead of relative)
C10-[=B10*(($C$3/100)/12)]
D10-[=$C$5-C10]
E10-[=$C$6]
A11-[=A10+1]
B11-[=B10-D10-E10]
Double check for accuracy and we're almost there.
B10-[=$C$2] (some spreadsheets use a + sign instead of the equals to indicate a cell reference. The $ characters make the cell reference absolute instead of relative)
C10-[=B10*(($C$3/100)/12)]
D10-[=$C$5-C10]
E10-[=$C$6]
A11-[=A10+1]
B11-[=B10-D10-E10]
Double check for accuracy and we're almost there.
How to Save Thousands of Dollars on Your Home Mortgage, 2nd Edition
Amazon Price: $17.05 (as of 02/09/2010) 
Mortgages For Dummies, 3rd Edition
Amazon Price: $11.55 (as of 02/09/2010)
Mortgage Free!, Second Edition: Innovative Strategies for Debt-Free Home Ownership
Amazon Price: $16.47 (as of 02/09/2010)
Mortgage Ripoffs and Money Savers: An Industry Insider Explains How to Save Thousands on Your Mortgage or Re-Finance
Amazon Price: $12.21 (as of 02/09/2010)
Copy some cells and we're almost done
Place your cursor on C10 and click and drag it so that it includes C10, D10 and E10. Now click on Copy or CTRL C and drag the cursor over C11, D11 and E11 and hit enter. Click Paste or CTRL V and enter. This duplicates the formulas into row 11.
Now place the cursor on cell A11 and drag it over to include the entire row through E11. Again click copy and enter. Reposition the cursor over A12...E12 and drag the entire group down to row 370. This will give you the 36 months for a 30 year loan. Click on copy and you're almost done.
Cell C372-[=sum(C10:C371)]
D372-[=sum(D10:D371]
E372-[=sum(E10:E371)]
There are a number of ways to do this, most spreadsheets use the greek letter epsilon which looks like a fancy E.
Now place the cursor on cell A11 and drag it over to include the entire row through E11. Again click copy and enter. Reposition the cursor over A12...E12 and drag the entire group down to row 370. This will give you the 36 months for a 30 year loan. Click on copy and you're almost done.
Cell C372-[=sum(C10:C371)]
D372-[=sum(D10:D371]
E372-[=sum(E10:E371)]
There are a number of ways to do this, most spreadsheets use the greek letter epsilon which looks like a fancy E.
Lets format and test our data....
Highlight the group of cells from B10 to E370 and click on Format:Cells:General:2 decimal places. Again, this will vary from one spreadsheet to the next so follow your's individual technique.
Now, go back to the top and place these numbers in these cells:
C2=100000
C3=5.75
C4=30
C5=583.57
You should see that you paid $479.17 interest in that first payment and $104.40 toward the principal (Ouch). Now scroll to the bottom and you'll see that there's a remainder of 2.74 and interest payments of 110087.94 and pricipal of 99997.26.
If this isn't what you get, recheck your formulas and numbers.
Now, go back to the top and place these numbers in these cells:
C2=100000
C3=5.75
C4=30
C5=583.57
You should see that you paid $479.17 interest in that first payment and $104.40 toward the principal (Ouch). Now scroll to the bottom and you'll see that there's a remainder of 2.74 and interest payments of 110087.94 and pricipal of 99997.26.
If this isn't what you get, recheck your formulas and numbers.
Fetching new data from eBay now... please stand by
Principal Prepayment can Save you Thousands
Now that everything is working correctly you should save a copy so that you can experiment with the copy you have on the screen.
Go ahead, put the data in for your own mortgage or a hypothetical one you want to test. You can get the monthly payment off of any mortgage calculator you find on google.
Then try adding even as little as $10 a month as a prepayment.
If you go down and delete the columns after the numbers become negative you will find that your $10 cut 15 months off your loan and saved you over $5600 in interest! That's over 150% return on your investment!
Have fun, and I hope you find this useful.
Go ahead, put the data in for your own mortgage or a hypothetical one you want to test. You can get the monthly payment off of any mortgage calculator you find on google.
Then try adding even as little as $10 a month as a prepayment.
If you go down and delete the columns after the numbers become negative you will find that your $10 cut 15 months off your loan and saved you over $5600 in interest! That's over 150% return on your investment!
Have fun, and I hope you find this useful.
Some Excellent Books on Mortgage Savings
- Creative Financing Secrets
- by David Leach
- How to Get a Mortgage
- by Dianne St. James
- Real Estate Mortgage Secrets Exposed
- by Trent Gray
- Mortgage Cycling Revealed
- by Craig Romero
Hope you found this helpful, let me know what you thought
Please rate my lens before you leave
submit
-
Reply
- Jul 25, 2009 @ 5:21 pm
- Really great lens, I found it very informative as well as insightful. There are numerous types of mortgages; I recommend thorough research before entering into any agreement with a mortgage company.
I recommend for those of you who are keen on finding mortgage endowment agreements to check out my site Cheap Insurance Life Policy, it contains all the information you need.
-
Reply
- jtmartin jtmartin Feb 17, 2008 @ 2:28 pm
- JT Martin
Great info, I'm going to set one up.
-
Reply
- writertiff writertiff Feb 16, 2008 @ 9:14 am
- Great tutorial! Thanks!
tiff :)
Explore related pages
Mortgage Calculator - How Financemate Beats BBC Mortgage Calculator- No one wishes to involve himself in the complications of the tedious calculation...
Pay off your Mortgage FAST!- I’m going to share some tips I’ve used to save me a tonne of money o...
Best Home Mortgage Deals
- People will spend months or even years planning to buy their own home. They will...
Free Online Mortgage Calculator- 80 percent of home buyers now conduct at least part of their home search on the...
Mortgages- The term "second mortgage" is easier to understand than home equity loan, but th...
Mortgage Calculators- A collection of free online mortgage and financial calculators for your blog and...
