Home MarketXLS
Dashboard MarketXLS
Screener MarketXLS
Options Profit Calculator MarketXLS
Stock Ranks MarketXLS
Spreadsheet Builder MarketXLS
Documentation MarketXLS
Logout MarketXLS

Sharpe Ratio Of Portfolio (With Marketxls)

Written by MarketXLS Team on 
Fri Jun 16 2017
 about Portfolio analysis and optimizationPortfolio Management
Sharpe Ratio Of Portfolio (With Marketxls) - MarketXLS
Boost your market advantage with MarketXLS. Real-time, enterprise-grade data. Book a demo or subscribe now!
Sharpe Ratio Of Portfolio (With Marketxls) - MarketXLS

Sharpe ratio is one of the most commonly used ratios to measure the reward versus the risk of an investment opportunity. This article will discuss what it is, how it is calculated, how marketXLS calculates it of your Portfolio, and how to calculate the Sharpe Ratio of a Portfolio in Excel using MarketXLS.

Contents

  1. What is Sharpe Ratio
  2. Importance of Sharpe Ratio
  3. How MarketXLS Calculates Sharpe Ratio of Portfolio
  4. How to Calculate Sharpe Ratio of Portfolio in Excel (Using MarketXLS)

 

What is Sharpe Ratio?

The Sharpe ratio was developed by Nobel laureate William F. Sharpe and is a measure for calculating the 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 Of Portfolio (With Marketxls)

Where:

Sharpe Ratio Of Portfolio (With Marketxls)

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 with a volatility of 0.6. Assuming a risk-free rate of 4.2%, the Sharpe ratio is (6% – 4.2%)/0.6 = 3.

Variations:

Sharpe Ratio can either be calculated on an ex-post or ex-ante basis. The ex-post variation utilizes historical returns and historical risk-free-rates/minimum acceptable return to compute the ratio, whereas the ex-ante variation utilizes expected future returns and expected risk-free-rates/ minimum acceptable return to compute the ratio.

Note:- Considering the dynamic nature of the ex-ante Sharpe ratio and the extent of variation that may exist in individual estimates marketXLS calculates only the ex-post Sharpe ratio, but this article will also deal with the calculation of the ex-ante variation using excel.

Importance of Sharpe Ratio

Below are a few crucial points about Sharpe ratio:

  1. The higher the ratio, the better the risk-adjusted-performance. For this reason, investors are advised to pick stocks or funds with a 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.
  4. It accounts for both, systemic as well as idiosyncratic risks.

How MarketXLS Calculates Sharpe Ratio of your Portfolio

MarketXLS® completely automates the process of calculating the Sharpe ratio and returns an annualized value to facilitate maximum comparability between different portfolios.

For either the ex-ante or ex-post, the following inputs are required:

Required Fields:

  1. Symbol of the asset:
    Enter the ticker under which the security in your portfolio is traded.
  2. Weight of the asset in your portfolio:
    Enter the percentage weight of each security as shown below.

Example:

Sharpe Ratio Of Portfolio (With Marketxls)

 

Optional Fields:

  1. Minimum acceptable return(Mar)/ risk-free-rate(Rf):
    To account for individuality in investor outlook, we make sure to give you the flexibility of determining what Mar/Rf you think is most viable. Default value: marketXLS assumes a Mar/Rf of 0% by default to calculate the ratio.
  2. Months under observation:
    To determine how many months you want to utilize retrospectively to arrive at your calculation, you may input the number of months you feel best, given that all the securities in your portfolio have existed long enough to have data for those periods.  Default Value: In the absence of any user input, marketXLS uses the data for the preceding 12 months from the date of the query.

Calculation:

The following is how the MarketXLS add-in calculates the Sharpe ratio for your portfolio, one may note, that this also serves as an explanatory step-by-step guide as to how to calculate the ratio.

1. Weighted Monthly Return Calculation:

Monthly return is calculated as follows:Sharpe Ratio Of Portfolio (With Marketxls)
Where:

Sharpe Ratio Of Portfolio (With Marketxls)

