Risk-adjusted performance evaluation: Sharpe, Treynor, and Jensen's Alpha (Excel) (SUB)

Does simply achieving higher returns make one a better investor?
When humans are risk-averse, that is not necessarily the case. Today we are discussing some of the simplest and widely known risk-adjusted return measures such as Sharpe ratio, Treynor ratio, and Jensen's alpha and how they can be used to compare the performance of portfolios with varying levels of risk.
Don't forget to subscribe to NEDL and give this video a thumbs up for more videos in Finance!
Please consider supporting NEDL on Patreon: / nedleducation

[@NEDLeducation]

You can find the spreadsheets for this video and some additional materials here: drive.google.com/drive/folders/1sP40IW0p0w5IETCgo464uhDFfdyR6rh7 Please consider supporting NEDL on Patreon: www.patreon.com/NEDLeducation

[@aditiware9472]

I have watched many videos related to calculating Sharpe ratios, this is one of the best video till now. He is actually showing how to calculate the standard deviation. Thank you so much.

[@kefeizhang8214]

Please keep on doing more videos!! They’re really helpful!

[@NEDLeducation]

Hi Kefei, thank you very much for your feedback! We will upload more videos soon, so stay tuned :)

[@BasiqLi]

Damn ! Who are those 2 bitter trolls who disliked this video ? Like always, you rocks Savva ;)

[@jpf1950]

Thank you @NEDL. I found your explanations to be very helpful.

[@schewiloffel3230]

Great Video! really helped me writing my bachelor thesis!

[@kamillamirodilova8626]

Thank you so much for doing these videos, they help a lot!

[@piyushkakkar4570]

thanks dude !! great video, looking forward for more of it!

[@NEDLeducation]

Hi Piyush, thank you for your feedback! :)

[@becalmrelaxandsleepwell8798]

This is the best video I have seen on this subject, breaks it down with very logical explanations. Thank you

[@sunitamalik5112]

Super helpful video for my CFA exam, thankyou!

[@sad-veg]

Thanks mate! This video has been really helpful for my project :))))

[@NEDLeducation]

Hi, glad the video helped! :)

[@badhombre7247]

Dude you are a beast, thank you so much.

[@likithcr8492]

Thanks a lot brother you made my life easy

[@NEDLeducation]

Hi Likith and glad the video helped :) Stay around for more videos on investment management in the future!

[@AI-ph3vj]

amazing video !

[@josenogueira3083]

I like very much your vdo. Many thanks.

[@olivetryphene8632]

thank you so much that got me a lot you are wonderful

[@NEDLeducation]

Hi Tryphene, and really glad the video helped!

[@neoramodike8161]

Hi, thanks for the great video. Just to make sure my calculations are correct, would the annualised return over 3 years be calculated as = PRODUCT(1 + all the yields)^(1/3) - 1? Moreover, to annualise the risk would i multiply the array by the Sqrt(12) or Sqrt(3)?

[@Diegoblismartmedpengar]

It was clearly described thank you@NEDL. I was also wondering why you didn’t use geometrical mean for risk free return? And why you didn’t use excess return to compute annualized return? And lastly why you didn’t use =geomean instead of =product …? Haha many questions I know, appreciate your time for these questions.

[@NEDLeducation]

Hi Diego, and glad you enjoyed the video! As for your questions, the risk-free rate is small so in practice it is generally subtracted like this, and the PRODUCT and GEOMEAN approaches are equivalent, so you could use either!

[@Diegoblismartmedpengar]

Great video and very easy explained! Thank you for these videos they are very helpful. I was thinking if it is still possible to attain Jensens alpha or the tracking error by only using annum return for both the mutual fund and the s&p500 or does it have to be monthly data? And if the latter is the case do you perhaps have a hint of where I can find monthly data for the returns or price of the mutual fund and S&P 500 I cannot seem to find it on yahoo finance or at Morningstar. Thank you for you answer!

[@NEDLeducation]

Hi Diego, and glad you are enjoying the channel. As for your question, theoretically, there is nothing wrong with using annual data for tracking if you are interested in extremely long-term performance (say, 100 years). However, for most reasonable applications you would need a more high-frequency series (either daily or monthly). You can use tracker funds to easily download data for S&P 500 from Yahoo Finance, say SPDR: finance.yahoo.com/quote/SPY/history?p=SPY. As for funds, Yahoo has not got many of these, but the biggest exchange-traded funds would be available. Hope this helps!

