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.
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 ten years (years 16 through 25).
- $98,400 p.a. for the final five years (years 26 through 30).
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:
which equates to $94,680. This is incorrect, as the five durations are not of equal length, and this 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, as shown in the screenshot below.
For each row, I may calculate the total costs by multiplying the cited expenditure by the duration (ie, the number of relevant periods), eg, for cell M14, the formula would be:
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:
in the above formula.
The value in cell M13 (above) simply adds these values together and divides by the total number of periods:
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 to write the entire calculation all in one cell. So, how do you do that?
I suggest the following formula:
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:
You can multiply the vectors together instead:
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 the comma delimiter will achieve the same results in this instance. The formula:
subtracts column J from column K and adds one to calculate the number of periods (ie, the duration) on an array basis:
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:
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)), as shown in the formula:
Please refer to this 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, and 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 a helper column, detailed above), as it makes it both easier for end users to understand and lessens the risks of calculation errors.
— Liam Bastick, FCMA, CGMA, FCA, is director of SumProduct, a global consultancy specialising in Excel training. He is also an Excel MVP (as appointed by Microsoft) and author of Introduction to Financial Modelling. Send ideas for future Excel-related articles to him at email@example.com. To comment on this article or to suggest an idea for another article, contact Jeff Drew at Jeff.Drew@aicpa-cima.com.