VBA NPER Function

HomeVBA Functions LIST (Category Wise)How to use the VBA NPER Function (Syntax + Example)

What is VBA NPER Function

The VBA NPER function is listed under the financial category of VBA functions. When you use it in a VBA code, it calculates the number of payments that need to be made for a loan or an investment where payment and rate of interest are the same.

how to use it

To use VBA's NPER function you need to understand its syntax and arguments:

Syntax

NPER(Rate, Pmt, Pv, [Fv], [Due])

Arguments

  • Rate: The rate of interest for the period.
  • Pmt: The fixed amount of payment per period.
  • PV: The present value of investment or loan.
  • [FV]: The future value of the loan/investment [This is an optional argument and if omitted VBA takes 0 by default].
  • [Due]: Defines whether the payment is due at the start or the end of the period, use 0 for the end of the period and 1 for the start of the period [This is an optional argument and if omitted VBA takes the end of the period by default].

Example to use NPER Function in VBA

To practically understand how to use VBA NPER function, you need to go through the below example where we have written a vba code by using it:

Sub example_NPER()
Range("A9").Value = NPer(0.08 / 12, 1000, 96000)
End Sub

In the above code, we have used the NPR to calculate the total number of periods for the investment and it has returned 67.17.

Notes

Below are some important points which you need to take care while using NPER function in VBA.

  • Both "rate" and "nper" arguments need to calculate using payment periods that are expressed in the same units.
  • If the future value supplied will never be met for the supplied interest rate and payments then the VBA will return an error.

About the Author

puneet one point one

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.