Issues calculated Sharpe Ratio, Information Ratio, Beta and Alpha

[nyctrader101]

I watched your video(s) and using your Excel spread sheet, calculated Sharpe Ratio, Information Ratio, Beta and Alpha. My numbers seem a bit high (157+ Sharpe), and I understand I have only 2 months results and those show a very high ROI (10+% both months), but even taking that into account, it seems off (especially the Sharpe Ratio).

I attached both of my spread sheets to this forum's post. The Beta formula was not working, so I changed it (BionicTurtle1 file).

I would appreciate someone taking a look at them for me. Thank you (Bionic Turtle) in advance and thanks for making your spread sheets available.

 

[emilioalzamora1]

I will check it and be back with in-depth explanations either today or tmw. Happy to help. However, i do encourage you get the book 'Investments' by William Sharpe first oft all. Its quite old but still available on amazon and by far the best reading about fundamental CAPM concepts and Performance measures.

 

[nyctrader101]

Thank you emilioalzamora. I eagerly await your input, evaluation and expertise.

 

[emilioalzamora1]

First, can you please simply skip these two spreadsheets because it's quite hard to follow what your problem is! I need to teach you some very basic concepts about Performance Measures to get you going. Then you will be easily able to follow the calculations in the spreadsheets.
I will come up with some rigorous bottom-up explanation about the Information-Ratio and the Sortino-Measure tomorrow; sorry I am too tired today to write a full story about these two.

 

[nyctrader101]

Can we focus on Sharpe Ratio and Information Ratio and skip the Sortino Ratio for now as no month has been below the benchmark so its N/A. Most importantly, I am interested in a conformation that the ratios in the spread sheet (Sharpe and Information Ratio) are correct. Then I most definitely would appreciate your explanation.

 

[nyctrader101]

I appreciate emilioalzamora's offer, and I would love to learn more about learning more about the Sharpe and Information Ratio, but for now, I really need a little help. If someone could confirm the Sharpe and Information Ratio's were calculated correctly on the spread sheets in my 1st text, I would be grateful.

 

[emilioalzamora1]

I have no clue what you are aiming at and I don't have the time to check every single column of the spreadsheet which have been compiled by David and which are definitely correct. I can't see what you did and what your actual problem is.
You need to be much more clear here.
Thanks!

 

[emilioalzamora1]

with regard to the Information-Ratio: can you please check my recent posts here in the forum:

https://forum.bionicturtle.com/thre...-significance-of-performance.7745/#post-46961

https://forum.bionicturtle.com/thre...harpe-and-alpha-calculations.7506/#post-47066

There is not much 'checking' to be done as it can be checked by yourself quite easily:

Sharpe: excess return per unit of risk (CML) which is the return generated by a single stock (or a portfolio) over the risk-free rate divided by the standard deviation of the single stock (portfolio).
Information Ratio (basic definition): alpha of the stock (portfolio) generated/achieved by the manager divided by the standard deviation of the difference between stock (portfolio) return and market (benchmark) return which is called tracking error (see my first posting/link above)

With regard to beta - 2 alternative formulas to remember (to yield the same beta!):

1.) ß = covariance(ri, rm) / variance(rm) >> In words, beta equals the covariance of asset (i) with the market (m) divided by the variance of the market

2.) ß = correlation(ri,rm) * [ std. dev(i)/ std. dev (m) ]>> In words, beta equals the correl. between asset (i) and the market (m) times the fraction of the the std. dev. the asset (i) and std.
dev. of asset (m).
Hence, beta depends on two factors: 1.) relative risk of the stock expressed as: standard deviation of asset (i) divided by standard deviation of the market (m)
AND 2.) degree to which the return of asset (i) moves with the market rather than independently expressed as the correlation coefficient between asset (i) and the market (m).

Beta math in Excel - 3 options:

• Slope-function:

using the return for (e.g.) Disney as the first argument (y) and the return of the S&P500 as the second argument (x)

• Regression function (LINEST):

y and x argument remain the same as under the slope-function, choose (1) for the 'const' and (o) for 'stats' to get beta.

• Variance-covariance matrix:

covariance(Disney,S&P500)/variance(S&P500)

>> You need to use the covariance.p ( ) and the var.p( ) which are the population formulas in order to get the exactly the same result as what the slope ( ) function yields!


Please do some reading first of all - these are BASIC concepts you need to grasp in and out otherwise you run into serious problems some time down the road.

Go to Damodaran's webpage (chapter 8 and 9) - see the following link:

http://pages.stern.nyu.edu/~adamodar/

I do recommend the following reading (chapter 20): I think each chapter can be purchased separately at 'wiley.com'.

https://www.amazon.com/dp/0470026634/?tag=bt077d-20

If this does not work for you, please detail the difficulties you are facing.
Thanks!

 

[nyctrader101]

I appreciate the information but that is really a recap of the ratios definitions, which I already have and understand.

My issue is the Sharpe Ration I calculated comes to 170.37. I would appreciate your checking my process and steps either below of in the spread sheet I re-attached to this post. The 2 bold lines are my numbers, prior to any calculations. Thank you.

