Monte Carlo simulation for Conditional VaR (Excel)

Can you solve a difficult mathematical problem by just throwing a bunch of random numbers at it? Turns out you can, at least sometimes. For Conditional Value-at-Risk, it is often the case that one needs to calculate a definite integral, and a closed-form solution does not exist. Here, Monte Carlo simulation can help. Today, we are learning how to apply Monte Carlo simulation and how to visualise it.
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]

Your tutorials are awesome!!!

[@andrewpackman2165]

Great video, my man

[@aswarmaulanaas1274]

Thank you very much, this video very helpful for me ❤️❤️🙏🙏

[@theoryofspeculation3112]

So good!

[@humdidoadi]

great videos. iam especially interested in risk measures like VaR.

[@parikshitsolutions]

Very good content

[@theone7857]

I appreciate for your video. It's very useful and practical I hope that God bless you~! ^^

[@NEDLeducation]

Thanks so much for the kind words!

[@francescom.9158]

Amazing, very interesting. It would be even more interesting to see how to backtest expected shortfall.

[@monour7907]

thank u sir, we want some videos about the stress tests THANK U IN ADVANCE

[@NEDLeducation]

Hi Omar and glad you liked the video! As for your suggestion, it would be challenging to make a video about stress tests in general, as they are designed internally by banks themselves and then approved and audited by the regulators, but I will think about how to present the idea the best in a tutorial.

[@NEDLeducation]

@@StallionStudios1234 Hi again, and thanks for the question! In terms of stress-testing, if you want to do it parametrically rather than specific to particular events, you might want to consider quantile regression as an approach: kzbin.info/www/bejne/qoaaZ5WXm8uihM0

[@deniswolf1846]

Good afternoon! Unfortunately, I didn't find the excel file NEDL_CVaR_Monte_Carlo for this video at this link. Could you add it to Google drive? Thank you in advance!

[@NEDLeducation]

Hi Denis, thanks for pointing this out. I have updated the Google Drive to include the CVaR spreadsheet, please check it out :)

[@deniswolf1846]

@@NEDLeducation Thank you so much for such great material, I really appreciate it! I learn from your videos, and no one does it better than you on youtube:)

[@NEDLeducation]

Thank you very much Denis, glad you found the videos helpful!

[@drachenschlachter6946]

Why is the are of the strip 10%??? shouldn't it be 10%*1% (length*width of the rectangular)?

[@Small1400]

Thanks for the great video! Any way you could provide an example applied to electricity trading, at a given price point or index? Much appreciated!

[@NEDLeducation]

Hi Carlos, and glad you liked the video! This approach in principle could be applied to any market-traded assets, including electricity as a wholesale commodity. You can access high-quality enough daily data here, for example: www.nordpoolgroup.com/Market-data1/GB/Auction-prices/UK/Daily/?view=table

[@Small1400]

@@NEDLeducation Much appreciated! I'll definitely take a look at the link/data you recommended. I work in power trading (physical and financial) for the most part using hourly and daily averages (index prices at different locations (price nodes). In terms of limitations, with regards to the hourly prices, which can be

[@anilgupta1426]

Hello, From the google drive which PDF to follow for Monte Carlo Analysis. Thank you

[@NEDLeducation]

Hi Anil, the Monte Carlo simulation is included into the CvaR spreadsheet, look at the second sheet in the document. As for the papers, there are lots of these that apply Monte Carlo to VaR and CVaR, but their language is quite technical. Hope it helps!

[@doktorroger9315]

May be I do not really understand the concept of CVAR. Why not initially calculating a VAR, let´s say at the 0.1% level, or at the 0.01% level? Why first calculating a VAR at the 5% level and then calculating an average expected shortfall (=CVAR) beyond this 5% probability? When calculating e.g. a 0.1%-VAR, I would have included most of the tail risk (?).

[@NEDLeducation]

Hi Roger and many thanks for an interesting question! Yes, we do have a video on Conditional VaR that does follow a similar procedure, i.e. calculating VaRs at small increments and averaging over those (kzbin.info/www/bejne/pabGk5yffbCpfc0). The Monte-Carlo procedure delivers equivalent results but it is more flexible. Conceptually, the difference between VaR and CVaR is that VaR is concerned with the loss at the cutoff point only, while CVaR is concerned with the average loss beyond the cutoff point. Therefore, for CVaR one should either calculate the definite integral (area under the curve) or do a reasonable approximation of the integral, using averaging (as in the prior video) or Monte Carlo simulation (as in this video). Hope it helps.

[@nicolaspatassi3877]

Very usefull as usual, that's a huge work ! But to extend your methodology on empirical CVaR to approximate the integral, why here use the average of a binary serie (0 and 1) adjusted with the minimum of random returns you choose, rather than the average of directly all theorical VaR(X) in excess of each random returns ? Results are not exactly the same : I tested your model with a loop of a thousand simulations, and for almost 20% of time, by averaging and adjusting the binary serie as you did, the CVaR is above the parametric VaR calculated in the VaR(VCV) column. And the fluctuation of the MC CVaR could be very significant. On the contrary, by directly averaging the VaR (X) serie if VaR(X) is below the random return, 100% of time the CVaR is below the VaR(VCV) and is quite stable throughout simulations (the CVaR fluctuate at maximum 0,02%)

[@NEDLeducation]

Hi Nicolas, and thanks for the excellent question! You are absolutely correct, this is the major shortcoming of Monte Carlo simulations in general and their applications to CVaR in particular. In my first CVaR video, I am actually showing the method you are mentioning: kzbin.info/www/bejne/pabGk5yffbCpfc0. Long story short, Monte Carlo simulations can be quite imprecise or very inefficient computationally, so do resort to them only if there is no easier approach :)

[@JCEurovisionFan1996]

Can you do a Monte Carlo simulation for US presidential elections based on the Electoral College votes and the past voting history?

[@NEDLeducation]

Hi John, I already have an election video, where I apply multiple robustness checks, including the analysis of the orders of magnitude and Monte Carlo simulations to show such test output is reliable, please check it out if you are interested: kzbin.info/www/bejne/qGi2YmCFeK2kmpo