🎓 Tutor With Me: 1-On-1 Video Call Sessions Available ► Join me for personalized finance tutoring tailored to your goals: ryanoconnellfinance.com/finance-tutoring/ 💾 Download the file created in this video free here: ryanoconnellfinance.com/product/sharpe-ratio-in-excel/
These are so helpful! I'm doing a finance msc after my undergrad in law, in Ireland, so had never used excel before!! Thanks for taking time and uploading
Learning the same info in my finance class right now; also used spy as our proxy in excel! Nice to watch a video on it, textbooks can get dull after a while.
Hi Ryan, I understand multiplying the stddev.p by 252 to annualize but why are you taking the square root as well? If it were yearly data for 3 years would you do the same thing? Thanks!
I notice that you base the Sharpe ratio based on stock price growth, rather than total return (price growth plus dividend yield). Is this how it’s done? I’m new to this, but it seems like dividend stocks would be penalized.
You are completely correct. A better way to fix this is to use adjusted close prices (instead of just close prices) as that accounts for stocks that pay dividends
I would likely use the same methodology used to calculate annualized return on the stock as I would on the risk free rate if possible. You could convert each days annual risk free return into a daily return then annualize the average daily returns
@@RyanOConnellCFA Why wouldn't you use the latest risk free rate? Wouldn't it represent the upcoming market conditions better? Thank you for your response!
@@JesusGGarza You absolutely could use the current risk free rate to make it more accurate! For example, you could use the current 10 year treasury rate
Hello Ryan, thanks for the videos, just want to ask you how can i calculate the sharpe ratio for my porfolio which is composed of: 1- Many S&P500 stocks, 2- Different ETF's stocks 3- Different ETF bond; As i want to check the sharpe ratio for my mixt portfolio! Thank you so much
Great vid, thanks for the awesome content. Quick question can you please speak on the intuition of how multiplying the daily st. dev by sqrt(252) annualizes the st.dev. I searched online for explanation and what I found was to annualize monthly deviations times it by sqrt(12) & to annualize weekly deviations times it by sqrt(52) It might be that the answer is right in front of me but I still can't wrap my head around it.
I'm not certain of a great way to intuitively understand it. But you are correct when you say "to annualize monthly deviations times it by sqrt(12) & to annualize weekly deviations times it by sqrt(52)". I would just say, to annualize standard deviation, you just multiple by the square root of the number of units that go into the year. So 365 days, 52, weeks, 12 months. I wish I could be more helpful haha
@@RyanOConnellCFA I’m assuming that you multiply variance by the number of periods to get the variance for the target period length. So to get the new std dev, you take sqrt(var * period_count) = std_dev * period_count^0.5
Dear Sir. How can I calculate the Annual Return and Annual Standard Deviation of stock with data from specific period (1 Jan 2024 to 15 May 2024)? Which formula should I use? Thank you❤
Between those two days, there are 135 days. If you convert it to years you get 0.3698 years. Yearly return rate is the same so the formula is (last price/first price)^(1/0.3698)-1. The rest is the same.
Hi Ryan how can I calculate the Sharpe Ratio of a set of multiple companies of a sector (in this case also S&P500). I calculated all annual return percentages and took the averages to get the one annual return percentage. Then for the annual st. dev. I selected the price changes of all funds together to the power of 12 (trading months per year) and I followed your .5 approach. Is this the right way?
Ruben, it is hard to say without seeing it but that sounds right to me! You could calculate st dev the same way I did it in this video except instead of using the single stocks daily returns you use the weighted average of all the stocks
@@RyanOConnellCFA Right, using simple returns can lead to conceptual inconsistences. Or else need to take the geometric average to make sense (but that is another variation of the Sharpe) 🙂
If I want to calculate the sharpe ratio in minute data, then is it true for this formula >>> stdev.p (data) * sqrt(365*24*60) ? Just want to make sure the correct value is put into sqrt() column, hope to hear from you soon. Thank you.
Hi @IamGirlsSone, your approach to adjusting the Sharpe Ratio for minute-level data is on the right track! However, for a more accurate annualization in the context of stock markets, I'd recommend to use 252 trading days and 6.5 trading hours per day. This change reflects the typical number of trading days in a year and the daily trading hours for major stock exchanges like NYSE and NASDAQ, providing a closer approximation to the actual trading environment. So, your formula would adjust to using stdev.p(data) * sqrt(252*6.5*60) to account for the annualization of volatility when dealing with minute-by-minute data
🎓 Tutor With Me: 1-On-1 Video Call Sessions Available
► Join me for personalized finance tutoring tailored to your goals: ryanoconnellfinance.com/finance-tutoring/
💾 Download the file created in this video free here: ryanoconnellfinance.com/product/sharpe-ratio-in-excel/
These are so helpful! I'm doing a finance msc after my undergrad in law, in Ireland, so had never used excel before!! Thanks for taking time and uploading
Thank you, I'm glad to hear this was helpful for you! By the way, I have visited Ireland before and loved it there
Learning the same info in my finance class right now; also used spy as our proxy in excel! Nice to watch a video on it, textbooks can get dull after a while.
The timing couldn't have been better! Glad you're finding it helpful Tyler
Nicely displayed and easy to follow, thanks.
Thanks you mr i love youuuu for your tutorial
Thank you for the feedback!
Hello Ryain, it’s amazing
Thank you my friend!
Hi Ryan, I understand multiplying the stddev.p by 252 to annualize but why are you taking the square root as well? If it were yearly data for 3 years would you do the same thing? Thanks!
I notice that you base the Sharpe ratio based on stock price growth, rather than total return (price growth plus dividend yield). Is this how it’s done?
I’m new to this, but it seems like dividend stocks would be penalized.
You are completely correct. A better way to fix this is to use adjusted close prices (instead of just close prices) as that accounts for stocks that pay dividends
Hi ryan, I was wondering how i would calculate annual return if i only have data from one year?
why you make risk free rate 1.5 did you calculate it?
If I have a series of risk free rates, should I use a geometric average of them as well?
I would likely use the same methodology used to calculate annualized return on the stock as I would on the risk free rate if possible. You could convert each days annual risk free return into a daily return then annualize the average daily returns
@@RyanOConnellCFA Why wouldn't you use the latest risk free rate? Wouldn't it represent the upcoming market conditions better? Thank you for your response!
@@JesusGGarza You absolutely could use the current risk free rate to make it more accurate! For example, you could use the current 10 year treasury rate
Can we do sharpe ratio on returns rather than price ?
That is what we are doing in this video. Sharpe ratio is always based on returns, but returns are based on price
Hello Ryan, thanks for the videos, just want to ask you how can i calculate the sharpe ratio for my porfolio which is composed of:
1- Many S&P500 stocks,
2- Different ETF's stocks
3- Different ETF bond;
As i want to check the sharpe ratio for my mixt portfolio!
Thank you so much
Hey Simon, you should get the cumulative daily value changes of the whole portfolio and then follow the same approach of what I did in this video
Thanks for the help!
Happy to help!
Great vid, thanks for the awesome content.
Quick question can you please speak on the intuition of how multiplying the daily st. dev by sqrt(252) annualizes the st.dev.
I searched online for explanation and what I found was to annualize monthly deviations times it by sqrt(12) & to annualize weekly deviations times it by sqrt(52)
It might be that the answer is right in front of me but I still can't wrap my head around it.
I'm not certain of a great way to intuitively understand it. But you are correct when you say "to annualize monthly deviations times it by sqrt(12) & to annualize weekly deviations times it by sqrt(52)". I would just say, to annualize standard deviation, you just multiple by the square root of the number of units that go into the year. So 365 days, 52, weeks, 12 months. I wish I could be more helpful haha
@@RyanOConnellCFA I’m assuming that you multiply variance by the number of periods to get the variance for the target period length. So to get the new std dev, you take sqrt(var * period_count) = std_dev * period_count^0.5
Considering and ETF, how would you calculate MER (year by year) for 5-10 years?
You could calculate quite easily if you had data on the ETFs expenses but I'm not exactly sure what API you would use to find that
Dear Sir. How can I calculate the Annual Return and Annual Standard Deviation of stock with data from specific period (1 Jan 2024 to 15 May 2024)? Which formula should I use?
Thank you❤
Between those two days, there are 135 days. If you convert it to years you get 0.3698 years. Yearly return rate is the same so the formula is (last price/first price)^(1/0.3698)-1. The rest is the same.
Great content! Can you please make a new video on how to calculate the Sharpe Ratio of Options Strategies?
Thank you! I can look into making a video on this topic in the future
Thank you, Ryan. I'm really looking forward to it. Meanwhile, can you give me guidance or say if there is any TH-cam channel or books?
Hi Ryan how can I calculate the Sharpe Ratio of a set of multiple companies of a sector (in this case also S&P500). I calculated all annual return percentages and took the averages to get the one annual return percentage. Then for the annual st. dev. I selected the price changes of all funds together to the power of 12 (trading months per year) and I followed your .5 approach. Is this the right way?
Ruben, it is hard to say without seeing it but that sounds right to me! You could calculate st dev the same way I did it in this video except instead of using the single stocks daily returns you use the weighted average of all the stocks
@@RyanOConnellCFA I just saw your reply, thank you!
This is so great ‘ thank you so much.
My pleasure!
Hi. According to chat gpt, we need to get the aritmatic average of returns instead of componded annual rate of retuen. I'm confused!
Hello there, it can depend on whether we are using simple or logarithmic returns. In this video, we are using simple
Isn’t the sharp ratio for a stock = mean daily return - risk free rate / standard deviation of daily returns
Sir if we want to calculate for one year for daily how can we do that plz explain its very important
Could you please clarify what you mean by "one year for daily"?
Usually log returns are considered
I agree and would recommend using =ln() to calculate returns in hindsight
@@RyanOConnellCFA Right, using simple returns can lead to conceptual inconsistences. Or else need to take the geometric average to make sense (but that is another variation of the Sharpe) 🙂
Absolutely, all my newer videos use log returns!
You skip the process of estimating Rf, could you please explain that part in more detailed way
Hey Anton! You could just look up the 10 year Treasury Return Rates over the same period of time as the risk free rate
If I want to calculate the sharpe ratio in minute data, then is it true for this formula >>> stdev.p (data) * sqrt(365*24*60) ? Just want to make sure the correct value is put into sqrt() column, hope to hear from you soon. Thank you.
Hi @IamGirlsSone, your approach to adjusting the Sharpe Ratio for minute-level data is on the right track! However, for a more accurate annualization in the context of stock markets, I'd recommend to use 252 trading days and 6.5 trading hours per day. This change reflects the typical number of trading days in a year and the daily trading hours for major stock exchanges like NYSE and NASDAQ, providing a closer approximation to the actual trading environment. So, your formula would adjust to using stdev.p(data) * sqrt(252*6.5*60) to account for the annualization of volatility when dealing with minute-by-minute data