|
 If you are buying a home or even a new car, then one thing you might be interested in seeing is an amortization schedule for the loan. Amortization schedules allow you 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 amortization calculations. We've also included seven different amortization tables in spreadsheet form that you can download for free.
Using Amortization Schedules
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.
Amortizing a loan is different than the concept of depreciating an asset. Depreciation is the concept of writing off the value of an asset to reflect the decrease in the asset's useful life.
In the consumer world, amortization schedules are used to demonstrate how a personal loan such as a 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 factors appearing in each of the tables including:
- Loan Amount or Principal - this is the amount of the original 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 simply the interest rate that you are charged for the use of the money and the risk associated with the loan. This value is not necessarily 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. Usually stated in terms of years or months.
Seems simple, right? In fact, to produce amortization tables or schedules, you only need the above three pieces of information - nothing more. Except for maybe Excel or another spreadsheet application used to perform the calculations.
Amortization Calculations or Formulas
From the above three pieces of information you can produce all of the amortization calculations or formulas that you need to produce an amortization table. The most common format for these tables includes columns of information 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 or formula used in an amortization schedule because it relies on a complex formula to perform the calculation. Fortunately, Excel and other spreadsheets 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 in an amortization table shows you 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.
- Loan Balance - this is usually the final calculation performed in the amortization table. Quite simply, this is the remaining balance left on the loan or the principal balance.
Amortization Schedule Downloads
Now that you understand the elements that go into the calculation of an amortization schedule and what the output looks like, here are the seven different amortization tables you can download. Each of these tables is in an Excel spreadsheet format and can be used in a variety of spreadsheet applications including Microsoft's Excel or Lotus 1-2-3.
The first three tables you can download are those typically used for car loans - they are of shorter term than typical home loans:
These next four amortization schedules are larger tables that are more appropriate for use in a longer term loan such as a mortgage:
Each of these spreadsheet models allows you to change all the necessary variables to customize the table to your particular loan. They are fully functional tools that even allow you to print out your schedules after inputting your data.
Online Amortization Calculations
If you'd rather not download a spreadsheet, we've also got an online amortization calculator that you can use too. This online calculator allows you to vary the term of the loan, interest rate and comes complete with instructions. We've got a mortgage amortization calculator too.
Reading Amortization Tables
Earlier we explained the typical calculations and columns of information appearing in an amortization table. We'll finish up this article with a quick example that explains the process of reading one of the tables we've provided to download.
Amortization Table Example
If you've downloaded the 3 year amortization schedule, at the top you will see a section that has three inputs appearing in blue. In this example, the loan is for three years at 6.0% and $5,000. The monthly payment amount is $152.11 for this loan and in the first month's payment, $127.11 is used to lower the remaining balance, and $25.00 goes towards interest payments. The balance on the loan is calculated after the first payment is $4,872.89.
Notice that the amortization spreadsheet also has an input labeled Extra Payment to Principal. Let's say that you decided that you wanted to put an extra $50 in each of your 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 you paid an extra $50 per month, then the loan would be paid off in 26 - 27 months - the point where the Balance on the loan goes past the zero point.
About the Author - Amortization Schedule
Copyright © 2005 - 2007 Money-Zine.com
|
Home 
