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.
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.
Why spend thousands on institutional research & data platforms when you can get it all in Excel for just around $40 a month? Get access to 1 Billion market data points in your Excel. Get your MarketXLS Demo
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 our Sharpe Ratio and Sortino Ratio Excel Templates for just $79
Special Offer: Get the templates at a special price of $89 Before it's too Late!
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
- FREE BONUS - GRAHAM VALUATION TEMPLATE
MarketXLS has a function called Sortino Ratio. You can simply use this function to calculate sortino ratio for a stocks.
=SortinoRatio("Stock Symbol","Start Date","End Date")
This function will report the Sortino Ratio of the stock within the time period specified.