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.

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.

Buy Sharpe Ratio and Sortino Ratio Excel Templates

These templates are open and have no password. Please feel free to modify as per your needs and extend them for your portfolio. Analyze your portfolios like never before.

• MarketXLS Sharpe Ratio Template
• MarketXLS Sortino Ratio Template

Instant Delivery

Secure Processing