Excel: NPer Function
In Excel, the NPer function returns the number of periods for an investment based on an interest rate and a constant payment schedule.
The syntax for the NPer function is:
NPer( interest_rate, payment, PV, FV, Type )
interest_rate is the interest rate for the investment.
payment is the amount of the payment for each period. This should be entered as a negative value.
PV is the present value of the payments.
FV is optional. It is the future value that you'd like the investment to be after all payments have been made. If this parameter is omitted, the NPer function will assume a FV of 0.
Type is optional. It indicates when the payments are due. Type can be one of the following values:
Value Explanation 0 Payments are due at the end of the period. (default) 1 Payments are due at the beginning of the period.
If the Type parameter is omitted, the NPer function assumes a Type value of 0.
Applies To:
- Excel 2007, Excel 2003, Excel XP, Excel 2000
For example:
Let's take a look at a few examples:
This first example returns the number of monthly payments (monthly payments are $150) for a $5,000 investment that earns 7.5% annually. Payments are due at the end of the period.
=NPer(7.5%/12, -150, 5000)
This next example returns the number of weekly payments (weekly payments are $175) for a $8,000 investment that earns 6% annually. Payments are due at the beginning of each week.
=NPer(6%/52, -175, 8000, 0 ,1)
This next example returns the number of annual payments (annual payments are $200) for a $1,500 investment that earns 5.25% annually. Payments are due at the end of each year.
=NPer(5.25%/1, -200, 1500)
VBA Code
The NPer function can also be used in VBA code. For example:
Dim LValue As Double
LValue = NPer(0.0525/1, -200, 1500)
In this example, the variable called LValue would now contain the value of 9.780722988.