How to Calculate Sharpe Ratio in Excel (2 Common Cases)

We use so many factors in the industry level to calculate the condition. Sharpe Ratio is just a factor of that kind. It will help to analyze the condition of the company based on the previous records. In this article, We’re going to elaborate the procedure of how to calculate Sharpe ratio in Excel.


Download Practice Workbook


Basics of Sharpe Ratio

The Sharpe Ratio, also known as Sharpe Index, is used to calculate the performance of an investment considering all the related risks. It compares investments of different risk profiles against each other.

In order to calculate the Sharpe Ratio, we follow the following formula:

Sharpe Ratio = (Rp - Rf) / Ꝺ

Where,
          Rp = Expected Rate of Return
          Rf = Risk Free Rate of Return
          Ꝺ = Standard Deviation

A higher value symbolizes a better investment. It means greater outcomes for the portfolio compared to the inherent risk.


Process to Calculate Sharpe Ratio in Excel

1.  Formula to Calculate Sharpe Ratio for Known Values

When the values are known, we can simply calculate the Sharpe Ratio by putting the values in the equation.
Here, we have a dataset with given Expected Rate of Return, Risk Free Rate of Return and Standard Deviation. Now, we can apply the formula to calculate the Shape Ratio.

How to Calculate Sharpe Ratio in Excel

Steps:

  • First, select a cell for the output of Sharpe Ratio (i.e. C9).
  • Input the following formula in C9:
=(C4-C5)/C6

Where,
          C4Expected Rate of Return
          C5Risk Free Rate of Return
          C6 = Standard Deviation

  • Finally, press ENTER.

How to Calculate Sharpe Ratio in Excel

Thus, we can easily calculate the Sharpe Ratio.

Read More: How to Do Ratio Analysis in Excel Sheet Format (with Quick Steps)


2.  Sharpe Ratio Calculation in Excel Based on Yearly Returns

We can also calculate the Sharpe Ratio considering the previous years’ records. Generally, when you need to calculate the Sharpe Ratio based on years, you’ll be provided a dataset with Actual Rate of Return, Risk Free Rate of Return.

Actual Rate of Return means the change in percentage of the actual investment and Risk Free Rate of Return means the hypothetical rate of return if there involves no risk.

Let’s assume, we have a dataset like the earlier now we’ll calculate the Sharpe Ratio from here.

Steps:

  • Create an additional column named Excess Return.

How to Calculate Sharpe Ratio in Excel

  • Use the following formula to calculate the Excess Return:
=C5-D5

Where,
          C5 = Actual Rate of Return
          D5 = Risk Free Rate of Return

  • Now, press ENTER.

How to Calculate Sharpe Ratio in Excel

  • Use Fill Handle to AutoFill the rests.
  • Input the following formula to calculate the Average Rate of Return as it explains the cash flow of the investments generated over the years:
=AVERAGE(C5:C9)

Where, the mean value of the cells C5:C9 is calculated.

How to Calculate Sharpe Ratio in Excel

  • Now, hit ENTER.

  • Now, write down the following formula to calculate the Standard Deviation from the Excess Return as it symbolizes the dispersion of a dataset with its mean:
=STDEV(E5:E9)

How to Calculate Sharpe Ratio in Excel

  • Press ENTER.

  • Finally, use the following formula to calculate the Sharpe Ratio:
=D11/D12

Where,
            D11 = Average Rate of Return

            D12 = Standard Deviation

How to Calculate Sharpe Ratio in Excel

  • Lastly, press ENTER to have our desired output.

Read More: How to Calculate Ratio in Excel (4 Handy Methods)


Sharpe Ratio Calculator

We have created a Sharpe Ratio Calculator. You will find this within the provided practice workbook. You just have to input the required variables to have the Sharpe Ratio.

How to Calculate Sharpe Ratio in Excel


Conclusion

We have tried to explain how to calculate Sharpe Ratio in Excel with 2 detailed explanations. We hope it will help the Excel Users. For further queries, comment below.


Related Articles

Naimul Hasan Arif

Naimul Hasan Arif

Hello, I am Arif. I am an Engineer who graduated from BUET. I want to pursue an enterprising career in a progressive environment where my skills & knowledge can be enhanced to their maximum potential to contribute to the overall success and growth of the organization.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo