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

arguments.

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

amount.

Here’s how to use the NPER function:

1. Enter the following into separate cells on your worksheet:

•Loanprincipal

•Annualinterestrate

•Periodicpaymentamount(theamountyoucanaffordtopay)

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.