How Do You Calculate the Sharpe Ratio in Excel?

Use the Sharpe ratio to evaluate an asset's risk vs. return

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.

Key Takeaways

  • The Sharpe ratio helps an investor measure an investment's risk-adjusted return in comparison to a risk-free alternative.
  • Using the Sharpe ratio, an investor can judge whether the risk is worth the return.
  • The higher the ratio, the better the return in comparison with the risk-free investment.
  • A ratio of less than one is considered sub-optimal.
  • A problem with the Sharpe ratio calculation is that it can overemphasize results for investments without a normal distribution of returns.

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.

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

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.

Sharpe Ratio Drawbacks

While the Sharpe ratio is a helpful tool for measuring risk-adjusted return for an investment or portfolio, it has its drawbacks:

  • The ratio includes an assumption that returns are normally distributed. Thus, results can be skewed by investments that don't have a normal distribution of returns.
  • The result can be raised by adding assets to a portfolio to improve its diversification.
  • Likewise, stocks with higher risk-adjusted results can push up the result.

What Is Considered a Good Sharpe Ratio?

Typically, a Sharpe ratio greater than 1.0 is viewed by investors as acceptable to good. One higher than 2.0 is rated very good. A ratio of 3.0 and above is rated excellent.

What Does the Sharpe Ratio Tell You?

It can give you an idea of how well an investment could perform in the long-term relative to a risk-free investment.

When Should You Calculate the Sharpe Ratio?

Consider using the Sharpe ratio when you want to see how well a potential (or existing) investment's return stacks up with that for a risk-free security like a Treasury bill. You can also use it as a comparison tool for different investments that you want to evaluate. The one with the highest Sharpe ratio could be the best choice.

Article Sources
Investopedia requires writers to use primary sources to support their work. These include white papers, government data, original reporting, and interviews with industry experts. We also reference original research from other reputable publishers where appropriate. You can learn more about the standards we follow in producing accurate, unbiased content in our editorial policy.
  1. The Journal of Portfolio Management. "The Sharpe Ratio."

Take the Next Step to Invest
×
The offers that appear in this table are from partnerships from which Investopedia receives compensation. This compensation may impact how and where listings appear. Investopedia does not include all offers available in the marketplace.
Service
Name
Description