• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

  • XIRR.xlsx
    9.4 KB · Views: 59
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

  • XIRR.xlsx
    9.1 KB · Views: 32
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.

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

Could you please look into this and help please.
 

Attachments

  • XIRR.xlsx
    10 KB · Views: 19
Unfortunately this is not working.

Could you please look into this and help please.
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))))))))

78363
 
Last edited:
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!!
 
Back
Top