Track Your Next Loan in Excel
October 22, 2007
While I have shown how to use the PMT function in past shows, today I want to show how to create an amortization table.
- Build the input section of the worksheet as shown in A1:B3. The formula for cell B4 is shown in red in C4.
- Enter the headings shown in Row 6.
- The initial balance in D7 is a formula that points to B1.
- In A8:A67, enter the numbers 1 through 60. (Tip: Enter 1 in A8. Select A8. Hold down the Ctrl key while you drag the Fill Handle downwards. The Fill Handle is the square dot in the lower right corner of the cell).
Use the PPMT function to calculate interest principal for any given payment in column B. Enter this formula in B8:
Use the IPMT function to calculate interest for any given payment in column C. Enter this formula in CB8:
- The formula in D8 is =D7-B8.
- Copy B8:D8 down to rows 9 through 67
One tip mentioned in the show is to replace column A with a reference to
ROW(A1). You can change the formula in B8 to be