Portfolio Optimization

# How to Calculate the Sortino Ratio in Excel

Used in post-modern portfolio theory (PMPT), the Sortino Ratio punishes only downside risk relative to some required return (usually zero). Unlike the Sharpe ratio, which punishes any type of volatility–up or down. It has some rationality to it–after all, I’ve never had someone call me mad about upside vol.

The discrete form of the Sortino Ratio is $\displaystyle SR = \frac{ r_p - r_t }{ \sqrt{ \frac{ \sum_i \min (0, r_t - r_i)^2 }{ n } } }$

Where r_p = portfolio return, r_t = target return, r_n = return at point i, and n is the number of observations.

The mathematical expression above is confusing. I personally use a heuristic (“heuristic” is a fancy word for mental shortcut, but “shortcut” sounds lazy so we assign a fancy word to it) proposed by Estrada (2008), which also allows us to optimize portfolios using a semideviation approach.

Here is how to calculate the Sortino Ratio in Excel:

(1) List your return observations for each asset class. (2) Specify your target return, and (3) add a column called “Conditional Return.”

(4) Put in the “conditional return” formula for each observation. Basically, we want to tell Excel that if our observed return minus our target return is bigger than 0%, then write 0% (because we hit our target return, thus no downside risk). But, if our observed return minus our target return is less than 0%, then write that number (because we did not hit our target, this is downside risk). The formula for that is (from picture above):

=IF((B6-$C$3)>0,0,(B6-$C$3))

The \$ sign around C3 isn’t bling, it is telling Excel not to change that when we copy and paste down our observations.

(5) Now we need to calculate the semideviation (which is the denominator in the Sortino Ratio). Basically, this is the square root of the average “Conditional Return” squared. To accomplish this in Excel, in the cell under “Target Return” add the formula:

=SQRT((AVERAGE(C6:C9)*AVERAGE(C6:C9)))

Now we are getting somewhere! Getting to the semideviation is the hardest part.

(6) From here we can input the remaining terms of the Sortino Ratio. Adding the Sortino Ratio formula to cell C2, we can use the formula:

=(AVERAGE(B6:B9)-C3)/C4

And that is your Sortino Ratio! Looks like it is a terrible investment!

Just to compare, let’s see how we evaluate this investment from a mean-variance (i.e. traditional) approach.

The Sharpe ratio is 0.17–also pretty terrible. It is likely that both a mean-variance portfolio optimizer and a semideviation portfolio optimizer would ignore this asset completely.

But, that is how you calculate the Sortino Ratio.

References
Rom, Brian and Ferguson, Kathleen. “A Software Developer’s View: Using Post-Modern Portfolio Theory to Improve Investment Performance Measurement” Chapter 5 of Managing Downside Risk in Financial Markets, pp 59 – 73.

Estrada, Javier. “Mean-Semivariance Optimization: A Heuristic Approach” Journal of Applied Finance, Spring/Summer 2008, pp. 57 – 72

Standard