This Excel VBA tutorial explains how to calculate net present value using Excel VBA NPV Function.

You may also want to read:

## Net Present Value

Net Present Value is to calculate the present value of future cash flow. For example, your client will give you $10000 when the project completes after 1 year, assume there will be inflation, the future $10000 will be less than the present $10000. Assume the inflation is 4%, the present value of $10000 is 10000/(1+4%) = 9615.384615 This calculation is just a simple maths, you don’t even need to memorize any formula.

If your company needs to invest $9900 today in order to gain $10000 after 1 year, then you should not take this project because $9615 you will gain is less than $9900 you invest today.

If you receive $10000 after 1 year, and $5000 in the second year, then

Net present value = $10000/(1+4%) + $5000/(1+4%)^{2}

= 9615.38 + 4622.78

= 14238.17

## Excel VBA NPV Function

Instead of calculating the Net Present Value manually, Excel NVP Function provides a quick way to calculate the Net Present Value.

Excel worksheet NPV Function is similar to Excel VBA NPV Function.

#### Syntax of VBA NPV Function

NPV(rate,valueArray())

Rate | Required. The rate of discount over the length of one period. |

valueArray() | Required. An array to store the cash flow in double format. |

It is important to note that array has to start from item 0. If you start from item 1, the first cash flow item is assumed to be $0.

#### Example 1

Using the above example, if the discount rate is 4%, while the cash flow return is $10000 after the first year, $5000 after the second year

Public Sub testNPV() Dim npvArray(1) As Double npvArray(0) = 10000 npvArray(1) = 5000 MsgBox (NPV(0.04, npvArray())) End Sub

**Result**

#### Example 2

If you have a lot of cash flow, you can type the values in worksheet first and then convert them into array in VBA.

Public Sub testNPV() Dim npvArray() As Double For r = 2 To Range("A" & Rows.Count).End(xlUp).Row ReDim Preserve npvArray(r - 2) npvArray(r - 2) = Range("B" & r).Value Next r MsgBox (NPV(0.04, npvArray())) End Sub

**Result**