Access VBA NPV Function to calculate Net Present Value

This Access tutorial explains how to use NPV Function in Access VBA to calculate Net Present Value.

You may also want to read:

Excel 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

Access Query NPV Function

Note that you cannot use NPV Function in Access Query, although you can see that in the Expression Values, unless you create a custom Function in VBA.

Access NPV Function 01

Access VBA NPV Function

Syntax of Access 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

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

Outbound References

https://support.office.com/en-us/article/NPV-Function-96BC0897-9B6E-46E0-937F-13BE698D0023

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also 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 in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

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