Hi,

I am using Excel 2016 and need help using the XIRR formula for the sample data below:

In the above sample data, I want to calculate XIRR in column D. It would be great if I could build the formula like =XIRR ((B$2:B3), C3), (A$2:A3), A3)) but unfortunately excel does not work as per my whims

I am unable to figure out a proper way to join/concatenate two ranges to use in the formula and the best I was able to build up after searching around for a couple of hours is:

Without using VBA, is it possible to have a formula created that would work for all cells D3 to D12 (drag down) ??

I am using Excel 2016 and need help using the XIRR formula for the sample data below:

In the above sample data, I want to calculate XIRR in column D. It would be great if I could build the formula like =XIRR ((B$2:B3), C3), (A$2:A3), A3)) but unfortunately excel does not work as per my whims

I am unable to figure out a proper way to join/concatenate two ranges to use in the formula and the best I was able to build up after searching around for a couple of hours is:

but this does not give the correct result as it ends up taking the value $315 multiple times and same with the date 30-Jun-2017 (takes it 3 times instead of twice)=XIRR(CHOOSE({1,2},OFFSET($B$2,0,0,MATCH($A3,A:A,FALSE)-1,1),VLOOKUP($A3,$A:$C,3,FALSE)),CHOOSE({1,2},OFFSET($A$2,0,0,MATCH($A3,A:A,FALSE)-1,1),VLOOKUP($A3,$A:$A,1,FALSE)))

Without using VBA, is it possible to have a formula created that would work for all cells D3 to D12 (drag down) ??