Microsoft Office Tutorials and References

In Depth Information

Chapter 12: Discounting

and Depreciation Formulas

In This Chapter

• Calculating the net present value of future cash flows

• Using cross-checking to verify results

• Calculating the internal rate of return

• Calculating the net present value of irregular cash flows

• Finding the internal rate of return on irregular cash flows

• Using the depreciation functions

The NPV (Net Present Value) and IRR (Internal Rate of Return) functions are perhaps the most commonly used

financial analysis functions. This chapter provides many examples that use these functions for various types of

financial analyses.

Using the NPV Function

The NPV function returns the sum of a series of cash flows, discounted to the present day using a single discount

rate. The cash flow amounts can vary, but they must be at regular intervals (for example, monthly). The syntax

for Excel's NPV function is shown here; arguments in bold are required:

NPV(rate,value1,value2, ...)

Cash inflows are represented as positive values, and cash outflows are negative values. The NPV function is sub-

ject 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.

Conversely, 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.