Sharpe Ratio Of Portfolio (With 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.

Where:

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:

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

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.

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:

• 1. Sortino Ratio in Excel

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