I want to calculate the SD of monthly returns, as well as the SD of only the
positive monthly returns and negative monthly returns.
The SD of the monthly returns is easy, but the SD of the positive or
negative returns is in error because I do not know how to calculate them, at
least in AFL.
If MnGain is the monthly return
Then SDMonthly = StDev(MnGain, LastValue(Period));
However, to determine positive monthly gains I use
MnGainUp = IIF(MnGain>0, MnGain, 0);
MnGainDn = IIF(MnGain<=0,MnGain,0);
Then SDUpMonthly = StDev(MnGainUp,Period);
is overstated because of the zeros which are within the range of gains on
which the SD function operates.
Take out the zeros in a column of MnUpGains within Excel and then take the
SD of the resulting series, and you get lower SDs compared to those with the
zeros in the series.
But, using
MnGainUp = IIF(MnGain>0, MnGain, Null);
MnGainDn = IIF(MnGain<=0,MnGain,Null);
(which is the same as taking out the zeros manually in Excel) blows up the
StDev calculation in AB (values in the millions).
The internal AB calculation of StDev apparently does not like Nulls within
the series---or else I am doing it wrong.
Anyone have a suggestion on getting the correctly calculated standard
deviations of just the up gains and down gains??
Ken
