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 but I'm not sure the best place to post it if someone wants to see the exact issue. Thanks again for the help!

Kevin