[@lalmajed2013]

Hi, from where did you get the numbers in column E ? thank you

[@littlebigfis]

thanks for the great video. Why use the S&P benchmark for the bond portfolio? Why not use a bond benchmark?

[@raydespoir]

Thanks for the video, it was great! Two questions though. 1. For the annualized risk-free rate, you used the annual bond yield for the first period of 4.02%. Alternatively, one could also use the current risk-free rate for the bond correct? Or what about if I wanted to compare how well the investment did compared to the maximum that the risk-free rate ever was? Are there any downsides/risks with these two approaches? 2. In column E, you have the U.S. 10-year bond yield. However, your data is returning the data on a monthly frequency, whereas I have it on a daily frequency. If I want to take the average of Column E and use that as my value for the annual risk-free rate, do I need to do anything to the formula? If I wanted to use the product 1+ function, would I use the following formula: =product(1+annual return)^(1/n), would n be the number of years of data I have or the number or data points since I have a daily frequency? Thanks again for your help!

[@davids2647]

my detailed case: i have monthly rates (2000-2020 -> 20 years) and monthly risk free rates (2000- 2020 -> 20 years), already adjusted. can I use the average rate and divide it by the std deviation like this: AVERAGE(A4:A232) / STDEV(A4:A232) ? is the standard deviation monthly too with that formula? do I have to calculate the annualized return and then the monthly return instead of the average?

[@NEDLeducation]

Hi David, that seems correct, you will get a monthly Sharpe ratio by using this procedure. To get an annualised ratio, you can just multiply the numerator by 12 and the denominator by sqrt(12). Obviously, a more precise calculation would involve the return geometric mean instead of an arithmetic mean, but your formula would give a good approximation as well.

[@S4tanMW3]

Great video! I have a question: I have annualized (monthly) returns over a period of 5 years for index funds. Say the annualized return is 10%, the risk free is 1,2%, and the standad deviation is 4%. That gives a sharpe ratio of 2,2. Does this make sense? I'm getting some crazy sharpe ratios for the funds I'm analyzing. Perhaps the risk free is wrong or something? The standard deviation is so low because (presumably) index funds have low risk.

[@NEDLeducation]

Hi, and glad you liked the video! As for your question, it seems you have got monthly standard deviation. To annualise, you can multiply it by sqrt(12). Hope this helps!

[@davids2647]

great video, thank you! i have the same rates as you but for 20 years on a monthly basis. how can i get the annualised riskfree rate? you just take the first yield of the gov bond, but how can i calculate that?

[@NEDLeducation]

Hi David and many thanks for your feedback! If you have got monthly yields, you can annualise those by using the usual scaling formula annual yield = (1 + monthly yield)^12 - 1. If you want to simulate a 20-year investment you can just use your first risk-free rate (as these are yields to maturity of government bonds at particular points in time), or you can average the risk-free rate over the period. As you have 20 years, you can use PRODUCT(1 + all the yields)^(1/20) - 1. Hope it helps!

[@davids2647]

thank you! is it possible to write a message to you? i made a excel sheet and it would be so nice if someone can look over it. i made it like this: average of (monthly returns - monthly risk free rate), excel formula for standard deviation with my monthly returns and then calculated alpha

[@user-xb1hn5ox6f]

Hi Thanks for video! Please, clarify: what option is better? the 1st one or the 2nd? Sharp ratio is more for option 1, Treynor and Alpha are more for option 2.

[@GM-on3fb]

@nedl if you have daily rates, can you change it to annual by raising to the power of 252(as in there’s 252 trading days in a year)?

[@NEDLeducation]

Hi and many thanks for the question! Yes, you can apply the formula annual return = (1 + daily return)^252 - 1. Hope it helps.

[@supritinanda8732]

I have a question, first of all Thank You for your video. Very Helpful. I know this is a video of over one year ago, but to calculate Sharpe Ratio you used the Standard deviation of returns of your portfolio. Shouldn't be used the Standard Deviation of excess returns of the Portfolio? Another fast question, we can calculate the Sharpe ratio monthly, then to annualize it just multiply with "square root of 12" ? would be the same, right?

