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

CHAPTER 10

Value at Risk

Many times we want to know for planning purposes what is the “worst that can happen.” In most situations, the worst that can happen is to lose our entire investment. However, this usually has an extremely low probability of occurrence. The concept of Value at Risk (VaR) was devised to obtain a risk measure that associates a severe loss with a probability level of reasonable interest to the decision maker, such as 1 percent or 5 percent. See Jorion (2007) for more about VaR. In this chapter, we see how to use Crystal Ball to find VaR and a related measure, Conditional Value at Risk (CVaR).

10.1 VAR

In practice, we can think of a potential loss L as the worst that can happen if the probability of losing L or more during a selected time period is a specified amount such as 5 percent. In that case, L is called the ”5 percent VaR.” More precisely, let R denote the total return (in dollars) on an investment, I, and let c denote the α percentile of the distribution of R. Then the α percent VaR is defined as L = I - c.

Figure 10.1 shows a segment of the one-year Crystal Ball model in PortfolioVaR.xls, which is adapted from the the file Portfolio.xls described in Chapter 9. The potential loss from investing in the portfolio, I - R, is measured directly in cell B11 with the Excel formula =A4-A11, which is simply the difference between the initial investment and the final value of the portfolio. A copy of the forecast window for this quantity is shown at the bottom ...