How Do I Create a Loan Amortization Table?

You can create an amortization spreadsheet for a fixed-rate mortgage using spreadsheet software like Microsoft Excel or OpenOffice Calc. A spreadsheet amortization table will allow you to see how the monthly payments are allocated to interest and principal. The spreadsheet allows changes in the loan amount, rate and term to see the effects on the loan payments.


Set up a loan data table in a new spreadsheet. One column will have labels for the loan data, including Loan Amount, Term in Months, Interest Rate and Payment. Put the labels in column A, cells 1 through 4. The loan data will go in column B. The rate cell should be formatted as a percentage, and the Loan Amount and Payment can be set up as currency.


Use the PMT financial function to calculate the monthly mortgage payment for cell B4. The loan amount, rate and term should be in cells B1 through B3. The payment calculation can be set up as =PMT(B2/12,B3,-B1), entered into cell B4.


Verify the PMT is calculated correctly. Use a loan amount of $100,000, an interest rate of 5 percent and a term of 360 months. The resulting payment should be $536.82.


Enter column labels for the amortization table in line 6 of the spreadsheet. Cells A through E can be labeled Payment Number, Principal, Interest, Total Interest Paid and Loan Balance.


Use the spreadsheet cell autofill feature to put the payment numbers in column A, starting with cell A7. You can autofill by typing 1 in cell A7 and then using your mouse to click and hold on the little black square in the lower right corner to pull the numbers down the column.


Use the PPMT and IPMT functions to calculate the principal and interest amounts for the first payment. These two functions' formulas go into cells B7 and C7: =PPMT($B$2/12,A7;$B$3,-$B$1) =IPMT($B$2/12,A7,$B$3,-$B$1) The first payment principal and interest amounts should show as $120.15 and $416.67, respectively.


Start the calculations in the Loan Balance column by typing =B1-C7 into column D7. In cell D8, type =D7-B8. Use the autofill to pull the formula from cell E8 into the rest of the Total Interest Paid column.


Start the calculations in the Total Interest Paid column by typing =C7 into column E7. In cell E8, type =E7-C8. Use the autofill to pull the formula from cell E8 into the rest of the Total Interest Paid column.

Things You Will Need

  • Microsoft Excel
  • OpenOffice Calc


  • If the amortization is functioning properly, the interest amount should decrease slightly with each payment and the principal amount should increase by the same amount. The interest and principal should always add up to the payment amount.
  • OpenOffice is a free software suite similar to Microsoft Office. OpenOffice Calc uses semicolons instead of commas between the function values.


  • If you get an error message, check the punctuation of your functions or use the function wizard of the software to analyze the problem.

About the Author


Suggest a Correction