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:
- 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.
About the Author
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 ﬁnd him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.