June 16, 2017

Calculate Sharpe Ratio of Portfolio in Excel (with MarketXLS)

Sharpe Ratio of Portfolio in Excel

Sharpe ratio is one of the most commonly used ratios to measure the reward versus risk of an investment opportunity. In this article, we will learn about what Sharpe Ratio is, how it is calculated, and how to calculate the Sharpe Ratio of Portfolio in Excel using MarketXLS.

Download Portfolio Sharpe Ratio Excel Template

What is Sharpe Ratio?

The Sharpe ratio was developed by Nobel laureate William F. Sharpe, and is a measure for calculating risk-adjusted return of an asset. Hence, it is calculated as the mean returns earned by an asset or a portfolio in excess of the risk-free rate per unit of volatility.

The higher the Sharpe Ratio, the better the portfolio or fund has performed in proportion to the risk taken by it.

Sharpe ratio = (Average Portfolio Returns – Risk-Free rate)/Standard Deviation of Portfolio

If the Sharpe ratio of a portfolio is 1.3 per annum, it implies 1.3% excess returns for 1% volatility.

Let’s say an investor earns a return of 6% on his portfolio that has a volatility of 0.6. Assuming a risk-free rate of 4.2%, the Sharpe ratio is (6% – 4.2%)/0.6 = 3.

Importance of Sharpe Ratio

Below are a few important points about Sharpe ratio:

  1. The higher the Portfolio’s Sharpe ratio, the better the risk-adjusted performance. For this reason, investors are advised to pick stocks or funds with higher Sharpe ratio.
  2. Investors can alter their portfolio’s Sharpe ratio by either increasing the returns or by decreasing the risk taken.
  3. It is also a good measure to compare funds or indexes with similar returns, or to compare investments with similar risk profile.

Calculate Sharpe Ratio of Portfolio in Excel

Especially relevant is that we can calculate Sharpe ratio of portfolio in Excel using MarketXLS functions. To do so, we have created a template, where you can enter your stock portfolio and it will automatically calculate the Sharpe Ratio of the portfolio. Also, the template is flexible and we can extend it to calculate Sharpe ratio of any portfolio.

Let us review the steps involved in calculating Sharpe Ratio of Portfolio in Excel.

1. Get Daily Stock Prices

Get daily stock prices for the last one year for each stock in your portfolio. To do this, simply add the stock symbols in excel, select the cells containing the symbols, and then press the ‘1 Year Data’ button in the MarketXLS panel.

2. Calculate Daily Returns

Calculate daily returns for the period using the following formula:

Daily Return = (Price 1 – Price 0) / Price 0

3. Calculate Standard Deviation

Calculate the standard deviation of each stock. Furthermore, we can do this using the Excel function STDEV() and apply it to the daily returns:

Standard Deviation of Stock = STDEV(Daily Returns)

Finally, standard deviation can be annualized by multiplying with with the square root of 252.

4. Prepare Correlation Matrix

Since we have a 3 stock portfolio, we can use the correlation matrix to calculate the portfolio variance. To calculate correlation between each pair of stocks, we will use the Excel’s CORR() function.

5. Calculate Annual Volatility

Once we have the stock’s individual volatility and correlation matrix, we can calculate the annual volatility of the portfolio using the following formula:

This will give us the annual variance. Then we will calculate the annual volatility as the square root of annual variance.

6. Calculate Sharpe Ratio

Now we can calculate the Sharpe ratio using the following formula:

Sharpe ratio = (Average Portfolio Returns – Risk-Free rate)/Standard Deviation of Portfolio

Download Portfolio Sharpe Ratio Excel Template

Leave a Reply

Your email address will not be published. Required fields are marked *