Expert: Josh - 11/23/2009

Question
QUESTION: Unlike a simple arithmetic mean or simple average, I want to give more weight to the more recent data points:
For example, the investment returns for each of the last 3 months is as follows: Nov = 3%, Oct = 6%, Sept= 8%,  The average return is 17/3 or 5.66%, but if I wanted to give more weight to the most recent months to yield a Weighted Average Return, how would I do that?

My attempt at calculating this weighted average is :

(3% * 3 ) + (6% *2) + (8% *1) / 6 = 3.50%

Is this the correct way of weighting or would an exponential average be better to give more weight to the most recent data.

Thank You for your time
Dennis

ANSWER: Hi Dennis,

There are many ways to compute the weighted average. Basically, the only mathematical requirement is that the weights all add up to one. I can say that the equation you gave is proper, since the sum of the weights {3/6, 2/6, 1/6} is unity. Whether it is appropriate to your needs is not something that I can comment. In part, it depends on how much emphasis you want to give to the most recent observations.

There are two aspects to consider when we want to calculate a weighted average.
1. Memory -- how far back in time do you wish to go when exploiting historical data.
|  In your example, the weighted average only depends on data in the last three months.
|  If we let R(n) be the investment return for the n^{th} month, then the weighted
|  average may be written as R(n) = w1*R(n-1) + w2*R(n-2) + w3*R(n-3). We need
|  w1+w2+w3=1, also, each weight (w1,w2 and w3) must be between 0 and 1.

2. Model for the weights -- As you have alluded to before,
  the weights can decay linearly, exponentially or in some other manner.
