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

How to address the limitations of value-at-risk? One of the most famous techniques used to measure expected losses and the one currently advised by Basel is conditional value-at-risk (CVaR), or expected shortfall (ES). It measures expected loss at a given threshold of worst-case scenarios. Even though the formulae might seem complicated, it is really easy to calculate it. Today we are doing just that.
Don't forget to subscribe to NEDL and give this video a thumbs up for more videos in Finance!
Please consider supporting NEDL on Patreon: / nedleducation

[@NEDLeducation]

You can find the spreadsheets for this video and some additional materials here: drive.google.com/drive/folders/1sP40IW0p0w5IETCgo464uhDFfdyR6rh7 Please consider supporting NEDL on Patreon: www.patreon.com/NEDLeducation

[@drachenschlachter6946]

Simple, exact and practical... A very good tutorial thank you!! 😊👍

[@kessler88]

Brilliant video, the best explanation of the ES I have come across. Thanks! 😀

[@evaristodiz]

excellent contribution and explanations

[@monour7907]

thanks . we need another video like this of calculating the var and cvar of a portfolio thanks in advance sir

[@elliecao8151]

this is well explained, thanks for the video!!

[@NEDLeducation]

Hi Ellie, thank you very much for your feedback! :)

[@francoguevara5806]

Amazing video, thanks. Maybe, for a video idea, a Portafolio optimization using conditional value at risk in excel. There are a good amount of scripts online, but there is no Excel tutorial, and for the majority of investor, excel is easier to understand.

[@Cybermemos]

Nice job NEDL you are great

[@NEDLeducation]

Hi Guillermo, and thanks for the kind words! Stay tuned for more videos on risk management or check the playlist: kzbin.info/aero/PLE4a3phdCOasYaGX5-FoUgkzr2W7G3ROv

[@maximilianmittendorfer5951]

Great, thank you

[@nicholasbloom6379]

Really good

[@Im-Assmaa]

Thank you so much ,could you please do a video on how to calculate VaR and CoVaR using quantile regression proposed by Brunnermeier and Adrian (2011).

[@mrhamy13]

Hi NEDL. thank you very much for this video - very helpful. I was wondering if it's possible to use the CVaR as a measure of risk instead of the standard deviation when calculating the Sharpe Ratio?

[@NEDLeducation]

Hi, and thanks for the excellent question! I am actually planning to release videos on various Sharpe ratio modifications using value-at-risk in particular, however it is most common to use modified VaR in the denominator in these instances. However, nothing prohibits you from using the CVaR instead, this is logically sound.

[@TheMAUMAU1414]

Hi NEDL, thanks for the video! Just have a question. Are the VaR and CVaR values for the day after? How would the study be done for a monthly estimation? Would we need to adjust the data into monthly averages?

[@NEDLeducation]

Hi Mauro and many thanks for your feedback! Yes, VaR and CVaR are daily estimates, as required by Basel market risk regulations. To estimate VaR or CVaR for other frequencies, you can just scale returns and volatilities for the time period you are concerned with. For example, for monthly VaR you can scale your return and volatility to a 21-day period (as there are typically 21 trading days in a month). Please check out our video on volatility scaling if you are interested :) kzbin.info/www/bejne/lauQYWutj7qZeMU

[@ZaWnClan]

Hi NEDL, Thank you for all your vidéo ;) ! I'm a student in Finance and I would like to know why you don't used the t-distribution to calculate the stock prices for the VaR and CVaR, you use the formula "NORM.S.INVERSE", but the simulated stock price of the stock not follow a normal distribution no ? Thank you :)

[@NEDLeducation]

Hi Jordan, and many thanks for your feedback! You are correct that stock returns do not usually follow normal distributions, however when parametric VaR and CVaR are introduced the normality assumption is just the starting point. We have a whole Mathematical Finance playlist where we cover different approaches to stock return modelling using heavy-tailed distributions. In terms of VaR, one of the potential solutions to tackle non-normality (with its own shortcomings though) is Modified VaR (kzbin.info/www/bejne/sGeUmnidirdsjJY). And actually later today another video will be released covering how to approach parametric VaR with non-normal distributions, so stay tuned :) With regards to T-distribution, it by all means can be used to model stock returns (see for example kzbin.info/www/bejne/b5-QXnaagq6UjsU if you are interested), however if you try and estimate parametric VaR using T-distribution confidence intervals with a high number of degrees of freedom, it will generate results very close to what you would get in the normality scenario. Hope it helps and thanks again!

[@ZaWnClan]

NEDL thanks you for your reply, it’s very interesting ! I gonna watch your videos about Modified VaR and The model stock returns, and I’m waiting for your video today ;). If I want to calculate the Monte Carlo VaR (with combined method historical and t-distribution) can I simulate the stock prices with a t-distribution and do it for 1000 scenarios, after that I used the historical method by Arrange my data in ascending order. For finish I do the average of the 1000 VaR. That’s a good solution ? Thank you !

[@georgekiossev7865]

Hi NEDL I am a bit confused why do you add the mean to the volatility and z-stat. if you consider it as expected return I think the formula should be “Mean - Z-stat*volatility”?

[@NEDLeducation]

Hi George, and thanks for the question! This is due to the fact Z-stat is negative here.

[@hayfakazouz3838]

hello NEDL, thank you for this video, can we estimate CVaR and VaR on STATA software? if yes can you share a video in this subject . Thanks

[@NEDLeducation]

Hi Hayfa, and really appreciate the feedback! Thanks for the suggestion! We are considering doing some videos on the use of statistical software packages, particularly when we start moving into econometrics and similar topics, but I really cannot say right now how soon it will be.

[@tshegophale2622]

Thanks for a great video, everything is clear except one point, if you could please clarify again: why HS gives consistently higher VaR-CVaR values than Variance-Covariance approach. Apologies if I've missed this point. Thanks

[@NEDLeducation]

Hi Tshego, and glad you enjoyed the video! As for your question, it is due to the fact that historical distributions of returns used in the historical simulation method are almost always more heavy-tailed than the normal distribution used in VCV approach, hence the discrepancy.

[@tshegophale2622]

@@NEDLeducation Brilliant, thank you.

[@fazafarihaz7237]

Thank you, could you please create VAR dan ES using monte carlo on excel? that would be helpful. Thank you

[@indian2031]

Can it be calculated for portfolio

[@NEDLeducation]

Hi Deepak, and thanks for the question! Yes, it can, just plug in your portfolio returns instead of stock/index returns.

[@chenglong7288]

I would like to learn CVaR for multi-assets

[@NEDLeducation]

Hi Pheng, applying CVaR for a portfolio of assets is very easy, just substitute the return series here for returns of a portfolio you wish to simulate. Hope it helps!