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

Подписаться 30 тыс.

Просмотров 41 тыс.

50% 1

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.

Опубликовано:

31 май 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 75

@RyanOConnellCFA Год назад

📘 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/

@relaxingmusics7853 2 месяца назад

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

@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 2 года назад

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!

@AmitBarnawal-ft3lc Месяц назад

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

@pepelepew1227 2 года назад

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

@RyanOConnellCFA 2 года назад

Lol what's the best sauce you got?

@pepelepew1227 2 года назад

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

@mohitbhangale2755 9 месяцев назад

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

@RyanOConnellCFA 9 месяцев назад

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

@samersaidayad2175 Год назад

Awesome job Ryan, Thank you man

@RyanOConnellCFA Год назад

My pleasure Samer!

@relaxingmusics7853 2 месяца назад

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

@zainabjamshed7260 Месяц назад

Amazing

@ron3252 7 месяцев назад

Absolutely epic

@RyanOConnellCFA 7 месяцев назад

Thank you!

@saadaziz001 10 месяцев назад

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 10 месяцев назад

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

@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

@user-xb5zq7rm7y 11 месяцев назад

Very helpful Ryan

@RyanOConnellCFA 11 месяцев назад

Glad to hear it!

@user-xb5zq7rm7y 11 месяцев назад

@@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 11 месяцев назад

What are you having trouble with in this video?

@user-xb5zq7rm7y 11 месяцев назад

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

@RyanOConnellCFA 11 месяцев назад

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

@ABDULLAHMALIK Год назад

Nice

@gomezchris276 2 года назад

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 2 года назад

Hey Chris, I've got a whole video on that very topic! You can find the link here: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-M6AYAxAEi8Y.html Good luck with your studies!

@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

@mlacorte21 8 месяцев назад

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 8 месяцев назад

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

@raunaknoah6819 Месяц назад

Where can we find the bond data?

@Il_dem 8 месяцев назад

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 8 месяцев назад

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 8 месяцев назад

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 8 месяцев назад

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.

@vesalmirzaei9579 3 месяца назад

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 3 месяца назад

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

@fritzklaus3771 4 месяца назад

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 4 месяца назад

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 3 месяца назад

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

@Harambe0 2 года назад

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

@RyanOConnellCFA 2 года назад

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 2 года назад

@@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 2 года назад

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

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

@sofiadha7584 4 месяца назад

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

@RyanOConnellCFA 4 месяца назад

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.

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

@JameelAhmed-xd8cg 20 дней назад

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 20 дней назад

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

@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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-y4AOyA28d0M.html

@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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-YVDv8Vmtqlc.html

@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 3 месяца назад

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

@RyanOConnellCFA 3 месяца назад

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

@floptsi7493 3 месяца назад

@@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 3 месяца назад

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)