XIRR Question - Working on Project from Boss!

[Zimmyer]

Long time reader, first time poster

I need to calculate gross xIRR for several companies in one fund. Each company has two columns (one for values and one for dates) How do I write the XIRR formula to combine multiple sets of columns?

EX:

Company A (Column A Values, Column B Investment Dates)
Company B (Column C Values, Column D Investment Dates)
​

​

=XIRR((A2:A84, C2:C84),(B2:B84,D2:D84),.1) was my guess but it returns the 'value' error...Thanks so much for the help!!

 

[Firefly2012]

Are you wanting to return a single IRR for the consolidated results of both companies? If so, then you need to generate an aggregate net cashflow at each unique date and use that in the XIRR calculation.

 

[Zimmyer]

That is exactly what I am trying to accomplish. Do you think the best way to do this would be to use the Aggregate function, unique value + lookup, or something else? I have ~15 companies with ~20 individual wire transfers

Thanks so much for your help!

Are you wanting to return a single IRR for the consolidated results of both companies? If so, then you need to generate an aggregate net cashflow at each unique date and use that in the XIRR calculation.