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

negative NPV.

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

definition.

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.