J
Jason
I am trying to create an excel function for downside
deviation (a statistic to measure downside risk based on
a series of investment returns). Downside deviation
considers only returns that fall below a defined Minimum
Acceptable Return (MAR). For example, if the MAR is
assumed to be 10%, the downside deviation would measure
the variation of each period that falls below 10%. The
formula is as follows:
Where Ri = Return for period I
Where N = Number of Periods
Where M = Period Arithmetic Mean
Where Rmar = Period Minimum Acceptable Return
Where Li = Ri - Rmar (IF Ri - Rmar < 0 )or 0 ( IF Ri -
Rmar >= 0 )
Downside Deviation = ( (Σ(Li)^2 )/N )^.5
For more clarity on the formula - go to
http://support.pertrac2000.com/statistics2000.asp and
choose "downside deviation" on the left side of the
screen. Bonus points if someone can help me also figure
out semi-deviation (right below downside deviation on the
website). I have a copy of the pertrac software but
would like to be able to indepently calculate the
statistic using an excel function. The Pertrac software
calculates this as 3.29% for the following stream of 50
data points:
7.10%
4.20%
4.00%
4.90%
4.80%
5.30%
(2.10%)
2.80%
0.60%
2.10%
3.00%
2.90%
1.50%
4.10%
3.20%
(1.50%)
2.00%
2.20%
(1.70%)
2.90%
0.20%
0.20%
3.10%
(0.60%)
2.60%
1.10%
2.20%
4.30%
5.40%
(0.10%)
2.00%
1.40%
(0.90%)
2.20%
0.50%
1.90%
(1.50%)
3.00%
2.30%
0.90%
(2.10%)
0.20%
5.90%
1.90%
2.10%
(0.90%)
3.20%
3.40%
0.60%
(0.50%)
I would be very grateful for someone's help with this.
Many thanks in advance.
-Jason
deviation (a statistic to measure downside risk based on
a series of investment returns). Downside deviation
considers only returns that fall below a defined Minimum
Acceptable Return (MAR). For example, if the MAR is
assumed to be 10%, the downside deviation would measure
the variation of each period that falls below 10%. The
formula is as follows:
Where Ri = Return for period I
Where N = Number of Periods
Where M = Period Arithmetic Mean
Where Rmar = Period Minimum Acceptable Return
Where Li = Ri - Rmar (IF Ri - Rmar < 0 )or 0 ( IF Ri -
Rmar >= 0 )
Downside Deviation = ( (Σ(Li)^2 )/N )^.5
For more clarity on the formula - go to
http://support.pertrac2000.com/statistics2000.asp and
choose "downside deviation" on the left side of the
screen. Bonus points if someone can help me also figure
out semi-deviation (right below downside deviation on the
website). I have a copy of the pertrac software but
would like to be able to indepently calculate the
statistic using an excel function. The Pertrac software
calculates this as 3.29% for the following stream of 50
data points:
7.10%
4.20%
4.00%
4.90%
4.80%
5.30%
(2.10%)
2.80%
0.60%
2.10%
3.00%
2.90%
1.50%
4.10%
3.20%
(1.50%)
2.00%
2.20%
(1.70%)
2.90%
0.20%
0.20%
3.10%
(0.60%)
2.60%
1.10%
2.20%
4.30%
5.40%
(0.10%)
2.00%
1.40%
(0.90%)
2.20%
0.50%
1.90%
(1.50%)
3.00%
2.30%
0.90%
(2.10%)
0.20%
5.90%
1.90%
2.10%
(0.90%)
3.20%
3.40%
0.60%
(0.50%)
I would be very grateful for someone's help with this.
Many thanks in advance.
-Jason
