Home MarketXLS
Dashboard MarketXLS
Screener MarketXLS
Options Profit Calculator MarketXLS
Stock Ranks MarketXLS
Spreadsheet Builder MarketXLS
Logout MarketXLS
Portfolio analysis and optimizationPortfolio Management

Sharpe Ratio Of Portfolio (With Marketxls)

Written by admin
Fri Jun 16 2017
Sharpe Ratio of Portfolio
See how MarketXLS helps you take advantage in the markets.
Download Option Templates →
Sharpe Ratio of Portfolio

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.


  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.


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.


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.



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.


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:

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:

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:
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.


        • 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
Download Option Templates
Stock screens
Public saved screens

Top 100 Gainers Today

Top 100 losers Today

Call: 1-877-778-8358
Ankur Mohan MarketXLS
I am so happy you are here. My name is Ankur and I am the founder/CEO of MarketXLS. Over the past four years, I have helped more than 2500 customers to implement their own investment research strategies and monitoring systems in Excel.
Implement “your own” investment strategies in Excel with thousands of MarketXLS functions and starter sheets.
Get started today
Search for a stock

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

Get started today

Top MarketXLS Rank stocks

Oppenheimer Holdings Inc. Class A (DE) logo

Oppenheimer Holdings Inc. Class A (DE)

Optionable: Yes
Market Cap: 451 M
Industry: Capital Markets
52 week range    
Midland States Bancorp Inc. logo

Midland States Bancorp Inc.

Optionable: No
Market Cap: 589 M
Industry: Banks - Regional - US
52 week range    
Kroger Company (The) logo

Kroger Company (The)

Optionable: Yes
Market Cap: 35,325 M
Industry: Grocery Stores
52 week range    
Ingles Markets Incorporated Class A Common Stock logo

Ingles Markets Incorporated Class A Common Stock

Optionable: Yes
Market Cap: 1,910 M
Industry: Grocery Stores
52 week range    
Amdocs Limited logo

Amdocs Limited

Optionable: Yes
Market Cap: 10,525 M
Industry: Software - Infrastructure
52 week range    
Tecnoglass Inc. logo

Tecnoglass Inc.

Optionable: Yes
Market Cap: 1,349 M
Industry: Building Materials
52 week range    
Interpublic Group of Companies Inc. (The) logo

Interpublic Group of Companies Inc. (The)

Optionable: Yes
Market Cap: 13,330 M
Industry: Advertising Agencies
52 week range    
Guess? Inc. logo

Guess? Inc.

Optionable: Yes
Market Cap: 1,107 M
Industry: Apparel Stores
52 week range    
Bristol-Myers Squibb Company logo

Bristol-Myers Squibb Company

Optionable: Yes
Market Cap: 168,030 M
Industry: Drug Manufacturers - Major
52 week range    
Best Buy Co. Inc. logo

Best Buy Co. Inc.

Optionable: Yes
Market Cap: 18,947 M
Industry: Specialty Retail
52 week range    

More Features