The VBA NPV Function

Description

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:

NPV( Rate, ValueArray )

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:

  • Negative values are treated as payments;
  • Positive values are treated as income.

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.



VBA NPV Function Examples

Example 1 - Initial Investment Made at the End of the First Period

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%
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 = NPV( discRate, cashFlows )
' 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:


Example 2 - Initial Investment Made at the Start of the First Period

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%
initInvest = -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 = NPV( discRate, cashFlows ) + initInvest
' 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: