The VBA NPV function is listed under the financial category of VBA functions. When you use it in a 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.
- Rate: The discount rate over one period (this should be supplied as a decimal).
- ValueArray: An array of cash flow that 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].
To practically understand how to use the 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 as a result, it has returned 3710.
- If the array we supplied doesn’t have one value as negative and one value as positive, VBA will return run-time error 5.