# 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 DoubleDim cashFlows(0 to 5) As DoubleDim npVal As DoublediscRate = 0.02   ' Discount rate of 2%cashFlows(0) = -5000   ' Initial investment of \$5,000 at end of year 1cashFlows(1) = 800   ' Return from year 1cashFlows(2) = 950   ' Return from year 2cashFlows(3) = 1080   ' Return from year 3cashFlows(4) = 1220   ' Return from year 4cashFlows(5) = 1500   ' Return from year 5npVal = 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:

• As the initial investment of \$5,000 is an outgoing payment, this is supplied to the function as a negative value;
• As the returns during years 1-5 are incoming payments, these are supplied to the function as positive values.

### 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 DoubleDim initInvest As DoubleDim cashFlows(1 to 5) As DoubleDim npVal As DoublediscRate = 0.05   ' Discount rate of 5%initInvest = -10000   ' Initial investment of \$10,000 at start of year 1cashFlows(1) = 2000   ' Return from year 1cashFlows(2) = 2400   ' Return from year 2cashFlows(3) = 2900   ' Return from year 3cashFlows(4) = 3500   ' Return from year 4cashFlows(5) = 4100   ' Return from year 5npVal = 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:

• As the initial investment of \$10,000 is an outgoing payment, this is a negative value;
• As the initial investment is made at the start of year 1, it is not provided to the NPV function, but is instead added to the result of the function;
• As the returns during years 1-5 are incoming payments, these are positive values.