Nội dung

CHAPTER 7 Net Present Value and Internal Rate of Return ow 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). N 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 NPV of the annuity is defined as NPV = 5
t=1 Ct + C0 . (1 + r)t (7.1) For the cash flows in Figure 7.1, if r = 10 percent then NPV = $13.72 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 $13.72 is greater than zero at that rate. Be aware that the definition of NPV in Expression 7.1 is slightly different from that used by the Excel NPV function. To find the NPV of the annuity in Figure 7.1, 105 106 FINANCIAL MODELING WITH CRYSTAL BALL AND EXCEL FIGURE 7.1 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 formula =NPV(0.1,B5:B9)+B4, (7.2) which is entered in cell B11 of NPV.xls. Most finance textbooks refer to the quantity calculated in this example by Excel’s NPV function as the present value at end of Year 0 of the cash flows obtained at the ends at Years 1 through 5. To get the net present value, we also consider the investment (negative cash flow) Net Present Value and Internal Rate of Return 107 FIGURE 7.2 Spreadsheet segment to model stochastic cash flows at the end of Years 1 through 5. Model 1 cash flows in Years 1 through 5 are IID normal(30,3). Model 2 cash flows follow an additive random walk with normal(0,3) increments. at Year 0, denoted by C0 in Expression 7.1. This can be confusing, but the NPV function has been defined this way for so many versions of Excel that Microsoft is understandably loath to change it at this point because so many of their existing customers are accustomed to the nontextbook definition and use it in many of their existing models. As an alternative to NPV, we can also help decide whether to purchase the annuity by calculating its IRR. The IRR is defined to be the value of r in Expression 7.1 that makes NPV = 0. Because there is no convenient closed-form expression for calculating IRR, we use Excel’s IRR function to find it for us. Notice that there is consistency between the financial definition of IRR and Excel’s IRR function. Cell B12 in Figure 7.1 shows that the IRR for the annuity is 15.24 percent. You can check this by replacing 0.1 with B12 in Formula 7.2 for cell B11 and seeing that NPV=0. SIMULATING NPV AND IRR Now let’s assume that we can purchase an investment product for $100 that has stochastic cash flows in Years 1–5. We will use two different stochastic processes for the risky cash flows, and compare the results to the annuity described in the previous section. Model 1. The cash flows at the end of Years 1–5 are independent and identically distributed (IID) over time. Specifically, each cash flow is calculated as Ct = 30 + 3Zt for t = 1, 2, 3, 4, and 5, where each Zt is drawn from a 108 FINANCIAL MODELING WITH CRYSTAL BALL AND EXCEL normal(0,1) distribution independently of the Zt s for the other years. The Model 1 cash flows are in cells C5:C9 of file NPVModels.xls shown in Figure 7.2. Model 2. The cash flows at the end of Years 1–5 are linked over time in an additive random walk model. Year 1 cash flow is computed as C1 = 30 + 3Z1 , so is equal to Model 1’s Year 1 cash flow. Years 2–5 cash flows are computed as Ct = Ct−1 + 3Zt where the Zt s for t = 2, 3, 4, and 5 are the same normal(0,1) random variates used to generate Model 1’s cash flows. The Model 2 cash flows are in cells D5:D9 of file NPVModels.xls shown in Figure 7.2. To compare the effect of the IID model to that of the additive random walk model, look at the differences between distributions of NPVs and IRRs in Figure 7.3. Overlay Chart 1 in Figure 7.3 compares the distributions of NPVs for the two models of cash flow. Each distribution has the same true expected value, which is $13.72 as it is for the annuity shown in Figure 7.1. However, a large difference in the variability of the two distributions is evident in the overlay chart. As you can see in Figure 7.4, the standard deviation of the distribution of Model 1 NPV is $5.14, while in Figure 7.5 the standard deviation of the distribution of Model 2 NPV is $16.11. This difference in variability is explained by the difference in the models used to calculate the cash flows. Because the cash flows are linked to each other in the random walk model (Model 2), their variability increases from year to year. For example, in Model 1 the cash flow for Year 5 is calculated as C5 = 30 + 3Z5 , so has a true variance of 32 = 9 and standard deviation of 3. In Model 2, the cash flow for Year 5 is linked to all previous years’ cash flows: C5 = C4 + 3Z5 = C3 + 3Z4 + 3Z5 = C2 + 3Z3 + 3Z4 + 3Z5 = C1 + 3Z2 + 3Z3 + 3Z4 + 3Z5 = 30 + 3Z1 + 3Z2 + 3Z3 + 3Z4 + 3Z5 , so has a true variance of 5(32 ) = 45 and standard deviation 6.708. The increasing dispersion of cash flow distributions over time in Model 2 reflects the decision maker’s increased uncertainty about the cash flows the farther into the future he or she looks. This increased uncertainty in cash flows causes the standard deviation for Model 2 NPV to be larger than the standard deviation for Model 1 NPV in Figure 7.2. Overlay Chart 2 in Figure 7.3 shows similar differences in dispersion for the distributions of IRR. Using simulation to find a distribution of net present value is heresy to some finance professors, yet many analysts do this routinely without knowing that it is controversial. When the concept of using distributions of NPV to compare investments Net Present Value and Internal Rate of Return 109 FIGURE 7.3 Overlay charts to compare distributions of NPV (Overlay Chart 1) and IRR (Overlay Chart 2) for Models 1 and 2 in Figure 7.2. In both charts the distribution for Model 1 cash flows has much less dispersion than the distribution for Model 2 cash flows. 110 FINANCIAL MODELING WITH CRYSTAL BALL AND EXCEL was first promoted some 40 years ago by Hertz (1968), computers were not widely available to managers as they are now. At that time, the only practical method of calculating present value available to financial analysts was to estimate the expected value (mean) of potential cash flows for each future period and discount them as we did for each deterministic Ct in Expression 7.1. Doing so ignores the variation in potential future cash flows, and could lead the uninitiated to conclude that there is no difference between the annuity and the investment with stochastic cash flows. However, there is clearly a difference in the nature of these investments, and using simulation to help illustrate the differences can be eye-opening for many decision makers. The controversy over whether to use simulation to calculate a distribution of NPV stems from the definition of NPV long ago as the sum of discounted expected cash flows. Under this definition, the NPV of any investment is a single number and some adherents of this definition bristle at talk of a distribution of NPV. Proponents of simulation, however, advocate finding the distribution of the sum of discounted potential cash flows from an investment as we did in Figures 7.4 and 7.5, then using the distribution for analyzing the investment’s riskiness. When speaking to those who bristle, you may find it helpful to refer to a distribution such as those in Figure 7.4 or 7.5 as a distribution of potential NPV rather than a distribution of NPV. Notice that in this case the means of the distributions of potential net present value are the same (within sampling error) as the NPV calculated as the sum of discounted expected cash flows, $13.72. This will not hold true for all models, and in the next section we will see a model for which the sum of the discounted expected cash flows will not be equal to the expected value of the distribution of the sum of potential cash flows, even after accounting for sampling error. FIGURE 7.4 Split view of forecast chart and statistics window for Model 1 NPV in Figure 7.2. Net Present Value and Internal Rate of Return 111 FIGURE 7.5 Split view of forecast chart and statistics window for Model 2 NPV in Figure 7.2. CAPITAL BUDGETING For an illustration of using simulation for capital budgeting decisions, consider an example from Chapter 10 of the excellent and popular finance textbook by Brealey, Meyers, and Allen (2006). I have tried to make the description below and the Excel models self-complete, but for a fuller discussion of this project see BMA 2006. Figure 7.6 shows a spreadsheet model in ScooterNPV.xls for the Otobai Company, who are considering the introduction of an electrically powered motor scooter for city use. The inputs to be varied and their base-case values are in cells A4:B8. The five variable inputs are: market size, market share, unit price (yen), unit variable cost (yen), and fixed cost (billions yen). The model represented in cells A14:C24 can be stated in the following expressions for Investment in Year 0 and the other variables in Years 1–10: Investment = 15 billion yen Revenue = Market size × Market share × Unit price Variable cost = Market size × Market share × Unit variable cost Depreciation = Investment ÷ 10 Pretax profit = Revenue − Variable cost − Fixed cost − Depreciation Tax = 0.5 × Pretax profit Net profit = Pretax profit − Tax Net cash flow = Net profit + Depreciation 112 FINANCIAL MODELING WITH CRYSTAL BALL AND EXCEL FIGURE 7.6 ScooterNPV.xls spreadsheet model. In the next section, we use this model to demonstrate the use of Crystal Ball’s Tornado Chart tool for a deterministic sensitivity analysis of the project’s NPV to the model inputs. As is done in BMA 2006, the Tornado Chart tool considers changes in each of the five inputs in turn while the other four are at their base-case values, and keeps track of the corresponding changes in NPV. Note that for each of the five inputs we have defined a Triangular Assumption with parameters shown in cells C4:E8. This was done to facilitate use of the Tornado Chart tool, not to obtain a realistic simulation model for the investment. See Figure 7.14 for a realistic simulation model that was created for risk analysis of this project. Tornado Chart Tool To use the Tornado Chart tool, select Run→Tools→Tornado Chart. . . from the top menu. You will see a dialog window like that shown in Figure 7.7. Because NPV is the only Crystal Ball forecast defined in the spreadsheet, click Next > to indicate that you wish to analyze the sensitivity of NPV to the inputs. Figure 7.8 is the dialog window for Step 2. Click Add Assumptions to cause the inputs to appear in the list as shown in Figure 7.8. Note that we defined the inputs as Crystal Ball assumptions only to make this step easier. After the inputs appear in the list, click Next >. Net Present Value and Internal Rate of Return FIGURE 7.7 Step 1 in using the tornado chart tool. FIGURE 7.8 Step 2 in using the tornado chart tool. 113 114 FINANCIAL MODELING WITH CRYSTAL BALL AND EXCEL The dialog for Step 3 of using the Tornado Chart tool is shown in Figure 7.9. In this window you can change some options that are self-explanatory. Note that if you take the default values as shown in Figure 7.9, the tool will consider five different levels for each of the five inputs. The levels are determined by the percentiles of the Crystal Ball assumptions that were defined in cells B4:B8. Click Start to get the results. A tornado chart like that shown in Figure 7.10 will appear in a new Excel workbook. This chart lists the inputs from top to bottom in decreasing order of the sensitivity of NPV to each input. Thus, Figure 7.10 indicates that NPV is most sensitive to unit variable cost, and least sensitive to market size. The size of any bar corresponds to the magnitude of change in NPV. The color corresponds to the direction of the change in NPV caused by an increase in the input. Because the biggest bars are at the top and the smallest bars are at the bottom, the result is a figure that resembles a tornado. The tornado chart is useful for initial investigation of sensitivities to suggest the order in which we should be concerned with the inputs. The spider chart in Figure 7.11 depicts the same information as the tornado chart. In this figure, it is the slope of any line that indicates the sensitivity of NPV to FIGURE 7.9 Step 3 in using the tornado chart tool.