Monthly returns are 10.6027% & 10.1901%.
Average Monthly Return = 10.4%
Projected Annual Return=124.8%
Annual Risk Free Return=3%
Avg Annual Excess Return, vs riskfree=121.8%
(P - PAVG)2 (portfolio returns minus avg portfolio returns/month)=.0004%
Standard deviation, monthly ((SQRT of (P-PAVG)2))=.21%
Volatility (annualized standard deviation)=.21%*SQRT(12)=.71%
Sharpe Ratio=170.37

 

[emilioalzamora1]

I am sorry to say but you did not get it! I just checked the original spreadsheet and I can't nowhere see where the portfolio returns of 10.6% in F16 in your sheet should come from? What the hell are you doing and from where did you get this numbers?

According to David's file (randomly generated returns) in F16 (t-1) the portfolio return is 3.4%, then in F17 it should be -0.1% and so forth (check the Youtube video uploaded by David 'Sortino Ratio' which yields across the whole column an average monthly portfolio return of approx. 1.21%

• mean scales with time. As given in David's spreadsheet this implies 1.21% x 12 = 14.5% (annual portfolio return)

The standard deviation used for the Sharpe Ratio is simply the standard deviation of the returns which is approx. 3.13% (monthly). Formula: STDEV(F16:F45) = 3.13%

• std. deviation scales with the square root of time. Hence, 3.13% x sqrt(12) = 10.8% (annual std. deviation)

Sharpe = 14.5% - (0.25*12)/10.8% = 1.06 >>> where 0.25*12 is the annualised rf-rate

It's a bit unfortunate that David is overcomplicating this here! I will let him know to adjust this.

 

[nyctrader101]

My trading systems returns were generated on another spread sheet (not included) and are as follows:
November 2016 = 10.6027%
December 2016 = 10.1901%

I used a risk free return of 3%/year or 0.25%/month. Based on these numbers, are my Share Ratio calculations correct?

 

[emilioalzamora1]

But then you need to use the standard deviation of your! returns as well and not the std. deviation in David's spreadsheet. Then you will get a meaningful Sharpe Ratio.
Rely on your own figures and then simply calculate the Sharpe Measure without copying your figures in David's spreadsheet and mixing the numbers up.

 

[nyctrader101]

I did use the STDEV of my returns. What do you get for the Sharpe Ratio based on my #s? Do you also get the insanely high 170.37? I just wanted to verify my #s were correct. Thanks.

 

[emilioalzamora1]

simply upload your returns (only returns and nothing else!!) and I will check one more time.

 

[David Harper CFA FRM]

@emilioalzamora1 I got your PM. As the XLS opens to blank, for me, in F18:F45, can I just clarify something. My XLS probably does indeed compute the denominator of Sharpe ratio as sqrt(sum:[r(i) - µ(r)]^2/n) which if mean = 0, is the sqrt(average squared return). As a check, my calculation should equal =STDEV.P which similarly squares the difference from the mean return. BTW, I really should have sample-sized (n-1) rather than (n) in there, yikes. To my knowledge, that is correct Sharpe per Carl Bacon of CIPM who I tend to trust. (But don't quote me, I need to check ...). I absolutely agree with you that it's okay to knowingly assume µ(return) = 0, but why is the (technically correct) standard deviation a mistake? With respect to volatility, I am aware of the arguments for/against µ=0, but I don't see the trouble with assuming mean in the Sharpe. Thanks for any links/pointers ....

 

[nyctrader101]

emilioalzamora1 - Thanks.
November 2016 = 10.6027%
December 2016 = 10.1901%
Risk Free Return of 3%/year

 

[nyctrader101]

David - Sharpe Ratio calculation is on line F73. Lines 18 through 46 are blank. In your original spread sheet, they contained additional months. My system has only been running 2 months.

 

[emilioalzamora1]

Apologies @David Harper CFA FRM!
I got so confused now what this guy is doing with the Sharpe-Ratio. All clear, you simply calculated the std. deviation 'by hand' in the spreadsheet and I mistakenly simply used the 'stdev' function instead of 'stdev.p' which yields a slightly different result.
I did not even think about (mean = 0).
By the way, I like Carlon Bacon's book as well. Excellent source for Performance Measurement.
All good now, David! Thanks for your input.

 

[emilioalzamora1]

The reason why you get such a high Sharpe Ratio is the fact that the standard deviation - using only two returns (which are approx. identical) - is close to zero (=0.21%).
Therefore, the whole Sharpe-Ratio gets diluted. You need a sample size of at least roughly 30 returns to get a meaningful ratio. At best use daily or weekly data but avoid monthly data as 'inter-month' movements disappear (even out).

 

[David Harper CFA FRM]

@emilioalzamora1 I was just about to write the same thing as @nyctrader101 : strictly speaking, your calcs look correct to me based on a 60 second look. The "problem" is only two return datapoints, each which is itself ~ 10.0% above the monthly Rf rate of 25 bps (!). It makes you wonder if the 10.0% returns are already annualized, but nonetheless as Emilio says, two points don't inform a ratio, they don't inform a volatility either (1 df is already consumed by the mean!). Thanks,