Semivariance Excel Example

The most in-demand topic on this blog is for an Excel semivariance example. I have posted mathematical semivariance formulas before, but now I am providing a description of exactly how to compute semivariance in “vanilla” Excel… no VBA required.

The starting point is row D. Cell D$2 contains average returns of over the past 36 months. The range D31:D66 contains those returns.  Thus the contents of D$2 are simply:

=AVERAGE(D31:D66)

This leads us to the semivariance formula:

{=SQRT(12)*(SQRT(SUM(IF((D31:D66-D$2)<0,(D31:D66-D$2)^2,0))/(COUNT(D31:D66-1))))}

We will now examine each building block of this formula starting with

IF((D31:D66-D$2)<0,(D31:D66-D$2)^2,0)

We only want to measure “dips” below the mean return. For all the observations that “dip” below the mean we take the square of the dip, otherwise we return zero. Obviously this is a vector operation, the IF function returns a vector of values.

Next we divide the resulting vector by the number of observations (months) minus 1. We can simply COUNT the number of observations with COUNT(D31:D66-1).  [NOTE 1: The minus 1 means we are taking the semivariance of a sample, not a population. NOTE 2: We could just as easily taken the division “outside” the SUM — the result is the same either way.]

Next is the SUM. The following formula is the monthly semivariance of our returns in row D:

{=SUM(IF((D31:D66-D$2)<0,(D31:D66-D$2)^2,0))/(COUNT(D31:D66-1))}

You’ll notice the added curly braces around this formula. This specifies that this formula should be treated as a vector (matrix) operation.  The curly braces allow this formula to stand alone.  The way the curly braces are applied to a vector (or matrix) formula is to hit <CTRL><SHIFT><ENTER> rather than just <ENTER>. Hitting <CTRL><SHIFT><ENTER> is required after every edit.

We now have monthly semivariance. If we wanted annual semivariance we could simply multiply by 12.

Often, however, we ultimately want annual semi-deviation (also called semi-standard deviation) for computing things like Sortino ratios, etc. Going up one more layer in the call stack brings us to the SQRT operation, specifically:

{=SQRT(SUM(IF((D31:D66-D$2)<0,(D31:D66-D$2)^2,0))/(COUNT(D31:D66-1)))}

This is monthly (downside) semi-deviation. We are just one step away from computing annual semi-deviation. That step is multiplying by SQRT(12), which brings us back to the big full formula.

There it is in a nutshell. You now have the formulas to compute semivariance and semi-deviation in Excel.

 

 

Advertisement