Excel 2016 Formulas

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 subject to the same restrictions that apply to financial functions, such as PV, PMT, FV, NPER, and RATE (see Chapter 11, “Borrowing and Investing Formulas”).

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