My wife and I just bought a house on Friday. It's our first house, after
living in two different apartments, so we are very excited about finally
paying for something that will hold a value. We both feel like we're dumping
money into a black hole by paying rent, whereas with a house, each mortgage
payment is adding more and more to the portion of the house that
we
own, instead of the bank.
So today at work, I decided I'd like to see what the payments and whatnot
will look like. Like any good computer guy, I built up a nice Excel
spreadsheet to list everything out. Turns out the whole mortgage thing is a
little frightening! We'll be paying for this house through about 2038, and
we'll end up shelling out about 2.25x the purchase price!
Obviously, this got me thinking of ways to pay it off a little early, and so
I decided to make the spreadsheet easy for anyone to use. Assuming I can
figure out how to post it in here somewhere, I'll release it for everyone to
take advantage of...
Update: So it seems Blogspot is unable to accept files other than
images/video, nor can I place it into a CDATA section within a text area. So
until I find a good hosting service, you'll just have to type this stuff in
yourself. Here is how I set up my spreadsheet:
Excel Mortgage Calculator
Across the top starting at cell A1, create bold, centered column headers:
- Year
- Month
- Payment
- Interest Paid
- Principle Paid
- Remaining Balance
- Paid So Far
On the next row, fill in a few of the cells like this:
- 1
- =$J$4
- (Empty)
- (Empty)
- (Empty)
- =$J$1
- (Empty)
On the next row, fill in the cells like this:
- (Empty)
- =DATE(YEAR(B2), MONTH(B2) + 1, DAY(B2))
- =$J$6
- =-IPMT($J$2 / 12, 1, $J$3 * 12, F2)
- =C3-D3
- =F2 - E3
- =SUM($C$3:C3)
Now, copy that last row and paste it into every row through 362. As a
shortcut, after copying the row, you can select that whole huge block of
cells and paste; they'll all fill in properly. After filling those in, write
in the year numbers down column A, one every 12 cells (so cell A2 has "1",
A14 has "2", and so on down to "31" in cell A362).
Next, create a small table over on the right side by creating bold labels
down column I like this:
- Starting Balance
- Interest Rate
- Term (years)
- Starting Date
- (Empty)
- Monthly Payment
Values for those labels you just created go in column J. For now, just enter
"30" for the term, and "=-PMT($J$2 / 12, $J$3 * 12, $J$1)" for the monthly
payment.
You're almost done now. Everything would work, but it wouldn't be very
legible, so let's do some quick formatting:
-
Click on the "B" column to select the whole thing, go to the menu
item "Format | Cells", choose "Date" as the category and "Mar-01" as
the type.
-
Columns C through G get the "Currency" category with negative
numbers marked as red and in parenthesis.
-
Cells J1 and J6 get the same Currency formatting as columns C-G.
-
Cell J2 gets formatting as a Percentage with 3 decimal places.
-
Cell J4 gets the same Date formatting as column B.
-
Feel free to change the column widths however you like.
To Use
Type in your mortgage terms in the little table at the right. It defaults to
30 years because that is common and it prevents Excel from filling all of
the cells with error codes, but you can change the value however you need.
The length of the table won't change if you change the term, though, so you
may need to copy/paste some new rows or delete/ignore existing rows.
The monthly payment will automatically be calculated based on the values you
set above; if it is not the same as your real mortgage, it is probably due
to extra fees charged by your bank (such as mortgage insurance), which have
nothing to do with your loan payments and are only tacked on for
convenience.
Now the table should be listing out how your mortgage will act as you pay it
off. To pre-pay portions of it, just change the payment for the month to see
how things change. For example, you can save quite a bit of time by paying
an extra $1000 per year or by paying as if it was a 20-year loan whenever
possible.
I hope this helps all of those other new home-buyers like myself figure out
how all this financing stuff works. Please feel free to leave comments on
how you like the spreadsheet. I'm sorry I wasn't able to post the original.
2013-04-22 Update: This spreadsheet is completely compatible with OpenOffice and LibreOffice, which is what I use now.
2016-02-28 Update: I have since switched to Google Docs, which means I can make a template so anybody can copy and edit without needing to trust a downloaded MS Office file. Just
go here and choose Make a Copy from the File menu. I've changed the formatting a little from what I described above.