Many of the participants I meet in Microsoft Excel training are interested in Excel’s financial functions. Functions related to calculating mortgage payments or car loans always generate a lot of interest… no pun intended!

**NPER is a financial function in Microsoft Excel. The NPER function will calculate the number of payment periods required to repay a loan or reach a savings goal, based on a constant interest rate and a series of future payments.**

In this article, we’ll cover everything an aspiring financial analyst needs to know to use the NPER function like a boss!

## NPER Function Syntax

The NPER function in Excel can utilize up to five arguments. Three are required, two are optional.

Here is the syntax for the Excel NPER function.

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

**rate** is a *required* argument. It represents the periodic interest rate.

**pmt** is a *required* argument representing the payment applied each period. Payments must be constant for the entire duration.

**pv** is a *required* argument representing the present value or the lump-sum amount that a series of future payments is worth right now.

**fv** is an *optional* argument representing the future value or the cash balance we want after the last payment is made. If fv is omitted, it defaults to 0.

**type** is an optional argument that specifies when payment are due. 1 = at the beginning of the period, 0 or omitted = at the end of the period.

## Using the NPER Function With a Loan

Let’s look at an example.

In the screenshot below, I have created a worksheet to determine how many monthly payments it will take to completely repay a loan.

- Cell B1 contains the amount to be financed: $20,000.
- Cell B3 contains the annual interest rate: 2.9%.
- Cell B4 contains the periodic interest rate: .2417%. To calculate the interest charged per period, divide the value in cell B3 by 12.
- Cell B6 contains the monthly payment amount: $500.

In order to calculate the number of payments needed to completely repay this loan, I entered the NPER formula in cell B8:

**=NPER(B4,-B6,B1,0,0)**

Note that *pmt* (the monthly payment) is a negative amount because I’m debiting my account $500 per month to pay the loan.

*fv* (future value) is 0 because I want to completely repay the loan.

*type* is 0 because I’m not making a loan payment until the start of the following month, after I secure financing.

The default values for *fv* and *type* are both 0 so I could have omitted them and entered the NPER formula like this:

**=NPER(B4,-B6,B1)**

Either way, I will need to make 42 payments of $500 plus one partial payment to fully repay the loan.

## Using NPER to Calculate the Number of Periods to Achieve a Savings Goal

With a couple of small changes, the Excel NPER function can be used to calculate the number of monthly payment periods to achieve a savings goal.

In this example, my goal is to set aside $20,000 in savings by depositing $500 per month into an account that pays .9% interest per year.

I’m starting with $0 and contributing money to an account at the beginning of each payment period.

I entered the NPER function into cell B8:

**=NPER(B4,-B6,0,B1,1)**

In this scenario, I need to make 39 payments of $500 plus one partial payment to achieve my goal of $20,000 in savings.

## What’s Next?

I hope you found this article about the Excel NPER function informative.

If you would like to learn about more functions in Excel, you should check out XLOOKUP vs VLOOKUP: Excel Trainer Explains Key Differences. Both of these functions are incredibly popular with Excel users who need to combine data from multiple lists.