Using Excel’s built-in amortization table

Thanks to our nascent house hunt, I’ve been trying to back into figures on mortgages and monthly payments. Luckily, Excel has a handy built-in amortization template just perfectly suited for this purpose.

Here’s how you get to the template in Excel 2007 (which according to my recent poll is the version most readers use, if only by a hair).

In Excel 2007, go to the “menu” button (the goofy-looking windows symbol in a circle) in the upper left-hand corner. Click on “New”. This will bring up the following window:

Click on “Installed templates”, which will bring up seven built-in templates for you to choose from. Double-click on “Loan Amortization”.

This will create a blank template for you to fill out, like so (click image below to enlarge):

You can fill in data in the orange boxes, and Excel will automatically calculate monthly payments, remaining amount on the principal, remaining balance, and interest paid to date, along with several other fields in the columns below. If you choose to make extra payments, the template automatically takes those into consideration as well. In the upper right-hand area is a summary of the loan scenario, including monthly payment amount and total interest to be paid.

Here’s an example of what all of this looks like for a hypothetical loan (click image below to enlarge):

In Excel 2003, you can get to the same template through the following steps. Go to “File” >> “New” >> “New Workbook” >> “On my computer…” >> “Templates” >> “Spreadsheet Solutions” >> “Loan Amortization”.

This template has helped me create a simple model to play around with figures to get a total picture of fund outlays, tax savings, and different mortgage scenarios in our nascent house-hunting process.

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss

***************************************************