[@NEDLeducation]

Hi Supriti, and glad you enjoyed the video! As for your questions: 1) Yes, you can estimate annual Sharpe by multiplying the monthly Sharpe by root 12. It would however scale the return in the numerator linearly rather than geometrically, but it would be a good approximation. 2) The impact of the risk-free rate on the volatility of excess returns in relation to raw returns is very small so in practice the volatility of raw return rather than excess return is almost always put in the denominator. However, you are correct that standard deviation of excess returns would be arguably more natural, and the formula "outperformance divided by volatility of outperformance" is what ultimately gave birth to the information ratio :) Hope this helps!

[@supritinanda8732]

@@NEDLeducation Thank You very much. Very clear. I really appreciated.

[@RishabhRishab]

@@supritinanda8732 Hi..can you tell me why has he taken only first value (4.02) in risk free rate and then used it throughout. Since all values are given for it , why not take average instead of only first value ?

[@vd4079]

When do I use log returns and when do I use simple returns for the calculation?

[@gozde6086]

hi, thank you for your explanation. I have two question. What formula converts the monthly risk free rate to the daily rate? And, Is beta calculated on Treynor ratio, index return or excess return?

[@NEDLeducation]

Hi, and thanks for the question! You can convert monthly risk-free rate into daily risk-free rate using the formula (1+monthly)^(1/21) - 1. Beta is calculated by taking the slope of excess returns onto excess market (index) returns.

[@gozde6086]

@@NEDLeducation Thank you so much for your explanation

[@RishabhRishab]

Is there are similar video where daily returns are given for say 5 years and not monthly ? If no, then pls tell the changes that will be there in columns

[@agrupgarrafa]

Why do you use sample instead of population standard deviation?

[@NEDLeducation]

Hi Mario, and thanks for the question! First, intuitively, in the video I have selected not all stock returns that have ever existed, but just a fraction of these, so a sample formula applies logically. Second, mathematically, the unbiased estimator of a variance of a random variable, which stock returns are considered to be most of the time in finance, has the sum of squared deviations divided by n-1 instead of n. To understand it using a simple example, imagine I try to estimate the variance of a stock return that is equal to 1 and -1 with 50/50 chance during a day, from two days of sample data. The true mean is 0.5*(1) + 0.5*(-1) = 0, and the true variance is 0.5*(0 - 1)^2 + 0.5*(0.5 - (-1))^2 = 0.5 + 0.5 = 1. Then, consider all possible two-day samples we can obtain from such a random variable. There are four possibilities, each occurring with probability 0.5^2 = 0.25: (1, 1), (1, -1), (-1; 1) and (-1, -1). If we estimate the variance using the population formula in each of the four cases, we will get 0 for instances 1 and 4, and 0.5 in instances 2 and 3, averaging it out to 0.25*0 + 0.25*1 + 0.25*1 + 0.25*0 = 0.5 < 1, which is by definition a biased estimator of variance (on average, it produces a result that deviates from a "true" value). If, on the other hand, we apply the sample correction (dividing by n-1 = 2-1 = 1 instead of n = 2), we will get variances 0, 2, 2, and 0 in each of the four cases, which will be on average correct, converging to the "true value": 0.25*0 + 0.25*2 + 0.25*2 + 0.25*0 = 1. The logic generalises for continuous random variables. The magnitude of this bias is smaller and smaller for large sample sizes (as instances where you get very low variance because random variable realisations coincide with each other are less and less often), but is still mathematically present. Another intuitive way of putting it verbally would be that when you estimate the variance of a random variable, there are fewer ways of overestimating than underestimating it, therefore the average result of the population formula is biased downward. There is nothing terrible in using the population formula when your sample size is large, but if it is small, the difference is material. Hope it helps!

[@rip_zoroXd695]

Hi sir, how come after key in {=PRODUCT(1+G3:G146)^(1/12)-1} for "Annualised return" the result show #VALUE! ?

[@NEDLeducation]

Hi, and thanks for the question! This might be due to some versions of Excel requiring matrix formulae to be enforced with Shift+Ctrl+Enter rather than simply Enter. {} brackets in the formula also hint towards this as this is what Excel does when processing a matrix formula.

