How to calculate sharpe ratio?

duffman

New member
Joined
Jan 25, 2012
Messages
2
Hello -

So I understand that the Sharpe Ratio is defined as (rate of portfolio return - risk free rate) / portfolio standard deviation. I'm just not quite sure on how to compute each of the inputs. Let's assume I have a data set spanning 5 years in cells A1:A1260:

Rate of Return: Would this be (A1260/A1 - 1) / 5?
Risk Free Rate: This one I have no idea. I know I'm supposed to use a risk free rate like LIBOR of returns on a US Treasury but do I just pick the most recent risk free rate? If I do that then essentially for a portfolio that had the exact same returns and standard deviation would have a different Sharpe in 2005 than today.
Portfolio Standard Deviation is this simply stdev(A1:A1260)?

Many thanks for the help. Much appreciated.
 
Are you serious?! Not you, personally, but is that REALLY the published method for the Portfolio Rate of Return? Yikes.

As far as the Risk Free rate, well, sort of just picking might be a good answer. Some might say just use the 10-year. Others, like me, might suggest you find a Money Duration of your portfolio and pick a Risk Free rate of about the same magnitude. You'll have to decide what serves your purpose.

As far as the standard deviation, no, that would not be even close. That makes no sense at all. Think it through - don't just guess. To get a standard deviation, you need some related data. How is one portfolio value related to the next? It's probably quite dependent and has little to do with randomness. I might do this:

1) Divide each successive portfolio balance by its predecessor. Use column B in your spreadsheet.
2) Since 1260/5 = 252, I'm going to guess this is daily, excepting weekends and holidays. You may wish to use column C to put the number of days. You should get mostly 1, some 3, and maybe a 4 or 5.
3) Annualize the values in column B. One day apart would be ^(365/1), two days apart would be ^(365/2), etc. Use columns B and C to create D.
4) It's still an accumulation and not a rate, so fix that. Column D less 1 is Column E. There are the individual rates of return!
5) The mean is still easy enough, but I would NOT use where you started. (last / first)^(1/5) - 1 will make you so much happier (unless you are required to use something else.)
6) The variance and standard devitation are a little tricky. Resist the temptation simply to use the stdev() function. That will be wrong. Close, but wrong. You will have to figure out how to calculate the variance with grouped data. Have you seen this?

I'll leave it at that and see where you land.
 
Hope I'm not too late with this...

This link contains a pretty comprehensive guide to calculating the Sharpe Ratio in Excel. The calculation is simple once you know how, but you probably need to appreciate some simple statistics principles before you fully understand what the Sharpe Ratio actually means.

Silo
 
Top