Excel: Using IRR() and MIRR() with Non-Contiguous Data

ID: Q65761

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 5.0
Microsoft Excel for OS/2, versions 2.2, 3.0

SUMMARY

The "values" parameter of the MIRR(values, finance rate, reinvest rate) and of the IRR(values, guess) functions is a reference to cells that contain the payments (negative values) and income (positive values) occurring at regular periods. The "values" parameter only allows for one reference to a range of cells, which can cause a problem when the cells containing the payments and income are in noncontiguous blocks.

Workaround

To work around this problem, you can either use a defined name to represent the cells or you can use parenthesis around the cell addresses that contain the payments and income. This is displayed in the example below.

Under the Formula menu's Define Name, "range" has been defined as the the cells A1 and A3:A7. (In version 5.0, choose Name from the Insert menu, then select Define and enter the word range.)


   A1:  -120,000   B1:       C1:  10%
   A2:             B2:       C2:  12%
   A3:    39,000   B3:       C3:
   A4:    30,000   B4:       C4: =IRR((A1,A3:A7))
   A5:    21,000   B5:       C5: =IRR(range)
   A6:    37,000   B6:       C6: =MIRR((A1,A3:A7),C1,C2)
   A7:    46,000   B7:       C7: =MIRR(range,C1,C2)

Note that the values in C4 and C5, as well as C6 and C7 are the same, but the values in C5 and C7 use the defined name for the range.

REFERENCES

"Online Help," version 5.0
"Function Reference," version 4.0, pages 247-248, 276-277
"Function Reference," version 3.0, pages 154, 134-135
"Functions and Macros," version 2.x, pages 84-86, 63-64

Additional query words: 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0 4.0 4.0a 4.00a 5.0 non-contiguous

Keywords :
Version :
Platform :
Issue type :