How to calculate XIRR with criteria & Non-continuous data series in Excel Formula

Hi Rahul,

Try below Array Formula in J3 confirmed with Ctrl+Shift+Enter and drag down:

=XIRR(INDEX(I:I,N(IF(1,MODE.MULT(IF($A$3:$A$16=$A3,{1,1}*ROW($A$3:$A$16)))))),INDEX(C:C,N(IF(1,MODE.MULT(IF($A$3:$A$16=$A3,{1,1}*ROW($A$3:$A$16)))))))

Select allOpen in new window

Format your cells with Percentage
Check in attached...
Rahul_Chauhan_XIRR-with-condition.xlsx

Use the Data Table to create a pivot table to show only the specific Stock and its dates. then build the XIRR calculation using the pivoted data.

Thanks Rob and Shums.

Shums, when I use the formula, I get #Name?  error.

Also, I notice that in your reply you had mentioned the formula as =XIRR(INDEX(I:I,N(IF(1,MODE.MULT(IF($A$3:$A$16=$A3,{1,1}*ROW($A$3:$A$16)))))),INDEX(C:C,N(IF(1,MODE.MULT(IF($A$3:$A$16=$A3,{1,1}*ROW($A$3:$A$16)))))))

whereas, the formula appearing in excel has _xlfn. added before MODE. I have pasted below the exact formula from the excel sheet that you had uploaded.

=XIRR(INDEX(I:I,N(IF(1,_xlfn.MODE.MULT(IF($A$3:$A$16=$A3,{1,1}*ROW($A$3:$A$16)))))),INDEX(C:C,N(IF(1,_xlfn.MODE.MULT(IF($A$3:$A$16=$A3,{1,1}*ROW($A$3:$A$16)))))))

I am not able to use the formula as it gives me the #Name? error all the time. Even if I copy the entire Sheet1 onto Sheet2, I still get the #Name? error.

I am always careful to do Crtl+Shift+Enter but still the same error.

The excel sheet is also attached for your ready reference and you can see the formula difference and Name error. What could be the issue?

Thanks and regards,

R
Rahul_Chauhan_XIRR-with-condition.xlsx

Hi,

pls try

Regards
EE20171004.xlsx

Thanks Gonzo. I will just check it out.

Shums, its indeed strange then. I am attaching the screenshot of sheet 1, where you can see the xlfn added before Mode in the formula however in sheet 1 it provides the correct XIRR values.  But if you were to see the screenshot of Sheet2, exactly same formula gives the Name error.

Maybe my excel is being weird?

Or Could it be that your excel has some special macros or plug ins which my excel doesn't have?
Sheet1-screenshot.jpg
Sheet2-screenshot.jpg

MODE.MULT is only available from XL 2010 afaik

Oh ok, that explains it then, because I am XL 2007.

Gonzo, I am using your formula, but I am not sure if I am putting in the correct values coz my main spreadsheet has many other col in between date & txn Amount. So let me plz bounce it off you.

In the attached file, Sheet 1 is the one where you gave the formula and sheet2 is my actual sheet with many other Cols in between.

So the Txn Amount in Sheet1 in Col I is now in Col P in Sheet2 and the Txn Date in Col C in Sheet 1 is now in Col F in Sheet2 and the Stock Symbol in Col A in Sheet 1 is now in Col D in Sheet2.

I will need to find the XIRR in Col S in Sheet 2. So if I need to find XIRR at S25 & onwards, then the formula I am putting is:

{=XIRR(INDEX($P$5:$P25,N(IF(1,SMALL(IF($D$5:$D25=$D25,ROW($D$5:$D25),""),ROW(OFFSET($D$5,0,0,COUNTIF($D$5:$D25,$D25))))))),INDEX($F$5:$F25,N(IF(1,SMALL(IF($D$5:$D25=$D25,ROW($D$5:$D25),""),ROW(OFFSET($D$5,0,0,COUNTIF($D$5:$D25,$D25))))))))}

But as you can see the the attached excel file, I am getting the error.
EE20171004.xlsx

then try

{=XIRR(INDEX($P$1:$P$27,N(IF(1,SMALL(IF($D$5:$D$27=$D25,ROW($D$5:$D$27),""),ROW(OFFSET($D$1,0,0,COUNTIF($D$5:$D$27,$D25))))))),INDEX($F$1:$F$27,N(IF(1,SMALL(IF($D$5:$D$27=$D25,ROW($D$5:$D$27),""),ROW(OFFSET($D$1,0,0,COUNTIF($D$5:$D$27,$D25))))))))}

Select allOpen in new window

EE20171004v1.xlsx

or in row 25

=XIRR(INDEX($P$1:$P25,N(IF(1,SMALL(IF($D$5:$D$27=$D25,ROW($D$5:$D25),""),ROW(OFFSET($D$1,0,0,COUNTIF($D$5:$D25,D25))))))),INDEX($F$1:$F25,N(IF(1,SMALL(IF($D$5:$D25=$D25,ROW($D$5:$D25),""),ROW(OFFSET($D$1,0,0,COUNTIF($D$5:$D25,$D25))))))))

Select allOpen in new window

and fill down

a bit shorter

=XIRR(INDEX($P$1:$P25,N(IF(1,SMALL(IF($D$5:$D25=$D25,ROW($D$5:$D25)),ROW(OFFSET($D$1,,,COUNTIF($D$5:$D25,D25))))))),INDEX($F$1:$F25,N(IF(1,SMALL(IF($D$5:$D25=$D25,ROW($D$5:$D25)),ROW(OFFSET($D$1,,,COUNTIF($D$5:$D25,$D25))))))))

Select allOpen in new window

Thanks Gonzo. It works. Just one small issue but critical issue...I will have some other data in first few rows in D, F, P & other Cols.

If my actual data, let's say, if it starts from Row 5, then in the formula, can I replace $P$1 with $P$5 and $F$1 with $F$5 & $D$1 with $D$5?

I tried doing that but it gave me a #Num! error. How to fix it please?

Thanks. Let me check out the latest formula you gave and if it solves the issue i spoke earlier.

I am still getting the error if I replace $P$1, $F$1 & $D$1 with $P$5, $F$5 & $D$5

Yes, Gonzo, you are absolutely right. I checked both the solutions. I was only worried that if I have some data on the rows above the headers esp in the Col D, F & P, it may interfere with the XIRR result but I am happy to note that either of the solutions doesn't get impacted with any data in the rows above the header row.

Infact, your original solution of making it $P$1 etc is better because the XIRR result doesn't change if I add a row after the header row. The result gives error only if I add the row above the header row. Whereas, in the second solution of making it $P$5 etc, the XIRR value returns error in either case of adding a row above or below the header.

So, that's why the original solution is better, and in case I need to add a row above the header row, i will subtract the number of rows as you have suggested in the second solution formula. Its much better that way.

Thanks a ton mate for your help and such a wonderful solution.

Thanks to Shums too. If not for my lower XL version his solution also seemed good, though I had no means to try it out.

Thanks and regards,

Rahul

Thanks Gonzo and Shums. I really appreciate your kind help.