How to Create an Amortization Table in Excel
The first step to creating an amortization table is to calculate the monthly payment. After that, the amount of principal and the amount of interest included in each payment needs to be calculated. The list that is generated with all of this information is called the amortization table. Spreadsheets such as Microsoft Excel make these calculations easy. Once the payment and the first month's principal and interest are calculated, all that is necessary is to copy the formulas until the entire table is constructed.
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)
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.
In cell A4, type the initial loan value. For the previous example, you would type "200000."
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.
Calculate the principal on the first payment. In cell B4, type the formula:=$A$1-C4This subtracts the calculated interest from the calculated monthly payment.
Calculate the second month's unpaid balance. In cell A5, type the formula:=A5-B4This subtracts the principal from the previous month's payment from the loan balance.
Copy the formulas down each column until the table is complete. A 30-year mortgage, for example, will have 360 rows.
Tips & Warnings
- A mortgage with bi-weekly payments can be amortized, as well. Simply replace "12" with "26" in the "PMT" function and in the interest calculations.