I said it worked for the first item only. Add
in column B 4 more entries with code 5 against them instead of code 4 and
put 5 in G4 and copy down teh array formula. You will get 0.

I do now know that the porblem is not with teh array folrmula but with xirr.
It requires teh first entry to be a negative cashflow and thus will not
accept a zero value in the first item. Thus it will only work for a single
asset thus making it not compatible with teh array formula. For a single
asset, I don't need the array formala.




"ShaneDevenshire" wrote:

Hi,

Well it works for me. i tested
A B C
Values Dates
-10,000 4 1-Jan-08
2,750 4 1-Mar-08
4,250 4 30-Oct-08
3,250 4 15-Feb-09
2,750 4 1-Apr-09

In G3 I entered 4

The formula
=XIRR(IF(B2:B6=G3,A2:A6,0),C2:C6)
which is just a modification of yours returned

0.373362535238266

Which is the correct answer.



--
Thanks,
Shane Devenshire


"James Buist" wrote:

Sorry, I forgot to say this was my attempt
={XIRR(IF($B$3:$B$13=G3,$C$3:$C$13,0),$A$3:$A$13)} where $B$3:$B$13 is the
asset ids and G3 is the asset ID of the asset I need the IRR for (G4 would be
the next asset id and G5 the one after that etc, $C$3:$C$13 are the cashflows
which are set to zero if the asset ID doesnt match and $A$3:$A$13 are the
dates of those cashflows. The logic seems fine and the zero cashflows dont
affect the result when I test it manually using XIRR on the whole range. The
formulas computes the correct IRR for the first asset in the list but then
returns zero for all the remaining assets in the array
Im puzzled why it doesnt work
Also, apologies for the empty post above. The site refused to accept this
reply first time and so I went back and tried again.


"James Buist" wrote:

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