Unexplainable XIRR #NUM! Error

[Jay]

I have this great model to calculate project returns, really flexible in lots
of ways. I have an amortization table for project loans and I can change the
amount of the loan and the interest rate on an input tab and see how the IRR
changes. I use the XIRR function which uses a line of cash flows and the
corresponding dates (calculated using an EDATE function). There is no guess
because the IRR could be anything.

This model works really well. As I slowly ramp up the amount of the loan the
IRR increases just as it should. For example 15% leverage with a 7% interest
rate gives an IRR of 8.22%. 20% leverage at the same rate gives a 17.88% IRR.
Then I go to 21% with the same interest rate and blammo! #NUM! error.

I have analyzed the cash flows for the 20% and 21% scenario and cannot see
any reason why the IRR would be so dramatically different.

I have replicated this issue over and over again with this model. Depending
on the project parameters there seems to always be a point where slightly
increasing the amount of my loan takes a reasonable IRR and turns it into a
#NUM! error.

This is an xlsx file so I don't think the EDATE should be the issue, plus it
works so well until the error that I just can't figure it out. I've even
tried to put in a guess that I know should be really close and it still
returns the error.

Please help!!!

 

[ryguy7272]

You may have two IRRS. I tried repeatedly to share an example of two IRRs
(on a free file hosting site). I couldn’t upload the file, but look for some
info on problems that occur with two IRRs. That may answer your question.

Good luck,
Ryan---

 

[Ashish Mathur]

Hi,

If you so wish, you may mail me the file at ask(at)ashishmathur(dot).com.
Please explain the problem very clearly.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Fred Smith]

While it's commonplace for XIRR to return #Num with the default guess of
10%, it's very unusual for XIRR to continue to fail with a decent guess. Are
you sure you are entering the guess properly? (For example, it needs to be
21% or 0.21, not 21).

If your cash flows are consistently periodic (ie monthly), then another
option you have is to use the IRR function. It sometimes behaves better.

If you want me to take a look at it, you can e-mail it to:
fred dot smith at shaw dot ca.

Regards,
Fred.