@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.
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 |
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).
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