How to Create an Amortization Table

From LoveToKnow Mortgage

If you need to know how to create an amortization table, your first step is to determine the components of the loan including the term, principal, interest and any agreement for escrow payments.

Components of an Amortization Table

An amortization table or schedule shows how the monthly payment is allocated across the various components of the loan including:

  • Principal – The total amount of the loan
  • Interest rate – The percentage agreed upon by the lender and the person taking out the loan
  • Escrow arrangement – The amount of money that will be set aside each month to pay scheduled payments such as property taxes and homeowner's insurance

Tables and schedules can be developed for any loan term including the common 30-year mortgage loan as well as shorter term loans such as a 10-year, 15-year or 20-year loan. Each table is designed to spread the payments across only one specific loan term. Two separate amortization tables must be created if a mortgage contains two distinct loan terms such as X percent for a 10-year loan followed by Y percent for a 20-year loan.

How to Create an Amortization Table

Although the concept of amortization can seem complex, it is relatively easy to create an amortization table. You can use an online amortization calculator or you create an amortization table using a spreadsheet which has been preprogrammed with the mathematical calculations necessary to amortize your information onto the spreadsheet.

In either case, keep these points in mind when developing your amortization table:

  • Use a reputable website as the source for your calculator or spreadsheet model. This will help ensure the accuracy of the numbers created for your table.
  • Look for a table that allows the calculation of the impact of any additional payments which you may decide to make during the term of your mortgage.
  • Find a table that is printable. You will probably want to keep a copy for your files.

Using an Online Calculator

Many of the mortgage, bank and financial websites on the Internet include mortgage calculators with amortization functions. Most of these calculators are free to use. The calculator will require you to input the specific information about your loan such as the term, amount of the loan, interest rate and escrow funds required. Based on the information provided, an amortization table will be produced which you can read online or print for your files.

Some online calculators do not calculate using the exact amount of the loan. Instead, these calculators may only show you the amortization based on specific loan amounts such as $100,000. So, if your loan is for $300,000 you would have to triple the payment information shown by the calculator.

Developing Your Own Spreadsheet

You can develop your own spreadsheet using any spreadsheet software such as Excel. You will need to build the mathematical model into each cell. The model calculates the amount of money allocated each month for each component of the loan over the length of the loan term. Depending on your level of math and spreadsheet skills, this could be relatively easy or very difficult and time consuming.

To save time you might want to consider of the pre-formatted spreadsheets which are available on the Internet. These charts already have the mathematical model added to the spreadsheet. All you need to do is customize the spreadsheet to your particular loan components such as the length of your loan or the interest rate. For example, Mortgage-x.com has several different pre-formatted spreadsheets including a straight line amortization chart which can be quickly created using an Excel spreadsheet.

More Amortization Resources

LoveToKnow Mortgage has several helpful articles with information about amortization which can give you additional information on how to create an amortization table:

Retrieved from "http://mortgage.lovetoknow.com/How_to_Create_an_Amortization_Table"

 


Comment on How to Create an Amortization Table



(Displayed with your comment)                        (Will not be displayed)
Verification Code:   
    

Mortgage Categories
LoveToKnow Tools
More Articles