Using IRR with a Range that Includes Blank Cells in Excel

 Last reviewed: November 2, 1994
Article ID: Q75504

SUMMARY

The Excel function IRR (internal rate of return) will ignore any blank cells encountered in the specified range of values and they will not be included in the calculation of periods.

MORE INFORMATION

Consider the following cash flows: 

   A1:     ($2,860.00)             B1:     ($2,860.00)
   A2:                             B2:           0.00
   A3:                             B3:           0.00
   A4:                             B4:           0.00
   A5:                             B5:           0.00
   A6:      $8,860.00              B6:      $8,860.00
When the IRR function is applied to each of these two ranges, two answers will result: 

   A7:     =IRR(A1:A6,10%)         B7:     =IRR(B1:B6,10%)
           =2.09                           =.254
The IRR calculation for column A is based on a 1-year term, while the IRR for column B is based on a 5-year term.

If a file is imported from Lotus 1-2-3, a problem may arise because Lotus includes blank cells in its IRR calculation. If the Lotus 1-2-3 IRR function is applied to the values A1:A6, the result will be .254. However, when this file is imported to Excel, the result will change to 2.09.

There are two ways to work around Excel's treatment of blank cells in the IRR function. First, you can enter zeros in the blank cells as shown in cells B2:B5 above. Or, you can create an array formula that will evaluate every cell in the range of values and replace blank cells with the zero value when calculating the IRR. The array formula is entered as follows: 

   A7:     {=IRR(IF(A1:A6="",0,A1:A6))}
Remember that because this is an array formula, the formula above is typed without the Braces and then entered into the cell by pressing CTRL+SHIFT+ENTER simultaneously.

REFERENCES

"Microsoft Excel Function Reference," version 3.0, pages 134-135.

"Microsoft Excel Functions and Macros," version 2.x, pages 63-64.

KBCategory: kbother
KBSubcategory:

Additional reference words: noupd

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 2, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.