Excel Amortization Table
From LoveToKnow Mortgage
An excel amortization table is an easy and readily available method for calculating loan amortization. Loan amortization is the complex calculation of a loan's payoff. It provides information on monthly payments, total interest paid and month-by-month principal payments. Using an amortization table, you will always know how much you have paid on your mortgage loan and how much is left to pay.
Creating an Excel Amortization Table
There are various websites offering a free amortization calculator you can use to create an amortization table. If you want to create your own, in Excel, you can do so. Use the following websites to help.
- RichMore.com: RichMore.com is by far the best website for learning to create your own Excel table. It provides step-by-step directions plus screenshots. This makes the process simple.
- Microsoft Office Online: If you have Microsoft Office, you can visit Microsoft Office Online to download the loan amortization schedule template. This template allows you to create an Excel table using the formulas already inputted for you into Excel. This is a faster method for those unfamiliar with Excel formulas. The website provides full instructions, too.
- DocStoc.com: DocStoc.com allows you to view and download other amortization tables from those who share them. There are a number of spreadsheets available there for amortization.
Calculating an Excel Table
To calculate an Excel amortization table, there are several numbers to know. Start by knowing:
- The total amount owed
- The interest rate or APR
- The length of the loan
- If a new loan, the down payment
From this information, you can calculate the details of the loan using the following methods.
- Payment Amount Per Period: This term is used to describe the amount of the periodic payment. This is usually a monthly payment. To calculate the total payment amount per period, you will first need to determine the interest rate per period and the total number of payments.
- Interest Rate Per Period: This number represents each of the monthly interest rate charged. To determine this number, you need to take the yearly rate and divide it by 12. For example, a 7.5 percent per year interest rate would equate to .0625 percent per period.
- Total Number of Payments: To calculate the total number of payments on the loan, multiply the years of the loan by 12. For example, a 5-year mortgage equates to 60 total payments.
To demonstrate how to calculate payment amount per period, visit CalculatorFreeOnline.com for a step-by-step example. To see an example of how to calculate the rate per period, visit Vertex42.com.
A Few Shortcuts
For those who do not want to have to deal with all of these formulas, you can use an amortization calculator to help you. These will calculate the payments for you and provide you with an amortization schedule. Bankrate.com and Interest.com are two websites providing free amortization calculators.
How to Use the Amortization Table
Once you have the amortization table completed, use it. The data on it can be used by a homeowner in a number of ways including to save money.
- Use the data to help you calculate the amount of your monthly payment based on a home you have found and the interest rate available to you. This can help any homebuyer know if they can afford the home.
- Utilize the Excel spreadsheet on a monthly basis to know where your loan stands. It can help you keep track of how much interest and principal you are paying per month and in total to that point.
- Allow the Excel amortization table to help you pay off your debt sooner. Add in an extra amount of money per month. For example, if you have an extra $100 this month, add it to your mortgage payment to pay down the principal. Update your Excel table to match this. It will help you pay off your debt sooner without paying as much interest.
Learn More
This page has been accessed 682 times. This page was last modified 04:00, 30 October 2009.
© 2006-2009 LoveToKnow Corp.
Print this page
Follow ltk on twitter
Visit us on facebook
