The Excel NPER function has the syntax shown below.

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

**Arguments used in the Excel NPER function**

Nper = Number of periods (n)

Rate = Discount rate per period (i)

Pmt = Periodic payment

PV = Present Value

FV = Future Value

Type = 0 or 1 depending on whether Pmt is at the end or start of a period

## Use of the Excel NPER Function

The Excel NPER function can be used to calculate the number of periods in time value of money calculations including the following circumstances:

- For a lump sum investment (PV) to grow to a future value (FV)
- For an annuity payment (Pmt) to grow to a future value
- For an annuity due payment (Pmt) to grow to a future value
- For a loan balance (PV) to be cleared with regular payments(Pmt)
- For a loan balance (PV) to be cleared with regular payments(Pmt) and a balloon payment (FV)

## Number of Periods for a Lump Sum to Grow

The Excel NPER function can be used to calculate the number of periods it takes for a lump sum investment to grow from its present value (PV) to a future value (FV).

### Number of Periods for a Lump Sum to Grow Example 1

If a lump sum of 50,000 is invested at the start of period 1 at a discount rate of 7%, how many periods will it take to grow to 92,000.

Rate = 7% Pmt = 0 (not used) PV = -50,000 (negative as cash flow out) FV = 92,000 (positive as cash flow in) Type = 0 (not used) Number of periods = NPER(Rate, Pmt, PV, FV, Type) Number of periods = NPER(7%, 0,-50000, 92000, 0) Number of periods = 9.01

The Excel NPER function shows that it will take 9.01 periods for the investment to grow from 50,000 to 92,000 if the discount rate used is 7%.

It should be noted that PV and FV should have opposite signs. Excel is expecting an amount PV (50,000) to be invested at the start of the term (cash flow out or negative), and then expects the amount FV (92,000) to be returned at the end of the term (cash flow in or positive).

When using the Excel NPER function to calculate periods relating to a lump sum value, the Pmt and the Type arguments are not used, they can be left blank or set to zero.

### Number of Periods for a Lump Sum to Grow Example 2

If the discount rate is 5% per year compounded monthly, calculate the number of months it takes for an investment to grow from 30,000 to 60,000.

Rate = 5%/12 monthly Pmt = 0 (not used) PV = -30,000 (negative as cash flow out) FV = 60,000 (positive as cash flow in) Type = 0 (not used) Number of periods = NPER(Rate, Pmt, PV, FV, Type) Number of periods = NPER(5%/12, 0,-30000, 60000, 0) Number of periods = 166.70 months

Since the rate and compounding are monthly, the period is one month and the answer is 166.70 months.

## Number of Periods for an Annuity to Grow

The Excel NPER function can be used to calculate how many periods it will take for an annuity (a series of regular periodic payments at the **end** of each period) to grow to a given future value.

### Number of Periods for an Annuity to Grow Example

If an amount of 600 is invested at the end of each period and the discount rate is 4%, how many periods will it take for the investment to grow to 8,000.

Rate = 4% Pmt = -600 (negative as cash flow out) PV = 0 (not used) FV = 8,000 (positive as cash flow in) Type = 0 Number of periods = NPER(Rate, Pmt, PV, FV, Type) Number of periods = NPER(4%, -600, 0, 8000, 0) Number of periods = 10.90 periods

At a discount rate of 4%, it will take 10.90 periods for the annuity of 600 per period is grow to a value of 8,000.

In this example the argument PV is not used and can be set to 0 or left blank. The argument Type is used and is set to 0 (or can be left blank), and indicates that the annuity payment is at the end of each period.

## Number of Periods for an Annuity Due to Grow

The Excel NPER function can be used to calculate how many periods it will take for an annuity due (a series of regular periodic payments at the **start** of each period) to grow to a given future value.

### Number of Periods for an Annuity Due to Grow Example

If an amount of 600 is invested at the **start** of each period and the discount rate is 4%, how many periods will it take for the investment to grow to 8,000.

Rate = 4% Pmt = -600 (negative as cash flow out) PV = 0 (not used) FV = 8,000 (positive as cash flow in) Type = 1 Number of periods = NPER(Rate, Pmt, PV, FV, Type) Number of periods = NPER(4%, -600, 0, 8000, 1) Number of periods = 10.56 periods

