Using your Spreadsheet to calculate mortgages

I keep getting requests about how to do amortizations for many more complicated scenarios, and 4 out of 5 times, the answer is probably already sitting in front of you. Just about every PC or Mac has a spreadsheet application of some sort on it, (typically Microsoft Excel within Office) and they are very good tools for doing mortgage analysis. Most of them have a built-in PMT type function that will calculate your monthly payment given a loan balance, interest rate, and the number of terms. Here is an example of how to make a spreadsheet that allows variable prepayments:

       A              B               C                D             E
1   Number of Years  30 
2   Interest Rate    8.0%
3   Principal        100000
4   Payment          =-PMT(b2/12,b1*12,b3)
5
6   Balance          Interest         Principal Paid   Pre-Paid  New Balance
7   =b3              =a7*$b$2/12      =$b$4-b7           200     =a7-c7-d7
8   =e7              =a8*$b$2/12      =$b$4-b8           300     =a8-c8-d8
9   =e8              =a9*$b$2/12      =$b$4-b9           150     =a9-c9-d9

Here the values in column D "Pre-Paid" can be added by the user to show how much additional principal is paid off that pay period. You can then add up the interest values in column B for each year to see how much tax deductible interest you have paid.

To do ARM's, you use the same idea, but stop the table after X payments and shift to a new interest rate which generates a new $b$2 value for column B interest values as well as a new payment (not $b$4 any more in column C). You can do all sorts of different scenarios this way by having the interest in a single cell you can change and watch the whole amortization table shift.

Here is hcmort.wks a very basic Lotus 123 compatible spreadsheet following the example above. Just about every spreadsheet made by mankind (and Microsoft too!) will accept WKS format spreadsheets. See if this works for you.

This example above uses the Excel version of PMT. Yours may differ.

Here is a Excel 5.0 module (Windows or Mac) for doing Canadian mortgages that I found at http://www.knowtech.ca/prod06.htm

Here is a shareware Excel 5.0 for example: Windows Version Mac Version

Mac users can also get a demo ClarisWorks amortization spreadsheet here which should work fine in Excel too.

If for some reason you do not have a spreadsheet like Excel or a similar office suite, you can download and use LibreOffice free for any platform. Windows or Linux GNOME users who do not want to use the full LibreOffice suite can use gnumeric too.