An amortization table in Excel can be created using the PMT, PPMT and IPMT functions. The table reflects the amount of each payment, the portions that go toward principal or interest and the outstanding balance.
An amortization table is used to outline the details of a long-term loan, such as a mortgage or auto loan. It displays each payment made toward paying off the loan.
Step 1: Create the necessary rows
The rows should be labeled "annual interest rate," "years," "payments per year," "amount of loan" and "payment amount." The next column over should have all the applicable figures next to these cells.
Step 2: Calculate the monthly payments
Use the PMT Excel function to calculate the payments. This function takes into account the interest rate, the total number of payments, the presentÂ value and the future value of the loan.
Step 3: Calculate how the payment will be applied
Use the PPMT function and the IPMT function to figure out how much of each payment will go toward principal, and how much toward the interest. After subtracting both from the outstanding amount, the balance should be updated.
Step 4: Outline the rest of the payments
Select the cells reflecting the first payment amounts and balance and drag it down one row. Update the formula to reflect the data for the second payment. Repeat for the remaining number of payments, as necessary, until the balance reaches zero.
Learn MoreIn Excel, the mean function is used to calculate the average of a set of numbers. The numbers used can come from any cell in an Excel spreadsheet by listing the cells involved into the average function box.
Full Answer >To divide by the sum of cells A1 through A10 by 2 in Excel, use the formula: =SUM(A1:A10)/2. It is also possible to enter numbers directly into the formula.
Full Answer >Though there are several functions in Excel for rounding numbers, the ROUND function is the most basic and the most commonly used. Keep in mind that when using any rounding function, its result is always treated as rounded by any formulas that reference it and can potentially alter calculations.
Full Answer >To remove duplicates in Excel, select the cells that include the duplicates, click on the Data tab, and select the Remove Duplicates option. Click OK on the dialogue box to delete the duplicate cells.
Full Answer >