Creating an Amortization Schedule
As noted in the beginning, an amortization schedule is simply a listing of each payment and the breakdown of interest, principal, and remaining balance. For this loan, an amortization table for the first six months would look like this:
The first thing that we want to do is to set up the table starting with the labels in A8:E8. Now, in column A we want a series of numbers from 0 to 360 (the maximum number of payments that we are going to allow). To create this series, select A9 and then choose Edit » Fill » Series from the menus. This will launch the Series dialog box. Fill it in exactly as shown, and then click the Ok button.
At this point, we are ready to fill in the formulas. Start with the beginning principal in E9 with the formula: =B2. That will link it to the principal balance as given in the input area. Now, select B10 and enter the formula: =PMT(B$4/B$5,B$3*B$5,-B$2), and you will see that the monthly payment is $1,297.20 as shown above. In C10 we will calculate the interest portion of the first payment with the formula: =IPMT(B$4/B$5,A10,B$3*B$5,-B$2). The principal portion of the payment can be calculated, in D10 with: =PPMT(B$4/B$5,A10,B$3*B$5,-B$2). Finally, we calculate the remaining balance in E10 with the formula: =E9-D10.
Check your results against those shown above, being very careful to type the formulas exactly as shown (the $ are important because they freeze the cell references so that they don’t change when we copy the formulas down). Once your results in row 10 match the picture, copy the formulas all the way down to the end of the table in row 369. (Note: The easiest way to do this is to select B10:E10 and then double-click the Auto Fill handle in the lower right corner of the selection. This will copy the formulas to the end of the current range, which is defined by the last data point in column A.)
You can now go into the input area (B2:B5) and change the loan terms. The amortization schedule will automatically recalculate.