How Do You Calculate the Sharpe Ratio in Excel?

The Sharpe ratio helps an investor evaluate the relationship between risk and return for a stock or any other asset. Devised by American economist William Sharpe of Stanford University in the 1960s and revised by him in 1994, the ratio has become one of the most widely used metrics in investing and economics.

The ratio measures the performance of the investment compared to the performance of a risk-free asset, after adjusting for the risk. The current rate of U.S. Treasury bills is generally used as the risk-free asset in the equation.

By quantifying both volatility and performance, the formula allows for an incremental understanding of the use of risk to generate a return.

How to Recreate the Formula in Excel

The Sharpe ratio formula can be made easy using Microsoft Excel. Here is the standard Sharpe ratio equation:

Sharpe ratio = (Mean portfolio return − Risk-free rate)/Standard deviation of portfolio return, or,
S(x) = (rx - Rf) / StandDev(rx)

To recreate the formula in Excel, create a time period column and insert values in ascending sequential order (1, 2, 3, 4, etc.). Each time period is usually representative of either one month, one quarter, or one year.

Then, create a second column next to it for returns and plot those values in the same row as their corresponding time period.

In the third column, list the risk-free return value. The standard value is the current return for U.S. Government Treasury bills. The same value should be used in every row in this column.

A fourth column has the equation for excess return, which is the return minus the risk-free return value. Use the cells in the second and third columns in the equation.

Copy this equation into each row for all time periods.

Next, calculate the average of the excess return values in a separate cell.

In another open cell, use the =STDEV function to find the standard deviation of excess return.

Finally, calculate the Sharpe ratio by dividing the average by the standard deviation.

Reading the Results

A higher ratio is considered better. It indicates a higher return or a moderate degree of risk, or both. In any case, it suggests that the investor got a substantial reward for taking a greater risk.

A negative ratio means that the investment underperformed the risk-free alternative when the risk of that investment is taken into account.

Sharpe ratios can also be calculated using Visual Basic for Applications (VBA) functions. However, you should understand how to use a VBA before attempting to provide Excel arguments for calculating the Sharpe ratio. 

Example

Let's say that you're considering an investment with an expected long-term return of 20%. The return of the risk-free alternative (Treasury bills) is 2.3%. Standard deviation is 15%. The calculation would be:

(20-2.3)/15 = 1.18

A Sharpe ratio of more than one but less than two is usually considered acceptable to good by investors.

As mentioned above, in Excel, in the first column, number the time periods using as many rows needed for the number of periods you're evaluating. In the second column, enter the investment returns for each period.

The third column would contain the risk-free rate of return of 2.3 in each row. Each row of the fourth column should display the calculated results for excess return. These rows reflect the part of the Sharpe ratio formula that subtracts the tax-free rate from the expected rate of return for the investment.

Next, insert the formula (=average) to calculate the average rate of return for the rows in column four (the excess returns). In our example above, that result would be 20%.

Following on that, calculate the standard deviation (=STDEV) for the figures in the fourth column. In the example, that's 15%.

Finally, calculate your Sharpe ratio by dividing the figure in the average return cell by the figure in the standard deviation cell. The result should be 1.18.