This Access tutorial explains how to use NPV Function in Access VBA to calculate Net Present Value.
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
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 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
Outbound References
https://support.office.com/en-us/article/NPV-Function-96BC0897-9B6E-46E0-937F-13BE698D0023
