Close

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

Buy the Sharpe Ratio Template for $9 here.

 

24 Comments on “Calculate Sharpe Ratio of Portfolio in Excel (with MarketXLS)

targeted visitors
August 29, 2018 at 7:29 pm

targeted visitors

[…]although sites we backlink to below are considerably not connected to ours, we feel they may be really really worth a go via, so have a look[…]

mcat cars importance
August 30, 2018 at 1:58 pm

mcat cars importance

[…]one of our visitors just lately encouraged the following website[…]

123klussers.nl
September 1, 2018 at 12:59 am

123klussers.nl

[…]Every after in a when we decide on blogs that we read. Listed below would be the latest websites that we opt for […]

silicone dildo
September 2, 2018 at 10:34 am

silicone dildo

[…]The information talked about inside the write-up are some of the very best obtainable […]

interracial porn online
September 4, 2018 at 10:20 am

interracial porn online

[…]the time to study or pay a visit to the content material or web sites we’ve linked to beneath the[…]

tiket pesawat murah
September 5, 2018 at 3:05 am

tiket pesawat murah

[…]just beneath, are several absolutely not associated web pages to ours, however, they are certainly really worth going over[…]

benwa balls
September 5, 2018 at 4:57 pm

benwa balls

[…]one of our guests not long ago proposed the following website[…]

Diccionario de regionalismos
September 6, 2018 at 9:41 am

Diccionario de regionalismos

[…]check beneath, are some absolutely unrelated sites to ours, nonetheless, they are most trustworthy sources that we use[…]

http://www.hunnitbrand.com
September 10, 2018 at 10:59 am

http://www.hunnitbrand.com

[…]we prefer to honor several other world wide web internet sites around the internet, even though they aren’t linked to us, by linking to them. Underneath are some webpages worth checking out[…]

FS19 mods
September 12, 2018 at 7:55 pm

FS19 mods

[…]below you will find the link to some internet sites that we think you should visit[…]

farming simulator 2019 mods
September 13, 2018 at 1:05 am

farming simulator 2019 mods

[…]we came across a cool site that you simply may possibly love. Take a look when you want[…]

porn podcast
September 13, 2018 at 8:55 am

porn podcast

[…]Here are a few of the sites we advocate for our visitors[…]

Stepfamily Porn Online
September 14, 2018 at 8:13 am

Stepfamily Porn Online

[…]always a major fan of linking to bloggers that I really like but don’t get a whole lot of link appreciate from[…]

Stepfamily Porn Online
September 14, 2018 at 11:59 am

Stepfamily Porn Online

[…]here are some hyperlinks to web sites that we link to because we consider they may be worth visiting[…]

Google
September 14, 2018 at 12:17 pm

Google

Always a large fan of linking to bloggers that I love but don’t get quite a bit of link enjoy from.

Stepfamily Porn Online
September 14, 2018 at 6:54 pm

Stepfamily Porn Online

[…]although web sites we backlink to beneath are considerably not connected to ours, we really feel they’re truly worth a go through, so have a look[…]

Google
September 16, 2018 at 1:32 am

Google

Although sites we backlink to below are considerably not connected to ours, we really feel they are basically worth a go by way of, so possess a look.

houses for sale in cerritos ca
September 16, 2018 at 9:13 am

houses for sale in cerritos ca

[…]please pay a visit to the sites we adhere to, like this one particular, as it represents our picks in the web[…]

xxnxx
September 16, 2018 at 9:00 pm

xxnxx

[…]we prefer to honor several other net web-sites around the net, even when they aren’t linked to us, by linking to them. Underneath are some webpages worth checking out[…]

Dolce and Gabbana
September 17, 2018 at 12:23 am

Dolce and Gabbana

[…]The details mentioned within the write-up are a few of the ideal accessible […]

Jimmy Choo
September 17, 2018 at 11:08 am

Jimmy Choo

[…]that is the finish of this article. Right here you will find some web sites that we believe you’ll enjoy, just click the links over[…]

Givenchy
September 18, 2018 at 9:14 am

Givenchy

[…]very few websites that take place to be detailed beneath, from our point of view are undoubtedly effectively really worth checking out[…]

best vibrator
September 18, 2018 at 11:43 am

best vibrator

[…]please visit the internet sites we follow, including this one, because it represents our picks through the web[…]

Web development services
September 20, 2018 at 9:22 am

Web development services

[…]Here is an excellent Blog You may Find Interesting that we Encourage You[…]

Comments are closed.