# MS Excel: NPV Function (WS, VBA)

Learn how to use the Excel **NPV function** with syntax and examples.

## Description

The Microsoft Excel **NPV function** returns the net present value of an investment.

## Syntax

The syntax for the Microsoft Excel **NPV function** is:

NPV( discount_rate, value1, [value2, ... value_n] )

### Parameters or Arguments

*discount_rate* is the discount rate for the period.

*value1, value2, ... value_n* are the future payments and income for the investment (ie: cash flows). There can be up to 29 values entered.

## Note

Microsoft Excel's **NPV function** does not account for the intial cash outlay, or may account for it improperly depending on the version of Excel. However, there is a workaround.

This workaround requires that you NOT include the initial investment in the future payments/income for the investment (ie: *value1, value2, ... value_n*), but instead, you need to subtract from the result of the **NPV function**, the amount of the initial investment.

The workaround formula is also different depending on whether the cash flows occur at the end of the period (EOP) or at the beginning of the period (BOP).

If the cash flows occur at the **end of the period** (EOP), you would use the following formula:

=NPV( discount_rate, value1, value2, ... value_n ) - Initial Investment

If the cash flows occur at the **beginning of the period** (BOP), you would use the following formula:

=NPV( discount_rate, value2, ... value_n ) - Initial Investment + value1

## Applies To

The **NPV function** can be used in the following versions of Microsoft Excel:

- Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

## Type of Excel Function

The **NPV function** can be used in Microsoft Excel as the following type of function:

- Worksheet function (WS)
- VBA function (VBA)

## Example (as Worksheet Function)

Let's look at an example to how you would use the **NPV examples** and explore how you would use the **NPV function** as a worksheet function in Microsoft Excel:

This first example returns a net present value of $3,457.19. It assumes that you pay $7,500 as an initial investment . You then receive the following income for the first four years (EOP): $3,000, $5,000, $1,200, and $4,000. An annual discount rate of 8% is used.

=NPV(8%, 3000, 5000, 1200, 4000) - 7500

This next example returns a net present value of $8,660.77. It assumes that you pay $10,000 as an initial investment. You then receive the following income for the first three years (BOP): $3,400, $6,500, and $10,000. An annual discount rate of 5% is used.

=NPV(5%, 6500, 10000) - 10000 + 3400

## Example (as VBA Function)

The **NPV function** can also be used in VBA code.

Let's look at some Excel NPV function examples and explore how you would use the **NPV function** in Excel VBA code:

This example returns a net present value of $3,457.19. It assumes that you pay $7,500 as an initial investment . You then receive the following income for the first four years (EOP): $3,000, $5,000, $1,200, and $4,000. An annual discount rate of 8% is used.

The VBA code would be:

Dim LNumber As Double Static Values(4) As Double Values(0) = 3000 Values(1) = 5000 Values(2) = 1200 Values(3) = 4000 LNumber = Npv(0.08, Values()) - 7500

In this example, the variable called LNumber would now contain the value of $3,457.19.