June 21, 2017

Sortino Ratio in Excel

Sortino Ratio in Excel

We know that Sharpe ratio is one of the most common ratios to measure the reward versus risk of an investment opportunity. Sortino ratio is a modified version of Sharpe ratio. While Sharpe ratio measures the return over the overall risk (volatility) in the portfolio, Sortino ratio only considers the downside risk in the portfolio. In this article we will learn about what Sortino ratio is and how to calculate Sortino Ratio in Excel using MarketXLS functions.

Download Sortino Ratio Template for MarketXLS

What is Sortino Ratio?

Sortino ratio is a modified version of Sharpe Ratio. It is named after Dr. Frank Sortino from the Pension research Institute. A higher Sharpe ratio signifies relatively less risk. In other words, even if a portfolio’s performance is average and the risk is low, the Sharpe ratio will become large. This is, however, not enough because investors don’t just want to look at the average performance of a portfolio. And this is where Sortino ratio differs from Sharpe ratio. Instead of using standard deviation as denominator, the Sortino ratio uses only the downside deviation.

Th Sortino ratio measures excess  return to the risk of not meeting an investor’s MAR.

Sortino ratio  = (Mean Portfolio Returns – MAR)/Downside Deviation

Here MAR is the Minimum Accepted Return by the investor which may be an absolute return, an index return, risk-free rate, or even zero.

The important thing to note here is that Sortino ratio quantifies downside volatility without penalizing upside volatility thereby addressing the shortcomings of Sharpe ratio.

Using Sortino Ratio

Sortino ratio is suitable as a relative measure to compare performance of portfolios, one fund with another, or to compare a fund with a benchmark index. A higher ratio indicates better risk-adjusted performance. It indicates a low risk of incurring large losses (because it considers only downside volatility).

Let’s take an example to understand this. Let’s say you are considering investment in two funds A and B. Fund A has a return of 10% in the first year and -10% in the second year. Fund B has 0% return in first year and 20% return in second year.

For both Funds A and B, the total variance is the same, i.e., 20%. However, Fund B is definitely a better investment . Sharpe ratio  will not differentiate between the two investments. However, Sortino ratio will spot the negative volatility in Fund A and will be able to identify Fund B as a better investment.

Sortino Ratio in Excel

We have created a template to calculate Sortino Ratio in Excel.  Especially relevant is that we can calculate Sortino ratio in Excel using MarketXLS functions. In the template, where you can enter your stock portfolio and it will automatically calculate the Sortino Ratio of the portfolio. Also, the template is flexible and we can extend it to calculate Sortino 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 Excess Returns and Excess Negative Returns

Using the daily returns and MAR, you can calculate the excess returns and also the negative excess returns.

4. Calculate Average Annualized Excess Returns and Downside Volatility

We can now calculate the annualized excess returns and also the downside volatility using the negative excess returns.

5. Calculate Sortino Ratio

Finally you can calculate the Sortino ratio using the formula provided above.

Download Sortino Ratio Template for MarketXLS

Author: Ankur Mohan

Educated in Finance, Business and Statistics, Ankur is passionate about finance, investing, trading and programming.

Leave a Reply

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