Calculating XIRR on Non-Contiguous Rows and Columns based on Criteria

suppose you want to add the green values to those arrays !

Please Login or Register  to view this content.

if you need this calculation several times, you can make an UDF (User Defined Function) of it.

See the formulas in H12:J23 in the attached Excel file.

The XIRR formula in I13 that corresponds to your calculation in I9 is:

Please Login or Register  to view this content.

The XIRR formula in J13 that corresponds to your calculation in I10 is:

Please Login or Register  to view this content.

Note that the ranges 2:35 and 2:36 purposely reference 1 and 2 rows, respectively, below the table of cash flows (2:34). In contrast, the range 2:34 is sufficient for the INDEX/MATCH.

The formulas must be array-entered (press ctrl+shift+Enter) in Excel 2010 and some other versions of Excel.

They can be simplified in Office Excel 365 and perhaps some later versions of Excel by using a LET function to calculate the MATCH index only once.

(Further simplification might be possible by using new functions in recent versions of Excel. I am not familiar with them.)

As "proof of concept", compare with the formulas in N1:AI39.

Note: In order to use the Excel file in your posting #1, I replaced the circular-reference formulas in column E with constants.

Please try at I10

PHP Code: 

=XIRR(IF(INDEX($A$2:$A$35,MATCH(H10,$A$2:$A$35,)):$A$35=H10,INDEX($C$2:$C$35,MATCH(H10,$A$2:$A$35,)):$C$35,IF(INDEX($A$2:$A$35,MATCH(H10,$A$2:$A$35,)):$A$35=0,VLOOKUP(H10,$A$2:$E$34,5,0),0)),IFERROR(1/(1/INDEX($B$2:$B$35,MATCH(H10,$A$2:$A$35,)):$B$35),VLOOKUP(H10,$A$2:$E$34,4,0))) 


Confirm with Ctrl+Shift+Enter

H10:Hxx => Fund 1 - xx

impressive, twice

@Bo_Ry, I like INDEX($A$2:$A$35,MATCH(H10,$A$2:$A$35,0)):$A$35=H10 as a simplification.

However, I suspect that we need something like ROW($A$2:$A$36)=ROW($A$35) and similarly for ROW($A$36) for the second XIRR that e4excel calculates in I10. Right?

-----

And beware of expressions like 1/(1/INDEX($B$2:$B$35,MATCH(H10,$A$2:$A$35,0)).

Although the double-inverse of an integer results in the same integer mathematically, that is not always the case in binary floating-point arithmetic.

For example, with 9/4/1989 or 3/30/2027 in A1, MATCH(1/(1/A1),A1,0) returns #N/A, indicating that the binary values do not match.

OTOH, the difference is infinitesimal. And I have not found a case where 1/(1/A1)=A1 returns FALSE. Thus, the values match when rounded to 15 significant digits (so far).

So it might not be a problem in this context. But it is something to keep in mind in other contexts.

The work-around is simple: use ROUND(1/(1/A1),0).

FYI, I have not found a case where INT(1/(1/A1)) does not work equally well. And that might be faster (we're talking about nanoseconds!). But ROUND is safer, IMHO.

I am aware that double-inverse could cause non-match due to floating-point.

But for XIRR calculation, Dates only take integers.

I can +0.1 or + 0.9 to the Dates without any difference in XIRR result
IFERROR(1/(1/INDEX($B$2:$B$35,MATCH(H10,$A$2:$A$35,)):$B$35),VLOOKUP(H10,$A$2:$E$34,4,0))+0.1
IFERROR(1/(1/INDEX($B$2:$B$35,MATCH(H10,$A$2:$A$35,)):$B$35),VLOOKUP(H10,$A$2:$E$34,4,0))+0.9

So floating-point cause nothing in this context

Originally Posted by Bo_Ry

So floating-point cause nothing in this context

Which is exactly what I wrote, to wit: ``it might not be a problem in this context. But it is something to keep in [mind] in other contexts``.

(Hmm, I just notice the typo!)

I see the double-inverse paradigm used by "experts" often. And the thread is read by others, who might find the paradigm appealing, but who might not be aware of the math-v-binary arithmetic differences.

My comment was just a heads-up.