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.
- 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].
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.
- 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.