How to Calculate Semi Variance in Excel

Semi Variance evaluates the variation among the observations in a sample. Semi-variance deals with the observations that are below the average of the dataset, or mean, or a target value only. The general formula to calculate semi-variance is:

Semi Variance = sum of ((observed value below mean – mean)^2) / number of observations below mean

The dataset below contains month-wise investment returns.

To calculate semi-variance and estimate the downside risk of this investment:

Method 1 – Combine the SUM and the COUNT Functions to Calculate Semi Variance in Excel

STEPS:

Select C18.
Use the formula:

=AVERAGE(C5:C16)

Press Enter.
The AVERAGE function will return the average of the dataset.

Click D5.
Enter the formula:

=IF(C5:C16<D18,C5:C16,"")

Press Enter.
Drag down the Fill Handle.
The IF function returns values smaller than the average: D18.

Select D19.
Use the formula:

=AVERAGE(D5:D16)

The new mean of the returns less than the original sample average is returned.

Click E5.
Enter the formula:

=IF(D5<>"",(D5-$D$19)^2,"")

Press Enter and apply the AutoFill.
The square of the differences will be returned.

To calculate the semi-variance, select D20.
Enter the formula:

=SUM(E5:E16)/COUNT(E5:E16)

Press Enter.
The COUNT function determines the total count of the cells with numbers in E5:E16.
The semi-variance is displayed.

Method 2 – Applying the SUM, IF, and COUNTIF Functions to compute the Semi Variance

STEPS:

Select C18.
Enter the formula:

=AVERAGE(C5:C16)

The sample mean is returned.

Select C19.
Enter the formula:

=AVERAGE(IF(C5:C16<C18,C5:C16,""))

The IF function returns values smaller than the sample mean.
The AVERAGE function calculates the average of the IF(C5:C16<C18,C5:C16,””) formula outputs.

In C20, use the formula:

=SUM(IF(C5:C16<C18,(C5:C16-C19)^2,""))/COUNTIF(C5:C16,"<833.75")

Press Enter.
The IF function determines the square of the differences between each value in C5:C16 smaller than the old mean (C18) and the new mean in C19.
The SUM function sums the IF(C5:C16<C18,(C5:C16-C19)^2,””) formula outputs.
The COUNTIF function counts the number of cells smaller than 75 or the old mean.
This is the semi-variance.

Method 3 -Using the VAR.P Function to Calculate the Semi Variance in Excel

STEPS:

Calculate the mean in C18, following the steps described in the previous methods.
Select C19.
Enter the formula:

=VAR.P(IF(C5:C16<C18,C5:C16))

Press Enter.
The IF function returns the values less than C18 (the sample mean).
The semi-variance is displayed.

How to Calculate the Semi Deviation in Excel

STEPS:

Find the mean in C18.
Select C19.
Enter the formula:

=STDEV.P(IF(C5:C16<C18,C5:C16))

Press Enter.
The STDEV.P function calculates the standard deviation.
The IF function to finds the semi-deviation and returns values smaller than C18 (the sample mean).
The semi-deviation is displayed.