NPER Function in Excel – Calculating Number of Periods

When taking a loan, one must be willing to know how many payments will he/she have to pay in total to pay for the loan amount. As a Financial Analyst, it is important to know how long would it take to reach the desired corpus. All of this can be resolved using the NPER Function in Excel.

Here we go 😎

When to Use NPER Function in Excel

The word NPER represents “Number of Periods”. The NPER function helps to get the total number of periods for a loan, investment, or project with constant payments and time intervals.

The NPER Function comes under the category of Financial Functions in Excel.

Syntax and Arguments

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

The below list contains information about the excel NPER Function.

• rate – It is the amount of interest for each period.
• pmt – It is the periodic payment that one need to pay either at the end or beginning of each period.
• pv – It is the present value. i.e loan amount or initial value of the investment.
• fv – fv is an optional argument that represents the final or future value of the investment.
• type – This is also an optional NPER Function argument. If payments are made at the starting of the period, it is 1 otherwise zero. If not specifies, the function assumes type = 0.

Points to Keep in Mind About NPER Formula

The following points about NPER Formula must be kept in mind before using it.

• The rate can vary annually, quarterly, monthly, semi-yearly. For companies that charge the rate monthly, quarterly or semi anually, we need to convert the annual rate. In other words, if the annual rate is 12%, the actual rate becomes 12%/12 (monthly), 12%/4 (quarterly) or 12%/2 (semi-yearly) for each period.
• pmt for each period includes the principal amount plus interest amount. It does not contain the taxes, fees or any additional charges within it.
• Keep the cash received as positive and cash deducted as negative.
• #NUM! error occurs when the present value and pmt are not enough to meet the future value of the project.

Examples to Learn the NPER Formula in Excel

In this section of the blog, we will do examples to learn the NPER formula of Excel.

Ex.1 – Simplest Example for NPER Function

Let us assume that a person is willing to take a loan worth $30,000. The annual interest rate is 5% or 0.05. The periodic payment to be due at the end of each period is $566.14.

A year contains 12 months. Therefore the number of periods in a year is 12. Apply the function formula to obtain the number of periods for this loan.

=NPER(B3/B4,-B2,B1)

As a result, the function has returned 60 periods. In other words, we can say that there will be a total of 60 payments ($566.14 each) at the end of this loan. The loan amount along with interest clears by 60/12 = 5 years.

Explanation – We will start with the function arguments. The annual rate changes into a monthly rate by dividing the annual rate (B3) by the number of periods in a year (B4). B2 is the periodic payment. It is negative (-B2) because payment deducts at the end of each period and it is a cash outflow. B1 is the loan amount. The function thus returned the number of periods or payments to be made to clear this loan. fv and type function arguments are zero, by default.

Ex.2 – Calculating the Number of Periods of an Investment Project

Let us assume that there is a project whose present value is $1,500. The project requires a quarterly payment of $407.27. The company is giving interest of 9.5% or 0.095 on this investment. The goal ( future value ) of this investment is $10,000.

Apply the below formula to get the number of periods for this investment.

=NPER(B3/B4,-B2,-B1,B5)

As a result, the function has returned 16 periods/quarters.

Explanation – We will go with explaining function arguments.

• The annual rate of return converts to quarterly rate of return by dividing the annual rate (B3) by total number of periods in a year (B4). This is the rate function argument.
• The pmt function argument is negative in this case as it is cash outflow. (-B2)
• The pv is also negative as it is a cash outflow (-B1).
• The future value of this investment is $10,000. This is positive (B5) as it is the cash inflow at the end of this project.
• The type function argument is not specified, so it is zero by default.

So this project will end after 16 quarters or 16/4 = 4 years.

Thank you for reading 😉