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.
Using Amortization Schedules
Additional Resources |
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.
Creating Amortization Schedules
Putting together a schedule in a spreadsheet is fairly straightforward, with the most important variables affecting the calculation being:
Loan Amount or Principal: this is the original amount of the loan, and is the starting point for the monthly loan calculation. As time progresses and monthly payments are made, the principal of the loan is reduced.
Interest Rate on Loan: this is the rate of interest charged for use of the money, and the risk of non-payment associated with the loan. This value is not the same as the Annual Percentage Rate, or APR, which includes additional costs such as mortgage points and loan origination fees.
Term of Loan: this is the length of time over which the loan will be paid off. This is typically stated in terms of years or months.
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.
Calculations or Formulas
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:
Month: this column reflects the payment of the loan over time. For example, a 36 month car loan will use an amortization table with 36 rows of information; one for each monthly payment.
Loan Payment: perhaps the most mysterious calculation used in an amortization schedule because it relies on a complex formula. Fortunately, Excel and other spreadsheet products have this formula built into the application. From this complex calculation comes the monthly payment on the loan.
Principal: this column of information shows the amount of the monthly payment that is applied towards reducing the principal of the loan.
Interest: this column shows the amount of the monthly payment that is associated with interest charges. The principal reduced, plus the interest charged, will always be equal to the payment amount.
Balance: this is usually the final calculation performed in the table. This is the remaining balance left on the loan, or the principal balance.
Amortization Schedule Downloads
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.
Online Amortization Calculations
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.
Reading Tables
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.
Example
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
