tailieunhanh - Financial Modeling with Crystal Ball and Excel Chapter 7

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 | 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 because using Monte Carlo simulation to develop distributions of NPV is a source of controversy among some academics even though it is done routinely by practitioners. In this chapter we will consider both sides of the controversy and see some models where the distribution of NPV can help the decision maker gain insight 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 see any introductory finance textbook such as Melicher and Norton 2006 . 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 . Denote the cash flow at the end of Year t as Ct and the relevant annual rate of interest as r. Then the NPV of the annuity is defined as 5 NPV 2 t 1 T rÿ Co- For the cash flows in Figure if r 10 percent then NPV as shown in cell B11. Therefore the annuity is a good investment for any individual with a required minimum rate of return of 10 percent because the investment s NPV of is greater than zero at that rate. Be aware that the definition of NPV in Expression is slightly different from that used by the Excel NPV function. To find the NPV of the annuity in Figure 105 106 FINANCIAL MODELING WITH CRYSTAL BALL AND EXCEL FIGURE Spreadsheet segment to model annuity with deterministic cash flows of - 100 at the end of Year 0 and 30 at the end of Years 1 through 5. we use the Excel .