Weighted moving average
 

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.
(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...



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