A:

The Sharpe ratio helps investors evaluate the relationship between risk and return for an asset. Since it was introduced by William Sharpe in the 1960s, the Sharpe ratio has become one of the most widely used metrics in finance and economics. By quantifying both volatility and performance, this tool allows for an incremental understanding of the use of risk to generate return. With the help of Microsoft Excel, the otherwise intimidating Sharpe ratio formula can be easily utilized.

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(x)

To recreate this 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 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, which is normally the current returns for U.S. Government Treasury bills. There should be the same value 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. Higher ratios are considered better.

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. 

(For related reading, see: Understanding the Sharpe Ratio.)

RELATED FAQS
  1. What is a good Sharpe ratio?

    Understand how the Sharpe ratio is calculated, and its significance and use for investors in evaluating the performance of ... Read Answer >>
  2. What is the difference between a Sharpe ratio and a Traynor ratio?

    Understand the difference between two methods of evaluating portfolio returns on investment, the Sharpe ratio and the Treynor ... Read Answer >>
  3. What is the difference between the Sharpe ratio and alpha?

    Use alpha and the Sharpe ratio to evaluate mutual funds by comparing their risk-adjusted returns. Learn what modern portfolio ... Read Answer >>
  4. How do you use Microsoft Excel to calculate liquidity ratios?

    Learn how to calculate the most common liquidity ratios in Microsoft Excel by inputting financial figures from a company's ... Read Answer >>
  5. How do I calculate the equity risk premium in Excel?

    Find out how to calculate the equity risk premium for an individual security using Microsoft Excel, including how to estimate ... Read Answer >>
  6. What is the formula for calculating the price-to-earnings (P/E) ratio in Excel?

    Find out more about the price-to-earnings, or P/E, ratio, the P/E ratio formula and how to calculate the P/E ratio in Microsoft ... Read Answer >>
Related Articles
  1. Investing

    Understanding The Sharpe Ratio

    The Sharpe ratio describes how much excess return you are receiving for the extra volatility that you endure for holding a riskier asset.
  2. Investing

    Mitigating Downside With the Sortino Ratio

    Differentiate between good and bad volatility with the Sortino ratio.
  3. Investing

    3 Investments With the Best Long Term Track Records

    Take a look at the three asset classes with the best risk-adjusted performance since 1990, and find out why the Sharpe ratio can help you make better decisions.
  4. Trading

    Improve your investing with Excel

    Excel is a useful tool to assist with investment organization and evaluation. Find out how to use it.
  5. Investing

    PRHSX: Risk Statistics of Health Sciences Mutual Fund

    Examine the risk metric of the T. Rowe Price Health Sciences Fund. Analyze beta, capture ratios and standard deviation to assess volatility and systematic risk.
  6. Investing

    Analyze Investments Quickly With Ratios

    Make informed decisions about your investments with these easy equations.
  7. Investing

    5 ways to measure mutual fund risk

    These statistical measures help investors understand investment risk and how it relates to returns.
  8. Investing

    How to Use a Benchmark to Evaluate a Portfolio

    What is an investment benchmark and how is it used to evaluate the risk and return in a portfolio.
  9. Financial Advisor

    The Debt To Equity Ratio

    The debt to equity ratio identifies companies that are highly leveraged and therefore a higher risk for investors. Find out how this ratio is calculated and how you can use it to evaluate a stock.
RELATED TERMS
  1. Sharpe Ratio

    The Sharpe ratio is the average return earned in excess of the ...
  2. Information Ratio (IR)

    The information ratio is a ratio of portfolio returns beyond ...
  3. Sortino Ratio

    A modification of the Sharpe ratio that differentiates harmful ...
  4. William F. Sharpe

    William F. Sharpe is an American economist who won the 1990 Nobel ...
  5. Sterling Ratio

    Sterling ratio is a risk-adjusted return measure that uses compounded ...
  6. Accounting Ratio

    Accounting ratios, also known as financial ratios, are used to ...
Hot Definitions
  1. Relative Strength Index - RSI

    Relative Strength Indicator (RSI) is a technical momentum indicator that compares the magnitude of recent gains to recent ...
  2. Dividend

    A dividend is a distribution of a portion of a company's earnings, decided by the board of directors, to a class of its shareholders.
  3. Inventory Turnover

    Inventory turnover is a ratio showing how many times a company has sold and replaces inventory over a period.
  4. Watchlist

    A watchlist is list of securities being monitored for potential trading or investing opportunities.
  5. Hedge Fund

    A hedge fund is an aggressively managed portfolio of investments that uses leveraged, long, short and derivative positions.
  6. Balance Sheet

    A balance sheet reports a company's assets, liabilities and shareholders' equity at a specific point in time.
Trading Center