How to use the VBA NPV Function

In this tutorial, you will learn how to use the VBA NPV function to calculate the net present value of an investment based on a series of cash flows and a discount rate. The net present value is a measure of how much an investment is worth today, considering the time value of money.

The VBA NPV function has the following syntax:

NPV (Rate, ValueArray)

Where:

• Rate is the discount rate per period, expressed as a decimal. For example, if the annual discount rate is 10%, and the cash flows are monthly, then the rate per period is 0.1 / 12 = 0.008333.
• ValueArray is an array of cash flow values, where negative values represent payments and positive values represent receipts. The NPV function assumes that the first cash flow occurs at the end of the first period.

Using the NPV Function with an Array

To use the VBA NPV function, you need to declare a variable to store the result, and assign it the value of the function with the appropriate arguments. For example, suppose you have an investment that requires an initial payment of $10,000, and then generates monthly cash flows of $1,000 for 12 months. The annual discount rate is 10%. You can calculate the net present value of this investment as follows:

Sub NPV_Demo1()
Dim NetPV As Double 'Declare a variable to store the result
Dim Values(12) As Double
Values(0) = -10000
Values(1) = 1000
Values(2) = 1000
Values(3) = 1000
Values(4) = 1000
Values(5) = 1000
Values(6) = 1000
Values(7) = 1000
Values(8) = 1000
Values(9) = 1000
Values(10) = 1000
Values(11) = 1000
Values(12) = 1000
NetPV = NPV(0.1 / 12, Values) 'Assign it the value of the NPV function
MsgBox "The net present value of this investment is " & Format(NetPV, "Currency") 'Display the result
End Sub

The result is $1,363.15, which means that this investment is worth more than its cost today.

Note that if your first cash flow occurs at the beginning of the first period, you need to adjust your calculation by adding the first value to the result of the NPV function. For example, suppose you have an investment that requires an initial payment of $10,000 at the beginning of the first month, and then generates monthly cash flows of $1,000 for 12 months. The annual discount rate is 10%. You can calculate the net present value of this investment as follows:

Sub NPV_Demo2()
Dim NetPV As Double 'Declare a variable to store the result
Dim Values(11) As Double
Values(0) = 1000
Values(1) = 1000
Values(2) = 1000
Values(3) = 1000
Values(4) = 1000
Values(5) = 1000
Values(6) = 1000
Values(7) = 1000
Values(8) = 1000
Values(9) = 1000
Values(10) = 1000
Values(11) = 1000
NetPV = -10000 + NPV(0.1 / 12, Values) 'Add the first value to the result of the NPV function
MsgBox "The net present value of this investment is " & Format(NetPV, "Currency") 'Display the result
End Sub

The result is $1,374.51, which is higher than the previous case because you pay less interest on your initial payment.

Ready to do more with VBA?
We put together a giant PDF with over 300 pre-built macros and we want you to have it for free. Enter your email address below and we'll send you a copy along with our VBA Developer Kit, loaded with VBA tips, tricks and shortcuts.

Using the NPV Function with a Loop

You can also use a loop to populate your ValueArray with dynamic values from a worksheet range or a user input. For example, suppose you have a worksheet with the following data:

You can calculate the net present value of this investment as follows:

Sub NPV_Demo3()
Dim NetPV As Double 'Declare a variable to store the result
Dim Rate As Double 'Declare a variable to store the discount rate
Dim ValueArray() As Double 'Declare an array to store the cash flow values
Dim i As Long 'Declare a variable to loop through rows
Dim n As Long 'Declare a variable to count rows

Rate = Range("B1").Value / 12 'Get the discount rate per month
n = WorksheetFunction.Count(Range("B4:B15")) 'Count the number of cash flow values
ReDim ValueArray(1 To n) 'Resize the array to match the number of values
For i = 1 To n 'Loop through each row
    ValueArray(i) = Range("B" & i + 3).Value 'Assign the value to the array element
Next i 'Go to the next row
NetPV = WorksheetFunction.NPV(Rate, ValueArray) 'Calculate the net present value using the NPV function
NetPV = NetPV + Range("B2").Value 'Add the initial payment to the result
MsgBox "The net present value of this investment is " & Format(NetPV, "Currency") 'Display the result in a message box
End Sub

In this example, we used a loop to populate the ValueArray with values from a worksheet range. This way, we can easily change the values in the worksheet and recalculate the net present value without modifying the code. We also used the WorksheetFunction object to access the Excel NPV function from VBA instead of the native VBA worksheet function. The WorksheetFunction object allows us to use any of the built-in Excel functions in our code, but we could have easily just used the native VBA NPV function, like we did earlier, and we would have gotten the same result: ($-18,116.84).

Using a loop and an array is a common technique in VBA programming. It can help us simplify our code and make it more dynamic and flexible. There are certainly more efficient ways to extract data from an Excel spreadsheet, but I wanted to demonstrate with loops and arrays since they can be adapted to many use cases.

In this tutorial, we learned how to use the VBA NPV function to calculate the net present value of an investment based on a series of cash flows and a discount rate. We also demonstrated how to use a loop and an array to calculate the net present value of an investment using VBA, both by using the native VBA NPV function and by using the WorksheetFunction object to access Excel functions from VBA. I hope you found this tutorial useful and informative. For more VBA tips and tricks like this, please subscribe using the form below. Thank you for reading!