Weighted moving average

I'm trying to created a weighted moving average calculation for the
betas I have calculated in excel, which use monthly data for the past
12M-- i would like to have half the weight on the prior 3 months, and
the other half of hte weight on the 9 months prior. I'm having problems
figuring out how to come up with the weights and how to do this in
excel. Any suggestions?

Boom1 wrote:
> I'm trying to created a weighted moving average calculation for the
> betas I have calculated in excel, which use monthly data for the past
> 12M-- i would like to have half the weight on the prior 3 months, and
> the other half of hte weight on the 9 months prior. I'm having problems
> figuring out how to come up with the weights and how to do this in
> excel. Any suggestions?

The key is: the sum of the weights should equal one. You have 12
data items. The sum of the weights for 3 items should be 1/2, and
the sum of the weights for 9 items should be 1/2. So the weights
are (1/3)*(1/2) and (1/9)*(1/2) respectively, which is 1/6 and 1/18.

The Excel formulation can be done by setting up the formula for the
most-recent cell of one 12-month period, then copy the formula to
the cells of each consecutive 12-month period. Excel will change
the relevative cell references accordingly.

For example, if your monthly data is in column A, where A1 is the
most recent data and B1 is the moving average for A1:A12, then
B1 is:

=sum(A1:A3)/6 + sum(A4:A12)/18

Copy B1 to B2:B(n-11), when "n" is the row number of the least
recent data. B2, for example, will become:

=sum(A2:A4)/6 + sum(A5:A13)/18

great--thx a ton!
[email protected] wrote:
> Boom1 wrote:
> > I'm trying to created a weighted moving average calculation for the
> > betas I have calculated in excel, which use monthly data for the past
> > 12M-- i would like to have half the weight on the prior 3 months, and
> > the other half of hte weight on the 9 months prior. I'm having problems
> > figuring out how to come up with the weights and how to do this in
> > excel. Any suggestions?
>
> The key is: the sum of the weights should equal one. You have 12
> data items. The sum of the weights for 3 items should be 1/2, and
> the sum of the weights for 9 items should be 1/2. So the weights
> are (1/3)*(1/2) and (1/9)*(1/2) respectively, which is 1/6 and 1/18.
>
> The Excel formulation can be done by setting up the formula for the
> most-recent cell of one 12-month period, then copy the formula to
> the cells of each consecutive 12-month period. Excel will change
> the relevative cell references accordingly.
>
> For example, if your monthly data is in column A, where A1 is the
> most recent data and B1 is the moving average for A1:A12, then
> B1 is:
>
> =sum(A1:A3)/6 + sum(A4:A12)/18
>
> Copy B1 to B2:B(n-11), when "n" is the row number of the least
> recent data. B2, for example, will become:
>
> =sum(A2:A4)/6 + sum(A5:A13)/18

Boom1 wrote:
> 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 ;-).