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 nearzero 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 howto 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 "presales" (sic) (inflows) starting in C13. Then development occurs in C18 (outflows)  and apparently some additional presales 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 =$C62I62.
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 "presales" might be hidden in the net cash flows.
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 nearzero 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 howto 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 "presales" (sic) (inflows) starting in C13. Then development occurs in C18 (outflows)  and apparently some additional presales 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 =$C62I62.
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 "presales" might be hidden in the net cash flows.
Attachments

14.6 KB Views: 0