USA What is wrong with CFs for IRR?

[kenm]

Please help me explain what is wrong with the CFs for an IRR calculation below, if only for my edification. Refer to columns B:E.

For brevity, I have hidden rows 9:12 and 30:59, which have zero CFs. See the attached Excel file for details.

The mathmetical calculation is correct for the monthly IRR in B64, as demonstrated by the near-zero NPV in C65.

The formulas are =IRR(C8:C62) and =NPV(C64,C8:C62).

But a monthly IRR of about 70.5% seems incredulous.

Do you agree?

-----

I suspect that the CF model is misstated and/or missing information. GIGO!

But I am not confident about the changes that I propose below.

Please feel free to speculate corrective changes to the CFs. I can crunch the numbers, if necessary.

Disclaimer: This is a how-to discussion. It is not about the pros and cons of the use of IRR for business analysis.

-----

I'm afraid I am not be able to fill in much descriptive detail that might be useful. The CF model problem was presented by another user.

According to the user, the cash flows are a real estate development project.

The first outflow in C8 is for "sunk costs" (sic). That is followed 4 months later with "pre-sales" (sic) (inflows) starting in C13. Then development occurs in C18 (outflows) -- and apparently some additional pre-sales or funding (inflows). That is followed 30 (!) months later with final sales (inflows) starting in C60.

The result is the CF model above, again provided by the user.

-----

FWIW, the following are some of the "corrections" (?) that I propose. Refer to columns G:J.

1. The first net cash flow should be zero.

In order for there to be an initial outflow, there must be a corresponding inflow (debt?) of at least that amount. That nets to zero or more.

And that changes the total "net cash flow" (sic) in G5.

2. The last net cash flow should include the remaining cumulative balance in I62, recorded as an outflow (negative).

To that end, the formula in H62 is =$C62-I62.

But the resulting IRR in H64 is zero, because the sum of the cash flows is zero, not surprisingly.

Mathematically, I understand why: there is no "internal" rate of change.

But any debt payments and interest on "pre-sales" might be hidden in the net cash flows.

 

[kirby]

For IRR to work correctly it assumes in the initial row that this is 100% of your payment for the investment and then the other rows are the cash flows related to that.

And also since you have many changes in cash flows from positive to negative read this.

Internal Rate of Return: Multiple IRRs | Saylor Academy

Learn how to make smart financial decisions by determining which projects will offer the best potential payoff, figuring out your best investment opportunities, and evaluating stock options.

learn.saylor.org

 

[kenm]

Kirby, thanks for your response.

To clarify, I wanted to concentrate on the cash flow model, not the method.

I do not need any assistance in understanding the IRR. I just discovered that I have more than 40 years of experience using the IRR and explaining it to others. I am very familiar with the theory and mathematics of the algorithm and its limitations, as well as its application to loans and investments.

No matter. I finally found the following CFI webpage: Real Estate Development Model . I am hopeful that will give me the insight that I need to apply to the original user's problem.

BTW, I draw your attention to the following section: ``Free Cash Flow and [COLOR=%s]IRR[/COLOR] [....] We can now calculate the levered free cash flows and resulting [COLOR=%s]IRR[/COLOR] of this project``.

-----

For IRR to work correctly it assumes in the initial row that this is 100% of your payment for the investment and then the other rows are the cash flows related to that.

I'm sorry, but that is patently wrong.

It is true that the IRR algorithm is more reliable when that condition is true. But that is not a requirement.

In fact, it is very common to calculate the IRR of an investment that has multiple deposits and withdrawals over time.

However, what is important is that the cash flow model reflects all of the external flows into and out from the model, including any initial balance and ending balance or "terminal value".

That is what I believe is lacking in the user's original cash flow model. Again, hopefully the CFI webpage fill in the gaps for me.

-----

since you have many changes in cash flows from positive to negative read this.

I am well aware that there might be multiple or no IRRs for some correct cash flow models.

But that is no the issue here.

As I noted previously, the IRR for this user's example was not incorrect (because NPV is nearly zero). It just seems "eccentric". Note that the monthly rate of about 70.51% compounds to an annualized rate of about 60300% (!).

So, the calculation of the IRR with the original cash flow model is not in question.

However, when I see such eccentric IRRs for real data (supposedly), often the reason is that the cash flow model is not constructed correctly.

Be that as it may, I neglected to include an NPV curve to demonstrate that the IRR at 70.51% is indeed probably the only IRR for the user's original cash flow model. This is demonstrated below.

The formula in D70 is =NPV(C70, $C$8:$C$62).

The NPV curve shows that there are no IRRs before the IRR at 70.51%. The sign change between 70% and 75% (green-highlighted) usually -- but not always -- indicates an IRR where the NPV curve crosses the x-axis (NPV=0).

There is also an inflection point near 150% (yellow-highlighted) where the NPV curve turns back toward zero. But it appears to be asymptotic to a value infinitesimally less than zero. And in any case, a monthly IRR beyond 10000% would be even more "eccentric" than the IRR at 70.51%.

 

[kenm]

Please help me explain what is wrong with the CFs for an IRR calculation below

The answer is: nothing! I was just having a brain f.a.r.t. (sigh)

-----

The mathmetical calculation is correct for the monthly IRR [....] But a monthly IRR of about 70.5% seems incredulous.

And the annualized IRR is about 60300% (!), which is what really what grabbed me -- and the original user.

But it is what it is!

There might be errors in the cash flows that cause the incredulous IRR. But we cannot imagine what they might be, based on the dearth of detail. So we must accept the numbers as they are.

-----

The first net cash flow should be zero. In order for there to be an initial outflow, there must be a corresponding inflow (debt?) of at least that amount.

Utter nonsense!

I always tell people that we can match the signs to the terms "inflow" and "outflow" arbitrarily, as long as we are consistent. It's just a matter of point of view.

I confused myself because I was looking for a problem that does not exist. Doh!

 

[kirby]

Hi kenm
Sounds like you might well have everything well under control. Good Luck!