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
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
|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.
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
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