At a discount rate of 4%, it will take 10.56 periods for the annuity due of 600 per period is grow to a value of 8,000.

In this example the argument PV is not used and can be set to 0 or left blank. The argument Type is used and is set to 1, and indicates that the annuity payment is at the start of each period.

The only change made in this example compared to the previous annuity example is that the Type argument has been set to 1 to represent payments at the start of the period. This has reduced the number of periods required since the payments from the annuity (600) are being invested one period earlier.

## Number of Periods to Clear a Loan Balance

The Excel NPER function can be used to find the number of periods it takes a regular payment (Pmt) to reduce a loan balance (PV) to zero.

### Number of Periods to Clear a Loan Balance Example

If a loan of 40,000 has a discount rate of 6% and periodic loan repayments of 5,000 at the **end** of each period, how long will it take for the balance to be cleared. The number of periods is given by the Excel NPER function as follows:

Rate = 6% Pmt = -5,000 (negative as cash flow out) PV = 40,000 (loan balance) FV = 0 (not used) Type = 0 Number of periods = NPER(Rate, Pmt, PV, FV, Type) Number of periods = NPER(6%, -5000, 40000, 0, 0) Number of periods = 11.22 periods

At a discount rate of 6%, it will take 11.22 periods for the loan balance of 40,000 to be reduced to zero with payments at the end of each month of 5,000.

In this example the argument FV is not used and can be set to 0 or left blank. The argument Type is used and is set to 0 (or can be left blank), and indicates that the annuity payment is at the end of each period.

### Loan Payments made at the Start of a Period Example

The same calculation can be carried out with payments made at the start of each period by setting the Type argument to 1. In this case the Excel NPER function gives

Rate = 6% Pmt = -5,000 (negative as cash flow out) PV = 40,000 (loan balance) FV = 0 (not used) Type = 1 (payments at the start of the period) Number of periods = NPER(Rate, Pmt, PV, FV, Type) Number of periods = NPER(6%, -5000, 40000, 0, 1) Number of periods = 10.35 periods

### Balloon Payments at the end of a Loan Example

By using the FV argument to represent a cash outflow at the end of the term, the Excel NPER function can be used to calculate the number of periods to clear a loan balance taking into account a final balloon payment.

For example, if a loan of 40,000 has a discount rate of 6% and periodic loan repayments of 5,000 at the end of each period, how long will it take for the balance to be cleared assuming a balloon payment of 18,176.02 is to be made on the last day of the term. The number of periods is given by the Excel NPER function as follows:

Rate = 6% Pmt = -5,000 (negative as cash flow out) PV = 40,000 (loan balance) FV = -18,176.02 (negative as cash flow out) Type = 0 Number of periods = NPER(Rate, Pmt, PV, FV, Type) Number of periods = NPER(6%, -5000, 40000, -18176.02, 0) Number of periods = 7 periods

At a discount rate of 6%, it will take 7.00 periods for the loan balance of 40,000 to be reduced to zero with payments at the end of each month of 5,000, and a final balloon payment of 18,176.02.

## Using the Excel NPER Function with Variable Arguments

Excel is at its most useful when the arguments can be varied without having to enter the Excel function each time a calculation is made. To do this a simple spreadsheet can be set up as shown below.

In this example spreadsheet, the variable arguments are entered in cells B4 to B8, and the Excel NPER function is entered at cell B11 as =NPER(B4,B5,B6,B7,B8). By changing any of the variables in B4 to B8, the number of periods can be recalculated without having to enter the Excel NPER function each time.

The Excel NPER function is one of many Excel financial functions used in time value of money calculations, discover another at the links below.

## About the Author

Chartered accountant Michael Brown is the founder and CEO of Plan Projections. He has worked as an accountant and consultant for more than 25 years and has built financial models for all types of industries. He has been the CFO or controller of both small and medium sized companies and has run small businesses of his own. He has been a manager and an auditor with Deloitte, a big 4 accountancy firm, and holds a BSc from Loughborough University.