# XIRR with IF condition

#### john.nth

##### New Member
Could you please help me with the formula for calculating XIRR from a set of cash flows based on a particular identifier?
To compute XIRR that fits the conditions, the formula should use the precise trend length.
In the attached file, I computed XIRR by manually picking the trend length, and every time the number of data points in the array increases, I have to manually update the XIRR calculation, which I like to avoid.

#### Attachments

• 9.4 KB Views: 27

#### john.nth

##### New Member
Thank you. This worked perfectly.

However, if the data arrangement is updated to sort it by ascending order, the formula is not working.

Can you please suggest something ?

Try,

In B3, formula copied down :

=XIRR(OFFSET(\$F\$2,MATCH(A3,\$E\$3:\$E\$16,0),,COUNTIF(\$E\$3:\$E\$16,A3)),OFFSET(\$D\$2,MATCH(A3,\$E\$3:\$E\$16,0),,COUNTIF(\$E\$3:\$E\$16,A3)))

View attachment 78197

#### Attachments

• 9.1 KB Views: 17

#### bosco_yip

##### Excel Ninja
Thank you. This worked perfectly.

However, if the data arrangement is updated to sort it by ascending order, the formula is not working.

Can you please suggest something ?
If Date column is sorted by order,

in B3, CSE formula copied down :

=XIRR(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,IF(E\$3:E\$16=A3,F\$3:F\$16,""))&"</b></a>","//b"),FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,IF(E\$3:E\$16=A3,D\$3:D\$16,""))&"</b></a>","//b"))

Edit: Depend on your Excel version, Excel 2019 need CSE entry, Office 365 is normal entry. #### john.nth

##### New Member
If Date column is sorted by order,

in B3, CSE formula copied down :

=XIRR(FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,IF(E\$3:E\$16=A3,F\$3:F\$16,""))&"</b></a>","//b"),FILTERXML("<a><b>"&TEXTJOIN("</b><b>",,IF(E\$3:E\$16=A3,D\$3:D\$16,""))&"</b></a>","//b"))

Edit: Depend on your Excel version, Excel 2019 need CSE entry, Office 365 is normal entry.

View attachment 78245
Unfortunately this is not working.

#### Attachments

• 10 KB Views: 10

#### bosco_yip

##### Excel Ninja
Unfortunately this is not working.

The post #4 formula worked for Excel 2019, Excel 2021 and Office 365, And also for Windows desktop versions of Excel, not for Mac.

Here is another option for all old Excel versions

In B3, array formula (press "Ctrl+Shift+Enter") copied down :

=XIRR(INDEX(F:F,N(IF(1,SMALL(IF(E\$3:E\$16=A3,ROW(\$3:\$16)),ROW(INDIRECT("1:"&COUNTIF(E\$3:E\$16,A3))))))),INDEX(D:D,N(IF(1,SMALL(IF(E\$3:E\$16=A3,ROW(\$3:\$16)),ROW(INDIRECT("1:"&COUNTIF(E\$3:E\$16,A3)))))))) Last edited:
• nipurngupta

#### nipurngupta

##### New Member
The post #4 formula worked for Excel 2019, Excel 2021 and Office 365, And also for Windows desktop versions of Excel, not for Mac.

Here is another option for all old Excel versions

In B3, array formula (press "Ctrl+Shift+Enter") copied down :

=XIRR(INDEX(F:F,N(IF(1,SMALL(IF(E\$3:E\$16=A3,ROW(\$3:\$16)),ROW(INDIRECT("1:"&COUNTIF(E\$3:E\$16,A3))))))),INDEX(D:D,N(IF(1,SMALL(IF(E\$3:E\$16=A3,ROW(\$3:\$16)),ROW(INDIRECT("1:"&COUNTIF(E\$3:E\$16,A3))))))))

View attachment 78363
Worked beautifully!!

Thank you!!

• bosco_yip