Amortization Calculation Formula

The formula for amortization calculation is really quite simple. I'll try to explain it without too much financial or accounting jargon.

When talking about loans, amortization is the process of paying off a loan. Each time you make a payment you pay some interest along with a part of the principal. The principal is the original loan amount, or the balance that you must pay off. By making regular periodic payments, the principal gradually decreases, and when it reaches zero, you're done.

Usually, whether you can afford a loan depends on whether you can afford the payment each period (commonly a monthly payment). So, the main amortization formula is the calculation of the payment amount per period.

Amortization Calculation

The formula for calculating the "Payment Amount per Period" (A) is based upon the "Loan Amount" (P), the "Interest Rate per Period" (r), and the "Total Number of Payments or Periods" (n). The calculator below uses the following amortization formula:

Example: What would the monthly payment be on a 5-year, $20,000 car loan with a 7.5% annual interest rate? We'll assume that the original price was $21,000 and that you've made a $1,000 down payment.

Using the amortization calculator, the Payment Amount (A) is $400.76 per month.

Calculating the Monthly Payment in Excel

Microsoft Excel has a number of built-in functions for amortization formulas. The function corresponding to the formula above is the PMT function. In Excel, you could calculate the monthly payment using the following formula:

Calculations in an Amortization Schedule

When you know the regular payment amount, it is easy to create an amortization schedule. The example below shows the first 3 and last 3 payments for the above example. Notice how much more interest you pay in the beginning than at the end of the loan!

The Interest portion of the payment is calculated as the rate (r) times the previous balance, and is usually rounded to the nearest cent. The Principal portion of the payment is calculated as Amount - Interest. The new Balance is calculated by subtracting the Principal from the previous balance. The last payment amount may need to be adjusted to account for the rounding.

A good amortization schedule will show you how much interest and principal you are paying each period, and usually an amortization calculator will also calculate the total interest paid over the life of the loan. Besides considering the monthly payment, you should consider the term of the loan (the number of years required to pay it off if you make regular payments). The longer you stretch out the loan, the more interest you'll end up paying in the end. Usually you must make a trade-off between the monthly payment and the total amount of interest.

You can use the Simple Interest Amortization spreadsheet to create your own amortization schedule and see how the interest rate, payment period, and length of the loan affect the amount of interest that you pay.