Compute Sharpe Ratio using Excel

Sharpe ratio is a measure of risk and it is named after Nobel Laureate William F. Sharpe. The Sharpe ratio simply stated is a ratio of return vs risk. Given a set of investment choices, Sharpe ratio can help decide which investment makes the most of your money. It is defined as the ratio between effective return of an investment and its standard deviation.

RpRf / σp

where,

Rp = expected return on investor’s portfolio

Rf = risk free rate of return

σp = the portfolio’s measure of risk or standard deviation

Example…

Let’s assume the expected return from your stock portfolio is 14%. Now if the return on risk free T-notes are 3%, and your portfolio carries 0.05 standard deviation, then the Sharpe portfolio can be calculated as:

(0.14 – 0.03) / 0.05 = 2.2.

In other words, if portfolio A generates a 8% return with a 1.50 Sharpe ratio and portfolio B also generates a 8% return with 1.25 Sharpe ratio, then A is better portfolio because you are getting the same return with less risk.

The higher the Sharpe ratio is, the higher the return an investor gets by bearing a unit of risk. The lower the Sharpe ratio is, the more risk the investor is bearing to earn additional returns. There are few assumptions which can be misleading to the investors. The main fallacy is that the assumption of investment returns are normally distributed. Returns can be skewed  or have other characteristics not described by normal distribution.

How to Compute Sharpe ratio using Excel

1. In the first column, enter annual returns of your portfolio such as ETF or Mutual Fund. You can get this information from your investment banker or online sources.

2. In the second column, enter risk-free return rate. This can typically be T-notes.

3. In the third column, take the difference between risk free return rate and actual return. This is the excess return. Name this column as Delta.

4.  Do the following sub-steps

a. Take the average of the Delta or excess return

b. Take the Standard Deviation of Delta use STDEV function

c. Lastly, take the ratio between average of Delta returns and Standard Deviation. This is your Sharpe Ratio.

Download Sharpe Ratio Spreadsheet here

More free spreadsheets at investsolver.com

Advertisements

One thought on “Compute Sharpe Ratio using Excel

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s