Excel VBA NPV Function to calculate Net Present Value

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

You may also want to read:

Excel worksheet NPV Function

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

Excel VBA NPV Function

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.

Excel VBA NPV Function 02

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

Excel VBA NPV Function 03

 

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *