How Do You Calculate 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.

Key Takeaways

  • The Sharpe ratio helps an investor measure an investment’s return in comparison to a risk-free alternative while adjusting for that risk.
  • The investor can judge whether the risk was worth the return.
  • The higher the ratio, the better the return in comparison with the risk-free investment.

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.

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

The otherwise intimidating Sharpe ratio formula can be made easy using Microsoft Excel.

How to Recreate the Formula in 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 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. 

About admin

Check Also

Government Accounting Standards Board (GASB)

Government Accounting Standards Board (GASB)

What Is the Government Accounting Standards Board (GASB)? The Government Accounting Standards Board (GASB) is …

Bir cevap yazın

E-posta hesabınız yayımlanmayacak.