Historical Method: Value at Risk (VaR) In Excel

Ryan O'Connell, CFA, FRM walks through an example of how to calculate Value at Risk (VaR) in Excel using the Historical 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 Stock Price Returns
1:25 - Define Portfolio Assumptions
1:44 - Find Daily Profits and Losses
2:37 - Calculate Value at Risk (VaR) Using Historical Method
3:10 - Create VaR Histogram
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.

[@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/value-at-risk-var-excel-workbook-a-comprehensive-tool-for-risk-management/

[@ksingh1743]

Nice and crisp

[@RyanOConnellCFA]

Really appreciate it!

[@thetoo6363]

Thank you very much

[@RyanOConnellCFA]

You are welcome

[@KazekageKidd]

Amazing, thank you so much for covering this. By any chance will you go into Basel III and Tier 1 etc?

[@RyanOConnellCFA]

It is my pleasure! I think I will get to those topics in the future but it likely won't be for a while

[@williama.rivera9414]

Hi Ryan, useful information. Using this approach or evaluation, can one consider to add more stocks or bonds in the portfolio.

[@RyanOConnellCFA]

Thank you, William. Yes, you definitely could. You would just need to include the other stocks/bonds daily returns into the overall gain or loss for each day. It should be relatively simple to add more stocks and bonds

[@Photon-1927]

Thanks for showing calculation. What does it mean

[@yassinekhezami4886]

Thank you very much Ryan, a quick question by rebalancing the portfolio every day ( starting with $100 000 each day) does not biased the profit and loss ? Why dont we multiply the return of each day with the new value of the portolio ? Thank you

[@mphys5370]

Would be great if you did a historical VaR video for interest rate Swaps

[@RyanOConnellCFA]

I could look into this topic in the future!

[@sandrogross4940]

Thanks for the explanation! If I get it right this represents the VaR with a holding period of 1 day. What would you have to do if you’d like to find the VaR for a 10-day holding period with the historical method?

[@TheSach102]

Had the same question, I did some research and you just multiply Daily VaR by sqrt(number of days), under the assumption that returns are independently and identically distributed (i.i.d.) and follow a normal distribution.

[@francisnash1740]

What if I use the log return? And What are the pros and cons🤔

[@riteshrayamajhi2041]

@RyanOConnellCFA is that a daily VAR or what can you please clarify the conclusion of this calculation?

[@rakanalmosalli2650]

@RyanOConnellCFA This is excellent and very informative! Instead of stock/bond returns, I'm interested in evaluating potential losses originating from foreign currency liabilities denominated in EURO and Mexican Peso, for example. Can I still apply the same assumptions provided here to assess this?

[@RyanOConnellCFA]

Thank you! You could follow similar logic by calculating the day over day returns on the liabilities I believe

[@SD-gw5vm]

Honestly. Is it that simple? I saw a video that just threw me off. Firstly, thank you for this. Secondly, could you please help us understand what VAR is used for in the investment world and what it is actually telling us. Third, could you please do something on CVAR and help us understand why they say its a better estimate than VAR I appreciate its a lot to ask but people like you are helping people like us who come from a tech background to better support finance professionals in their jobs. We appreciate all the good work you do 😊

[@RyanOConnellCFA]

1. This is a simplified implementation so it is possible that both my video and the other video are correct but that one has more nuance! Overall, the historical method of VaR is the simplest and easiest to implement so what you see in this video should be most of what you need. 2. I have a video on this here: kzbin.info/www/bejne/aISwnJWDed6jjqs 3. Great suggestion, I will make a video on CVAR in the future 4. That is great to here that you getting value out of my videos! It is cool that people on the tech implementation side are getting value out of these as well

[@SD-gw5vm]

@@RyanOConnellCFA Thank you for agreeing to make them. Not sure how things are where you are but in the UK it's gotten so competitive some tech jobs actually ask for CFA/FRM alongside your tech qualifications. Overtime I think our knowledge of finance needs to be really good if we want to stay competitive. I really appreciate you getting back to me. Maybe you could run a paid service on how some of these topics are applied in the industry. I for one would be very interested in taking some classes

[@RyanOConnellCFA]

@@SD-gw5vm wow I'm really surprised to hear that tech jobs would be requiring those designations! It seems like overkill for someone in a tech focused role. I'm definitely looking into developing a course like this. It is hard for me to find time for all the projects that I want to take on but I think this is one product that could have a lot of demand

[@SD-gw5vm]

@@RyanOConnellCFA Some roles in London do this now for positions like Business analysts it's a something that comes up. Actually some finance analysts shift to the IT space and make a lot of money in IT. I recently onboarded some contractors who worked as regulatory and market risk analysts. The demand definitely exists. We from the tech crowd are simply trying to protect our tuff😂. I look forward to enrolling on the courses.

[@TheSach102]

Hello Ryan, thanks for the explanation, it was very useful. I have a few questions regarding this: is daily returns necesary or I can go for montly returns instead? Does it make sense that if I determine that certain commodity prices fluctuate in seasons to define the data that wad instead of whole years? Cheers from Chile

[@RyanOConnellCFA]

Hi @TheSach102, cheers from the US and thanks for your questions! You can definitely use monthly returns instead of daily returns for calculating VaR; it often simplifies the analysis without compromising too much on accuracy, especially for long-term risk assessment. Additionally, if you observe that certain commodity prices exhibit seasonal fluctuations, it absolutely makes sense to adjust your data accordingly. Analyzing the data within the context of these seasonal trends can provide more precise risk estimates that better reflect the underlying market dynamics. This tailored approach can significantly enhance your risk management strategy.

[@hantran5894]

Hi is this applicable if all of my assets do not occupy the whole 100% of the portfolio value?

[@imirosmanov2745]

Hi, Mr. Ryan, Could you please explain how to calculate VaR for operational risk.

[@RyanOConnellCFA]

Hey Ivan, to be honest I'm not sure how to do that and do not have experience with that topic

[@aarondelarosa3146]

Excellent. Could you replicate it in Python?

[@RyanOConnellCFA]

Absolutely! I will be uploading a Python implementation of this in about 2 weeks

[@sonalirainavaid173]

Can I use this for multi asset portfolio?

[@RyanOConnellCFA]

You certainly could Sonali. You just need to take a weighted average of all the assets to get a single daily return for the whole portfolio. Then you can do the same analysis as shown in the video on those daily returns

[@levent-aksu]

The link to data file doesn't work. Thanks.

[@RyanOConnellCFA]

Hello, the link to the downloadable file is working just fine on my end

[@samirhajiyev6905]

why didn't you Sort the data before the calculation of VAR ? could you please explain it?

[@RyanOConnellCFA]

Hey Samir, what makes you think that we need to sort the data? We can simply use inbuilt Excel formulas to find the significance level we are interested in without sorting anything

[@samirhajiyev6905]

Hi Ryan, I've seen it on several youtube channels as well as ChatGPT. 1-kzbin.info/www/bejne/l4HLZKCQiMpobLM 2-kzbin.info/www/bejne/gnSclmmId9GpgMk 3-kzbin.info/www/bejne/Y5jViJSug9F4nac

[@RyanOConnellCFA]

Does my solution make sense to you now Samir?

[@samirhajiyev6905]

@@RyanOConnellCFA your calculation are similar to their. Only difference is you have not sorted data. I wanted just to understand this part.

[@RyanOConnellCFA]

@@samirhajiyev6905 I believe sorting the data is an unnecessary step. With the robust capabilities of Excel, there's no need to sort your data. Instead, you can harness the power of Excel's built-in formulas to quickly and accurately estimate the VaR at your desired confidence interval. By using the PERCENTILE or PERCENTILE.INC functions in Excel, you can easily determine the VaR without sorting the data first. These functions calculate the specified percentile of a dataset, which in this case corresponds to the historical return level that the portfolio is unlikely to exceed at a given confidence interval.