0
$\begingroup$

I'm taking a quiz, and trying to calculate the annualized Sharpe ratio of 11 years' worth of SPY fund monthly returns vs. a risk free investment return of 1.5%. When I write the function in Excel as =(AVERAGE(G3:G145)-0.015)/(STDEV(G3:G145)*Sqrt(12)), I'm getting -0.16. In my spreadsheet, G3:G145 is the range of monthly returns for the SPY fund. The possible answers for the question on the quiz are .56, .53, .50, and .48, so clearly I'm far off. What would be the correct way to write this formula in Excel?

Also, here are the exact instructions given by the quiz:

The annual Sharpe Ratio calculates the difference between the annual return of a stock and the annual return of a risk-free investment in government bonds - then divides that difference by the annualized standard deviation of returns of the stock.

Estimate the annualized standard deviation of returns by multiplying the monthly population standard deviation of returns by the square root of 12.

$\endgroup$
  • $\begingroup$ The 0.015 is the annualized rate? Should you compare that to monthly returns? $\endgroup$ – shabbychef Dec 15 '15 at 0:02
  • $\begingroup$ The answer by Alex C gave me a correct answer, according to the quiz. And I've provided the full text of the quiz question. I'm not experienced enough to say whether comparing an annualized risk-free return rate to an annualized return rate based off of monthly return data is correct or accurate, but according to my class, that's the right way to do it. $\endgroup$ – xxxRxxx Dec 16 '15 at 19:59
0
$\begingroup$

You have to annualize the monthly avg return also:

=(12*AVERAGE(G3:G145)-0.015)/(STDEV(G3:G145)*Sqrt(12))

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.