[@glensanthmayor3928]

hello there, so I did this with an equity index(NIFTY 100 ESG) of India and used Indias 10 year government bond yields as risk free rate. Annualised Return -0.17% Annualised Risk (s.d.) 0.25 Annualised risk free 7.55% Sharpe Ratio -0.31 This was my result. How do I interpret this? And does this look normal? does it look right?

[@julianmuschal107]

How can i get the annual risk free rate for a period with monthly risk free rates? Thanks in advance. I´m using the monthly data from kenneth/french data website. Thanks in advance.

[@NEDLeducation]

Hi Julian! You can also get annual risk-free rates from Kenneth French website. If you want to annualise the monthly risk free rate, just apply the usual formula (1+monthly rate)^12 - 1. Make sure your rate is not in % (French reports it that way), so for example 0.12 would be 0.12%, i.e. 0.0012. Then, the annualised rate would be 1.0012^12 - 1 = 0.0145 = 1.45%. Hope it helps.

[@julianmuschal107]

@@NEDLeducation thank you for your help! Really appreciate it.

[@julianmuschal107]

@@NEDLeducation another question: is the Jensen Alpha on monthly basis? Can i annualize it with the factor 12? Thanks in advance.

[@NEDLeducation]

@@julianmuschal107 Hi Julian! Alpha is calculated on an annual basis in the video but you could calculate and report it for any frequency potentially if need be. You can just scale it linearly as you suggest, but more accurate results would be achieved if you scale the initial returns (of the portfolio and for the risk-free asset) using continuously compounded rates, while beta would stay the same. So for example, if you wanted to calculate monthly alpha given annual alpha, you could do (monthly return - monthly risk free) - beta*(monthly benchmark return - monthly risk free). Hope it helps.

[@julianmuschal107]

@@NEDLeducation thank you for your helpful reply. Really appreciate it!

[@duartepombo551]

It really does not make sense for me the way the sharpe ratio of the bond portfolio is computed. Im not sure if im correct or not, but imagine the situation in which that bond portfolio is a portfolio 100% invested in the risk free rate. The sharpe ratio would obviously be equal to 0, because we would be doing (Rp-Rf) =0,divided by stdev(Rp). The stdev(Rp) would not show as 0, because the yield of the risk free is not a constant and therefore has a standard deviation different than 0. Hence Sharpe ratio =0/(Something >0) =0. But supposedly there isnt a sharpe ratio for the risk free. Because the result would be 0/0. So which one is the correct measure even? Imagine that the bond portfolio was invested 95% in the risk free, well the volatility observed could mainly be from the volatility observed in the risk free (which we should've assumed to be 0). So, shouldn't we instead of assuming a constant risk free, compute the Sharpe ratio as E[Rp-Rf] /sigma[Rp-Rf] or something??

[@clivebruce7334]

Since you had a change Risk-free rate why did you not get the average and annualise it, assuming it will change year on year

[@NEDLeducation]

Hi Clive and many thanks for the question! As the risk-free rate is the yield to maturity of a government bond, it is meaningful for portfolio evaluation to compare the return of a risky investment with an available risk-free rate for the investment period in question. If someone were to invest at the start of the sample period they would then have the starting period risk-free rate available, hence it is the starting risk-free being subtracted to get the excess. However, for regressions in asset-pricing you generally do consider period-by-period excess returns, so your approach is also valid. Hope it helps!

[@clivebruce7334]

@@NEDLeducation thanks man makes sense! Yes for my finance thesis we averaged out the risk-free rate as it was 5 year monthly data, i just realised your data is already annual so makes sense for a starting point to be fixed here.. thanks bro, video is pretty cool! Keep it up

[@NEDLeducation]

Thanks, glad the video was helpful!

[@ancoli1000]

thanks for sharing. and u a very cute ;)

[@TherealkevinAR]

I've watched your videos. Man, this is underrated... I wish people checked your channel more often. Thank you so much for building this amazing content. (let's link up on LinkedIn?)

[@NEDLeducation]

Hi, and thank you so much for the kind words! Sure, here is my profile link: www.linkedin.com/in/savvashanaev/

[@TherealkevinAR]

@@NEDLeducation Sent you a request. See you on LinkedIn ;)