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

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

Sub test()
     Dim My_Dates(), My_CF()

     My_Dates = Application.Transpose(Range("I2:I5").Value2)    'transposed range of dates
     My_CF = Application.Transpose(Range("J2:J5").Value2)

     ReDim Preserve My_Dates(1 To UBound(My_Dates) + 1)         'add 1 element
     ReDim Preserve My_CF(1 To UBound(My_Dates))

     My_Dates(UBound(My_Dates)) = CDbl(Range("I6").Value)                    'new date = today
     My_CF(UBound(My_Dates)) = CDbl(Range("j6").Value)                              'new cashflow

     my_Xirr = WorksheetFunction.Xirr(Application.Transpose(My_CF), Application.Transpose(My_Dates))     'new IRR
End Sub

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:

=XIRR(IF(ROW($A$2:$A$35)=ROW($A$2), INDEX($C$2:$C$34,MATCH($H13,$A$2:$A$34,0)),
IF(ROW($A$2:$A$35)=ROW($A$35), INDEX($E$2:$E$34,MATCH($H13,$A$2:$A$34,0)),
IF(ROW($A$2:$A$35)=ROW($A$2)+MATCH($H13,$A$2:$A$34,0)-1, 0,
IF($A$2:$A$35=$H13, $C$2:$C$35, 0)))),
IF(ROW($A$2:$A$35)=ROW($A$2), INDEX($B$2:$B$34,MATCH($H13,$A$2:$A$34,0)),
IF(ROW($A$2:$A$35)=ROW($A$35), INDEX($D$2:$D$34,MATCH($H13,$A$2:$A$34,0)),
IF($A$2:$A$35=$H13, $B$2:$B$35, 0))))

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

=XIRR(IF(ROW($A$2:$A$36)=ROW($A$2), INDEX($C$2:$C$34,MATCH($H13,$A$2:$A$34,0)),
IF(ROW($A$2:$A$36)=ROW($A$35), INDEX($E$2:$E$34,MATCH($H13,$A$2:$A$34,0)),
IF(ROW($A$2:$A$36)=ROW($A$36), $J$6,
IF(ROW($A$2:$A$36)=ROW($A$2)+MATCH($H13,$A$2:$A$34,0)-1, 0,
IF($A$2:$A$36=$H13, $C$2:$C$36, 0))))),
IF(ROW($A$2:$A$36)=ROW($A$2), INDEX($B$2:$B$34,MATCH($H13,$A$2:$A$34,0)),
IF(ROW($A$2:$A$36)=ROW($A$35), INDEX($D$2:$D$34,MATCH($H13,$A$2:$A$34,0)),
IF(ROW($A$2:$A$36)=ROW($A$36), $I$6,
IF($A$2:$A$36=$H13, $B$2:$B$36, 0)))))

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

@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.

impressive, twice

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.