@NPV

@NPV(interest;range;[type]) calculates the net present value of a series of future cash-flow values (range), discounted at a fixed periodic interest rate.



Arguments

interest is a decimal or percentage value greater than -1.

range is the range that contains the cash flows.

when type is ... Cash flows occur ...
0 At the end of each period; default if you omit the argument
1 At the beginning of each period


Notes

Use @NPV to evaluate an investment or to compare one investment with others. @NPV calculates the initial investment necessary to achieve a certain cash outflow at a certain rate.

@NPV returns ERR to indicate an error if range contains more than one row or more than one column. For example, @NPV returns ERR if range is A1..D25, but does not return ERR if range is A1..D1 (a single row) or A1..A25 (a single column).



Examples

This example uses @NPV to discount to today's dollars a series of irregular distributions invested at an 11.5% annual percentage rate.

range is a list of cash flows, one a month for 12 months, in a range named DISTRIBUTIONS:

$       0.00$       0.00$   2,500.00$   2,500.00$   3,000.00$   5,000.00$   6,000.00$   9,000.00$   3,000.00$   2,500.00$       0.00$   7,500.00

To provide @NPV with the correct number of periods, months in which no distribution is made must be included in range. The distributions are monthly, so @NPV requires interest (the discount rate), in a cell named DISCOUNT, to be expressed as a monthly percentage:

0.115/12 = 0.96

@NPV(DISCOUNT;DISTRIBUTIONS) = $38,084.13

The result is different if the cash outflows occurred at the beginning of each period.

@NPV(DISCOUNT;DISTRIBUTIONS;1) = $38,449.10



See Also
@PV calculates the present value of an annuity based on a series of equal payments.
@FV calculates the future value of an annuity.
More power with @functions
@functions A-Z