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!!!