How to Create a Loan Amortization Table in Excel

1. Download the free Amortization Schedule for Excel 1.6 spreadsheet program available by Vertex42 and CNET. The program is designed for the layman and makes it easy to create a loan amortization table that quickly visualizes the relationships between a loan's principal, its time period and interest rate. Once created, the table may be changed by adjusting any of these variables on-the-fly. The flexibility of this template makes it easy to see how the loan is affected by the occasional extra payment. It also demonstrates the consequences of a missed payment. The table supports all the popular payment schedules, from weekly to annual payments. It optionally allows payment information to be rounded.
2. Download a Microsoft template for loan amortization. The company that makes Excel also makes its own amortization tables available to users. These are provided for free from the Microsoft Office website. The template takes advantage of Microsoft's own financial formulas built in to the Excel program. The variables for a fixed interest rate, payment schedule and loan principal are input and the template creates an interactive amortization table to show all the relationships. In addition to the table itself, the Microsoft template also creates a loan summary section where the overall parameters and outcomes of the loan are spelled out. This includes the total amount of interest paid after the loan cycle completes.
3. Buy a more robust loan amortization package from a company such as the Spreadsheet Store, which provides a set of six Excel loan analysis spreadsheets for a fee of $25 as of March 2010. While all loans have similar parameters, the Loan Calculator for Excel package creates an amortization schedule for many different scenarios for added flexibility to nearly any loan situation. Some loans follow a 'balloon' style which requires a large lump sum at the end of the payment term. Other loans break the loan cycle up into a graduated repayment plan where the initial round of payments cover the interest only. Still other loans do not use a fixed interest rate and thus the payment sizes can change periodically. This package of spreadsheets covers all these loan situations and more so the user is ready to visualize any credit opportunity with greater clarity. In addition to the loan amortization tables, these spreadsheets also create graphs that depict the loan parameters in a particularly visual layout.