XIRR Help - Ignore Cells without Values

I am trying to figure out a way to use the XIRR calculation so that it will continue to work when the number of Dates/Values change. Basically, I have a set of cash flows and specific dates. This is real estate related, so it's an original purchase price, then contributions and distributions, and eventually a sale (reversion). In order to keep the model flexible, I need to be able to change the Date I sell the building (currently by using a referenced drop-down list). Due to this, the number of inputs in the range (CF's) changes depending on the year of the exit. I need all of the input years, and then the reversion year, included in the XIRR formula. Row 17 includes the specific dates I'd like to use, and Row 19 includes the values. Cell T17 has where I had been attempting the XIRR. B6 is the dropdown to choose what year to sell the asset.
I've attached the spreadsheet to make things more clear.

XIRR Help.xlsx

Please let me know if I have not clarified well enough.

Originally Posted by cchase22

Subject: XIRR Help - Ignore Cells without Values

But you do not have cells "without values". Instead, you have cells whose value is text, specifically dash ("-"), under some conditions.

As you discovered, the IRR function ignores text, but the XIRR function does not (sigh).

The simplest (least) change, is: Array-enter the following formula in T17 (press ctrl+shift+Enter, not just Enter):

=XIRR(IF(ISNUMBER(C19:R19),C19:R19,0),IF(ISNUMBER(C17:R17),C17:R17,0))

Also see step 5 and the note about IRR usage below.

Alternatively, if you do not intend to use the IRR function, make the following changes:

1. Change the formula in F19:O19 to (F19 for example): =IF(I$4<=$B$6,I$7,0)
2. Change the format for C19:R19 to Custom * #,##0_);* (#,##0);"-";@
3. Note that the horizontal alignment for C19:R19 is Center
4. Change the formula in C20:O20 and R20 to (C19 for example): =CHOOSE(2+SIGN(C19),"C","-","D")
5. Change the horizontal alignment for C20:R20 to Center
6. Normally-enter the following formula into T17 (press Enter as usual): =XIRR(C19:R19,C17:R17)

Arguably, the second alternative is not conducive to also using the IRR function because it is difficult to distinguish zero as a cash flow from zero as a placeholder for "no data" due to the formula in step 1 above.

On the other hand, note that your formula =IRR(C19:R19) is incorrect. That treats the data as if there are 7 annual cash flows, C19:H19 and R19. But the cash flows in H19 and R19 both occur on 6/1/2017. So there should be 6 annual cash flows, combining H19 and R19 into one net cash flow: 2,396,892 + 548,009,421 = 550,406,313. Then the IRR function would return about 40.032%, which is closer to the rate that the XIRR function returns.

i am trying to do something similar to what cchase needed done. attached is my excel file where i am running into trouble with XIRR calcs omitting rows 29-72 in this case. As you can see, i have set up the sheet to populate that data based on entries from other sheets so it would be great to get the XIRR to omit those cells that do not report.

thanks and please let me know if i was not clear on anything.

IRR Model v2.4_web scrub.xlsm

cchase22
XIRR Help - Ignore Cells without Values

I downloaded your spreadsheet and concluded that your formula was not working because you had the dates referenced first and then the values! XIRR needs the values in the first column!

=XIRR(IF(ISNUMBER(C16:C73),C16:C73,0),IF(ISNUMBER(B16:B73),B16:B73,0))