I have a sheet with different assets and their cashflows and the dates of
those cashflows sucked out of a database. I need to return an IRR for each
individual asset and feel that this should be possible with an array formula.
If I were simply summing or getting an average it is very simple. However, I
can't figure out how to handle a function that requires two parameters in an
array.
If I were summing I would use this:
={SUM(IF($B$3:$B$13 = G3,$C$3:$C$13,0))} where
$B$3:$B$13 holds the asset Ids and $C$3:$C$13 holds the cashflows The real
list is several thousand rows). I could use the IRR function by replacing SUM
with IRR. However that doesnt take account of the dates of the cashflows and
assumes that each entry is a fixed period apart (like months). Mine are not
so I need to use XIRR (in the analysis tool pack). However, that requires a
range parameter for the cashflows as well as one for the dates. The same
could hold true for other functions that require 2 parameter inputs.
Id rather not write a special function in VBA if I can help it and am sure
there must be a clever way of doing this using an array formula but I can't
seem to get it.

Many thanks