Microsoft Office Tutorials and References
In Depth Information
Figuring Loan Calculations
that goes toward reducing the principal of the loan and another portion that
is interest. With the PPMT function, you can find out the amount that reduces
The ratio of the interest portion to the payment on principal portion varies
payment by payment. In a typical loan, the portion of the payment that
is interest is highest in the first period and is reduced in each successive
period. Turning that around, the last payment is almost all toward paying
down the principal.
The PPMT function takes four inputs: the principal, the interest rate, the
number of payments for the loan, and the number of the payment in
question. For example, a loan may have 36 payments, and you’re interested in
how much principal is included in just the last payment. Here are the steps
to use this function:
1. Enter the loan principal, the annual interest rate, the number of
payment periods, and the number of the actual period for which the
interest is to be calculated in separate cells within the worksheet.
You can add labels to adjacent cells to identify the values, if you want.
2. Position the cursor in the cell where you want the results to appear.
3. Enter =PPMT( to begin the function entry.
4. Click the cell where you entered the interest rate, or just enter the cell
5. Enter /12 to divide the annual interest rate to get the monthly
6. Enter a comma ( ,).
7. Click the cell where you entered the number of the payment to
analyze, or just enter the cell address.
8. Enter a comma ( ,).
9. Click the cell where you entered the number of payments, or just
enter the cell address.
10. Enter a comma ( ,).
11. Click the cell where you entered the principal amount, or just enter
the cell address.
12. Type a ), and press Enter.