Hey, thank you very much for the video. You answered some very technical questions I had. The level of detail that you went on this video was exactly what I needed and could not find on the web. Thanks!
Perfect balance of the technical calculation and practical use and application. Not too little,... not too much,... not too long. Thanks for the content. Subscribed.
Great video... however I have a sample, eg. 120 days... how would I adjust the formula to work with a period of time and see the Sharpe Ratio for that trade vs. annualised return.
Glad you liked the video! If the period is less than 1 year, just use the simple return of the period. You can still use the same formula for annualizing the volatility of the daily returns. Make sure whatever you're comparing it against is for the same period. Keep in mind sharpe ratio is more meaningful over longer timeframes (multiple years).
@@TactileTrade Actually have a quick question if you don't mind. I took your approach and created my own Sharpe Ratio for a security in my current portfolio (EMR). I got a Sharpe Ratio of .53 using weekly returns from 7/18/16 - 7/12/21. I used a risk free rate of 1.4%, CAGR of 15.3%, Annual STDev of 26.4%. Assumed 50 trading weeks per year. However, I wanted to compare my calculations to another source and portfolioslab.com/symbol/EMR says EMR consistently has a Sharpe Ratio of over 2.0. Am I doing something wrong here or is this source not a good one to use? Thank you!!!
@@StockSpotlightPodcast A long term sharpe ratio above 2 is very rare. Had a look at the link, seems like this is a rolling 12 month sharpe ratio. That's probably why since EMR is up about 55% over the last year. That would be my guess
It depends on your data. For example, there is the S&P 500 Index, and the S&P 500 Total Return Index, which is adjusted to assume all cash payouts to be automatically reinvested. In your case with the SPY, chances are your data does not include dividends. Perhaps you can find a SPY total return dataset but if not, you would probably have to look back on the historical dividends and manually add them on the dates they were paid, and then calculate CAGR.
What would be the difference between using daily returns or larger time frames like weekly or monthly returns when calculating the standard deviation? I assume the shorter the time frame the more accurate the volatility would be and the more accurate the Sharpe ratios would be?
Because everything is being annualized, the end results should be close to each other. For example, to answer your question I quickly checked the standard deviation of the daily and weekly price changes in GLD back to 2007. At first, they were different but once I annualized them, they ended up being 18.06% and 17.35% respectively. So short answer, yes you are right because weekly returns will be less granular, but the difference should be pretty minor in most cases, over the long term.
Hi, thank you very much for the video. What if the period of trading days that I have it is 280 (more than one calendar year), to calculate the annualised volatility do I still have to multiply for SQRT(252)?. Thanks in advance
Hey Eduardo, yes you would still multiply by SQRT(252). As long as you have daily returns, then you would use this multiplier, it doesn't matter how long the period is. Hope that helps!
i know im randomly asking but does any of you know a way to get back into an Instagram account..? I was stupid lost the password. I would appreciate any assistance you can give me!
Hey, really cool vid. I wonder whether the natural log of the daily changes should be used for the volatility calculation? My thinking is that otherwise we’d be treating +1% and -1% moves as equal.
I'll try and answer to the best of my knowledge. My understanding of log vs. simple arithmetic returns is that arithmetic returns are asymmetric (a +50% return followed by -50% return on $100 would result in $75) whereas the same log returns would have no net change (still $100). Since simple arithmetic therefore reflects the compounding environment from one period to the next, it is more suitable for the volatility calc. My master spreadsheet I use to track my strategies daily uses 1-day arithmetic returns and is very accurate. Also - thanks for watching the video!!
Log returns are approximately equal to returns if x is small (|x| < 0.1, so ±10%) ---> log(1+x) ≈ x if x->0 Anyway, I'd use log returns (but the results are still accurate with the erithmetic calculation) 🙂
I am using the Sharpe to evaluate a hedge fund after a long time. What I really want to ask is shouldn't the Vol of the portfolio (Tesla) be calculated on the Excess returns (i.e., over the risk free rate)? And given the current backdrop where the fund started in 2014, would the risk free rate be the current 90-day Treasury rate or an average of the entire period? I'd appreciate your response.
You're exactly right about using excess returns, which the formula achieves by subtracting Rf from Ri. In terms of what to use for Rf, 2% is a safe bet although you could work out an average if you want to get very particular. You could reference this for determining an average Rf: www.macrotrends.net/2492/1-year-treasury-rate-yield-chart
@@TactileTrade Thanks again. So your annualized volatility in the video is not based on excess returns, its just daily returns. Correct me if I'm wrong please.
@@djvicks21 That's correct, annualized volatility is calculated using the straight up, daily returns. No need to subtract Rf on a daily basis (or in the volatility portion of the formula at all)
@@TactileTrade got it, Thanks! It's surprising but many hedge funds and indices in the industry use excess returns for their volatility calculation (the denominator) in Sharpe. Which is why the question - I just wanted to be sure I am right after all these years...
I do not have STDEV.P() function in my MSExcel. I have STDEVP(), which is not same as STDEV.P(). How can I calculate annualized standard Deviation (Volatility) as mentioned in this video. Please suggest.
Thank so much for the video. I wanted to ask you how would you calculate the Sharpe Ratio for the whole portfolio assuming you have 0.6 in Tesla and 0.4 in SPY. The Sharpe ratio for the whole portfolio instead for individual assets?l Do you need to multiply the CAGR and anualised volatility by the weights? and sum all the individual sharpe ratios?
@@anacokovic You would just use the returns of your total portfolio, which should be a weighted average of each holdings. Can't really explain anymore without more details or context
Hi, thanks for the video! I was just wondering whether it will be possible to calculate a timeframe of 81 days using this method? Perhaps like this ((B57/B2)^(365/81))-1?
Glad you found it useful and thanks for the sub :) unfortunately, I made this awhile ago and I no longer have the sample sheet I built in the video. Sorry about that!
Since you're using a daily sampling frequency, shouldn't we be using the annualized expected daily return for the numerator instead of the annualized 5 year return, or are there just different flavors of the Sharpe Ratio calculation?
It's always better to use actual returns where possible, and since the sharpe ratio's purpose is primarily to compare two investments, as long as it is an apples-to-apples comparison it will be fine.
Risk free rate represents what you would receive from a risk free investment. For most people, that is just a savings account, or maybe a government bond. Inflation isn't an input in the sharpe ratio formula.
I know about the rule of thumb that a Sharpe ratio larger than 1 is considered good. But I have always wondered how could an investment or strategy generate such high of a Sharpe ratio? SPY is very much the market portolio, i.e. the mean-variance efficient portfolio. If SPY historically yields a Sharpe ratio between 0.4 to 0.6, how could any strategy beat it by that much?
Very good point. They do exist although they are few and far between. Even the ones that do, maintaining it isn't easy. Based on my experience and knowledge the only legitimate two I could attest to that are accessible (i.e. not Jim Simon's Medallion Fund or other private funds) would be InvestInVol or Volatility Trading Strategies. There are lots of things that would have still been a "good investment" historically like Tesla, bitcoin, etc but measured by unit of risk per the sharpe ratio, not so much. Maybe the > 1 rule of thumb is too picky, or perhaps it just goes to show how hard trading really is!
Hello Eli. I am using Thinkorswim for trading. I trade options on Index(SPX). I want to calculate the Sharpe Ratio of my trades. Can you please guide me on that?
You should be able to generate a report from ToS with your money-weighted rate of return for a given period. You'll then just need to find a way to pull the monthly or daily returns during that period to calculate the volatility denominator. I use Interactive Brokers personally and it can generate a report that includes the sharpe ratio. I'm sure ToS can too.
CAGR is used to annualize returns over multiple years. If you only have one year of data, you can just use the simple return which is (last price-first price)/first price. Hope that helps!
Hello and thank you for the video. I have a question. Suppose I want to rebalance my portfolio every month and add or replace new stocks, how far back should the history go here? 1 year? 6 months?
When calculating the sharpe ratio, the longer the time period, the more meaningful. For example, the sharpe ratio for the live trading history of my strategy only, which is a period of about 8 months, is ridiculously high at like ~2.8 (but so is my benchmark) simply because neither have been tested by a bear market. But if I calculate the sharpe ratio for my entire 13 year backtest, and my benchmark for the same period, it's much more realistic, closer to ~1.3 and < 1 for the benchmark. I would say for a meaningful reading, go back at least a year. But the longer the better. Just make sure you're using the same time period for both investments, so that you are comparing apples to apples. About rebalancing, just make sure the daily or monthly P&L % includes whatever investments were held at their correct weights. For example, if your portfolio held 4 stocks at 25% weightings each, make sure each stock gets a 25% weighting in the overall P&L % that you use to calculate ST.DEV on the whole portfolio. Whether you simply take each stock return and *0.25, or create an elaborate, precise portfolio tracker that tallies up all dollar amounts, it shouldn't matter too much. As long as you end up with that % array to use in the ST.DEV formula. Hope that helps!
Yes, still annualize it. The daily returns still need to be converted. Remember to adjust the Rf accordingly, for example with a 2% annual Rf, half a year = (6/12)*2% = only 1%.
Hey. I am doing the same but for a 10 year period. So the only change I need to make is while calculating CAGR right? How about St Deviation? Do we still mutiply it by the square root of 252 regardless of the number of years we are taking?
That's exactly right. Calculate CAGR for the 10 year period, and as long as your data is daily returns, then you still multiply by SQRT(252). You'll just have a larger dataset which is always good.
Hello Eli. Thanks for the content. I would like to backtest the Options strategy and its Sharpe Ratio along with Profit Factor. Is it possible to accomplish the task in Excel?
Backtesting linear instruments like stocks, ETFs and getting lots of good data from it is totally doable. Backtesting option strategies is a different story. The historical data is vast because all the various contracts (strike, expirations) need to be compiled and queried. I don't think it is realistic to use Excel alone.
As far as I know, you would just use the same CAGR for those formulas as well. You could also use total return if the period is less than 1 year, as long as you also use total return for the risk free rate. I hope that answers your question!
Hello Sir, thank you so much for this video. What if the calculation period is 3 years (1st April 2018 to 31st March 2021) to calculate annualized volatility for one of the mutual funds?
The time period doesn't matter, as long as you multiply by SQRT(252) if the data is daily, or SQRT(12) if monthly. It could be 1 year, 5 or 10. You just use the same ST.DEV formula. Of course, the bigger the sample the better.
The annualized return can be calculated several ways as long as it is annualized. If you have prices, you can calculate CAGR using the beginning, ending prices and number of periods. If you only have % returns (no prices), then you could use the geometric average. In general, geometric average accurately reflects the compounding whereas simple/arithmetic does not. That is why geometric average should always be used when calculating returns over multiple periods.
New subscriber to your channel. Appreciate you posting these videos! How would I calculate sharpe ratio for a portfolio with regular contributions throughout the year? If I use the method outlined in the video the regular contributions make the CAGR seem artificially high. Thanks!
Thank you so much for your support and welcome aboard :) You'll want to find out the time-weighted rate of return (TWR). TWR will give you total return for a period without the compounding of deposits/withdrawls. If the period is longer than a year, you'll then need to annualize it using CAGR. Probably easiest to use an online TWR calculator like this one: www.rateofreturnexpert.com/time-weighted-return-calculator/ More about TWR: www.investopedia.com/terms/t/time-weightedror.asp Hope that helps!
I downloaded the daily historical NAV for mutual funds is that correct to use instead of prices, will I be able to calculate the daily price change with NAV?
Hi, I have a question. For my thesis, i am comparing the investment results of a magazine with some indexes. These are weekly, but at some weeks, no magazine is published. Each year there are 46 magazines. Do I have to take the SQRT(46)or SQRT(52)? Thank you in advance!
If it is an investment you are talking about, you would need the percentage returns on that investment for given periods. If you had an investment which reported weekly % returns, then yes you would use SQRT(52). If there was no gain or loss during some periods, then the return for those periods would just be 0.00% but you would still include them in the ST.DEV calculation. Are you sure you are evaluating an investment? Remember the sharpe ratio is a metric for comparing the percentage returns of an investment against its volatility.
Hi, thanks for the video. What if I have just monthly returns and I do not have the daily prices (hedge fund performances) ? Can I calculate the Annualized Returns just summing the monthly returns and then divide it for the number of years? Thanks in advance
You would basically follow the same steps in the video except instead of daily returns, use the monthly returns. So for annualized volatility of the returns, take the standard deviation of all monthly returns observed and multiply by SQRT(12) instead of SQRT(252). For CAGR, all you need is the ending price of the investment, beginning price, and number of years. The formula for CAGR is ((last price/first price)^(1/years))-1 If you don't have ANY price data, only % returns, then you could use geometric average return to find CAGR. You could google it but here's an example: Year 1: +25% Year 2: -10% Year 3: +10% ( ( (Y1)(Y2)(Y3) )^(1/N) ) -1 ( ( (1.25)(0.90)(1.10) )^(1/3) ) -1 = 7.36% geometric average/CAGR If you only have monthly data, you'd probably have to use this formula for each 12 month period (month 1 to 12), and then again for each year (year 1 to N). If you have excel it should be pretty efficient. Does that answer your question?
First of all, thank you very much for the answer I will use this method. However, why I cannot just sum all the monthly returns and then divide the total for the number of years? Example: 1 month = 3% 2 month = 4 % Etc ( per 2 years) And then calculate the Annualized Returns doing (sum 24 months/2 years). If the answer is long, just put the link. Thanks in advance again.
@@lucacare4622 Summing all monthly returns and dividing by number of occurences would just result in a simple average. This would not consider period-to-period compounding. That is why you need to calculate the geometric return, it considers the compounding over multiple periods. Here is a good explanation: www.investopedia.com/ask/answers/06/geometricmean.asp
It would be best to use the average throughout the duration of which sharpe ratio is being calculated. 2% is a pretty good average/round number to use. Technically the risk free rate = treasury yield, but I like to view Rf as an alternative risk free investment and the most accessible for many people would probably be a savings account or GIC. This chart of the 10 year yield is a good reference point: www.macrotrends.net/2016/10-year-treasury-bond-rate-yield-chart
@@TactileTrade Thank you, just one more question, i saw these kinds of measurements along the way. Sharpe Ratio Treynor's Mesurment Sortino Ratio M2 Jensen's Alpha and want to ask if there is even more sophisticated approach to measure portfolio risk.
@@rolandm.9652 Great question. They are all good ways to measure portfolio or investment risk and have their benefits. I would say besides the sharpe ratio, the top 3 would be: Sortino ratio (like the sharpe, but only considers downside volatility), Maximum drawdown (worst loss ever incurred peak to trough & time it took to recover = VERY important to know) Jensen's Alpha, or just alpha (the extra return above and beyond the benchmark which can be attributed to the skill of the manager or strategy) Always remember to compare apples to apples for a fair comparison. Similar sector, industry, type of ETF, etc.
Hey great video, I was wondering in regards to asset volatility, was this referring to Std Dev? , if say an asset or a portfolio had a CAGR of 9% across 10 years,with a std dev of 6%. How would we interpret this in simple English to understand. I do understand that lower std dev means lower risk and volatility. However, in this case, does it means that for a CAGR of 9% across 10 years, the volatility of returns (lost/gains) were within 6% from CAGR?
Hey, sorry I just saw this and realized I hadn't yet replied. The Std Dev is just an average reading of how much the asset fluctuated up/down, on an annualized basis. Annualized volatility is not tied to the CAGR in that sense, where it would be within 6% of CAGR. An asset with 15% CAGR (9% + 6%) would likely have a higher annualized volatility than 6%, and therefore be considered riskier. I hope that answers your question
hi thank you for information. I used 3 years data. So while i'm calculating the CAGR, I used the ^(1/3) at formula. Did I use correct? I also saw usage of taking average of returns of stock except calculating CAGR. Is it a different version for calculating Sharpe?
Do i need to annualize volatility if i only want to consider the sharpe ratio for one year. In detail im taking the last and first date of price and have daily price changes
@@TactileTrade Thank you very much! I have two more questions: 1. How do I calculate with cryptocurrencies? Do I then take the square root of 365 because I have values for the whole year? My second question would then be: How can I calculate the Sharpe Ratio on a monthly basis? Do I then just have to calculate the standard deviations of the return of the months together or what would you recommend as a method?
@@albavalon1 For crypto, yes again. Since it is traded 24/7, there will be more observations therefore you should use SQRT(365). For calculating monthly sharpe ratio, you would take the same daily price changes from the given month and multiply by SQRT(12), and use that as the denominator for the monthly return. Remember to only subtract 1/12 of the annual risk free rate from it though! For example, 2% risk free rate *(1/12) = 0.16% monthly risk free rate. Keep in mind sharpe ratio readings for comparing investments are more valuable over longer periods, unless you are using this methodology for testing out trading indicators or something experimental like that :)
@@TactileTrade thank you again! I have thought about calculating with monthly returns and i want sharpe ratios for each month, but I do not quite understand whether I then have to calculate the standard deviation for all monthly returns together. But then comes out a value for the standard deviation that is the same for each month or not? So what would be the methodology if i dont wanna sqrt daily returns but if i want to get sharpe ratios for each month seperately
@@albavalon1 For monthly sharpe ratio in an individual month, you would want to calculate st.dev using only the approximately 21 trading days in the given month. You would still use those daily returns and convert them to monthly volatility using SQRT(12), but only for the days in the month in question.
To explain briefly, volatility is proportional to the square root of time, not time itself because day-to-day assets prices are assumed to be a "random walk". It's one of those things that is a bit tricky to wrap one's head around at first but this explains it pretty well: www.macroption.com/why-is-volatility-proportional-to-square-root-of-time/. Thank you for your support and I'm so glad you subscribed :)
To annualize the daily return of the portofolio or stock when i want to find the weights of max sharpe ratio, do i need to miltiply by 252;;;; Thank you bery much
I want to find the max Sharpe ratio. I have daily returns of 2.5 years. I annualise the volatility by multiply with square root 252, but i don’t understand how exactly i annualise the returns. Right now i just have the average daily return of every stock. Thank you very much for your help.
@@Saboman To find the annualized return, use the CAGR formula: ((final/initial)^(1/years))-1 using the asset's price. I'm not sure what you mean by maximum sharpe ratio. Sharpe ratio is not a range, it's one number
@@TactileTrade i understand what you mean. What i wanted was to find the portfolio weights that maximize the sharpe ratio. I had daily prices for 3 years data. I had found the returns with the formula: (Pt/Pt-1)-1 and i wasn’t sure how i will find the annual return of the data set. I was thinking just to take the average return of every stock but now will use the formula you mentioned in order to proceed. Thank you very much for the help. Your videos are amazing, very helpful.
I like to use the data that you use in your video, to troubleshoot my own spreadsheet. Using the same dates as you for the price data, my TSLA data is not the same as your data. Are your closing prices made up or is it actual historical data? For example, on 1/2/2015 you show a close of 42.862. Yahoo Finance shows a close of 14.62. Or am I somehow getting the wrong historical numbers? Thanks
Hmm.. Make sure there is no text in any of the cells, that the cells aren't formatted as text, and that you are calculating it on the % returns. Here is a good resource: corporatefinanceinstitute.com/resources/excel/functions/stdev-function/
@@TactileTrade thank you! Also if you don't mind me asking, I'm trying to do this with crypto, so I'd assume the risk free rate would be totally different? I've tried looking for approximate rates online but can't seem to find much.
@@IsmaeelMohammedally That's why I'm here :) The risk free rate should represent the return you could have received from parking your money in a risk-free investment which is usually a savings account or government bond. Because of that, the risk free rate shouldn't really change as a result of the investment you're evaluating - in your case, crypto. Doesn't matter if you're calculating the sharpe ratio for a stock, ETF, bitcoin, or a fund, a savings account is still a savings account. If that makes sense.
if we calculate the stock performance for three months but the three months sharpe ratio is calculated separately, how to calculate each sharpe ratio? because after I calculate the return for the standard deviation for each month, the result is that the standard deviation is too high. do we need to enter all the month's returns to calculate the standard deviation for each month?
@@dreadroid5671 I'm not sure I fully understand your question, what do you mean by calculating a three month sharpe ratio separately? If you only have 3 months of data, then you should still express it annually (although 3 months of data is not very much and won't be as meaningful in comparing it to other investments). The video and my answer above explained how to annualize the volatility, to annualize the return you could do the "quick and dirty" way of multiplying your total return for 3 months x 4. Does that help?
You'll never know how much this helped me. You have a new subcriber for life!!!
So glad this helped you out! Thank you for the comment and your support :)
Hey, thank you very much for the video. You answered some very technical questions I had. The level of detail that you went on this video was exactly what I needed and could not find on the web. Thanks!
I'm so glad I was able to help! Thank you for your support.
Perfect balance of the technical calculation and practical use and application. Not too little,... not too much,... not too long. Thanks for the content. Subscribed.
Thank you! I'm glad it was helpful
Great video, the only source I could find which gives good instruction on how to workout Sharpe in excel
Glad it was helpful, thanks for the comment!
Short & to the point. Great way to explaining. Keep it going.
Thank you! I'm glad you enjoyed it.
Thank you for sharing!
Glad it was helpful, thanks for the comment :)
Thank you for simplifying this for everyone.
Glad you liked it!
Great simple explanation. thank you
Glad it was helpful!
You made it easily understandable.
Thank you!
Thank you a lot! You saved me, just suscribed
Glad you found it helpful!
Thank you for this content!
This is going to help with my FP for my investing class.
Keep up the good work!
Glad you found it helpful!
Very helpful .. good job dude
Thank you!
Thank you from Brazil!
Glad you liked it! Thank you for the support.
Great video... however I have a sample, eg. 120 days... how would I adjust the formula to work with a period of time and see the Sharpe Ratio for that trade vs. annualised return.
Glad you liked the video! If the period is less than 1 year, just use the simple return of the period. You can still use the same formula for annualizing the volatility of the daily returns. Make sure whatever you're comparing it against is for the same period. Keep in mind sharpe ratio is more meaningful over longer timeframes (multiple years).
Thank you so much, you nailed it clearly and simply. Just still little confused why u used the standard deviation population not Sample ?
Nice demonstration.
I'm so glad you found it helpful. Thanks for your support!
Hi there, thank you for this valuable video, could you please do another video on ulcer performance index?
Noted! The ulcer index is a good one. Glad this one was helpful
Excellent !
Thank you!
Great video!
Thank you for your support! Glad you found it helpful.
@@TactileTrade Actually have a quick question if you don't mind. I took your approach and created my own Sharpe Ratio for a security in my current portfolio (EMR). I got a Sharpe Ratio of .53 using weekly returns from 7/18/16 - 7/12/21. I used a risk free rate of 1.4%, CAGR of 15.3%, Annual STDev of 26.4%. Assumed 50 trading weeks per year. However, I wanted to compare my calculations to another source and portfolioslab.com/symbol/EMR says EMR consistently has a Sharpe Ratio of over 2.0. Am I doing something wrong here or is this source not a good one to use? Thank you!!!
@@StockSpotlightPodcast A long term sharpe ratio above 2 is very rare. Had a look at the link, seems like this is a rolling 12 month sharpe ratio. That's probably why since EMR is up about 55% over the last year. That would be my guess
@@TactileTrade awesome, thank you! Appreciate the additional insight. Keep up the good work
VERY WELL DONE!!
Can u plz tell how to calculate annualized returns of mutual funds for each year using monthly average returns
If a stock pays dividends (in the case with SPY), is the CAGR (9.40%) already include the dividend yield (CAGR + Dividend Yield = True CAGR) or no?
It depends on your data. For example, there is the S&P 500 Index, and the S&P 500 Total Return Index, which is adjusted to assume all cash payouts to be automatically reinvested. In your case with the SPY, chances are your data does not include dividends. Perhaps you can find a SPY total return dataset but if not, you would probably have to look back on the historical dividends and manually add them on the dates they were paid, and then calculate CAGR.
What would be the difference between using daily returns or larger time frames like weekly or monthly returns when calculating the standard deviation? I assume the shorter the time frame the more accurate the volatility would be and the more accurate the Sharpe ratios would be?
Because everything is being annualized, the end results should be close to each other. For example, to answer your question I quickly checked the standard deviation of the daily and weekly price changes in GLD back to 2007. At first, they were different but once I annualized them, they ended up being 18.06% and 17.35% respectively. So short answer, yes you are right because weekly returns will be less granular, but the difference should be pretty minor in most cases, over the long term.
@@TactileTrade Awesome! Thanks for the response and great video explanation btw!
@@nicoqueijo Thanks for the support! :)
Hi, thank you very much for the video. What if the period of trading days that I have it is 280 (more than one calendar year), to calculate the annualised volatility do I still have to multiply for SQRT(252)?. Thanks in advance
Hey Eduardo, yes you would still multiply by SQRT(252). As long as you have daily returns, then you would use this multiplier, it doesn't matter how long the period is. Hope that helps!
i know im randomly asking but does any of you know a way to get back into an Instagram account..?
I was stupid lost the password. I would appreciate any assistance you can give me!
Hey, really cool vid. I wonder whether the natural log of the daily changes should be used for the volatility calculation? My thinking is that otherwise we’d be treating +1% and -1% moves as equal.
I'll try and answer to the best of my knowledge. My understanding of log vs. simple arithmetic returns is that arithmetic returns are asymmetric (a +50% return followed by -50% return on $100 would result in $75) whereas the same log returns would have no net change (still $100). Since simple arithmetic therefore reflects the compounding environment from one period to the next, it is more suitable for the volatility calc. My master spreadsheet I use to track my strategies daily uses 1-day arithmetic returns and is very accurate. Also - thanks for watching the video!!
Log returns are approximately equal to returns if x is small (|x| < 0.1, so ±10%) ---> log(1+x) ≈ x if x->0
Anyway, I'd use log returns (but the results are still accurate with the erithmetic calculation) 🙂
I am using the Sharpe to evaluate a hedge fund after a long time. What I really want to ask is shouldn't the Vol of the portfolio (Tesla) be calculated on the Excess returns (i.e., over the risk free rate)? And given the current backdrop where the fund started in 2014, would the risk free rate be the current 90-day Treasury rate or an average of the entire period? I'd appreciate your response.
You're exactly right about using excess returns, which the formula achieves by subtracting Rf from Ri. In terms of what to use for Rf, 2% is a safe bet although you could work out an average if you want to get very particular. You could reference this for determining an average Rf: www.macrotrends.net/2492/1-year-treasury-rate-yield-chart
@@TactileTrade Thanks again. So your annualized volatility in the video is not based on excess returns, its just daily returns. Correct me if I'm wrong please.
@@djvicks21 That's correct, annualized volatility is calculated using the straight up, daily returns. No need to subtract Rf on a daily basis (or in the volatility portion of the formula at all)
@@TactileTrade got it, Thanks! It's surprising but many hedge funds and indices in the industry use excess returns for their volatility calculation (the denominator) in Sharpe. Which is why the question - I just wanted to be sure I am right after all these years...
I do not have STDEV.P() function in my MSExcel. I have STDEVP(), which is not same as STDEV.P(). How can I calculate annualized standard Deviation (Volatility) as mentioned in this video. Please suggest.
Thank so much for the video. I wanted to ask you how would you calculate the Sharpe Ratio for the whole portfolio assuming you have 0.6 in Tesla and 0.4 in SPY. The Sharpe ratio for the whole portfolio instead for individual assets?l Do you need to multiply the CAGR and anualised volatility by the weights? and sum all the individual sharpe ratios?
Hello, Can you use sharpe ratio to evaluate portfilo performance as a total, or it is used to evaluate individual stocks performance only?
You could use it for both, potentially as a trading indicator or deciding between two investments. Get creative!
Can you please give short info on how to calculate it for the whole portfolio? Thank you
@@anacokovic You would just use the returns of your total portfolio, which should be a weighted average of each holdings. Can't really explain anymore without more details or context
Hi, thanks for the video! I was just wondering whether it will be possible to calculate a timeframe of 81 days using this method? Perhaps like this ((B57/B2)^(365/81))-1?
thanks, better than 1 is good. few etfs have that
great. thanks friend 😘
Glad you liked it!
Awesome man, just subscribed now. Btw, is it possible to download your excel sheet :) ?
Glad you found it useful and thanks for the sub :) unfortunately, I made this awhile ago and I no longer have the sample sheet I built in the video. Sorry about that!
Hi sir, this Sharpe ratio can apply for returns..
Here you select daily prices variation , can I put returns instead of prices
Since you're using a daily sampling frequency, shouldn't we be using the annualized expected daily return for the numerator instead of the annualized 5 year return, or are there just different flavors of the Sharpe Ratio calculation?
It's always better to use actual returns where possible, and since the sharpe ratio's purpose is primarily to compare two investments, as long as it is an apples-to-apples comparison it will be fine.
canyou contrast with CAPE (Cyclically Adjusted Price Earnings)
how will you calculate the risk free return and what does that do with inflation rate?
Risk free rate represents what you would receive from a risk free investment. For most people, that is just a savings account, or maybe a government bond. Inflation isn't an input in the sharpe ratio formula.
@@TactileTrade Thank you so much bro.I got a clear idea of what it is.
I know about the rule of thumb that a Sharpe ratio larger than 1 is considered good. But I have always wondered how could an investment or strategy generate such high of a Sharpe ratio? SPY is very much the market portolio, i.e. the mean-variance efficient portfolio. If SPY historically yields a Sharpe ratio between 0.4 to 0.6, how could any strategy beat it by that much?
Very good point. They do exist although they are few and far between. Even the ones that do, maintaining it isn't easy. Based on my experience and knowledge the only legitimate two I could attest to that are accessible (i.e. not Jim Simon's Medallion Fund or other private funds) would be InvestInVol or Volatility Trading Strategies.
There are lots of things that would have still been a "good investment" historically like Tesla, bitcoin, etc but measured by unit of risk per the sharpe ratio, not so much.
Maybe the > 1 rule of thumb is too picky, or perhaps it just goes to show how hard trading really is!
Hello Eli. I am using Thinkorswim for trading. I trade options on Index(SPX). I want to calculate the Sharpe Ratio of my trades. Can you please guide me on that?
You should be able to generate a report from ToS with your money-weighted rate of return for a given period. You'll then just need to find a way to pull the monthly or daily returns during that period to calculate the volatility denominator.
I use Interactive Brokers personally and it can generate a report that includes the sharpe ratio. I'm sure ToS can too.
What if the data is from Nov 2017 until Nov 2018? How to calculate the CAGR?
CAGR is used to annualize returns over multiple years. If you only have one year of data, you can just use the simple return which is (last price-first price)/first price. Hope that helps!
Hello and thank you for the video.
I have a question. Suppose I want to rebalance my portfolio every month and add or replace new stocks, how far back should the history go here?
1 year? 6 months?
When calculating the sharpe ratio, the longer the time period, the more meaningful. For example, the sharpe ratio for the live trading history of my strategy only, which is a period of about 8 months, is ridiculously high at like ~2.8 (but so is my benchmark) simply because neither have been tested by a bear market. But if I calculate the sharpe ratio for my entire 13 year backtest, and my benchmark for the same period, it's much more realistic, closer to ~1.3 and < 1 for the benchmark.
I would say for a meaningful reading, go back at least a year. But the longer the better. Just make sure you're using the same time period for both investments, so that you are comparing apples to apples.
About rebalancing, just make sure the daily or monthly P&L % includes whatever investments were held at their correct weights. For example, if your portfolio held 4 stocks at 25% weightings each, make sure each stock gets a 25% weighting in the overall P&L % that you use to calculate ST.DEV on the whole portfolio. Whether you simply take each stock return and *0.25, or create an elaborate, precise portfolio tracker that tallies up all dollar amounts, it shouldn't matter too much.
As long as you end up with that % array to use in the ST.DEV formula. Hope that helps!
@@TactileTrade Thanks :-)
If I am solving for year to date sharpe ratio, do I still annualize the daily price change?
Yes, still annualize it. The daily returns still need to be converted.
Remember to adjust the Rf accordingly, for example with a 2% annual Rf, half a year = (6/12)*2% = only 1%.
Hey. I am doing the same but for a 10 year period. So the only change I need to make is while calculating CAGR right? How about St Deviation? Do we still mutiply it by the square root of 252 regardless of the number of years we are taking?
That's exactly right. Calculate CAGR for the 10 year period, and as long as your data is daily returns, then you still multiply by SQRT(252). You'll just have a larger dataset which is always good.
@@TactileTrade Cool. Thank you.
Hello Eli. Thanks for the content. I would like to backtest the Options strategy and its Sharpe Ratio along with Profit Factor. Is it possible to accomplish the task in Excel?
Backtesting linear instruments like stocks, ETFs and getting lots of good data from it is totally doable. Backtesting option strategies is a different story. The historical data is vast because all the various contracts (strike, expirations) need to be compiled and queried. I don't think it is realistic to use Excel alone.
Hi thanks for the video, just wondering if any adjustments required to calculate treynor and jensen's alpha using the CAGR returns?
As far as I know, you would just use the same CAGR for those formulas as well. You could also use total return if the period is less than 1 year, as long as you also use total return for the risk free rate. I hope that answers your question!
Great info!
Thanks :)
Hello Sir, thank you so much for this video. What if the calculation period is 3 years (1st April 2018 to 31st March 2021) to calculate annualized volatility for one of the mutual funds?
The time period doesn't matter, as long as you multiply by SQRT(252) if the data is daily, or SQRT(12) if monthly. It could be 1 year, 5 or 10. You just use the same ST.DEV formula. Of course, the bigger the sample the better.
What about CAGR?
If the data time line (332) year and 5 months
How supposed be the CAGR?
Thank you very much
The stock price is daily
When we take the return, why do we take the geometrical average? rather than arthmetic average (annualized) from the daily returns?
The annualized return can be calculated several ways as long as it is annualized. If you have prices, you can calculate CAGR using the beginning, ending prices and number of periods. If you only have % returns (no prices), then you could use the geometric average.
In general, geometric average accurately reflects the compounding whereas simple/arithmetic does not. That is why geometric average should always be used when calculating returns over multiple periods.
@@TactileTrade thanks for replying back!
New subscriber to your channel. Appreciate you posting these videos! How would I calculate sharpe ratio for a portfolio with regular contributions throughout the year? If I use the method outlined in the video the regular contributions make the CAGR seem artificially high. Thanks!
Thank you so much for your support and welcome aboard :) You'll want to find out the time-weighted rate of return (TWR). TWR will give you total return for a period without the compounding of deposits/withdrawls. If the period is longer than a year, you'll then need to annualize it using CAGR. Probably easiest to use an online TWR calculator like this one: www.rateofreturnexpert.com/time-weighted-return-calculator/
More about TWR: www.investopedia.com/terms/t/time-weightedror.asp
Hope that helps!
@@TactileTrade wow thanks for the quick reply and providing the links to help answer my question. Looking forward to future videos 👍
I downloaded the daily historical NAV for mutual funds is that correct to use instead of prices, will I be able to calculate the daily price change with NAV?
Absolutely, the change from one daily NAV to the next would be treated the same as price. Sorry for the late response, thank you for the comment!
@@TactileTrade No worries Thank you a lot
Hi, I have a question. For my thesis, i am comparing the investment results of a magazine with some indexes. These are weekly, but at some weeks, no magazine is published. Each year there are 46 magazines. Do I have to take the SQRT(46)or SQRT(52)? Thank you in advance!
If it is an investment you are talking about, you would need the percentage returns on that investment for given periods. If you had an investment which reported weekly % returns, then yes you would use SQRT(52). If there was no gain or loss during some periods, then the return for those periods would just be 0.00% but you would still include them in the ST.DEV calculation. Are you sure you are evaluating an investment? Remember the sharpe ratio is a metric for comparing the percentage returns of an investment against its volatility.
Hi, thanks for the video.
What if I have just monthly returns and I do not have the daily prices (hedge fund performances) ? Can I calculate the Annualized Returns just summing the monthly returns and then divide it for the number of years?
Thanks in advance
In case I cannot do it, how can I solve the problem?
You would basically follow the same steps in the video except instead of daily returns, use the monthly returns. So for annualized volatility of the returns, take the standard deviation of all monthly returns observed and multiply by SQRT(12) instead of SQRT(252). For CAGR, all you need is the ending price of the investment, beginning price, and number of years. The formula for CAGR is ((last price/first price)^(1/years))-1
If you don't have ANY price data, only % returns, then you could use geometric average return to find CAGR. You could google it but here's an example:
Year 1: +25%
Year 2: -10%
Year 3: +10%
( ( (Y1)(Y2)(Y3) )^(1/N) ) -1
( ( (1.25)(0.90)(1.10) )^(1/3) ) -1
= 7.36% geometric average/CAGR
If you only have monthly data, you'd probably have to use this formula for each 12 month period (month 1 to 12), and then again for each year (year 1 to N). If you have excel it should be pretty efficient.
Does that answer your question?
First of all, thank you very much for the answer I will use this method.
However, why I cannot just sum all the monthly returns and then divide the total for the number of years?
Example:
1 month = 3%
2 month = 4 %
Etc ( per 2 years)
And then calculate the Annualized Returns doing (sum 24 months/2 years).
If the answer is long, just put the link.
Thanks in advance again.
@@lucacare4622 Summing all monthly returns and dividing by number of occurences would just result in a simple average.
This would not consider period-to-period compounding. That is why you need to calculate the geometric return, it considers the compounding over multiple periods. Here is a good explanation: www.investopedia.com/ask/answers/06/geometricmean.asp
Hello the risk free rate should be average or current?
It would be best to use the average throughout the duration of which sharpe ratio is being calculated. 2% is a pretty good average/round number to use. Technically the risk free rate = treasury yield, but I like to view Rf as an alternative risk free investment and the most accessible for many people would probably be a savings account or GIC. This chart of the 10 year yield is a good reference point: www.macrotrends.net/2016/10-year-treasury-bond-rate-yield-chart
@@TactileTrade Thank you, just one more question, i saw these kinds of measurements along the way.
Sharpe Ratio
Treynor's Mesurment
Sortino Ratio
M2
Jensen's Alpha
and want to ask if there is even more sophisticated approach to measure portfolio risk.
@@rolandm.9652 Great question. They are all good ways to measure portfolio or investment risk and have their benefits. I would say besides the sharpe ratio, the top 3 would be:
Sortino ratio (like the sharpe, but only considers downside volatility),
Maximum drawdown (worst loss ever incurred peak to trough & time it took to recover = VERY important to know)
Jensen's Alpha, or just alpha (the extra return above and beyond the benchmark which can be attributed to the skill of the manager or strategy)
Always remember to compare apples to apples for a fair comparison. Similar sector, industry, type of ETF, etc.
Thanks
Glad it was helpful!
Hey great video, I was wondering in regards to asset volatility, was this referring to Std Dev? , if say an asset or a portfolio had a CAGR of 9% across 10 years,with a std dev of 6%. How would we interpret this in simple English to understand. I do understand that lower std dev means lower risk and volatility. However, in this case, does it means that for a CAGR of 9% across 10 years, the volatility of returns (lost/gains) were within 6% from CAGR?
Hey, sorry I just saw this and realized I hadn't yet replied. The Std Dev is just an average reading of how much the asset fluctuated up/down, on an annualized basis. Annualized volatility is not tied to the CAGR in that sense, where it would be within 6% of CAGR. An asset with 15% CAGR (9% + 6%) would likely have a higher annualized volatility than 6%, and therefore be considered riskier. I hope that answers your question
Hi is the CAGR a must? Cant we use total return of (2020-2015)/2015?
Using total return wouldn't work because the figure would be too large in comparison to the annualized volatility.
hi thank you for information. I used 3 years data. So while i'm calculating the CAGR, I used the ^(1/3) at formula. Did I use correct? I also saw usage of taking average of returns of stock except calculating CAGR. Is it a different version for calculating Sharpe?
Thanks for the comment! using ^(1/3) sounds correct to me. CAGR should be used for this formula
Hello from where can I find the price range data
Yahoo Finance is a good free source for ETFs and stocks. eoddata.com/ is another one I've used to buy volatility ETN data that isn't as easy to find.
Do i need to annualize volatility if i only want to consider the sharpe ratio for one year. In detail im taking the last and first date of price and have daily price changes
Yes, you’ll still want to multiply the daily volatility (st dev) by SQRT(252) because that converts it from daily to annual.
@@TactileTrade Thank you very much! I have two more questions: 1. How do I calculate with cryptocurrencies? Do I then take the square root of 365 because I have values for the whole year? My second question would then be: How can I calculate the Sharpe Ratio on a monthly basis? Do I then just have to calculate the standard deviations of the return of the months together or what would you recommend as a method?
@@albavalon1 For crypto, yes again. Since it is traded 24/7, there will be more observations therefore you should use SQRT(365). For calculating monthly sharpe ratio, you would take the same daily price changes from the given month and multiply by SQRT(12), and use that as the denominator for the monthly return. Remember to only subtract 1/12 of the annual risk free rate from it though! For example, 2% risk free rate *(1/12) = 0.16% monthly risk free rate. Keep in mind sharpe ratio readings for comparing investments are more valuable over longer periods, unless you are using this methodology for testing out trading indicators or something experimental like that :)
@@TactileTrade thank you again! I have thought about calculating with monthly returns and i want sharpe ratios for each month, but I do not quite understand whether I then have to calculate the standard deviation for all monthly returns together. But then comes out a value for the standard deviation that is the same for each month or not? So what would be the methodology if i dont wanna sqrt daily returns but if i want to get sharpe ratios for each month seperately
@@albavalon1 For monthly sharpe ratio in an individual month, you would want to calculate st.dev using only the approximately 21 trading days in the given month. You would still use those daily returns and convert them to monthly volatility using SQRT(12), but only for the days in the month in question.
I don't get why is Square Root, could you explain me plss?? Thank you for the video I subscribed to your channel!!
To explain briefly, volatility is proportional to the square root of time, not time itself because day-to-day assets prices are assumed to be a "random walk". It's one of those things that is a bit tricky to wrap one's head around at first but this explains it pretty well: www.macroption.com/why-is-volatility-proportional-to-square-root-of-time/. Thank you for your support and I'm so glad you subscribed :)
What is annual risk free return?
A good assumption I use is 2%, or what you could expect from a high interest savings account. The wide-accepted Rf is US Treasury yield
@@TactileTrade yes but unfortunately Metatrader 5 (MetaQuotes software) has a different formula, I think without annual free risk 🤷♂️
To annualize the daily return of the portofolio or stock when i want to find the weights of max sharpe ratio, do i need to miltiply by 252;;;;
Thank you bery much
What exactly do you mean by the weights of max sharpe ratio? You'll need to annualize the volatility if the returns aren't in annual terms.
I want to find the max Sharpe ratio. I have daily returns of 2.5 years. I annualise the volatility by multiply with square root 252, but i don’t understand how exactly i annualise the returns. Right now i just have the average daily return of every stock.
Thank you very much for your help.
@@Saboman To find the annualized return, use the CAGR formula: ((final/initial)^(1/years))-1 using the asset's price. I'm not sure what you mean by maximum sharpe ratio. Sharpe ratio is not a range, it's one number
@@TactileTrade i understand what you mean. What i wanted was to find the portfolio weights that maximize the sharpe ratio. I had daily prices for 3 years data. I had found the returns with the formula: (Pt/Pt-1)-1 and i wasn’t sure how i will find the annual return of the data set. I was thinking just to take the average return of every stock but now will use the formula you mentioned in order to proceed.
Thank you very much for the help. Your videos are amazing, very helpful.
@@Saboman Glad it was helpful!
Hello ... I hv a query..may I ask
I like to use the data that you use in your video, to troubleshoot my own spreadsheet. Using the same dates as you for the price data, my TSLA data is not the same as your data. Are your closing prices made up or is it actual historical data? For example, on 1/2/2015 you show a close of 42.862. Yahoo Finance shows a close of 14.62. Or am I somehow getting the wrong historical numbers? Thanks
TSLA has undergone a 3 for 1 split since this video was made, so you’re probably using split-adjusted prices. The daily % changes should be the same
Subscribed 💸💸💸
Happy to have you onboard💪
When performing the annualized volatility error calculation I keep getting a #VALUE! error. Any idea as to why?
Hmm.. Make sure there is no text in any of the cells, that the cells aren't formatted as text, and that you are calculating it on the % returns. Here is a good resource: corporatefinanceinstitute.com/resources/excel/functions/stdev-function/
@@TactileTrade thank you! Also if you don't mind me asking, I'm trying to do this with crypto, so I'd assume the risk free rate would be totally different? I've tried looking for approximate rates online but can't seem to find much.
@@IsmaeelMohammedally That's why I'm here :) The risk free rate should represent the return you could have received from parking your money in a risk-free investment which is usually a savings account or government bond. Because of that, the risk free rate shouldn't really change as a result of the investment you're evaluating - in your case, crypto. Doesn't matter if you're calculating the sharpe ratio for a stock, ETF, bitcoin, or a fund, a savings account is still a savings account. If that makes sense.
@@TactileTrade That clarifies things so much! Thank you again for all the help, wishing you all the best :)
@@IsmaeelMohammedally Happy to help!
if the data is monthly , are we still use 252?
If the % returns are monthly, then you would use SQRT(12)
@@TactileTrade ok thank you sir
if we calculate the stock performance for three months but the three months sharpe ratio is calculated separately, how to calculate each sharpe ratio? because after I calculate the return for the standard deviation for each month, the result is that the standard deviation is too high. do we need to enter all the month's returns to calculate the standard deviation for each month?
@@dreadroid5671 I'm not sure I fully understand your question, what do you mean by calculating a three month sharpe ratio separately? If you only have 3 months of data, then you should still express it annually (although 3 months of data is not very much and won't be as meaningful in comparing it to other investments). The video and my answer above explained how to annualize the volatility, to annualize the return you could do the "quick and dirty" way of multiplying your total return for 3 months x 4. Does that help?
@@TactileTrade ok thanks for the answer sir
I love Excel scribble. No one can see anything.
Great video!
Glad you found it helpful!