XIRR fail -- cash flows alternate between positive and negative

[macrorookie]

Hi - I'm having a hard time determining the IRR on a project with cashflows that are sometimes positive (+) and sometimes negative (-). I understand this is a limitation of the XIRR formula (through google searches), but I can't find a proper alternative to the XIRR formula.
My challenge is to find the IRR for these irregular and alternating (+/-) cash flows.
The attached sample workbook shows my problem.
Thanks in advance for any help!
Jeff

[macrorookie]

I attached a new sample workbook with the following clarification: my current XIRR formula is returning a 232554% return (wildly wrong!).
I believe I need to use XIRR because my cashflows are inconsistent; however, the alternating +/- cashflows are messing up my XIRR formula.
Thanks much.
Jeff

[curiouscat408]

my current XIRR formula is returning a 232554% return (wildly wrong!).

What makes you think that 232554% is wrong? What IRR do you expect?

Since your data is monthly (except for the first cash flow), you can use Excel IRR as follows:

=IRR((D13, F13:BF13))

That returns 90.4318425005324%.

(The syntax (D13, F13:BF13) creates a "range union". Excel IRR is one of the few functions that supports that syntax. In effect, I pull the initial cash flow on 12/31/22 into 1/1/23 -- a one-day difference compared to XIRR. So for Excel IRR, the cash flow series is effectively B13,F13,G13,...,BF13.)

But that is a monthly IRR. Excel XIRR returns an annual IRR.

We can annualize the monthly IRR as follows:

=(1 + IRR((D13, F13:BF13)))^12 - 1

That returns 227344.182337612%, which is "close" to the XIRR result.

-----

I suspect that your cash flows are incorrect.

What do they represent?

What is -595445 on 12/31/22?

What is 4011719.83 on 5/1/23?

If negative numbers are outflows (see B7), why does your cash flow series start with an "outflow" (loss?) and end with an inflow?

-----

And just a curiosity.... Why do your "dollar" amounts have more than 2 decimal places?

They appear to be the result of calculations.

For example, I13 is 4011719.83171275 + 2.79E-9.

What are you calculating?

[macrorookie]

Thank you, curiouscat408! I made a mistake on my 12/31/22 date. It should be 12/1/222, thereby creating consistent monthly increments. You raise a great point about the monthly IRR, versus the annual IRR - that solves a big mystery for me (thank you!).

I believe my cash flows are correct. This is for a real estate development project. Therefore the 12/1/22 cash outflow is for sunk costs, then pre-sales happen (inflows), and then development starts (outflows), until final sales happen (inflows).

If I calculate the NPV for these cash flows, I get 70.5%, but as you point out that's a monthly number (525% annualized). I just need to create a good explanation for why the IRR/NPV is so high.

[curiouscat408]

my 12/31/22 date [....] should be 12/1/22

So:

XIRR(D13:BF13,D11:BF11) returns 62103.9447021484%
and
IRR(D13:BF13) returns 70.5110946805731%

-----

If I calculate the NPV for these cash flows, I get 70.5%, but as you point out that's a monthly number (525% annualized).

Errata.... Monthly 70.5% -- actually IRR(D13:BF13) -- annualizes to 60299.2729556859% -- not 525% (sic) or even 525 (assuming a typo).

The formula is =(1 + IRR(D13:BF13))^12 - 1.

And arguably, even if you think we annualize by 12*IRR(D13:BF13), the result is 846.133136166878% -- still not 525% (sic).

-----

And the NPV can be anything that you want, depending on the required discount rate.

But more to the point: it returns a dollar amount, not a percentage.

In fact, I was just going to follow up my previous comments by noting that the real validation of the results from XIRR and Excel IRR is the fact that XNPV(xirr, D13:BF13,D11:BF11) and NPV(xlIrr, D13:BF13) return about zero.

Arguably, it is possible that there are other IRRs that we might perceive to be "better".

But in this case, I looked at the NPV curves, and I believe there aren't.

[macrorookie]

Wow curiouscat408 - that is a great explanation! Now I know more about Excel and discounted cash flows. Thank you so much!

[curiouscat408]

[.... deleted ....]