Microsoft Excel has many financial functions. Functions related to calculating mortgage payments or car loans are especially popular.
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.
