Mortgages

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.