Note: To maintain the consistency with theory in this regard, Close Price is a security’s Adjusted Close (Close prices adjusted for dividends and splits) of the last day of a given month and the Open Price is the Adjusted Close price for the last day of the previous month.

The values so received are multiplied by the respective weights of each security in the portfolio, and their sum is the weighted return for that month.

This process is repeated for all the months that are to be taken into consideration.

2. Average Monthly Return Calculation:

The average portfolio monthly return is calculated by taking the mean of values arrived at in step 1.

3. Standard Deviation of Returns calculation:

The values arrived at in step 1 are used and the standard deviation is calculated using the following formula:

Sharpe Ratio Of Portfolio (With Marketxls)

4. Calculation of Ex-Post Sharpe Ratio:

Consequently, all necessary components to calculate Sharpe ratio are available we can simply calculate the Sharpe ratio as follows:
Sharpe Ratio Of Portfolio (With Marketxls)Note: Since the calculation is based on monthly data, a monthly estimation of the risk-free-rate should be used.

5. Annualising the Resultant Ratio:

The Ex-Post Sharpe ratio is annualized by multiplying the value attained in Step 4 with the square root of 12. This number is used because monthly data is utilized for the given stocks.

Calculate Sharpe Ratio of Portfolio in Excel

Especially relevant is that we can calculate the Sharpe ratio of the 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 same for your portfolio. The template is flexible to ensure scalability to different portfolios.

Let us review the steps involved in calculating the 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:

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

Note: Here, Price 1 is the adjusted close of the last day of the present period, and price 0 is the adjusted close of the last day of the previous period. The period could be days, weeks, months or even years.

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(Periodic Returns)

 

4. Calculation of Ratio

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

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

5. Annualise Ratio

Finally, to facilitate comparison among different portfolios, annualize the Sharpe ratio by multiplying it with the annualizing factor as follows:

Annualizing Factor = SQRT(Number of Periods a Year)

For instance, if taking daily data, seeing as a trading year has 252 days, multiply the ratio with the square root of 252 to arrive at the annualized value of Sharpe Ratio.

Similarly,

        • for yearly returns, annualizing factor = SQRT(1)
        • for monthly returns, annualizing factor = SQRT(12)
        • for weekly returns, annualizing factor = SQRT(52)
        • for daily returns, annualizing factor = SQRT(252)

Ex-ante Sharpe ratio:

Consequently, the ex-ante variation can also be calculated using the method stated above, by simply replacing the average portfolio returns with expected returns.

Also Read:

Check out this impartial review of MarketXLS from trusted advisors at The Stock Dork!

Interested in building, analyzing and managing Portfolios in Excel?
Download our Free Portfolio Template
I agree to the MarketXLS Terms and Conditions
Call: 1-877-778-8358
Ankur Mohan MarketXLS
Welcome! I'm Ankur, the founder and CEO of MarketXLS. With more than ten years of experience, I have assisted over 2,500 customers in developing personalized investment research strategies and monitoring systems using Excel.

I invite you to book a demo with me or my team to save time, enhance your investment research, and streamline your workflows.
Implement “your own” investment strategies in Excel with thousands of MarketXLS functions and templates.

We respect your privacy and will never share your email.
MarketXLS is a complete Excel stock solution

Kevin Hsu

StockKevin.com

I have used lots of stock and option information services. This is the only one which gives me what I need inside Excel

Lloyd Lenase

Option Day Trader

MarketXLS is a data junkie’s dream. It gives me the flexibility to mine for hidden treasures.

Dave

Swing trader since 2011

I like to access historical closing prices on a particular date. That makes tracking performance easy.

Patrick Cusatis, Ph.D., CFA

Associate Professor of Finance - Penn State University

Get Access to 1 Billion Usable Market data points IN YOUR EXCEL SHEETS WITH EASY TO USE EXCEL FUNCTIONS

Get started today

🎉 Exciting news! 🎉

You are invited to join our Discord Channel.

Interact, learn, and grow with experts in the markets!

Join our Discord