Microsoft Office Tutorials and References
In Depth Information
You may have a need to calculate an average growth rate, or average rate of return. Because of compounding, a
simple arithmetic average does not yield the correct answer. Even worse, if the flows are different, an arithmetic
average does not take these variations into account.
A solution uses the IRR function to calculate a geometric average rate of return. This is simply a calculation
that determines the single percentage rate per period that exactly replaces the varying ones.
This example (see Figure 12-9) shows the IRR function being used to calculate a geometric average return
based upon index data (in column B). The calculations of the growth rate for each year are in column C. For ex-
ample, the formula in cell C5 is
The remaining columns show the geometric average growth rate between different periods. The formulas in
Row 10 use the IRR function to calculate the internal rate of return. For example, the formula in cell F10, which
returns 5.241%, is
In other words, the growth rates of 5.21%, 4.86%, and 5.66% are equivalent to a geometric average growth rate
The IRR calculation takes into account the direction of flow and places a greater value on the larger flows.
Figure 12-9: Using the IRR function to calculate geometric average growth.