Conditional Value-at-Risk (Expected shortfall) - measuring expected extreme loss (Excel) (SUB) - YouTube

Hello everyone and welcome again to NEDL!

The best platform around for distance learning in Business, Finance, Economics

and much much more! My name is Savva, and today we are continuing the discussion of Value-ar-Risk and various

improvements that have been made to the technique of assessing expected losses throughout the decades, that address some of the

limitations of the original Value-at-Risk framework.

Today, we're going to talk about the conditional Value-at-Risk, or, as it is known in the regulatory circles,

expected shortfall. That is something that banks are currently advised to calculate in place of Value-at-Risk.

Conditional Value-at-Risk, or expected shortfall, is designed to address one of the most notable limitations of original Value-at-Risk, that is,

that Value-at-Risk accounts only for a certain percentile of losses and does not look or provide any insight at all

at what losses might occur if the situation is even worse than the threshold that we assume.

For example, if we calculate the Value-at-Risk at 95%, so 5% worst-case scenarios, it will only do the cut-off at 5% worst-case scenarios

and would not give us any insight at all, into what might happen if we fall even further down the left tail of the distribution.

The conditional Value-at-Risk, or the expected shortfall, addresses that by calculation the conditional expectation of losses in n% worst-case scenarios.

So, if you look at ordinary Value-at-Risk, it will just present you with a cut-off, while the expected shortfall

will tell you what is the average loss you're expected to endure in n%, for example, 5%, 2.5%, or whatever, of worst-case outcomes.

Well, if you look and compare the formulas for Value-at-Risk and conditional Value-at-Risk,

or expected shortfall, you see that the conditional Value-at-Risk can be expressed as an integral, or conditional expectation,

if we have a function that generates Values-at-Risk for a particular confidence interval,

x, then we can calculate the conditional Value-at-Risk by just averaging over all the potential values of

confidence intervals from 0, so the extreme absolute worst-case scenario all the way up to alfa,

alfa being our confidence interval, so we'll just average over the number of scenarios from the worst-case scenario to the scenario that would

be our cut-off point in the original Value-at-Risk approach. But for variance-covariance approach, even, and for historical simulation

all the more so,

there are no closed-form solutions for those integrals.

So, you can estimate them in two potential ways: first of all, you could use something like a Monte Carlo simulation,

just throw a bunch of random numbers, and see where you arrive at, but the simpler way would be

to approximate the integral with averaging over smaller and smaller increments. So, for example, if we would calculate our

standard Value-at-Risk using variance-covariance approach or historical simulation approach, at tiny intervals of the

probability, so for example, we'll pick a very small increment of probability, like 0.1%, then we can just average over all of the

VaRs that are corresponding to a smaller or equal confidence interval that we want to calculate our expected shortfall for,

and we'll arrive at a very-very precise approximation of this integral, of the definition of the conditional Value-at-Risk.

So, without further ado, let's just calculate the VCV and historical simulation VaR at a bunch of confidence intervals at

reasonably small increments, and then calculate the respective conditional Value-at-Risks. So, we've got our

S&P500 returns for the last 5 years, and we can just calculate the mean as the average return over the last 5 years on daily data, so 0.04%.

And the standard deviation can be calculated just using the =STDEV.S formula. Again, over the whole area of returns.

Then, the variance-covariance approach VaR would be very straightforward to calculate by just using the sample mean, that we need to lock,

then we need to add our sample standard deviation,

and multiply it by the Z-stat that corresponds to a particular confidence interval.

So, =NORM.S.INV, inverse normal distribution,

with probability corresponding to this particular confidence interval,

so in that case, it would be the inverse standard normal distribution for 0.1%.

And we'll arrive at -2.58%, which will correspond to 2.58% loss within a day.

And then we can enforce this formula for all of those confidence intervals,

and see that the higher the confidence interval is, the lower is the loss under the variance-covariance approach,

which is really intuitive.

Now, for the historical simulation approach, it's even easier, we just need to apply

the =PERSENTILE.EXC function over the array of historical returns,

locking all of the cells on the way,

and then, as we have percentile, we just need to use

the respective confidence interval again.

And that will generate the historical simulation VaRs for each of those corresponding confidence intervals.

And if you are attentive enough.

you can see that here the confidence intervals go in increments of 0.1%,

so, with reasonable accuracy, we will be able to calculate conditional VaRs, expected shortfalls,

for both of these methods, by just averaging over the respective range of VaRs.

So for example, we can just average

over the range of variance-covariance VaRs from the very beginning, so 0.1%,

that's the lowest confidence interval available to us in that case,

so we'll start at 0.1%,

and we'll lock the row,

so it doesn't change as we drag this down,

up to the current value of the confidence interval.

And then, we can drag this across, so it calculates that for the historical simulation VaR as well,

and bottom-right-click it all the way down.

So, for example, for some value of the confidence interval that we might care about,

for example, 1%, we see that in each of those cases it just averages over the values for VaR,

for all confidence intervals that are lower or equal to the confidence interval we are caring about at the moment.

And that's exactly the approximation of this interval using averages over small increments of the confidence interval.

For the historical simulation, it would be exactly the same.

Well, let's look at another confidence interval, shall we?

Well, the most commonly used confidence interval that is quoted in the regulatory framework is 2.5%,

well, it's 1% or 2.5%, those two are the most commonly used,

so let's look at 2.5%,

we can see that here, the conditional VaR, or expected shortfall, would be an average

over 25 different VaRs, that correspond to confidence intervals from 0.1% all the way to 2.5%.

And we can see that the conditional VaRs are always much higher in terms of loss,

much smaller in terms of the value, because those are negative,

than their corresponding VaRs.

That's unsurprising, because, well, while VaRs only account for the cut-off, conditional VaRs also account for

the expected loss that you're expected to endure in n% of worst-case outcomes.

And obviously, if you had a larger sample, you would be able to estimate the conditional VaR

with even more precision by choosing smaller increments.

The smaller increment you choose, for example, you can choose 0.01%, the closer the approximate

value of the conditional VaR that you will obtain will be to the true value that would be estimated by this integral.

And that's all we have regarding the conditional VaR and expected shortfall!

Please leave a like under this video if you found it helpful,

please leave your suggestions for any further topics in Business, Economics of Finance that you want me to investigate in further videos,

and please don't forget to subscribe to our channel!

Thank you very much and stay tuned!