VBA NPV Function

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

What is VBA NPV Function

The VBA NPV function is listed under the financial category of VBA functions. When you use it ina VBA code, it calculates the net present value of an investment using the discount rate and a series of future cash flows. You can learn more about net present value from here.

how to use it

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


NPV(Rate, ValueArray)


  • Rate: The discount rate over one period (this should be supplied as a decimal).
  • ValueArray: An array of cash flow which represents the payments and income. Payments would be in negative values and incomes would be in positive values [It must contain at least one negative and at least one positive value].

Example to use NPV Function in VBA

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

Sub example_NPV()
Dim cF(0 To 9) As Double
Dim dRate As Double
cF(0) = -1000
cF(1) = 213.6
cF(2) = 259.22
cF(3) = 314.6
cF(4) = 381.79
cF(5) = 463.34
cF(6) = 562.31
cF(7) = 682.42
cF(8) = 828.19
cF(9) = 1005.09
Range("A1").Value = NPV(dRate, cF)
End Sub

In the above code, we have used NPV to calculate the net present value for the investment. We have used -1000 as the initial investment amount and the nine incoming payments and in the result, it has returned 3710.


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

  • If the array we supplied doesn’t have the one value as negative and one value as positive, VBA will return run-time error 5.

Related Functions

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.