Calculate the Sharpe Ratio with Excel

This article describes how you can implement the Sharpe Ratio in Excel. As an alternative method, I’ll also give some VBA code that can also be used to calculate the Sharpe Ratio.

If you just want the spreadsheet, then click here, but read on if you want to understand its implementation.

The Sharpe Ratio is a commonly used benchmark that describes how well an investment uses risk to get return. Given several investment choices, the Sharpe Ratio can be used to quickly decide which one is a better use of your money. It’s equal to the effective return of an investment divided by its standard deviation (the latter quantity being a way to measure risk).

This is the Sharpe Ratio formula

There are several assumptions which can often mislead investors. The primary failing is that the math assumes the investment returns are normally distributed. This isn’t always the case – sometimes returns can be skewed or have other characteristics not described by the normal distribution

The math behind the Sharpe Ratio can be quite daunting, but the resulting calculations are simple, and surprisingly easy to implement in Excel. Let’s get started!

Steps to Calculate Sharpe Ratio in Excel

Step 1: First insert your mutual fund returns in a column. You can get this data from your investment provider, and can either be month-on-month, or year-on-year.

Step 2: Then in the next column, insert the risk-free return for each month or year. This is literally the return you would have got if you’d invested your money in a no-risk bank account (in case you need to, raise the yearly return to a power of 1/12 to convert it to a monthly return).

Step 3: Then in the next column, subtract the risk-free return from the actual return. This is your Excess Return

Step 3: Now calculate

the average of the Excess return. In the example above the formula would be =AVERAGE(D5:D16)

the Standard Deviation of the Exess Return. For my example, the formula would be =STDEV(D5:D16)

Finally calculate the Sharpe Ratio by dividing the average of the Exess Return by its Standard Deviation (in my example this would be =D18/D19)

This function can be called by giving it two arguments; the first is the range containing the investment returns, while the second range contains the risk-free interest rates. For my example, the formula would be =SharpeRatio(B5:B16,C5:C16).

Thanks for brief explanation. I does clarify a couple of things. What I do miss in your explanation are the the specific reason for your used assumptions. Why are you using the arithmetic average of the returns and not geomatric?
The building blocks of the Sharpe ratio—expected returns and volatilities—
are unknown quantities that must be estimated statistically and are, therefore, subject to estimation error.The question which Iam stuck at is wheter to use simple retruns (R1-R0)/R1 or LN (R1/R0). I know this has something to with normality, but what do think is better? And if I have computed the returns, which mean should I use..

To calculate the numerator work out the return for your investment first, this will mean geometrically linking (ie compounding) all of the 1 month returns. Averaging (as above) is incorrect. I then like to annualise this figure. Finally subtract the annualised risk free rate that has been realised over the period. One of the errors above is that you are meant to do the subtraction after the total return has been worked out (only doing one subtraction), not before as is the case on this web page.

Then work out the denominator. If you are using monthly returns this number will need to be adjusted. Just multiply it by the square root of 12 If your using quarterly data multiply by the square root of 4, ect. This adjustment was not done above.

Osama and Samir: You need to use standard deviation of returns not the standard deviation of excess returns (tracking error). You may be confusing the Sharpe ratio with the information ratio which is much more benchmark relative. Sharpe is more absolute.

CFA charterholder, you’re wrong, sorry. Look at Sharpe’s 1994 paper (http://www.stanford.edu/~wfsharpe/art/sr/sr.htm), who actually designed the formula. He clearly uses the average, not the geometric, in the numerator. For both numerator and denominator, he also uses excess return, not actual.

Very helpful – I am wanting to use the VBA across columns (not rows) so figured I would just change InvestReturn.Rows.Count to InvestReturn.Columns.Count but it doesn’t work for me (looked everywhere, tried all resources I have). Any ideas? Thanks.