How to Create an Amortization Table in Excel

1. Use the loan APR, amount and term in Excel's 'PMT' function to calculate the monthly payment. If you have a 30-year mortgage for $200,000 at a 6 percent APR, type the following in cell A1.
=-PMT(0.06/12,30*12,200000)
2. Type in column headings for the amortization table. In cell A3, type 'Unpaid Balance.' In cell B3, type 'Principal.' In cell C3, type 'Interest.' Adjust the column widths as necessary.
3. In cell A4, type the initial loan value. For the previous example, you would type '200000.'
4. Calculate the interest on the first month's payment. In cell C4, based on the previous example, type the formula:
=A4*(.06/12)
This multiplies the unpaid balance by the monthly portion of the interest rate.
5. Calculate the principal on the first payment. In cell B4, type the formula:
=$A$1-C4
This subtracts the calculated interest from the calculated monthly payment.
6. Calculate the second month's unpaid balance. In cell A5, type the formula:
=A5-B4
This subtracts the principal from the previous month's payment from the loan balance.
7. Copy the formulas down each column until the table is complete. A 30-year mortgage, for example, will have 360 rows.