The VBA NPV function calculates the Net Present Value of an investment, based on a supplied discount rate, and a series of future cash flows.
The syntax of the function is:
Where the function arguments are:
Rate    The discount rate over one period (supplied as a decimal). 
ValueArray   
An array of cash flows, representing a series of payments and income, where:

Note that, the NPV function assumes that the first cash flow is made at the end of the first period.
If your initial cash flow is made at the start of the first period, this should not be included in the series of cash flows supplied to the NPV function and should instead, be added onto the result of the NPV function. This is illustrated in Example 2 below.
In the following VBA code, the NPV function is used to calculate the net present value of an investment with an annual discount rate of 2%, and an initial investment of $5,000, made at the end of year 1. The investment generates a series of cash returns over 5 years.
' Calculate the net present value of an investment that has an annual discount rate of 2%,
' an initial investment of $5,000 (made at the end of yr 1) and a series of returns over 5 years. Dim discRate As Double Dim cashFlows(0 to 5) As Double Dim npVal As Double
discRate = 0.02 ' Discount rate of 2%
npVal = NPV( discRate, cashFlows )cashFlows(0) = 5000 ' Initial investment of $5,000 at end of year 1 cashFlows(1) = 800 ' Return from year 1 cashFlows(2) = 950 ' Return from year 2 cashFlows(3) = 1080 ' Return from year 3 cashFlows(4) = 1220 ' Return from year 4 cashFlows(5) = 1500 ' Return from year 5 ' npVal is calculated to be 196.882179876776. 
After running the above VBA code, the variable npVal is equal to 196.882179876776.
I.e. the investment has a net present value of $196.88.
Note that:
In the following VBA code, the NPV function is used to calculate the net present value of an investment with an annual discount rate of 5%, and an initial investment of $10,000, made at the start of year 1. The investment generates a series of cash returns over 5 years.
' Calculate the net present value of an investment that has an annual discount rate of 5%,
' an initial investment of $10,000 (made at the start of yr 1) and a series of returns over 5 years. Dim discRate As Double Dim initInvest As Double Dim cashFlows(1 to 5) As Double Dim npVal As Double
discRate = 0.05 ' Discount rate of 5%
npVal = NPV( discRate, cashFlows ) + initInvestinitInvest = 10000 ' Initial investment of $10,000 at start of year 1 cashFlows(1) = 2000 ' Return from year 1 cashFlows(2) = 2400 ' Return from year 2 cashFlows(3) = 2900 ' Return from year 3 cashFlows(4) = 3500 ' Return from year 4 cashFlows(5) = 4100 ' Return from year 5 ' npVal is calculated to be 2678.67763309477. 
The above VBA code calculates the net present value of the investment to be $2,678.68.
Note that: