NPER Function in Excel: Syntax and Examples

This article provides a clear overview of NPER Function in Excel, with the main facts, useful context, and practical details organized for easy reading.

What This Feature Does

The function is used to calculate the number of periods for an investment with a given security based on equal payments and a constant interest rate.

Syntax

NPER (rate, pmt, pv, [fv], [type]) .

Arguments

- rate: Interest rate of securities according to maturity, required parameters.

- pmt: Payments for each period, required parameters. Typically pmt contains principal and interest.

- pv: Current value or one-off payment amount corresponding to future payments, it is a required parameter.

- fv: Future value or your desired balance after the final payment is made, this parameter is not required, if omitted the default is 0.

- type: Determine the payment time, there are 2 values: 0 ? final payment and 1 ? payment at the beginning of the period.

Example

Give the following data table.

- Calculate the number of periods of the investment.

- Calculate the number of periods of the investment minus initial payments.

- Calculate the number of periods of the first paragraph unless the future value is 0.

1. Calculate the Number of Periods of the Investment

- in the target cell enter the following formula: = NPER (D5 / 12, D6, D7, D8, D9) .

- Results:

2. Calculate the Number of Periods of the Investment Minus the Initial Payments

- in the target cell, enter the formula: NPER (D5 / 12, D6, D7, D8) implies the final payment.

- Results:

3. Calculate the Number of Periods in the First Clause Unless the Future Value Is 0

- in the target cell enter the following formula: = NPER (D5 / 12, D6, D7) .

- Results:

The result of comparing 3 cases if the payment at the end of the period is higher than that at the beginning of the period, the case of the future value equal to 0, the number of periods is too small to be negative.