Boom1 said:
If I do it on the return itself, one month mar 2006 will be in the
lower weighted category but as time goes on, it will eventually
be in the higher weighted category--i cant figure out how to
account for this...
I do not understand this comment. First, I do not see what this
has to with using the "return itself" v. anything else, e.g. the
linest()
results.
Second, I do not see how a month moves from a "lower" weight
to a "higher" weight -- at least, not according to the way that I
suggested setting up the weights, following your description.
(Your description was ambiguous. I assumed you wanted each of
the more-recent 3 months to be weighted more than each of the
previous 9 months.) Perhaps this is just a semantic misunderstanding.
Are you calling 1/6 "lower" and 1/18 "higher"? If so, you have it
reversed. In any case, the whole point of a moving average is for
data to move from one "weight category" into the other as the
average "moves" in time. I do not see that as something to "account
for" or avoid.
sorry--i dont think i actually explained this correctly. I have returns
for 24 months for our fund and then for the indices that we're measure
our exposure to. To get the betas, i'm using the linest function in
excel, using the last12M of data--i'm showing 12 data points and so i
think i need the linest function to incorporate the moving average.
There may be a couple ways that this could be done. But first, I
wonder if one of us does not understand what is the right thing to
do statistically. Linest() is a method of determining a best-fit
straight
line for some data. A moving average is a method for reducing the
volatility of data. Should the input to linest() be the raw data or
the
moving-averaged data?
There is nothing "wrong" with computing the linest() of the moving
average. But it is like passing white light through a series of
colored
filters: each filter removes some degree of detail. Conclusions
about the filtered light (moving average) might not reflect the true
behavior of the original source (actual returns).
My (weak) understanding of stock "beta" is: it is measure of
volatility against an index (or another security). By using the moving
average to waterdown the volatility of one or both sets of data, I
would think that the beta would be misleading. And in any case,
I do not see what this has to do with linest(). Hmm, perhaps there
different definitions of "beta".
Having said that, to determine the linest() results for the moving
average, I would simply create a "helper column" with the moving
averages, and pass that column to linest() instead of the original
data.
That seems "too obvious". So I wonder if I am misunderstading
the question or the intended objectives. Off-hand, I do not see
any way to avoid the "helper column" -- other than use a macro.
But perhaps someone else can see how to build an array formula
or perhaps use sumproduct() in some esoteric fashion (grumble!).
HTH. I am very interested in what you are trying to calculate. So
I hope you post back with more information, if I have missed the
mark. A mathematical description of what you want to compute
might be helpful, to me at least ;-).
