Calculating Expected Shortfall CVAR in Excel. Imagine a board meeting where you have just presented your Value at Risk (Va. R) analysis and a board member asks a simple question. What is the expectation? What is the most that we can drop if we cross your Value at Risk threshold?” or “What lies beyond the barrier X? What would it cost us to give that risk away and insure it?” To answer this series of simple questions you need Conditional Value at Risk or CVAR estimate. We all understand what Value at Risk is. VAR versus expected shortfall MasterClass 01 March 2007 Tweet. This has led many financial institutions to use it as a risk measure internally. This is an edited extract from John Hull's. Extreme Value at Risk and Expected Shortfall during Financial Crisis Lancin. To get easily computed coherent risk measure, Acerbi et al. Financial Risk Management - Methods, Tools. The purpose of this seminar is to give you a good and practical understanding of financial risks and of methods and tools for managing these. We discuss the coherence properties of Expected Shortfall (ES) asafinancial risk measure. This statistic arises in a natural way from the estimation of the “average of the 100p% worst losses ” in a sample of returns to a. A worst case loss, associated with a probability and a time horizon. CVa. R or conditional Value at Risk is the expected loss, the average loss if that worst case threshold is ever crossed. It answers the what really lies beyond barrier X question. While Va. R is an estimate that board members sometimes have difficulty quantifying, the insurance policy and premium analogy is much more easier to present, quantify and understand. What is the expected loss if the Value at Risk threshold is breached? Sounds like a simple enough question with a simple enough answer. But there is a problem. Since most financial models assume a normally distributed world, CVa. R numbers produced by assuming the normal distribution grossly underestimate the actual risk. We therefore need a technique or tool that would supplement and correctly model the tail risk. While there is no one perfect solution, the historical returns method is one possible candidate for estimating more realistic CVa. R estimates. We review that in the post below. Conditional Va. R – Context and background. In the paper by Yamai and Yoshiba – Comparative analysis of expected shortfall & Value at risk under market stress – Expected Shortfall is defined as “the conditional expectation of loss given that the loss is beyond the Va. Expected Shortfall as a Tool for Financial Risk Management Carlo Acerbi . We propose the Expected Systemic Shortfall (risk in the financial system ESS). Measuring Risk with Expected Shortfall. Expected Shortfall as a Tool for Financial Risk Management. Carlo Acerbi, Claudio Nordio and Carlo Sirtori Additional contact information Claudio Nordio: Derivatives Desk, Abaxbank, Milano Italy Carlo Sirtori. 2 Value at Risk, Expected Shortfall, and Marginal Risk Contribution 1. Introduction Value at risk (VaR) is today the standard tool in risk management for banks and other financial institutions. It is defined as the worst loss. R level“. You can also look at the following two additional sources for more background on CVa. R. http: //www- iam. SP0. 1/Uryasev. pdf as well as the original BIS paper at http: //www. The authors mention in their findings that though Expected Shortfall addresses some of the underestimation of risk of securities which have fat tailed distributions and a potential for larger losses the measure is still exposed to tail risk if losses are infrequent and large, especially when the market is stressed. However under more lenient conditions (such as normal market conditions) when the Va. R measure would still be exposed to tail risk because it disregards any losses beyond the confidence level, expected shortfall would have no tail risk because it considers the conditional expectation of loss beyond the Va. R level. To review the calculation methodology of conditional Va. R (CVa. R) see our post, Calculating Value at Risk (Va. R) . For each approach we have generated a series of returns and used these returns to calculate 9. Va. R %. We then compare the results to see which approach produced bigger and more realistic CVa. R estimates. Once the daily Va. R metric is obtained, the calculation of CVa. R follows the same process for all three Va. R approaches. As an example we have used the daily Va. R from the Historical Simulation approach as an input in our CVa. R worksheet. After we review the CVa. R methodology we will present the results from all three methods. To determine the expectation of loss given that the loss is beyond the Va. R level we first need to determine the loss incurred at the Va. R level. Consider the following instance: Current Gold price is 1,6. Va. R % using the Historical simulation approach is 4. The loss at the Va. R level or the price shock at the Va. R level is 6. 8. 7. Next we determine the loss amounts. For each of the 3. For the losses column we will only consider the negative price shocks (i. For positive price shocks we will consider a loss of zero. What is the conditional expectation of loss if the loss amount exceeds 6. Conditional Value at Risk – Calculation methodology review. The methodology followed here is the same as that used for determining the conditional expectation or expected value of a roll of a fair die given that the value rolled is greater than a certain number. First let us consider the unconditional expectation of a six sided fair die. It is equal to the sum product of the value on the face of the die that turns up when rolled times the probability of that occurrence. For a fair die, as there are six possible occurrences, the probability of any value being rolled is 1/6. The unconditional expectation is then equal to 1*1/6 + 2*1/6 + 3*1/6 + 4*1/6 + 5*1/6 + 6*1/6 =2. The conditional expectation is equal to the sum product of the value on face the die that turns up given that it is greater than a certain number times the probability of its occurrence. Supposing that it is given that the value rolled is greater than 3. There are three occurrences that meet this condition (4,5,6), each having an equal probability of occurrence, i. The conditional expectation works out to 4*1/3 + 5*1/3 + 6*1/3 = 5. In a similar manner once we have determined the loss amounts for each data point, we need to factor in the condition that the loss amount exceeds 6. Va. R loss amount. This is factored in the worksheet as follows- we will only take losses that exceed 6. We may do this in one of two ways. Calculate a separate column which will take the loss amount as is if it exceeds 6. We then apply the AVERAGEIF function to the array of these conditional losses so that only those instances where the loss exceeds zero are considered. Note that as we consider each return as a separate observation the probability of occurrence is 1/number of occurrences where the conditional loss is greater than zero. The conditional Va. R amount or Expected Short fall works out to 8. The same result may be obtained by directly applying the AVERAGEIF function to the array of unconditional losses and resetting the criteria from greater than zero to greater than the Va. R Amount, i. e. This CVa. R% may be determined directly from the array of returns by applying the AVERAGEIF function to the array of returns and setting the criteria to the Daily Va. R (%), specifically CVa. R%=- AVERAGEIF(array of returns, CONCATENATE(“< “,- Daily Va. R%). How do the results from the Monte Carlo simulation using Historical returns approach compare to those obtained using the historical simulation method and the original MC- Normal approach. The average CVa. R%s over 2. Also see Unexpected Loss (UL) and The Economic Capital Case Study if you are interested in extending the shortfall model for Economic Capital applications.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
January 2017
Categories |