Excel NPER Function

This tutorial demonstrates how to use the Excel NPER Function in Excel to calculate the number of periods for an investment.

NPER Main

NPER Function Description:

The NPER Function Calculates the total number of payment periods.

To use the NPER Excel Worksheet Function, select a cell and type:
nper formula syntax

(Notice how the formula inputs appear)

NPER Function Syntax and Inputs:

rate – The interest rate for each period. For example, if a housing loan is obtained at an annual interest rate of 9% and payments are made monthly. Then the interest rate needed to be calculated in months and then entered into the formula. In this case, the interest rate would be 9%/12, or 0.75%, or 0.0075.

pmt – It’s the payment per period. Usually, it can be the principal amount and interest but doesn’t include taxes or other fees. For example, the monthly payments of five years of housing loan of $20,000 at an interest rate of 9% are $415.17. The PV argument must be provided in case pmt is omitted from the formula.

pv – The present value of the investment that future payments are worth now. In the excel PMT function, if the PV argument is omitted, it’s assumed to be zero (0). The [fv] argument must be provided in case PV is omitted.

fv – OPTIONAL: The Future Value of the investment or loan at the end of the number of payment periods. For example, a number of payments are made monthly at a 10% interest rate in a bank with the goal of the savings to be totaled at $10,000, at the end of five years. Then the future value of the savings is $10,000. If the FV argument is omitted, it’s assumed to be zero (0). The present value, PV must be provided in case FV is omitted.

type – OPTIONAL: The type argument shows when the payments are made, either at the end of the period by 0 or at the start of the period by 1. By default, in the excel PMT function, the payment type is considered 0.

What Is NPER?

NPER stands for Number of Periods. It is defined as the total number of periods over which constant payments are made with a constant interest rate.

If the Present Value is given then the NPER is calculated using the following equation:

NPER Formula with given PV

If the Future Value of the investment is given then NPER is calculated using this equation:

NPER Formula with FV given

Where:

PV = Present Value

FV = Future Value

R = Rate of Return

PMT = Payment per period

What Is Excel NPER Function?

Excel NPER function helps in calculating the total number of periods required to pay off the loan at a specific interest rate with constant payments.

Example 1

Let’s take a look at an example, where Excel NPER function is used to calculate the total number of payments required to pay off the student loan of $80,000 with an annual interest rate of 5%. The payment of $10,000 is made at the start of each year.

The annual interest rate charged on the student loan is

Rate = 5%

The regular payments being made at the start of the year and it is with a negative sign as it shows outgoing cash

PMT = -$10,000

The present value of the loan in the example given is

PV = $80,000

And the future value of the investment was not mentioned in the above example, so the [fv] argument is simply omitted or entered with the following value

FV = 0

The payments are being made at the start of the year, so the value of [type] argument is

Type = 1NPER Example 01

The Excel NPER function returns the following value

NPER = 9.83 years

It would take about 10 years to completely pay off the student loan.

Example 2

Suppose we invest $5,000 to achieve $100,000 at the end of the investment period. The annual rate of return is 8% and we also make regular contributions of $500, every month. Let’s find out how long it will take us to earn $100,000.

As the payments are made monthly, the annual interest rate is converted into monthly interest by

Monthly Interest Rate – 8% (annual interest rate) / 12 (months per year) = 0.67%

The regular payments being made at the start of the year and it is with a negative sign as it shows outgoing cash

PMT = -$500

The value of the initial investment or the present value of the investment is

PV = $5,000

And the future value of the investment is

FV = $100,000

And the type of the payments wasn’t mentioned in the example, so the [type] argument is simply omitted or have default value i.e.

Type = 0

NPER Example 02

The Excel NPER function returns the following value

NPER = 137.90

As the monthly interest rate was put in the Excel NPER function, the result is also in months. To convert it into years

Yearly number of payments NPER – 137.90 (monthly NPER) / 12 (months in a year) = 11.49 years

It would take about 11 and a half years to get $100,000 investment in return.

NPER in Google Sheets

All of the above examples work exactly the same in Google Sheets as in Excel.

NPER Google

Additional Notes

Use the NPER Function to calculate the number of payment periods of an investment.

In the Financial Functions the cash outflows, such as deposits, are represented by negative numbers and the cash inflows, such as dividends, are represented by positive numbers.

NPER Examples in VBA

You can also use the NPER function in VBA. Type:
application.worksheetfunction.nper(rate,pmt,pv,fv,type)
For the function arguments (rate, etc.), you can either enter them directly into the function, or define variables to use instead.

Return to the List of all Functions in Excel