Loan Amortization Spreadsheet

– Make It Work For You

By: - Loans - May 7, 2011
loan amortization spreadsheet

When you are purchasing a home or a newer vehicle, you may wish to use a loan amortization spreadsheet, to visualize or calculate what will happen to your loan as time passes. There are various ways to use these schedules, and they were originally used for accounting purposes for businesses.

Amortization of a loan is a different concept than that of the depreciation of an asset. Consumers use an amortization table to determine how a mortgage or a car loan will be paid off, over a specific period of time. For car loans, the schedules are usually used in periods of three to five years. When you work on a schedule for a mortgage, the more common time spans are 10, 15, 20 and 30 year loan periods.

To create an amortization schedule into a spreadsheet form, you simply need to use the variables that will affect the calculations. The principal, or loan amount, will be the original amount of the loan, and it’s the starting point for your calculation of monthly payments. As time passes and more monthly payments have been made, the principal of your loan will be reduced.

You will also need to figure in your loan’s interest rate. This amount is for the risk the lender takes when loaning you the money, and what you are charged for using that money. This may show up as the APR in your loan amortization spreadsheet, and it may include any fees for loan origination or mortgage points.

The term of your loan has a place in your amortization table, as well. This refers to the time through which you will pay your loan off. This is usually recorded in months or years. The process is relatively straightforward, and for producing amortization schedules, you only need these three information pieces.

From these information pieces, you can produce any of the amortization formulas or calculations for your loan amortization spreadsheet. The most common format includes a series of columns with the information included.

To make your amortization table complete, you will need to include all the pertinent information. This includes the:
month each payment is scheduled
amount of each payment
principal
interest
balance

After you understand all of the elements you will use in your amortization schedule, and what your spreadsheet will look like, you can download examples of spreadsheets online. These are usually in Excel form, and you can import these into various spreadsheet applications, like Lotus 1-2-3 or Microsoft Excel.

The tables you download will be different, depending on what you are using them for. Vehicle loans will usually have shorter terms, and tables used for mortgages will have longer time periods. You can use the various models of spreadsheets to customize one to your own loans.

  •