Weighted averages await

Our company often reviews / audits others’ financial models and over the past few weeks we have seen the same scenario on several occasions. Consider the following scenario:

Here, various amounts of annual expenditure are incurred:

• $100,000 p.a. for the first seven years
• $90,000 p.a. for the next two years (years 8 and 9)
• $75,000 p.a. for the next six years (years 10 through 15)
• $110,000 p.a. for the next 10 years (years 16 to 25 inclusive)
• $98,400 p.a. for the final five years (years 26 to 30 inclusive).

The question is, what is the average annual expenditure? Several of our clients had simply used the AVERAGE function, but this takes the arithmetic average of the five values, ie

=AVERAGE(H14:H18)

which equates to $94,680. This is incorrect, as the five durations are not of similar length, and understates the true weighted average of $97,400 by nearly 3%, which may be significant in times of significant cash constraints, for example.

The correct average is easy enough to calculate with a helper column, viz.

For each row, I may calculate the total costs by multiplying the cited expenditure by the duration (i.e. the number of relevant periods), eg for cell M14, the formula would be

=(K14-J14+1)*H14

At this point, do note that there is already an opportunity for a modelling error to occur. Many modellers will calculate the duration by subtracting the first period from the last period. This is not right, as this will exclude the first period: the number of periods is actually equal to the last period less the first period plus one, given by

K14-J14+1

in the above formula.

The value in cell M13 (above) simply adds these values together and divides by the total number of periods:

=SUM(M14:M18)/MAX(J14:K18)

That's simple enough and probably not worthy of an article in its own right.

However, the reason that has driven me to write about this is that many do not use helper columns but try and write the entire calculation all in one cell. So, how do you do that?

I suggest the following formula:

=SUMPRODUCT(H14:H18*((K14:K18)-(J14:J18)+1))/MAX($J$14:$K$18)

Just to explain, and as a reminder of SUMPRODUCT, consider the following side example:

The sales in column H are simply the product of columns F and G, for example, the formula in cell H12 is simply =F12*G12. Then, to calculate the entire amount cell H19 sums column H. This could all be performed much quicker using the following formula:

=SUMPRODUCT(F12:F17,G12:G17)

You can multiply the vectors together instead:

=SUMPRODUCT(F12:F21*G12:G21)

This will produce the same result, and this is what is required in more complex scenarios.

Here, I have chosen to use the multiplication operator to make interpretation of the formula clearer, but using comma will achieve the same results. The formula

=SUMPRODUCT(H14:H18*((K14:K18)-(J14:J18)+1))/MAX($J$14:$K$18)

subtracts column J from column K and adds one to calculate the number of periods (i.e. the duration) on an array basis

=(K14:K18)-(J14:J18)+1

In our example,

this would produce the durations 7, 2, 6, 10 and 5 for rows 14, 15, 16, 17 and 18 respectively.

This duration is then multiplied by each cost on a row by row basis to obtain $700,000, $180,000, $450,000, $1,100,000 and $492,000 respectively for a grand total of $2,922,000:

=SUMPRODUCT(H14:H18*((K14:K18)-(J14:J18)+1))

Finally, this is divided by the total number of periods, which may be determined by calculating the maximum period number in the range (given by MAX($J$14:$K$18)), viz.

=SUMPRODUCT(H14:H18*((K14:K18)-(J14:J18)+1))/MAX($J$14:$K$18)

Please refer to the attached Excel file for a modelled example.

Word to the Wise

This is a common calculation that is used to normalise amounts for valuation purposes, calculate depreciation, determine expected values, required funding, etc. Therefore, it is an essential technique to compute correctly.

However, many modellers make errors with this calculation as they strive to construct it all in one cell. To address this, I have created such a formula, but I would recommend it is preferable to step out such a calculation in a more longhand fashion (such as using helper column, detailed above) as it makes it both easier for end users to understand and lessens the risks of calculation errors.