Parametric Method: Value at Risk (VaR) In Excel
Vložit
- čas přidán 15. 05. 2024
- Ryan O'Connell, CFA, FRM explains how to calculate Value at Risk (VaR) in Excel using the parametric method (variance-covariance 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 (Parametric Method)
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.
![[Vowel]물고기는 물에서 살아야 해🐟🤣Fish have to live in the water #funny](http://i.ytimg.com/vi/3G9MH063Cx4/mqdefault.jpg)
📘 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/parametric-method-value-at-risk-var-excel-template/
what would I do without you? thank you. you will remain in my prayers for the rest of my life.
Your videos are to the point and very informative. I Really enjoy watching them. Keep up the good work 👍
Really appreciate the feedback Prekshit! Thank you
That was really helpful, Ryan. Thanks!
My pleasure Tomas!
Really appreciate your video! It helps me a lot.
Glad to hear it!
You’re got a really good channel!
Thank you so much!
Thank you very much for this video
You are welcome!
Hi Ryan, could the VAR be shown in return terms rather than dollars? If so would it just be the dollar VAR/Portfolio Value?
Hey! You could do this by omitting cell F3 (the portfolio value) when calculating expected return @4:39 . You will also need to omit cell F3 from the calculation @5:42 when calculating VaR. When I incorporated the portfolio value into this formula I converted it from a percentage to a dollar based VaR.
Thank you so much
You're most welcome
@@RyanOConnellCFA💕💕💕
Hi Ryan I really appreciate the content - Ive used it to self study most of my finance degree!
Do you know of a method to implement stop-loss orders into this VaR method? I’m struggling because, based on the period and management structure in question, theres a certain risk of lost return potential.
Thank you again!
I'm truly humbled to hear my videos have significantly aided your finance degree studies-thank you for sharing that with me!
One thing you could try is to utilize the historical simulation VaR approach to determine your stop-loss levels. By analyzing past market movements and your VaR calculations, set your stop-loss orders at points that align with historical losses exceeding your VaR threshold.
Hi, how would it be if we only analyze one stock, not a porfolio?? Thank you for your explanation!!
In order to do this, you would just take the returns of one stock instead of combining them. It would be easier to do actually
Hi Ryan, can you please explain why do you incorporate the Expected Return in the VaR formula? Most other sources I found online simply calculate without that. Thanks
Hi Richard, great question! The Expected Return is often incorporated into the Value at Risk (VaR) calculation for a more precise long-term estimation. While it's true that some methods calculate VaR solely from the standard deviation of historical returns, including the Expected Return gives us a more comprehensive view.
When we incorporate Expected Return, we're not only looking at the risk in terms of potential loss but also in relation to the potential gains.
@@RyanOConnellCFA Would you mind explaining on how to obtain the Expected Return of an asset?
Hey Ryan, could you please explain me how did we get the z score of 1.645 for confidence level of 0.95? Thanks a lot!
My pleasure! This is the Z-score for a 1-tailed distribution at a confidence level 0.95. You can find a "1 tailed z score table" by Googling that phrase
I thought for 1.64 was used for 95% one tail and 90% two tail? 1.96 for 97.5% one tail and 95% two tailed? I must have it backwards, either way, great video as usual. Love the work you do.
@@Ryanhess1986 Hey Ryan, thank you for calling this out. I had it backwards in the comment I replied to so I just edited that comment. This is in fact a 1 tailed test. VaR is based on 1 tailed tests as we are only calculating the left tail and you are correct that 1.645 is used for 95% one tailed tests
@@RyanOConnellCFA now it all makes more sense, lol! Thanks for clarifying, as VaR is something I am not too familiar with at all, so that comment certainly made it “click”!
@@Ryanhess1986 Awesome, I'm glad to hear that Ryan! This video here may be an even better starting point for you then: czcams.com/video/2SMkbMDypXI/video.html
Hi Ryan, great video! can you please explain how to calculate VaR using this method on an ECB loan?
Thank you Padmanaban! Sorry, but I don't have any familiarity with that type of loan so I'm not sure how you'd accomplish that
Hi Sir. Your video here is really good. I got a new insight. Would you mind creating a video using Excel of making 95%-Expected Shortfall (CVaR) from a portfolio using a historical simulation approach? It would be very great and helpful :D Thank you so much
This is a good video idea! I'll put it on my list for the future but it may be a while as I have quite a backlog of videos
Can I understand why you use expected return to minus those values? I couldn’t understand it
Hello, Value at Risk is a measure used to understand the potential downside or loss an investment portfolio could face over a specific period for a given confidence interval. When calculating VaR, it's important to understand that we are interested in the unexpected negative deviations from our expected return.
Think of the expected return as our 'average' or 'normal' return for any given day. It's the scenario we most likely anticipate. So, when we want to measure the potential loss relative to this 'normal' situation, we subtract out the expected return. This helps us isolate and focus only on the unfavorable deviations, giving us a clearer picture of the risk.
In simpler terms, by subtracting the expected return, we're setting our starting point at what we think is going to happen on an average day. From there, we measure how bad things could get relative to this baseline
Thanks for the response. However, I still have few questions:
(1)Understand that the formulae of VaR is Amount invested x standard deviation x Z score.
In this case, why would we use Expected return - VaR (which is amount invested Amount invested x standard deviation x Z score) to arrive at VaR again? Are there any duplications?
(2) Are there two different formulae for VaR. One is amount invested Amount invested x standard deviation x Z score. And second one is Mean - Zscore (standard deviation) - this is what I learnt from your first video. What are the differences between these two?
Appreciate your advice on this.
Where did you get that VaR formula from? VaR calculations should take into account the expected return so you may have an incorrect formula.
how do you know de expected values from the variables do you make de arege of the variebles or what?
Please take a look at the start of this video to see how I will determine a value for the expected return and standard deviation of an asset based on its historical data: czcams.com/video/XQS17YrZvEs/video.html
Why is the square root of trading days used for the annual conversion? I understand that using 252 directly gets us a pretty unreasonable number, so my question is more "whats the reasoning behind the sqrt of days as opposed to some other product of the number of days?"
In order to convert daily standard deviation to annual standard deviation, you must multiply daily standard deviation by the square root of the number of days. We assume there are 252 trading days in a year. This is simply a mathematical rule
please make a DCF valuation video or several videos
You can find my most recent video on this topic here: czcams.com/video/YVDv8Vmtqlc/video.html
Can you explain how to calculate the Cost at Risk (CaR) ??
I'll put this on my list of videos to make in the future!
I think you missed the second term of the equation in the prtfolio variance?
Could you please elaborate on what you think is missing? This video has been viewed almost 20,000 times and you're the first person to mention this