XIRR - Array Formula Error

[dtarquin]

Hi All -

I am having an issue when calculating return figures for my portfolio...

My CFs are setup as follows:
Column A: Disposition Year
Column B: CF Dates
Column C: CF Values

I am using an array formula to sift through the data to calculate IRRs based on the year the asset was disposed of. My formula works for years with positive returns, but does not for Years 2010 & 2011 when negative returns occurred (yawn!). When I use a positive guess for '10 & '11, the formula produces a 0.0%. When I use a negative guess for said years, the formula produces an error - #NUM. My array formulas are below.

Array Formula (Positive Return Guess):
{=+XIRR(IF($A$3:$A$1700=F17,$C$3:$C$1700,0),$B$3:$B$1700,0.1)}

Array Formula (Negative Return Guess):
{=+XIRR(IF($A$3:$A$1700=F17,$C$3:$C$1700,0),$B$3:$B$1700,-0.1)}

F=17 is the Disposition Year...

Thank you for the help!

_Dave

Portfolio Calcs

	A	B	C	D	E	F	G	H	I	J
	C	2009
	C	2010
	C	2011
	C	2012
	D	2009
	D	2010	#NUM!	<==
	D	2011	#NUM!	<==
	D	2012

<colgroup><col style="WIDTH: 30px; FONT-WEIGHT: bold"><col style="WIDTH: 100px"><col style="WIDTH: 100px"><col style="WIDTH: 100px"><col style="WIDTH: 64px"><col style="WIDTH: 22px"><col style="WIDTH: 64px"><col style="WIDTH: 134px"><col style="WIDTH: 64px"><col style="WIDTH: 75px"><col style="WIDTH: 81px"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #0066cc"] [/TD]
[TD="bgcolor: #0066cc"] [/TD]
[TD="bgcolor: #0066cc"] [/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]Disp. Yr.[/TD]
[TD="align: center"]CF Date[/TD]
[TD="align: center"]CF[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Jan-06[/TD]
[TD="align: right"](5,129,940)[/TD]

[TD="bgcolor: #0066cc"]Cumulative Returns[/TD]
[TD="bgcolor: #0066cc"] [/TD]
[TD="bgcolor: #0066cc"] [/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Feb-06[/TD]
[TD="align: right"](114,485)[/TD]

[TD="align: right"]10.2% [/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Mar-06[/TD]
[TD="align: right"](114,485)[/TD]

[TD="align: right"]0.0% [/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Apr-06[/TD]
[TD="align: right"](114,485)[/TD]

[TD="align: right"]0.0% [/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]May-06[/TD]
[TD="align: right"](114,485)[/TD]

[TD="align: right"]0.2% [/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Jun-06[/TD]
[TD="align: right"](114,485)[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Jul-06[/TD]
[TD="align: right"](114,485)[/TD]

[TD="bgcolor: #0066cc"]Dispo Year Returns[/TD]
[TD="bgcolor: #0066cc"] [/TD]
[TD="bgcolor: #0066cc"] [/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Aug-06[/TD]
[TD="align: right"](114,485)[/TD]

[TD="align: right"]10.2% [/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Sep-06[/TD]
[TD="align: right"](114,485)[/TD]

[TD="align: right"](7.7271%)[/TD]
[TD="align: right"](18,006,526)[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Oct-06[/TD]
[TD="align: right"](114,485)[/TD]

[TD="align: right"](10.2379%)[/TD]
[TD="align: right"](34,175,705)[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Nov-06[/TD]
[TD="align: right"](114,485)[/TD]

[TD="align: right"]13.3% [/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Dec-06[/TD]
[TD="align: right"](114,485)[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Jan-07[/TD]
[TD="align: right"](114,485)[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Feb-07[/TD]
[TD="align: right"]31,078 [/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]Mar-07[/TD]
[TD="align: right"]31,078 [/TD]

</tbody>

Spreadsheet Formulas

Cell Formula G4 {=+XIRR(IF($A$3:$A$463<=F4,$C$3:$C$463,0),$B$3:$B$463,0.1)} F5 =+F4+1 G5 {=+XIRR(IF($A$3:$A$463<=F5,$C$3:$C$463,0),$B$3:$B$463,0.1)} F6 =+F5+1 G6 {=+XIRR(IF($A$3:$A$463<=F6,$C$3:$C$463,0),$B$3:$B$463,0.1)} F7 =+F6+1 G7 {=+XIRR(IF($A$3:$A$463<=F7,$C$3:$C$463,0),$B$3:$B$463,0.1)} G10 {=+XIRR(IF($A$3:$A$463=F10,$C$3:$C$463,-0.0005),$B$3:$B$463,0.1)} F11 =+F10+1 G11 {=+XIRR(IF($A$3:$A$463=F11,$C$3:$C$463,-0.0005),$B$3:$B$463,-0.1)} I11 =+XIRR(C52:C228,B52:B228,-0.1) J11 =+SUM(C52:C228) F12 =+F11+1 G12 {=+XIRR(IF($A$3:$A$463=F12,$C$3:$C$463,-0.0005),$B$3:$B$463,-0.1)} I12 =+XIRR(C229:C280,B229:B280,-0.12) J12 =+SUM(C229:C280) F13 =+F12+1 G13 {=+XIRR(IF($A$3:$A$463=F13,$C$3:$C$463,-0.0005),$B$3:$B$463,0.1)} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

 

[emilnm]

Did you ever solve the problem? I have the exact same problem! with this formula

{=XIRR(IF(N2=$B$2:$B$7216;IF($K$2:$K$7216<>0;$K$2:$K$7216;-1E-29);-1E-29);$F$2:$F$7216;Q2)}

Emil