So I don't usually get stumped in excel but.. here's my question hopefully someone can solve in Excel 2010:

I have a set of cash flows that are regularly periodic (monthly) for 12 years stripped out of a financial model. The first two years are periodic investments that are negative cash flows which occur some months and not others (shown as zero values). Currently I am using an XIRR formula to get the return for the full 12 years of CF's. In a positive IRR situation it works fine. If I grab the first 3-4 years which should produce a negative IRR, the XIRR formula goes to 0%.

Okay, I've researched this issue and it requires you to put a formula in the "Guess" argument of the XIRR formula such as 0.1*SIGN(SUM(A1:A3)) where A1:A3 are the cashflows. As soon as I put in this type of formula my XIRR goes directly to #NUM error. I believe this means that after 100 iterations IRR did not find a solution. I have tested multiple negative guess numbers with no success and I know about the range of the negative IRR it should produce (-2% or something).

At this point I decided that because the CFs are periodic I'll switch this to a regular IRR formula and annualize it. When I plug in an IRR formula it returns 34% which is completely bogus and gets worse when you annualize it. Upon further inspection I realize that no matter what size selection I use for the regular IRR formula of 3+ years it has the same % return. Another obvious error.

Here are some other quirks about the data that might be helpful:
Unfortunately the first value of the data is -0.000001 to negate the issue with XIRR of having a positive first CF value. There are many zero values in between months that have CF values.

Please let me know if you have any further questions and I really would appreciate the help. I have stripped out this issue into a separate excel file as an example and it is attached. Thanks again for the help!

Kevin

In case anyone cares I solved this myself. I think it's just a bug in excel.

The first nominal value I was using of -0.0001 to start the CF's with a negative value I changed to -5. Luckily my CF's are 100k or larger so the IRR will not be affected. This small change in combination with the SIGN guess logic described above fixed the problem....

I guess sometimes guess and check is the best way when you are dealing with an excel bug workaround.