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: 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 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 >>
  2. What is the difference between a sharpe ratio and an information ratio?

    Understand the meaning of the Sharpe ratio and the information ratio, and understand how they differ as tools for evaluating ... Read Answer >>
  3. How can I use alpha in conjunction with the Sharpe Ratio?

    Take a deeper look at the differences between alpha and the Sharpe ratio, two mutual fund performance measures based on modern ... 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 can I calculate the times interest earned in Excel?

    Read about a simple way to calculate times interest expense by using Microsoft Excel. TIE is a common ratio used to evaluate ... Read Answer >>
  6. Can you calculate the marginal tax rate in Excel?

    Learn how to approximate taxes owed and if Excel and marginal tax rate data provide an accurate number for personal tax calculation. Read Answer >>
Related Articles
  1. Investing

    5 Ways To Rate Your Portfolio Manager

    Investopedia explains: These five performance ratios will help you measure how good your money manager is at increasing the value of your portfolio.
  2. Trading

    Improve Your Investing With Excel

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

    Analyze Investments Quickly With Ratios

    Make informed decisions about your investments with these easy equations.
  4. 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.
  5. 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.
  6. Investing

    6 Basic Financial Ratios And What They Reveal

    These formulas can help you pick better stocks for your portfolio once you learn how to use them.
  7. Financial Advisor

    Does Your Investment Manager Measure Up?

    These key stats will reveal whether your advisor is a league leader or a benchwarmer.
RELATED TERMS
  1. Modified Sharpe Ratio

    A ratio used to calculate the risk-adjusted performance of an ...
  2. Risk-Adjusted Return

    A risk-adjusted return takes into account the amount of risk ...
  3. Ratio Analysis

    A ratio analysis is a quantitative analysis of information contained ...
  4. Sortino Ratio

    A modification of the Sharpe ratio that differentiates harmful ...
  5. Current Ratio

    The current ratio is a liquidity ratio that measures a company's ...
  6. Active Risk

    Active risk is a type of risk that a fund or managed portfolio ...
Hot Definitions
  1. Intrinsic Value

    Intrinsic value is the actual value of a company or an asset based on an underlying perception of its true value including ...
  2. Bubble

    1. An economic cycle characterized by rapid expansion followed by a contraction. 2. A surge in equity prices, often more ...
  3. Swap

    A swap is a derivative contract through which two parties exchange financial instruments, such as interest rates, commodities, ...
  4. Yield Curve

    A yield curve is a line that plots the interest rates, at a set point in time, of bonds having equal credit quality, but ...
  5. Gross Profit

    Gross profit is the profit a company makes after deducting the costs of making and selling its products, or the costs of ...
  6. Risk Tolerance

    The degree of variability in investment returns that an individual is willing to withstand. Risk tolerance is an important ...
Trading Center