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)

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

Download Excel Spreadsheet for the Sharpe Ratio