XIRR and IRR

[kevwags]

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

[MarvinP]

Hi Kevin and welcome to the forum,

To post your sample, click on the "Go Advanced" below the message area and then on the PaperClip Icon above the new advanced message area. This will open a window asking for a file to attach.

I find the msdn sites most helpful for these kinds of problems. Read the Remarks on
http://msdn.microsoft.com/en-us/library/ff837759.aspx
to see that all the parameters/arguments are the correct objects.

[kevwags]

File is attached. Thanks!

[kevwags]

Thanks for message - I have reviewed all the remarks previously and I think my issue may have to do with the 100 iteration limit language:

Excel uses an iterative technique for calculating XIRR. Using a changing rate (starting with guess), XIRR cycles through the calculation until the result is accurate within 0.000001 percent. If XIRR can't find a result that works after 100 tries, the #NUM! error value is returned. The rate is changed until: where: di = the ith, or last, payment date. d1 = the 0th payment date. Pi = the ith, or last, payment.

Either way I'm looking for a work around! I attached my file in a separate reply to the thread.

[MarvinP]

Hey Kevin,
I edited my post above yours. Have you tried the link? Have you read the remarks? Do they answer your question?

We're looking for one of the Smart Gurus for an answer on this one. I'm over my head.

[kevwags]

Please see my previous post above. I had previously read the link and I think I know the main source of the issue to be due to iterations but I need a work around.

Thanks again!

[MarvinP]

Hi Kevin,

I had this math class a long time ago and we talked about convergence. The teach kept plugging the answer into the next guess which then got closer, but only sometimes. We were supposed to learn that some original guesses are outside the range that finally converge. If you picked a good guess you got a close enough answer in 5 iterations and it you didn't, you may never get an answer.

I believe this comes into effect in the remarks of the msdn link, when they say one needs to be positive and the other negative.

When Excel included the Solver Tool - long after my college class. I wondered how they got around this "bad first guess" problem. I've understood recently they now have improved solver to use the best algorithm, way beyond the best guess, to give answers. See http://office.microsoft.com/en-us/ex...001124595.aspx

I'm wondering if the answer to your problem is to "Make a Better First Guess"?

I'm still way over my head with this problem, but hope the above gives some philosophical answer.