Microsoft KB Archive/75504

{| = Using IRR with a Range that Includes Blank Cells in Excel =
 * width="100%"|

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=&quot;&quot;,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.