How to Create an Amortization Chart

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.

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.

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.

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.

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.

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.

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.