Microsoft Office Tutorials and References

In Depth Information

**Using the IRR Function**

Cell D21 contains this formula:

=FV(D20,C3,0,–1)–1

The following formula, in cell D23, is a validity check:

=NPV(D20,D7:D18)+D6

The IRR is the rate at which the discounting of the cash flow produces an NPV of zero. The

formula in cell D23 uses the IRR in an NPV function applied to the same cash flow. The NPV

discounting at the IRR (per month) is $0.00 — so the calculation checks.

Geometric growth rates

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-13) 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 example, 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.