# XIRR - Array Formula Error

1. ## XIRR - Array Formula Error

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. I have attached a sample of my workbook for further reference and 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)}

Thank you for the help!

_Dave  Register To Reply