XIRR on selected lines

[susselyd]

Hello,

I have used the function below to get the IRR on a chased project.
For some reason it works only for the project on the first line (A in this case). If I would switch A and B on the first 2 lines then I would get only the IRR for B and zero for A.
Any idea why it does not work for all projects?
Thanks in advance for any help


Project Date CF
A 01/01/13 -50,000.00
B 01/01/13 -40,000.00
A 01/07/13 20,000.00
B 01/07/13 14,000.00
A 01/12/13 20,000.00
B 01/12/13 14,000.00
A 01/02/14 20,000.00
B 01/02/14 14,000.00

=XIRR(IF(A2:A9=E2,C2:C9,0),IF(A2:A9=E2,B2:B9,0))
(Cell E2 has the Project letter)

B A
0.0% 24.7%

 

[Andrew Poulsom]

For B the result is the same as:

Excel 2010
	O	P	Q	R
1	Project	Date	CF
2	A	00/01/1900	0.00	0.000000298%
3	B	01/01/2013	-40,000.00
4	A	00/01/1900	0.00
5	B	01/07/2013	14,000.00
6	A	00/01/1900	0.00
7	B	01/12/2013	14,000.00
8	A	00/01/1900	0.00
9	B	01/02/2014	14,000.00
Sheet1

Cell Formulas
Range	Formula
R2	=XIRR(Q2:Q9,P2:P9)

 

[susselyd]

I'm looking for a solution that by only changing the Project letter in a specific cell (E2 in my case) would give the IRR value, without changing the data of the main table.

 

[Domenic]

Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=XIRR(N(OFFSET($C$2:$C$9,SMALL(IF($A$2:$A$9=E2,ROW($A$2:$A$9)-ROW($A$2)),ROW(INDIRECT("1:"&COUNTIF($A$2:$A$9,E2)))),0,1)),N(OFFSET($B$2:$B$9,SMALL(IF($A$2:$A$9=E2,ROW($A$2:$A$9)-ROW($A$2)),ROW(INDIRECT("1:"&COUNTIF($A$2:$A$9,E2)))),0,1)))

Hope this helps!

 

[susselyd]

Domenic, works perfectly, Thank you sos so so much!!!!

 

[Domenic]

You're very welcome!

Cheers!