Downside Deviation

G

Guest

Can anyone tell me how to create a formula for downside deviation in excel? Downside deviation considers only returns that fall below a defined Minimum Acceptable Return (MAR) rather then the arithmetic mean. For example, if the MAR is assumed to be 10%, the downside deviation would measure the variation of each period that falls below 10%. For the following stream of returns (50 observations), the downside deviation (assuming an MAR of 10%) is approximately 0.95% (or 3.29% annualized). Further info on the definition can be found at http://support.pertrac2000.com/statistics2000.asp. I just am having trouble building a formula in excel...any help would be greatly appreciated. Thanks in advance

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%

tyrian

Can anyone tell me how to create a formula for downside deviation in excel? Downside deviation considers only returns that fall below a defined Minimum Acceptable Return (MAR) rather then the arithmetic mean. For example, if the MAR is assumed to be 10%, the downside deviation would measure the variation of each period that falls below 10%. For the following stream of returns (50 observations), the downside deviation (assuming an MAR of 10%) is approximately 0.95% (or 3.29% annualized). Further info on the definition can be found at http://support.pertrac2000.com/statistics2000.asp. I just am having trouble building a formula in excel...any help would be greatly appreciated. Thanks in advance

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%