To make an amortization table in Excel, place the annual interest rate, the amount of the loan, the loan term and payments per year in consecutive rows in the A and B columns. Use the PMT, PPMT and IMPT functions to calculate required variables, explains Excel Easy.
Continue ReadingWrite Annual Interest Rate in A1, Loan Term in Years in A2, Payments Per Year in A3 and Loan Amount in A4, states Excel Easy. In the B column, enter in the corresponding value to each term. Leave row five blank, and then across row six write Month, Payment, Principal, Interest and Balance in separate columns from A to E. Starting in A6, type in one and then continue for the number of payments. For example, a two-year loan requires 24 payments.
To calculate the payments, write the following equation in box B6, = PMT(B$1/B$3,B$2*B$3,B$4), according to Dr. Timothy R. Mayes for TVMCalcs.com. The payment amount appears as a negative number, so highlight the cell, go to the box on the home page that says General and change it to Accounting. Do this for all of the boxes. In column C, starting at row six, type in the equation, =PPMT(B$1/B$3, A6, B$2*B$3,B$4). In column D, starting at row six, type in the equation, =IMPT(B$1/B$3, A6, B$2*B$3,B$4). In column E, the balance is equal to the amount of the loan minus the principal. Highlight B6 to E6; in the bottom right corner, there is a tiny square, click on that and drag the highlight down as many rows as the payments to have the equations fill in for all the boxes.
Learn more about Software