How to Make a Loan Amortization Table in Excel

A loan amortization table displays the details of a loan scenario in a spreadsheet grid. The table can take different forms, depending on your needs. Some loan amortization tables create a two-dimensional grid that displays one parameter of a loan as one axis, and another parameter as another. Common variables used are the interest rate and the term of the loan. A potential loan borrower can quickly see how monthly payments would change based on adjustments to these parameters. Other tables, called amortization schedules, clarify the payment plan for a particular loan scenario. There are many tools available to make a loan amortization table in Microsoft Excel.

Instructions

1

Install the Amortization Schedule for Excel product by Vertex42. This is a free download through CNET. You can quickly build a table that reflects all the different parameters of a loan. While most loans are based on a set payment schedule, this Vertex42 template shows how occasional changes to the payments affects the overall loan. If a payment is missed, or the occasional payment is increased, this table reflects the consequences of these actions on the final payback amount. Simply download the file and open it in Excel, and the interface is straightforward from there. No prior financial experience or programming background is required to quickly build amortization tables.

2

Open an official Microsoft Excel template designed for amortization tables. Microsoft offers hundreds of free templates for all Microsoft Office products in a wide range of application categories. As Excel is commonly used for financial analysis, amortization templates are readily available from the Microsoft website. Excel natively supports many financial formulas and calculations relating to interest rates and scheduled loan payments. The Microsoft templates simply organize these built-in functions into an easy interface so anyone can take advantage of Excel's programming functions without having to construct formulas manually. Users who desire added functionality or custom formatting can build on the template with their own content, if desired.

3

Purchase an Excel add-in for a more comprehensive analysis of loan scenarios. The Spreadsheet Store provides an Excel loan analysis package called "Loan Calculator for Excel." This suite of multiple Excel worksheets covers nearly any loan scenario you will encounter. As of March 2010 the price for this package is $25. Special payment schedules are supported by the program. As many loan types do not involve a set payment amount each month, the "Loan Calculator for Excel" may be the best option. Variable interest rates and graduated payment plans are among the situations not easily analyzed by free Excel software. This package easily manipulates these extra variables. Unlike other products, it also creates charts to more effectively display the parameters of a loan.