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

=(B5/B4)–1

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

=IRR(F4:F8,–90%)

In other words, the growth rates of 5.21%, 4.86%, and 5.66% are equivalent to a geometric average growth rate

of 5.241%.

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.

Checking results