Master Sharpe Ratio Calculation in Excel for Better Investing
Key Takeaways
- The Sharpe ratio is a key metric to evaluate risk-adjusted returns against a risk-free investment like U.S. Treasury bills.
- Calculating it in Excel involves using average excess returns divided by the standard deviation of these returns.
- A Sharpe ratio above 1.0 generally indicates an acceptable investment; above 2.0 is considered very good.
- Assumptions of normally distributed returns may limit the accuracy of the Sharpe ratio.
- Understanding risk-to-return ratios can aid in better investment decision-making.
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.
It measures the performance of an 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. The formula allows for an incremental understanding of the use of risk to generate a return by quantifying both volatility and performance. Microsoft Excel makes it straightforward to calculate this ratio, even for those with little financial expertise.
Learn how to set up an Excel spreadsheet to calculate the Sharpe ratio accurately. You’ll gain a solid understanding of not just the calculation process, but how to interpret the results meaningfully to make informed investment decisions as well.
Calculating the Sharpe Ratio 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.
Important
A negative Sharpe ratio indicates that the investment underperformed the risk-free alternative when risk is taken into account.
Interpreting Sharpe Ratio 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.
Sharpe Ratio Calculation 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.
Limitations of the Sharpe Ratio
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.