Microsoft Office Tutorials and References
In Depth Information
Figuring Loan Calculations
The PPMT function returns the amount of the payment that reduces the
principal. This amount will be smaller than the full periodic payment amount.
How much smaller depends on which sequential payment is being examined.
The remainder of the payment, of course, is the interest charge.
The PMT function tells how much each payment is. The IPMT function tells
you the interest portion. The PPMT tells you the principal function. For any
given payment period, the amounts returned by IPMT and PPMT should equal
the amount returned by PMT.
You can use two optional arguments with PPMT:
✓ Future Value: The amount you want the loan to be worth at the end of
its life. The default is 0.
✓ Type: This tells the function whether payments are applied at the end
of the period or the beginning of the period. A value of 0 indicates the
end of the period. A value of 1 indicates the beginning of the period. The
default is 0.
These optional arguments, when used, become the respective fifth and sixth
Calculating the number of payments
The NPER function tells you how many payments are necessary to pay off
a loan. This is useful when you know how much you can afford to pay per
month, and need to know how long it will take to pay off the loan. The inputs
for this function are the principal, the interest rate, and the periodic payment
Here’s how to use the NPER function:
1. Enter the following into separate cells on your worksheet:
Enter the periodic payment amount as a negative number because
payments are a cash flow out. You can add labels to adjacent cells to
identify the values, if you want.