Microsoft Office Tutorials and References
In Depth Information
Chapter 12: Discounting and Depreciation Formulas
Cash inflows are represented as positive values, and cash outflows are negative values. The NPV
function is subject to the same restrictions that apply to financial functions, such as PV, PMT, FV,
NPER, and RATE (see Chapter 11).
If the discounted negative flows exceed the discounted positive flows, the function returns a
negative amount. Alternatively, if the discounted positive flows exceed the discounted negative
flows, the NPV function returns a positive amount.
The rate argument is the discount rate — the rate at which future cash flows are discounted. It
represents the rate of return that the investor requires. If NPV returns zero, this indicates that the
future cash flows provide a rate of return exactly equal to the specified discount rate.
If the NPV is positive, this indicates that the future cash flows provide a better rate of return than
the specified discount rate. The positive amount returned by NPV is the amount that the investor
could add to the initial cash flow (called Point 0 ) to get the exact rate of return specified.
As you may have guessed, a negative NPV indicates that the investor does not get the required
discount rate, often called a hurdle rate. To achieve the desired rate, the investor would need to
reduce the initial cash outflow (or increase the initial cash inflow) by the amount returned by the
The discount rate used must be a single effective rate for the period used for the cash
flows. Therefore, if flows are set out monthly, you must use the monthly effective rate.
Definition of NPV
Excel’s NPV function assumes that the first cash flow is received at the end of the first period.
This assumption differs from the definition used by most financial calculators, and it is
also at odds with the definition used by institutions such as the Appraisal Institute of
America (AIA). For example, the AIA defines NPV as the difference between the
present value of positive cash flows and the present value of negative cash flows. If you use
Excel’s NPV function without making an adjustment, the result will not adhere to this
The point of an NPV calculation is to determine whether an investment will provide an
appropriate return. The typical sequence of cash flows is an initial cash outflow followed by a series of
cash inflows. For example, you buy a hot dog cart and some hot dogs (initial outflow) and spend
the summer months selling them on a street corner (series of inflows). If you include the initial
cash flow as an argument, NPV will assume the initial investment isn’t made right now but
instead at the end of the first month (or some other time period).
Figure 12-1 shows three calculations using the same cash flows: a $20,000 initial outflow, a series
of monthly inflows, and an 8% discount rate.