Individuals thinking about buying a home, or even a new car, are oftentimes interested in seeing an amortization schedule for the loan. These schedules allow users to calculate, or visualize, what happens to the loan as time progresses.
In this article, we are going to first discuss how to use amortization schedules, and then talk briefly about their calculations. We've also included seven different amortization tables in spreadsheet form that users can download for free.
The concept of using amortization tables has its beginnings in the accounting world. The most common reference being to straight line amortization of bonds sold at a discount or a premium. In the accounting world, to amortize a loan is to "write off," or decrease, that loan over time.
In the consumer world, amortization schedules are used to demonstrate how a personal loan, car loan or a mortgage, is paid off over time. Typically, consumers are interested in 3-year, 4-year, and 5-year amortization schedules for car loans. For mortgages, the most common schedules include those for 10-year, 15-year, 20-year, and 30-year loans.
Putting together a schedule in a spreadsheet is fairly straightforward, with the most important variables affecting the calculation being:
This process seems simple, right? In fact, to produce amortization tables or schedules, only the above three pieces of information are needed. The only other thing needed is Excel, or another spreadsheet application such as Calc, to perform the calculations.
From the above three pieces of information, it's possible to produce all of the calculations or formulas needed to produce an amortization table. The most common format for these tables includes columns of data containing the following information:
Now that we've explained the elements that go into the calculation of an amortization schedule, and what the output looks like, listed below are the seven most common amortization tables available for download. Each of these tables is in an Excel spreadsheet format and can be easily imported into a variety of spreadsheet applications including OpenOffice or LibreOffice Calc.
The first three tables are those typically used for car loans, which are a shorter term than home loans:
These next four schedules are larger tables, which are better suited for use in a longer term loan such as a mortgage:
Each of these spreadsheet models allows the user to change all the variables of a loan. They are fully functional tools that even allow users to print out their schedules after inputting their data.
We also have an online amortization calculator for users looking for an easily accessible alternative. Once again, this calculator allows users to vary the term of the loan, interest rate, and comes complete with instructions.
Earlier, we explained the typical calculations, and columns of information appearing in an amortization table. We'll finish this article with a quick example that explains the process of reading one of the tables provided for users to download.
Here we're looking at the 3 year amortization schedule. At the top of the sheet is a section that has three inputs appearing in blue. In this example, we have a $5,000 loan for three years at 6.0%. The monthly payment amount is $152.11. From this payment, $127.11 is used to lower the remaining balance, and $25.00 goes towards interest charges. The balance on the loan is calculated after the first payment, and is $4,872.89.
Notice the amortization spreadsheet also has an input labeled Extra Payment to Principal. Here's how this works: Let's say that a borrower decided they wanted to include an extra $50 in each of their monthly payments; these spreadsheets have the ability to calculate these variables too. In this example, the extra $50 is applied directly to the principal. Reading the amortization table goes like this: If the borrower paid an extra $50 per month, then the loan would be paid off in 26 to 27 months. This is the point where the Balance on the loan goes below zero.
About the Author - Building and Using Amortization Schedules (Last Reviewed on September 14, 2016)