Historical Value-at-Risk (VaR) and Conditional VaR (CVaR) in Excel
Vložit
- čas přidán 5. 06. 2024
- ☕ Like the content? Support this channel by buying me a coffee at www.buymeacoffee.com/riskmaestro
This is a 3-part video on Value-at-Risk (VaR) and Conditional Value-at-Risk (CVaR).
Part 1: Historical VaR and CVaR
Part 2: Parametric VaR and CVaR (using Gaussian/Normal distribution)
Part 3: Cornish-Fisher VaR and CVaR
In Part 1, I will be using Netflix's daily stock prices from 20th April 2015 to 17th April 2020 (5 years data) to compute the historical VaR and CVaR. The CVaR is also called the expected shortfall (ES).
VaR is a loss measure, and hence the final amount is always quoted without the negative sign.
Note that the XLOOKUP and SORT functions are only available on Office 365.
More resources on financial modeling on www.fabianmoa.com.
#ValueAtRisk #ConditionalValueAtRisk #VaR #CVar #ConditionalVaR #Netflix #FinancialModeling #FinancialModel #HistoricalVaR #ParametricVaR #CornishFisher
I have been watching a bunch of videos of CZcams about this topic but none of them are as accurate as yours. Thank you for sharing it.
Wow, thank you!
I really appreciate your video! Great work!
Thanks!
This video is too good! Thanks a lot! 😉
Really thanks for the video
Clear, concise and understandable. But if you have a portfolio with both shares and bonds how you calculate VAR?
Really appreciate it
Thanks for this video
Very easy to understand explanation! Thank you for this. Question: How to now calculate 10-day, 30-day Var? Do we simply multiply the Var(p) * STDEV.S()8SQRT(10) for example to get a 10-day var?
Dear Mr. Fabian,
Could you please explain how to calculate VaR for operational risk based on loss data base.
Hi Fabian, why didn't you use the average of the first 63 ranks instead using the first 62 ranks to find the CVaR?
As You told we can take " Average " Function straightaway in Covar calculations.
Very good and useful video
Thanks a lot!
Hi your video was easy to understand and concise. May I ask why did we calculate the returns using log
It is to compute continuous return (which I use for smaller intervals). You can use discrete returns if you want.
Sir, where can I kindly buy more content related to practical excel implementation (or VBA) and relating to risk ? 🙂This stuff is simply impressive, thank you so much!
Hi, thanks for the feedback. I don't have much videos on risk modelling at the moment. Will post videos on my youtube whenever I have time after the CFA exam season is over
thank you for that descriptive video... i understand well how it works now. but i had a problem when i tried to exercise with the same data. i downloaded the data well, but when i convert..., it doesn't work well. i still have text data while i should have had number in my sheet. then i don't know how to have number. i tried everything uselessly.
What if the VAR(95) is positive and CVAR(95) is negative? How to interpret the values?
Thanks for the video. I had a question: why do you use formula "=LN(F3/F2)" for calculating returns? If for example we apply this formula to values 100 -> 50 we will receive value -0,693147181. But this is definitely should be 50% loss not 69%.
Shouldn't we use formula: "=F3/F2-1" for calculating returns?
The natural logarithm is used to create the returns due to the cumulative properties of logarithms, which make it easier to compute continuously compounding returns, which also makes it easier to compare returns over different time horizons. In your example you would do 100*e^-0.69=50.15 which is correct. Also computing annualized returns or daily returns is much easier. E.g.: 1.01^365=37.78, and backwards you do 37.78^(1/365), which is going from daily to yearly returns and back. Hope this helps, even though it's a bit late :D
Thanks for the video. I had a question, how do we calculate HS Var when there is a rolling window of say 250 days?
You need to calculate the daily returns, then use the function =PERCENTILE.EXC(, 0.05) for 5% HS VaR
Thank you very much!
For GARCH model, if we are given the parameters as w=0.41221, alpha=0.13434 and beta=0.85988; do we need to change the base for w to calculate daily volatility?
hello i need help in calculation of ΔCoVaR method?
Can we measure it using eviews
Hi , thank you so much , this was helpful, can you post a video on how to calculate VaR and CoVaR using quantile regression proposed by Brunnermeier and Adrian (2011). Im kind of stuck PLEAASE
Hi, have you found the tutorials? I really need the tutorial for calculating VaR and CoVar using quantile regression for my thesis, please let me know if you could help :(
@@rivashaputri Hi, give me your email; I'll send you the method i used.
@@Im-Assmaa Hi, thank you for replying. I can't give the email here because youtube keep deleting my comment if i write my email. Can i have your instagram? i'll give the email on the dm. Thank you in advance, i really need this and it means a lot to me.
Mine is the same as my youtube username: rivashaputri. Thank you it really means a lot.
@@Im-Assmaa Hi again, i'm still waiting for your dm. Thank you so much.
is this a non-parametric approach?
Yes, this is just for class, in reality we model and fit distribution functions in order to capture the real volatility and weight on tails of distributions, not just rankings
Sir how to calculate Change in CoVaR?
Change in CVaR as a result of?
@@FabianMoa change in CoVar for financial institution contribution to the financial system
symbolically,
CoVaRsysji
t;˛ CoVaRsys t;˛jRitDVaR it;˛ minus CoVaRsys t;˛jRitDmediani