The Net Present Value (NPV) function in Excel 2013 calculates the net present value based on a series of cash flows. The syntax of this function is
=NPV(<i>rate</i>,<i>value1</i>,[<i>value2</i>],[...])
where value1, value2, and so on are between 1 and 13 value arguments representing a series of payments (negative values) and income (positive values), each of which is equally spaced in time and occurs at the end of the period.
The NPV investment begins one period before the period of the value1 cash flow and ends with the last cash flow in the argument list. If your first cash flow occurs at the beginning of the period, you must add it to the result of the NPV function rather than include it as one of the arguments.
The figure illustrates the use of the NPV function to evaluate the attractiveness of a five-year investment that requires an initial investment of $30,000 (the value in cell G3).
The first year, you expect a loss of $22,000 (cell B3); the second year, a profit of $15,000 (cell C3); the third year, a profit of $25,000 (cell D3); the fourth year, a profit of $32,000 (cell E3); and the fifth year, a profit of $38,000 (cell F3). Note that these cell references are used as the value arguments of the NPV function.
Unlike when using the PV function, the NPV function doesn’t require an even stream of cash flows. The rate argument in the function is set at 2.25 percent. In this example, this represents the discount rate of the investment — that is, the interest rate that you may expect to get during the five-year period if you put your money into some other type of investment, such as a high-yield money-market account.
This NPV function in cell A3 returns a net present value of $49,490.96, indicating that you can expect to realize a great deal more from investing your $30,000 in this investment than you possibly could from investing the money in a money-market account at the interest rate of 2.25 percent.
