How to use the VBA NPV Function (Syntax + Example)

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

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.

Syntax

NPV(Rate, ValueArray)

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

Notes

  • 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

DDB | FV | IPMT | IRR | MIRR | NPER | PMT | PV | RATE | SLN | SYD