12 Easy Steps — Computing A Mortgage

Mortgage Calculation Spreadsheets

By John Weeks

Introduction

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.

Mortgage Spreadsheets

10 Year Mortgage

15 Year Mortgage

20 Year Mortgage

30 Year Mortgage

Instructions

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.