Sortino Ratio - Excel

Hi all,

In my company there seems to be a bit of confusion over the sortino ratio and how exactly we are calculating this.

Conventionally: Rp - Target Return / Downside Deviation (vol)

Target return we have set to 0.

For the downside deviation various articles are showing different formulas.

We have decided to essentially take the return stream and apply an IF formula to say any returns that are below 0 are shown, if they are above 0, make them equal to 0.

The downside deviation is then caluclated as the Sum of Squares of the negative returns / TOTAL number of observations (including the 0’s)

Some articles show that N (total periods) should be all periods including 0, some suggest N should just be for the negative periods as that is what we are trying to work the volatility on.

Can anyone please explain how they would calculate this on a return stream? If you are able to share your excel formula that would be really useful.


We have also seen some articles just use a semi-deviation as the denominator. If anyone can clear this up that would be great. We are a classic CTA hedgefund so if anyone is in this industry and would share their approach thats great.

I work for a CTA in London and we went through the same thought process you guys are going through. We settled on the following calculation:

where you keep the zero values and include those time periods in the denominator. The thing that tipped it for me was the logic that the frequency of downside returns is also important, not just the magnitude.

This is great S666 and confirms my thoughts too. Perfectly summed it all up. Thanks for sharing.


No probs, glad it helped.

Is it true that you use the average return and not the annualized?