Compute the semideviation
The semideviation measures the amplitude of below-average returns. Investors sometimes prefer using it in place of the volatility because this metric focuses on the downside risk of an investment.
In this exercise, your goal is to compute the semideviation of ABC historical returns.
To do so, take the square root of
[(R_1-Average)^2 + (R_2-Average)^2 + ... + (R_L-Average)^2] / L
,
where R_1, R_2, …, R_L are the L returns lower than the average return.
Use a combination of SUMIFS()
, COUNTIFS()
, and SQRT()
. Recall that to write a logical condition, include it within quotation marks “… ”
and if necessary, use the concatenation operator &
(for example, “<”&H4
means smaller than cell H4
).
This exercise is part of the course
Financial Analytics in Google Sheets
Exercise instructions
In
E3:E62
, compute the squared deviations from the mean.In
H10
, useSUMIFS()
to compute the sum of squared deviations. Consider only below-average returns. This is the numerator of the formula.Next, use
COUNTIFS()
to count below-average returns. This is the denominator of the formula.Finally, use
SQRT()
to compute the semideviation.
Hands-on interactive exercise
Turn theory into action with one of our interactive exercises
