XIRR with Inflows and Outflows on Different Sheets

[bmarq1]

Hi All, thanks in advance for your review of this question.

I'm trying to run an XIRR on non-contiguous data, in which the negative and positive cashflows are on different sheets in my workbook.

I've tried the solutions posted here: http://forum.chandoo.org/threads/xirr-for-non-contiguous-data-solved.7022/

But since my inflows outnumber my outflows, the CHOOSE solution doesn't work.

I've also tried using the FREQUENCY function as per this thread: http://stackoverflow.com/questions/...nt-cell-in-a-range-series-in-the-xirr-formula

But it only seems to work for the specific case in the thread (a single, separated value from the rest), and I can't figure how to modify for my specific case.

I've attached a sample worksheet showing my general issue, though for simplicity the values are on the same tab.

I'd like to avoid VBA if possible, but I'll take any solution at this point. Thanks!

 

[bmarq1]

Shortly after posting this, I did stumble upon a UDF that seems to work well, at the link below.

I'd still welcome any non-VBA ideas, but this seems to work quite well. Thanks!

http://stackoverflow.com/questions/19248509/how-merge-mix-range-of-cell-as-formula-parameter

 

[bosco_yip]

Try...

In A9,

=XIRR(CHOOSE({1,2,3,4,5,6,7,8},B9,C9,D9,B12,C12,D12,E12,F12),CHOOSE({1,2,3,4,5,6,7,8},B8,C8,D8,B11,C11,D11,E11,F11),0.1)

Regards

 

[bmarq1]

Thanks bosco_yip, that certainly works for a few items. But a real worksheet I work with will likely have so many flows that CHOOSE isn't really feasible.

 

[Rushikesh Ambekar]

Try...

In A9,

=XIRR(CHOOSE({1,2,3,4,5,6,7,8},B9,C9,D9,B12,C12,D12,E12,F12),CHOOSE({1,2,3,4,5,6,7,8},B8,C8,D8,B11,C11,D11,E11,F11),0.1)

Regards

Hey everyone,

I am fairly new to this forum. I was searching similar solution & this has solved purpose. But I have 2 different data sets in different columns against which I have to calculate XIRR.

Currently I am using following formula:
=XIRR(CHOOSE({1,2,3,4,5,6,7,8},G5,G6,G7,G8,-L5,-L6,-L7,-L8),CHOOSE({1,2,3,4,5,6,7,8},C5,C6,C7,C8,H5,H6,H7,H8), 0)

there are 2 set of ranges one from C5:C8 and H5:H8 but choose formula does not allow using range tn this formula. And major problem is When I insert any row I have to go back and edit the formula, possibility of using range would minimize human error in calculations.

Thanks in advance.

 

[Lori]

This might work for you with CTRL+SHIFT+ENTER:

=XIRR(IF({1,0},G5:G8,-L5:L8),IF({1,0},C5:C8,H5:H8))

In the original attachment you can also try entering:

=XIRR(IF({1;0},B9:F9,B12:F12),IF({1;0},B8:F8,B11:F11))

 

[bines53]

Hi @ Lori ,

Is the combination of IF({1,0}, can work with 3 different ranges?

Thank you !

David

 

[Lori]

For three ranges use CHOOSE({1,2,3} instead.
IF is just used in the case of two ranges for brevity.

 

[Rushikesh Ambekar]

This might work for you with CTRL+SHIFT+ENTER:

=XIRR(IF({1,0},G5:G8,-L5:L8),IF({1,0},C5:C8,H5:H8))

In the original attachment you can also try entering:

=XIRR(IF({1;0},B9:F9,B12:F12),IF({1;0},B8:F8,B11:F11))

Thanks @Lori

The first solution worked.

I am sorry being so demanding here but we have to do SHIFT+CTRL+ENTER every time after clicking on that cell and even the formulae cannot be dragged.

If any solutions which will take care of the above That would be very great. Thanks in advance.

 

[Lori]

Hi @Rushikesh Ambekar . Try this variation:

=XIRR(IF({1,0},G5:G8,INDEX(-L5:L8,)),IF({1,0},C5:C8,H5:H8))