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

• 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)

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