XIRR - Array Formula Error

dtarquin

New Member
Joined
Mar 6, 2012
Messages
10
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

ABCDEFGHIJ
C2009
C2010
C2011
C2012
D2009
D2010#NUM!<==
D2011#NUM!<==
D2012

<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
CellFormula
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>
 
Last edited:
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
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top