ICAEW.com works better with JavaScript enabled.
Exclusive

Weighted averages await

Author: Liam Bastick

Published: 24 Jan 2022

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.
Sometimes, Excel problems are like buses. You don’t see a particular problem for a while and then suddenly, several come along, almost at the same time. That is exactly what happened with the subject of this article – calculating weighted averages where values change over time.
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:
Excel screenshot

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.

Excel screenshot

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:

Excel screenshot
=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:

Excel screenshot

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,

Excel screenshot

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.

Archive and Knowledge Base

This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.

Open AddCPD icon

Add Verified CPD Activity

Introducing AddCPD, a new way to record your CPD activities!

Log in to start using the AddCPD tool. Available only to ICAEW members.

Add this page to your CPD activity

Step 1 of 3
Download recorded
Download not recorded

Please download the related document if you wish to add this activity to your record

What time are you claiming for this activity?
Mandatory fields

Add this page to your CPD activity

Step 2 of 3
Mandatory field

Add activity to my record

Step 3 of 3
Mandatory field

Activity added

An error has occurred
Please try again

If the problem persists please contact our helpline on +44 (0)1908 248 250