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.
 
Search JabSto ::




Custom Search