XIRR with Noncontiguous Cell Ranges Issue

Hello,

I've read a bunch of topics on how to tackle noncontiguous XIRR calculations (not allowed to post links yet)

The solution essentially says to put another parentheses around the XIRR ranges and dates like so:

Please Login or Register  to view this content.

However, when I try this with my data, all I get is #VALUE (see attachment).

My formula is as follows:

Please Login or Register  to view this content.

My cell types for dates is MM/DD/YYYY (works for contiguous). And my amounts is formatted as "general".

Excel version is Office Professional Plus 2016.

Any help would be very much appreciated!

Originally Posted by AnExcelGuy

not allowed to post links yet

You can work around that by spelling out the link to some degree. For example:

www dot excelforum dot com /newreply.php?do=postreply&t=1381497

-----

Originally Posted by AnExcelGuy

The solution essentially says to put another parentheses around the XIRR ranges and dates like so:
[...]
=XIRR((L39:L40,L42),(K39:K40,K42))

That range-union syntax works with Excel IRR, but not with Excel XIRR -- at least in Excel 2010 and earlier, and apparently not yet in Excel 2016.

(Perhaps MSFT "fixed" that in a recent build of Office 365 Excel and/or Excel 2019 and later.)

The messy way to write that is:

=XIRR(IF(ROW(L39:L41)=ROW(L41),L42,L39:L41), IF(ROW(K39:K41)=ROW(K41),K42,K39:K41))

That must be array-entered (press ctrl+shift+Enter) in some versions of Excel.

-----

PS.... The above might be a useful paradigm for you. But in your example Excel file, you can simply write =XIRR(B2:B5,A2:A5). XIRR tolerates empty rows in the middle and end of the ranges. It is not tolerant of empty rows in the beginning of the ranges.

Good luck with that. I won't be able to help you further.

The version number you've posted as part of your profile isn't the most helpful, though it does suggest you have the latest Insider functions.

=XIRR(VSTACK(C2:C5,D5),VSTACK(B2:B5,B5))

Regards