How to Create an Amortization Chart

An amortization schedule is extremely helpful in managing personal debt. An amortization schedule will help you determine how much your monthly debt payment will be for a certain amount of debt at a given interest rate; how much of your monthly debt payment is used to pay off principal versus interest; and how much you can save in interest expense by paying off the debt early. Creating an amortization schedule is easy with Microsoft Excel.

Advertisement

Step 1

Enter the amount of debt you owe, the interest rate and the repayment period from your promissory note into Excel. The promissory note is the document you signed with your lender when you took out the loan. If you did not save a copy of the promissory note, you should request a new copy from your lender.

Advertisement

Video of the Day

Step 2

Create the following five column headers in Excel: Beginning Balance, Payment, Interest, Principal, Ending Balance. Suppose that you have a $50,000 loan with an annual interest rate of 8 percent with monthly payments and a 20-year repayment period. Enter $50,000 in the first row under the Beginning Balance header.

Advertisement

Step 3

Use Excel's payment formula to calculate your monthly payment in the cell under the Payment header. The payment formula is as follows: =PMT(rate,nper,pv), where "rate" is the interest rate on the loan, "nper" is the total number of payments you will make and "pv" is the total amount of the loan. In this case, you would enter =PMT(0.7%,240,50000). You must enter 8%/12 = 0.7% for "rate" because 8% is the annual interest rate and you will be making monthly payments. Also, you must enter 240 for "nper" because you will make one payment each month for 20 years: 20 x 12 = 240. This formula calculates your monthly payment as $418. Copy this number down 240 rows (one for each monthly payment) in the Payment column in Excel.

Advertisement

Step 4

Multiply the monthly interest rate by the beginning balance found in the Beginning Balance column in the first cell under the Interest heading. This calculation tells you how much of this month's payment is used to pay interest. In this example, the interest payment for the first month is 0.7% x $50,000 = $350.

Advertisement

Advertisement

Step 5

Subtract the interest payment calculated in Step 4 from the monthly payment in the Payment column in the first cell under the Principal heading. This calculation tells you the amount of this month's payment that is used to pay down principal. In this case, the principal payment equals $418 – $350 = $68.

Advertisement

Step 6

Subtract the monthly payment from the beginning balance figure in the first cell under the Ending Balance heading. This calculation tells you how much principal you have left to pay after this month's payment. In this case, the ending balance of principal equals $50,000 – $418 = $49,582.

Advertisement

Step 7

Copy the Ending Balance figure into the Beginning Balance column in the second row. When you make your second month's payment, the interest component will be based on the lower principal balance. For each row, the beginning balance figure should equal the ending balance figure in the previous row. Copy all of the formulas described in the steps above down 240 rows (one for each month). This will give you a full amortization schedule for your loan. If the calculations are correct, the ending balance figure in the final row should be zero since you will have paid the loan off in full.

Tip

When creating an amortization schedule, it is best to do all of the calculations in Excel by referencing cells and copying formulas (rather than hard-coding the numbers in the cell). Linking cells will allow you to copy formulas, which will automatically repeat the calculations in each row of the table. Consult Excel’s online tutorial if you are unsure of how to reference cells in Excel.

Things You'll Need

  • Promissory note

  • Microsoft Excel

Video of the Day

Advertisement

Advertisement