|  Example 1 [Linear weights]: w1=3/6, w2=2/6, w3=1/6.
|  Example 2 [Exponential weights]:
|    Let {w1,w2,w3} form a geometric sequence.
|    i.e., assume w1=a, a<1, w2=a*a, w3=a*a*a.
|    Then, to satisfy the unity requirement, w1+w2+w3=1, we need a+a^2+a^3=1.
|    Using the formula for a finite geometric series, this is same as
|    a(1-a^3)/(1-a)=1. Simplifying, we have a-a^4=(1-a), a^4-2a+1=0.
|    This quartic polynomial can be solved numerically, or using the technique in
|    [http://mathworld.wolfram.com/QuarticEquation.html].
|  Approximate solution: w1=a=0.543689, w2=a^2=0.295597, w3=a^3=0.160714.

Re: Is this the correct way of weighting?
Ans: If the objective is to predict the future return on investment, based on the recent past, then we have got to ask two questions. (a) Is the present rate of return pretty much independent of the observed data beyond the last 3 months? [i.e., older historical data does not inform us about the current trend, to what extent is this true?]  (b) Does our model (a simple one here) capture the underlying process? [i.e., if our goal is to predict changes, how well does it match reality?]  It might do better than a simple average. The only way to know for sure is to test and compare different models.

---------- FOLLOW-UP ----------

QUESTION: Hi Josh,

Thank you for your timely and well presented response.  You have given the Quartic Equation weights for a three(3)memory(history) sample as w1=54, w2=30, w3=16. Can you define the appropriate weights for a six(6) period Quartic Equation ?
Lastly, can you define why one might choose the Quartic equation weighting(54,30 %26 16)vs the simpler Weighted Average of w1=50, w2=33 %26 w3=17!.

Thank You again

Dennis

ANSWER: Hi Dennis,

For a weighted average of the last 6 months based on an exponential weight model, we have a sixth order (sextic) polynomial. [Note: A quartic is just a fourth degree polynomial]

To satisfy w1+w2+w3+...+w6=1, we need a(1-a^6)/(1-a)=1
Solving a^6-2a+1=0, a=0.504138 (to 6 decimal pl.)

i.e., w1=a, w2=a*a, w3=a*a*a and so forth.
w2=0.254155, w3=0.128129, w4=0.064594, w5=0.032564, w6=1-(w1+w2+w3+w4+w5)

Which average you use depends on your objective.
If you are using weighted average to compute a short-term statistic, there may not be a strong preference one way or the other. If the data changes rapidly, using the simple average will help you smooth the data out more. On the other hand, using a weighted average which gives greater importance to the previous month will retain more of the underlying random fluctuation (it will be quite "noisy").

If you want to use the weighted average to predict the investment return for the current month, then you would like your prediction P(n) (the weighted average, or simple average) to be close to the actual investment return R(n) for the current month. The discrepancy between P(n) and R(n) gives us the prediction error.

How can we evaluate which is the better predictor, i.e., which one captures the underlying process?

For a m^{th} order predictor, P(n)=w1*R(n-1)+w2*R(n-2)+...+wm*R(n-m). Suppose we fix m=3.

Using linear weights, we can measure the prediction error as E_linear(n)=P_linear(n)-R(n), where P_linear(n) means we compute the P(n) term using specifically the chosen weights w1=1/2, w2=1/3 and w3=1/6.

Using exponential weights, we can measure the prediction error as E_exp(n)=P_exp(n)-R(n), where P_exp(n) means we compute the P(n) term using specifically the chosen weights w1=0.544, w2=0.295 and w3=0.161.

Using simple average, w1=w2=w3=1/3. We can measure the prediction error as E_av(n)=P_av(n)-R(n).

We can observe E_linear(n), E_exp(n) and E_av(n) over a 24 month period, say (just a suggestion). Using historical data, we estimate the average error as A{E_linear} = [E_linear(4)+E_linear(5)+...+E_linear(27)]/24. A{E_exp} and A{E_av} are similarly defined. If A{E_linear} is the smallest of the three, then the 3rd order model based on linear weights is the best predictor amongst those we have considered.

EXAMPLE: To show how things are computed (not how it should be done)

True data for investment return: R(1)=12, R(2)=10, R(3)=15, R(4)=15, R(5)=16,...

Here, we consider only a third order predictor (m=3). The general prediction formula is P(n)=w1*R(n-1)+w2*R(n-2)+w3*R(n-3).

P_linear(4)=(1/2)*15+(1/3)*10+(1/6)*12=12.833333
P_linear(5)=(1/2)*15+(1/3)*15+(1/6)*10=14.166666
...

P_av(4)=(1/3)*15+(1/3)*10+(1/3)*12=12.333333
P_av(5)=(1/3)*15+(1/3)*15+(1/3)*10=13.333333
...

P_exp(4)=0.543689*15+0.295597*10+0.160714*12=13.039873
P_exp(5)=(1/3)*15+(1/3)*15+(1/3)*10=14.19643
...

E_linear(4)=P_linear(4)-R(4)=-2.1666
E_linear(5)=-0.8333
E_av(4)=-2.6666
E_av(5)=-1.6666
E_exp(4)=-1.9601
E_exp(5)=-0.8035

Hence, average prediction error
A{E_linear}=(-0.8333-2.6666)=-1.5
A{E_av}=-2.166
A{E_exp}=-1.3818

From our limited observation, for this set of data, the "exp" weighted average model works best.

| This is not ideal, it is silly to assess the performance
| of our predictors based upon two error points.
| In practice, we should probably keep going and observe
| their behavior for a longer period to get a more reliable
| estimate of the prediction error......but you get the idea



---------- FOLLOW-UP ----------

QUESTION: Hi Josh,

Again, you work is terrific. But, I don't understand the example below:

If the most recent true returns are R(1#=12, R#2#=10, R#3#=15, wouldn't the weighted average result be as follows:

P_linear#4#=(1/2#*12+#1/3#*10+#1/6#*15=11.8000

not

P_linear#4#=#1/2#*15+#1/3#*10+#1/6#*12=12.833333

Thank You again!

Dennis







EXAMPLE: To show how things are computed #not how it should be done#

True data for investment return: R#1#=12, R#2#=10, R#3#=15, R#4#=15, R#5#=16,...

Here, we consider only a third order predictor #m=3#. The general prediction formula is P#n#=w1*R#n-1#+w2*R#n-2#+w3*R#n-3#.

P_linear#4#=#1/2#*15+#1/3#*10+#1/6#*12=12.833333
P_linear#5#=#1/2#*15+#1/3)*15+#1/6)*10=14.166666
...


Answer
Okay, if I haven't missed anything, your question is asking why
|  P_linear(4)=(1/2)*15+(1/3)*10+(1/6)*12=12.833333 [#]
and not
|  P_linear(4)=(1/2)*12+(1/3)*10+(1/6)*15=11.8000

The number P(3)=15 corresponds to the observed data for the most recent month.
If we think of (4) as April, then the previous month "March" would have an index of (3). This carries much more weight than "February" and "January" which have
- an index of (2) and (1), respectively;
- a value of 10 and 12, respectively;
- a weight of 1/3 and 1/6 (in the case of linear weights), respectively.

We can look at R(1) as the investment return for January. This is further back in time relative to R(3), the observed investment return for March. So, the line [#] is correct.

I hope this makes sense.