5

# 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.

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)

## VBA for the Sharpe Ratio

A cleaner solution is the following VBA function.

```Function SharpeRatio(InvestReturn, RiskFree) As Double
Dim AverageReturn As Double
Dim StandardDev As Double
Dim ExcessReturn() As Double
Dim nValues As Integer```
``` nValues = InvestReturn.Rows.Count
ReDim ExcessReturn(1 To nValues)```
``` For i = 1 To nValues
ExcessReturn(i) = InvestReturn(i) - RiskFree(i)
Next i```
``` AverageReturn = Application.WorksheetFunction.Average(ExcessReturn)
StandardDev = Application.WorksheetFunction.StDev(ExcessReturn)
SharpeRatio = AverageReturn / StandardDev
End Function```

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).

### 5 Responses to "Calculate the Sharpe Ratio with Excel"

1. Osama says:

Thanks for upload this for excel user.

According my understanding, Standard deviation needs to be calculated of Portfolio Return instead of Excess return (as u did).

Please refer Investopedia or inform me if i am wrong

• Samir says:

According to Wikipedia, the denominator is the standard deviation of the Excess Return (asset return – benchmark return).

So I think I’m right…

2. kai says:

if the required rate of return is constant, then the standard deviations of both cases are the same.

3. Alex says:

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..