Excel 2000 in a Nutshell

Name

NPER

Synopsis

You can use NPER to determine how many payments are needed to reach your future value on an investment, or how many payments are needed to pay off a loan. To use this function the loan or any other type of investment must have a constant rate of interest.

To Calculate

=NPER(Rate, Pmt, PV, FV, Type)

The FV and Type arguments are optional, but the FV argument should only be omitted when calculating the payment for a loan. All other arguments are required for this function.

Rate

Indicates the interest rate used to calculate the rate for each period. For example, if you make monthly payments and the interest rate is 12% you need to specify an interest rate of 0.01 (0.12/12) for each month.

Pmt

Indicates payment that is made for each period. This amount is the payment toward principal and interest. For example, if you are making monthly payments of $355, that is the value of the Pmt argument. The value of this argument should be negative if the amounts are paid out, such as a loan or deposit in an account. If the payment is received, such as dividend checks, this argument should have a positive value.

Example

NPER determines the number of payments remaining on a loan, as shown in Figure 12-16. The value of the Rate argument was determined by taking the annual interest rate of 9.5%, expressed as a decimal value of 0.095, and dividing it by 12, as there are monthly payments made on the loan. Also, the payment amount is negative to reflect that it is a payment. Since the ...