How To Computing A Mortgage
Mortgage Calculation Spreadsheets
|I find myself often computing mortgage
data when looking at my own mortgage, or when thinking about buying
investment property. In particular, I often am curious about the impact
of making additional principal payments. To compute these numbers, I
developed a spreadsheet for generating a table of data about any given
mortgage and to run this what-if analysis.
|The first step is to fill in the principal
amount and the interest rate. Those will be in cells F2 and F3. To
computer the spreadsheet, select the very bottom value in the column
for Ending (such as I370 in the 30 year version) and pick the Goal Seek
menu option (under the Tools menu). You will want to goal see the
ending value to value 0 by changing cell F4. Hit OK to run the Goal
Seek, and accept the final value. When complete, cell F4 will hold
the payment number.
Once you have the payment, you can experiment with
adding additional payments. Note that the ending zero number happens
earlier in the mortgage as you pay more principal. This result can be
dramatic. For example, making one additional payment a year can reduce
a 30 year mortgage to be 23 years.