Monte Carlo Method: Value at Risk (VaR) In Excel

[@RyanOConnellCFA]

🔑 Join this channel to get access to perks & support my work: kzbin.info/door/Akyj2N9kd0HtKhCrejsYWQjoin 👨‍💼 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/

[@relaxingmusics7853]

I swear no one has explained Monte carlo in such easy and descriptive manner Thanks

[@RyanOConnellCFA]

It is my pleasure, thank you for the nice feedback!

[@shih-binshih9889]

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

[@RyanOConnellCFA]

Really appreciate the feedback, my friend! It means a lot

[@RyanOConnellCFA]

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!

[@mohitbhangale2755]

practical examples provided a clear illustration of how this technique is applied in real-world scenarios. Thanks for sharing such an informative video!

[@RyanOConnellCFA]

I agree, I learn much better with hands on examples! Thank you for the feedback

[@pepelepew1227]

wow this is so nostalgic. now i just sell sauced chicken and kimchi.

[@RyanOConnellCFA]

Lol what's the best sauce you got?

[@pepelepew1227]

@@RyanOConnellCFA sichuan mala and thai :) kimchi lasts long enough unrefrigerated to survive free shipping.

[@Tyokok]

thank you so much!!!

[@RyanOConnellCFA]

My pleasure!

[@relaxingmusics7853]

Great Videos and more descriptive what if we dont have expected return then what should be our methology?

[@samersaidayad2175]

Awesome job Ryan, Thank you man

[@RyanOConnellCFA]

My pleasure Samer!

[@rickguerrero2282]

Nice work!

[@RyanOConnellCFA]

Thank you!

[@Fortuna_10_]

sir, you are genius!

[@RyanOConnellCFA]

I'm just a guy who has spent a lot of time studying one specific thing! Thank you though

[@bankoleeluyera9495]

I am trying to this for Bonds, T-Bills, Eurobonds and Promissory notes

[@audreylubis]

Thank you for your explanation!! May I know what is the references for calculating the Scenario Gain or Loss? (for referencing purpose) Thank You!!

[@RyanOConnellCFA]

It is my pleasure! Could you clarify what you mean by reference? Do you mean textbooks?

[@audreylubis]

@@RyanOConnellCFA yes sir, the textbook

[@Durranisvlogs]

Hi Ryan! For Variance and StDev of portfolio you've not incorporated the number of trading days. Is there a reason? As i am new to financial modelling and i am picking up the basics through your course.

[@ron3252]

Absolutely epic

[@RyanOConnellCFA]

Thank you!

[@AmitBarnawal-ft3lc]

Ryan why you multiply 252*.5 (0.5 is not understood) can you please explain

[@gomezchris276]

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?

[@RyanOConnellCFA]

Hey Chris, I've got a whole video on that very topic! You can find the link here: kzbin.info/www/bejne/g2ekinSudqqcbrs Good luck with your studies!

[@LEO-n6p8q]

Very helpful Ryan

[@RyanOConnellCFA]

Glad to hear it!

[@LEO-n6p8q]

@@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

[@RyanOConnellCFA]

What are you having trouble with in this video?

[@LEO-n6p8q]

@@RyanOConnellCFA I cannot understand the difference between scenario var and Monte carlo var as well as why we do that

[@RyanOConnellCFA]

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

[@zainabjamshed7260]

Amazing

[@RyanOConnellCFA]

Thank you!

[@vesalmirzaei9579]

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.

[@vesalmirzaei9579]

also what if the prices of 3 stocks are being considered?

[@RyanOConnellCFA]

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

[@kaylemorgan9173]

Hello sir, can i ask you how to calculate VaR for an unlisted company?

[@saadaziz001]

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)?

[@RyanOConnellCFA]

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

[@sofiadha7584]

Can we use VaR formula if the data is not normally distributed?

[@RyanOConnellCFA]

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.

[@Il_dem]

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?

[@RyanOConnellCFA]

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.

[@mlacorte21]

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?

[@RyanOConnellCFA]

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

[@Im-Assmaa]

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.

[@RyanOConnellCFA]

Sorry I just saw this comment, but this is comment requires a more time to research than I have right now

[@fritzklaus3771]

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.

[@RyanOConnellCFA]

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.

[@fritzklaus3771]

@@RyanOConnellCFA Thank you very much for taking the time to respond. I definitely have a better understanding of how it works thank you!!!

[@mlacorte21]

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?

[@RyanOConnellCFA]

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.

[@humichael7228]

Why are we using normal distribution when one of the primary purposes of using monte carlo is to avoid the assumption of normal distribution?

[@itskaicaeli]

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)?

[@RyanOConnellCFA]

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

[@0000deutschland]

Hello, why you didn't take the mean of the ETF returns instead of the 7% . Thanks a lot

[@RyanOConnellCFA]

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.

[@ABDULLAHMALIK]

Nice

[@AdityaGupta-xg7ez]

hey ryan , how do u do this for multi asset portfolios ?

[@RyanOConnellCFA]

Hello Aditya, it is hard for me to explain in a comment but I may elaborate on this in a full length video

[@AdityaGupta-xg7ez]

@@RyanOConnellCFA Please do , Multiasset generalization is a need right now - multiasset series maybe ? ( cvar , var , sharpe , portfolio optimization )

[@raunaknoah6819]

Where can we find the bond data?

[@Idontknow-xi9hi]

But why do we need to take the square root of the time period?

[@JameelAhmed-xd8cg]

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.

[@RyanOConnellCFA]

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

[@JameelAhmed-xd8cg]

Yes, I think you are correct. Thank you

[@Harambe0]

@2:35 why do you not multiply by 252^.5 like you did for Std. dev?

[@RyanOConnellCFA]

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?

[@Harambe0]

@@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

[@RyanOConnellCFA]

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

[@samuaa]

​@@RyanOConnellCFA I think he s referring to the fact that to gauge st.dev. you multiply by 252squareroot but then to gauge covariance (around 2.20) you don't use the square root

[@georgechristou7982]

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.

[@RyanOConnellCFA]

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.

[@alejandroiglesiasgoyanes9395]

this is exactly the same of the parametric method]

[@RyanOConnellCFA]

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: kzbin.info/www/bejne/r2WkgKx3Z52XZq8

[@alejadroigoyanes]

@@RyanOConnellCFA thanks man! now you need to do some videos on how to value a company by DCF

[@RyanOConnellCFA]

@@alejadroigoyanes My pleasure! I have made on video on that topic here: kzbin.info/www/bejne/j4enp2uMotmkosU

[@alejadroigoyanes]

@@RyanOConnellCFA but that's the dividend discount model not a discounted cash flow model

[@RyanOConnellCFA]

@@alejadroigoyanes Oh, you're right I misread that. I will look into a discounted cash flow model in future

[@floptsi7493]

I am affraid that's not a monte carlo VaR, at leat not how it's done in real world

[@RyanOConnellCFA]

Please enlighten us with how it is done in the real world

[@floptsi7493]

@@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

[@floptsi7493]

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)