Monte Carlo Method: Value at Risk (VaR) In Excel
Вставка
- Опубліковано 6 чер 2024
- Ryan O'Connell, CFA, FRM walks through an example of how to calculate Value at Risk (VaR) in Excel using the Monte Carlo Method.
📘 FRM Exam Prep Discount - AnalystPrep:
► Get 20% off FRM Part 1 and Part 2 complete courses with promo code "RYAN20". Explore here: analystprep.com/shop/frm-part-1-and-part-2-complete-course-by-analystprep/?ref=mgmymmr
🎓 Tutor With Me: 1-On-1 Video Call Sessions Available
► Join me for personalized finance tutoring tailored to your goals: ryanoconnellfinance.com/finance-tutoring/
👨💼 Freelance Financial Modeling Services:
► Custom financial modeling solutions tailored for your needs: ryanoconnellfinance.com/freelance-finance-services/
💾 Download Free Excel File:
► Grab the file from this video here: ryanoconnellfinance.com/produ...
Chapters:
0:00 - Calculate Daily Returns Using Yahoo! Finance
0:43 - Calculate Security Standard Deviation and Covariance
2:35 - Create Assumptions for Portfolio
2:58 - Calculate Variance and Standard Deviation of Portfolio
4:04 - Calculate Value at Risk (VaR) In Excel (Monte Carlo Method)
8:24 - Create a Histogram to Interpret VaR
Disclosure: This is not financial advice and should not be taken as such. The information contained in this video is an opinion. Some of the information could be wrong. This channel is owned and operated by Portfolio Constructs LLC. Some of the links above are affiliate links, meaning, at no additional cost to you, I will earn a commission if you click through and make a purchase.
📘 FRM Exam Prep Discount - AnalystPrep:
► Get 20% off FRM Part 1 and Part 2 complete courses with promo code "RYAN20". Explore here: analystprep.com/shop/frm-part-1-and-part-2-complete-course-by-analystprep/?ref=mgmymmr
👨💼 Freelance Financial Modeling Services:
► Custom financial modeling solutions tailored for your needs: ryanoconnellfinance.com/freelance-finance-services/
💾 Download Free Excel File:
► Grab the file from this video here: ryanoconnellfinance.com/product/monte-carlo-method-value-at-risk-var-excel-template/
I swear no one has explained Monte carlo in such easy and descriptive manner
Thanks
As a CFA charterholder as well, I must said that sir you are the true CFA charterholder with hands on experience, really appreciate that, let the knowledge being accessible and pratical, thanks
Really appreciate the feedback, my friend! It means a lot
In cell I7, I've named a calculated field "Scenario VaR". I think a more appropriate name would be "Scenario Gain or Loss". That field is just the gain or loss on that particular scenario. Thank you for checking out my video!
Awesome job Ryan, Thank you man
My pleasure Samer!
practical examples provided a clear illustration of how this technique is applied in real-world scenarios. Thanks for sharing such an informative video!
I agree, I learn much better with hands on examples! Thank you for the feedback
Great Videos and more descriptive
what if we dont have expected return then what should be our methology?
Absolutely epic
Thank you!
Amazing
wow this is so nostalgic. now i just sell sauced chicken and kimchi.
Lol what's the best sauce you got?
@@RyanOConnellCFA sichuan mala and thai :)
kimchi lasts long enough unrefrigerated to survive free shipping.
Nice
Ryan why you multiply 252*.5 (0.5 is not understood) can you please explain
Very helpful Ryan
Glad to hear it!
@@RyanOConnellCFA you are very supportive and helpful however there are times that I do not understand something due to my background,especially in Monte carlo
What are you having trouble with in this video?
@@RyanOConnellCFA I cannot understand the difference between scenario var and Monte carlo var as well as why we do that
I get that! I mislabeled the pert that says "Scenario VaR". A better name for the would be "scenario gain or loss". We are just finding the gain or loss in the scenario in that part. Then we simulate that number thousands of times to get a distribution of gains and losses. We use that distribution to calculate Monte Carlo VaR
Hi, thank you so much for your video! helps me very well with my dissertation.
For the time period at the value at risk, I have decided to use 'years' instead of 'days' since I have annualised everything including the returns and covariances. Does it mean I just need to multiply the number without putting the square root for the VaR? (VaR = Expected return - portfolio value * st dev * z score * no. of years)?
My pleasure! I don't think the formula you posted will work because it doesn't appear to account for the compounding of returns. I think you could try to calculate the cumulative expected return over a 5 year period using compounding interest. Then run 10,000 scenarios with that and find the scenario that corresponds to the Z-score you are interested in
Hey Ryan, Im set to graduate from college next semester with my degree in Finance. I plan on starting to study for the CFA level 1 exam a few months after graduating. I was wondering if you could give any tips and tricks on how to study for the CFA?
Hey Chris, I've got a whole video on that very topic! You can find the link here:
ua-cam.com/video/M6AYAxAEi8Y/v-deo.html
Good luck with your studies!
Thanks for video.
Is it possible to use Norm.Inv(rand(),xx,yy) direct on mean and st.dev.(adjusted to 20 days) instead of calculating z score first inorder to come up for Scenario Gain or Loss (scenario VAR)?
My pleasure, and great question! I think you could implement it that way just fine and it is a good creative way to solve the problem
Hi thank you so much , this was so helpfull. I have a question , how can i estimate the value at risk using quantile regression, and how can i compute quantiles for a specific p using Rankit-cleveland method, Please help it s urgent.
Sorry I just saw this comment, but this is comment requires a more time to research than I have right now
Where can we find the bond data?
Hi Ryan, thanks for the video! I have a question, I usually see the approximation VaR(95%,h_days)=VaR(95%,1_day)*sqrt(h). Do I get same result as your if i use Daily standard deviation and the approximation above?
The formula you mentioned is the square root of time rule for scaling Value at Risk (VaR). If you use the daily standard deviation and apply this rule, you'll get a result that's close to what I showed, especially for shorter time horizons. However, always be cautious as assumptions might vary and this rule assumes returns are normally distributed and independent over time.
Hi Ryan, have you seen anybody use the Monte Carlo method with historical returns of a single portfolio? In other words rather than making it more complex by having the covariance of many securities that make up the portfolio just using the returns of the portfolio and simulating the VAR?
Hello, I think you can definitely do it that way as the correlations between the assets should be baked into the total portfolios daily returns already. So that could be a quick and dirty method to avoid using a covariance matrix
hi and thanks for the great videos, so i have a question if i may ask. in all of your VAR videos you considered the daily prices and calculated the VAR for a year, what if one considers the monthly prices, and wants to calculate the VAR for 5 years period. how would that change the formulas. I would be more than grateful if you could answer me.
also what if the prices of 3 stocks are being considered?
Hi @vesalmirzaei9579, thanks for reaching out with your question! When you switch from using daily to monthly prices for calculating VaR over a 5-year period, the scaling adjustments change as follows:
For monthly prices, you would annualize the average return by multiplying by 12 (since there are 12 months in a year) rather than 252 as with daily returns. For the standard deviation, you would multiply the monthly standard deviation by the square root of 12 to annualize it, instead of the square root of 252 used for daily returns.
As for 3 stocks, you just use 3 instead of 5 and adjust the weights
Hi Ryan, If I wanted to find the VAR as in % terms would I just multiply the var in dollars as a % of investment dollars?
Hey! You could do this by omitting cell F3 (the portfolio value) when calculating expected return @4:24 . You will also need to omit cell F3 from the calculation @6:01 when calculating Scenario VaR. When I incorporated the portfolio value into this formula I converted it from a percentage to a dollar based VaR.
hey ryan , how do u do this for multi asset portfolios ?
Hello Aditya, it is hard for me to explain in a comment but I may elaborate on this in a full length video
@@RyanOConnellCFA Please do , Multiasset generalization is a need right now - multiasset series maybe ? ( cvar , var , sharpe , portfolio optimization )
Hi great video, I just got one question also regarding your other videos on VaR. When we calculate the scenarios in the monte carlo simulation with the normal distribution function around the expected return and expected standard deviation (like you did) we would get as a result a similar VaR for the portfolio as we would have if we had used the parametric method wouldn't we? The explanation would be that because we use the standard normal distribution in one method to calculate the VaR directly and assume the returns are normally distributed and in the other we generate returns that are also based on normal distributions and therefore the VaR is similar. Is this correct or am I completely wrong? Probably there is the option in monte carlo var to use other distributions with fatter tails to get a more realistic picture.
You are correct in observing that using a normal distribution for both the Monte Carlo simulation and the parametric method will likely yield similar VaR results, as both approaches assume normally distributed returns. However, the Monte Carlo method offers more flexibility, allowing you to model returns using different distributions (although I didn't do that in this video), including those with fatter tails, which can provide a more accurate risk assessment in markets where returns are not normally distributed. This adaptability makes the Monte Carlo method valuable for capturing more complex risk scenarios that the parametric method might not fully address.
@@RyanOConnellCFA Thank you very much for taking the time to respond. I definitely have a better understanding of how it works thank you!!!
Can we use VaR formula if the data is not normally distributed?
Yes, you can use the Value at Risk (VaR) formula even if the data is not normally distributed, but the approach and interpretation might differ. In cases of non-normal distributions, it's important to use methods that capture the skewness and kurtosis of the data, like the Historical VaR or Monte Carlo simulations that can model different types of distributions. These alternative methods allow for a more accurate estimation of VaR in financial markets where returns often exhibit fat tails and are not symmetrically distributed.
Hello, why you didn't take the mean of the ETF returns instead of the 7% . Thanks a lot
The historical long term average of 7% for S&P 500 returns offers simplicity and consistency, utilizing data over a large period to smooth fluctuations. But it relies heavily on the continuation of past performance, potentially ignoring recent market changes and economic forecasts.
In contrast, using last year's adjusted close price data for the S&P 500 offers timely and relevant estimates, factoring in recent changes and aspects like stock splits and dividends. However, this approach may overemphasize short-term trends and is more complex to implement. It also might be influenced by short-term volatility, potentially skewing expected returns. A lot of my other videos utilize this method so you can do it either way you'd like.
@2:35 why do you not multiply by 252^.5 like you did for Std. dev?
If you're referring to the p[art where I calculate Portfolio Variance and Portfolio St. Dev in cells F7 and F8, we don't multiply by 252^.5 at that part because those are just the annual metrics. Whenever you see 252 involved, that is when we are adjusting those annual numbers for the number of days in the time period we are interested in. Does that make sense?
@@RyanOConnellCFA Yes that part makes sense. Where I’m confused is @1:21 in cell C4, why was the std dev multiplied by 252^.5? Im struggling to understand why we take the sq rt of 252 to annualized the SD
I see where you are confused. This is because the we are using daily stock price returns to calculate the standard deviation. So we are converting the daily returns standard deviation into annual. And then later on in the video we are using that annual standard deviation measure to calculate the the standard deviation for a specific time period in days
So if I understood this method correctly, the 10,000 simulations represent the VaR of the portfolio for 10,000 random normal z-values. Then we sort them and select the 100th lowest value, which represents the 0.99% confidence interval. It seems to me that this is pointless, why don't we simply select the z-value that corresponds to the 0.99% confidence interval (i.e. the parametric method), rather than simulating 10,000 random normal z-values , just to approximate the z-value? I would think that the monte carlo method would be a simulation of asset returns, rather than a way to approximate the z-value that we already know of.
Hi @georgechristou7982, great observation! In the Monte Carlo simulation for VaR, we indeed generate 10,000 random Z-scores assuming a normal distribution to simulate potential outcomes. However, the key here is that we're translating these Z-scores into actual dollar gains and losses for the portfolio, not just approximating a Z-value. This allows us to account for the specific characteristics and scale of the portfolio's assets, providing a more concrete and practical measure of risk in monetary terms. We sort these results and chop off the worst 1% to find the VaR at a 99% confidence level, thus identifying potential extreme losses in realistic financial terms.
I think you should have insert VaR in the histogram and guide how to do this, that would be great instead of just clicking the bars that their 5% VaR lies.
It is all a trade off of how much time you want to make the video vs how much value a new feature adds. I'm not sure that would have been worth the added time to most viewers
this is exactly the same of the parametric method]
It isn't the same. The parametric method comes up with a single calculation whereas the Monte Carlo method performs hundreds to thousands of simulations and gets a distribution of possible outcomes. Please see this video for the parametric method: ua-cam.com/video/y4AOyA28d0M/v-deo.html
@@RyanOConnellCFA thanks man! now you need to do some videos on how to value a company by DCF
@@alejadroigoyanes My pleasure! I have made on video on that topic here: ua-cam.com/video/YVDv8Vmtqlc/v-deo.html
@@RyanOConnellCFA but that's the dividend discount model not a discounted cash flow model
@@alejadroigoyanes Oh, you're right I misread that. I will look into a discounted cash flow model in future
I am affraid that's not a monte carlo VaR, at leat not how it's done in real world
Please enlighten us with how it is done in the real world
@@RyanOConnellCFA simulating correlated returns of your assets using actual distribution and a copula. If you simulate a normal distribution, you are pretty much doing the same as a parametric VaR
You can think of 100$ clean zero coupon bond with 5% probability of default. VaR 95% is 100$ (5% of the time, the bond defaults and you lose 100$). Using the method you describe will give VaR = 0$ (because volatility is 0)