Q:

# How can you make an amortization table in Excel?

A:

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.

## Keep Learning

Write 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.

Sources:

## Related Questions

• A: Each worksheet in an Excel 2010 workbook can accommodate up to 1,048,576 rows, along with up to 16,384 columns. Each row is limited to 409 points in height... Full Answer >
Filed Under:
• A: The AutoSum feature in Excel provides a shortcut to adding up columns or rows of numbers in an Excel spreadsheet. It can add up to 255 numbers at one time.... Full Answer >
Filed Under:
• A: One can use Excel to create reports with detailed spreadsheet databases, customized tables with rows and columns, as well as pivot tables. Generally, Excel... Full Answer >
Filed Under:
• A: Microsoft Excel can be used to generate a loan amortization schedule. Microsoft Support provides detailed instructions for the creation and graphical repre... Full Answer >
Filed Under:
PEOPLE SEARCH FOR