Financial Modeling with Crystal Ball and Excel, + Website, 2nd Edition

CHAPTER 7

Net Present Value and Internal Rate of Return

Now that we have completed your introduction to Crystal Ball, we will begin looking at several different types of situations for which Crystal Ball models are useful. We start with net present value (NPV) models, and see situations where the distribution of NPV can help decision makers gain insights into the problem at hand. We will also consider the pros and cons of using internal rate of return (IRR) as a Crystal Ball forecast. It is assumed that you are already familiar with these concepts. For more background information on NPV and IRR in deterministic models, see any introductory finance textbook such as Melicher and Norton (2011).

7.1 DETERMINISTIC NPV AND IRR

Suppose that you have the opportunity to purchase an annuity that costs you $100 at Year 0, and is certain to return $30 to you at the end of each Year, 1 through 5. These cash flows are depicted in the Excel chart on the spreadsheet segment in Figure 7.1. Denote the cash flow at the end of Year t as Ct, and the relevant annual rate of interest as r. Then the net present value (NPV) of the annuity is defined as

(7.1)

FIGURE 7.1 Spreadsheet segment to model annuity with deterministic cash flows of at the end of Year 0, and at the end of Years 1 through 5.