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



> [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