What is an Amortization Schedule?

An amortization schedule is kind of like an exercise journal...

You know you want to lose 20 pounds, and you know how many calories you need to cut and burn to lose it. But as you lose weight, the number of calories you burn during the same exercises decreases, so you need to either cut more calories or do a more intense workout to compensate. You could plan this out in a journal, detailing how you’ll react as you get closer to your goal of losing 20 pounds. Similarly, an amortization schedule breaks down each individual loan payment so you better track your pay-off progress.

An amortization schedule is a list or table that details all the individual payments that a borrower will make on a loan, including the amount of interest and principal per payment. In more precise terms, it details the amortization of the loan — the process of paying it off.

When you take out a loan, there are two things you need to pay: the principal, which is the amount you’re borrowing, and the interest, which is a fee the lender charges as a percentage of the principal. As you pay down the principal, the amount of interest will naturally decrease — 10%, say, of the nearly full loan amount at the beginning is going to be a lot more than 10% of the balance left when you’re a year away from paying off a 30-year loan. An amortization schedule gives buyers and lenders more insight into what goes into each payment by detailing the amount of interest and principal in each payment as these amounts change.

The purpose of an amortization schedule is to outline how a loan will be paid off in the most precise way possible. For example, instead of simply saying that a 30-year fixed mortgage will be paid off in 360 payments of $1,600, an amortization schedule would break those payments down into interest and principal.

In a fixed-rate mortgage, the monthly payment stays the same, but what that payment goes to changes. At the start, you’ll typically be predominantly paying interest, not principal. However, as you near the end of your mortgage, the proportion reverses, and you’ll be paying mainly principal. Essentially, the amounts of principal and interest paid per payment have an inverse relationship with each other.

At its heart, the purpose of an amortization schedule is to set a schedule for how a loan will be paid off. It can also be used to expense the cost of an intangible asset such as a copyright or patent.

An amortization schedule calculates the amount of interest and principal in each periodic loan payment.

In a fixed-rate loan, the periodic payment (typically monthly) stays the same, but the proportion of interest to principal paid each month changes in an inverse relationship. If you know the monthly payment, you can calculate each month’s principal and interest by dividing the yearly interest rate by 12 and then dividing it by 100 to convert it into a percentage.

From there, you’ll take that monthly interest rate and multiply it by the current outstanding balance to get your interest payment for the month. Then, subtract that amount from the fixed monthly payment amount to find out how much you’ll pay in principal for the period. Repeat this process until the balance reaches zero to make a complete amortization schedule.

For example, let’s say you have a loan for $10,000, an interest rate of 4%, and a monthly payment of $100. Start by taking the interest rate and dividing it by 12 to get 0.33% per month. Multiply this by the starting balance to get $33 — That’s your first interest payment. Then, subtract that from $100 (the monthly payment), to get $67 — That’s your first principal payment.

In the next month, do the same thing, but use $9,933 as the balance to account for the $67 of principal you paid off. For this case, your interest would be $32.78, and your principal would be $67.22. Continue in this way until the loan is paid off.

As you can see, as you pay off more of your loan, the amount of interest you pay each month decreases, and the amount of principal you pay increases. The changes are typically in very small increments, but by the end of the lifetime of the loan, the changes are very significant.

A similar method can be used for loans that don’t have a fixed total payment (including principal and interest), the only difference being that you don’t subtract the values from the monthly payment. If you wanted to pay off a loan with fixed principal payments of $100 each month, perhaps to pay off a credit card, for example, you’d calculate the interest payment the same way, but it would be in addition to the $100 principal payment. So, for the same loan as the above example but with a different payment schedule, the first payment would be $133 ($100 principal and $33 interest), and the second would be $132.78 ($100 principal and $32.78 interest). This type of payment schedule is not as common, but it is possible.

Here’s an example of what an amortization chart looks like:

There are many amortization schedule calculators you can consult online. If you wish to create one yourself, however, here’s how to do it.

To make an amortization schedule for a fixed-rate mortgage, you would:

• Divide the annual interest rate by 12 to get the monthly rate and then divide by 100 to make it a decimal instead of a percentage
• Multiply the number from Step 1 by the outstanding principal balance to get your interest payment
• Subtract the interest payment from Step 2 from the monthly payment to get your principal payment
• Subtract the principal payment from Step 3 from the outstanding balance to get your new current balance
• Repeat these steps using the number from Step 4 as your outstanding balance in Step 2 until the outstanding balance reaches zero. List the balance, interest, and principal amounts for each monthly payment in a table as you go.

Some amortization schedules may also include periodic payments summaries. For example, even if the loan is paid monthly, there may be an extra line each year that indicates the total interest and principal paid each year.

Making an amortization schedule by hand is a good exercise when you’re learning what it is, but in real-world applications, you would likely use a spreadsheet program like Excel or Google Docs to crunch the numbers for you.

When calculating the amortization schedule in a spreadsheet, you have a lot of freedom as to what you’d like to include. To create a schedule with the most information, you’ll want to start off labeling 8 cells to create 8 different columns. These labels should be in the first cell of each column (i.e. A1, B1, etc.) and should correspond to:

• Month or payment number (A1)
• Total payment (B1)
• Escrow (C1)
• Principal and interest payment (D1)
• Interest (E1)
• Principal (F1)
• Remaining Balance (G1)
• Loan ratio (the percent of the loan that’s still remaining) (H1)

Next, you’ll want to start including the month or payment numbers in the second cell in the first column (A2) starting from payment number 0. After that, you’ll click cell A3, type =, then click cell A2, and continue typing + 1 so that A3 contains the code =A2+1. Then, press enter.

You’ll need to manually calculate how many payments will be in the loan (e.g. 360 for a 30-year loan, 180 for a 15-year loan, etc.). Once you know that, you can copy the formula in cell A2 to the appropriate number of cells by clicking in the bottom right corner of the cell and dragging down.

You’ll also want to include the beginning balance in cell G2. We will use this later.

In column B, you’ll input the total monthly payment. Column C should contain the escrow payment, which is insurance and taxes. These two numbers will stay constant. You can copy them by dragging as well.

Column D will contain the amount for principal and interest, which is the monthly payment minus the escrow payment. You can have Excel calculate this by selecting D2, typing = to start a formula, clicking B2, typing -, then clicking C2, and pressing enter. The formula in D2 should read “=B2 - C2”.

In E2, we’ll input the monthly interest payment. To do this, divide the yearly interest rate by 100 and then by 12. Then enter the formula “=G2 *” the number you just calculated. For an interest rate of 4%, E2 should look like “=G2 * 0.0033”.

Column F contains the principal. To calculate this, we’ll input the formula “= D3 - E3” in cell F3.

Next, we need to update the remaining loan balance in column G. Cell G3 should contain the formula “=G2 - F3”.

Finally, column H will show the loan ratio. In cell H3, input the formula “=G2/G$2”. Now, you can drag these formulas down with your cursor just like you did with the values in column A.

Typically, a single formula is not used for an amortization schedule. However, you can use the following formula to calculate the amount of a specific payment:

In this formula, x is the payment, P is the principal borrowed, i is the periodic interest rate, and N is the number of payments.