First, your data goes through
BG94, not
AJ94.
I confirmed that the
annualized IRR for AJ94:
BG94 is indeed about 435732.67%, and for AJ94:
BF94, it is indeed about 20.67%, which are close to your 435731.6%
[sic] and "about 20%"
[sic].
(The differences are probably because you posted rounded displayed values, but the underlying values have digits after the decimal point.)
Second, your cash flows are what is strange, IMHO. I suspect they are incorrect. If you want help with that, post an explanation of the numbers.
Because of the "strange" cash flows, the data in AJ94:
BG94 have
3 IRRs between -99% and 200%. BTW, the data in AJ94:
BF94 have
2 IRRs.
(I didn't bother to look beyond 200%.)
The "stranger" the cash flows, the more likely it is that there are multiple IRRs or even none at all. See the wiki IRR webpage for more information.
By coincidence, with the
default "guess" (10%), IRR(AJ94:
BG94) finds the
highest IRR, but IRR(AJ94:
BF94) finds the
lower IRR.
The "inconsistent" behavior is not unexpected. First, mathematically, the iterative IRR method is unpredictable when there are multiple IRRs. Second, the
Excel IRR implementation is flawed, IMHO. Another implementation might behave more "consistently" with this data.
However, IRR(AJ94:
BG94,
5%) finds the IRR that annualizes to about 18.71%, which is close to 20.66% for IRR(AJ94:
BF94).
The multiple IRRs for the data in AJ94:
BG94 can be seen by creating a table of the "NPV curve", which is the NPV for various discount rates between -99% and 200% (in this case).
| A | B | C | D | E | F |
|---|
| 1 | %discnt | npv | | guess | monthly irr | annl irr |
| 2 | -99% | -5.09E+123 | | -35% | -34.64% | -99.39% |
| 3 | -90% | -4.81E+64 | | 5% | 1.44% | 18.71% |
| 4 | -80% | -7.69E+46 | | 10% | 101.04% | 435732.67% |
| 5 | -70% | -2.79E+36 | | 105% | 101.04% | 435732.67% |
| 6 | -60% | -9.91E+28 | | | | |
| 7 | -50% | -1.38E+23 | | | | |
| 8 | -40%
| -1.27E+18 | | | | |
| 9 | -30%
| 1.62E+14
| | | | |
| 10 | -20% | 2.82E+11 | | | | |
| 11 | -10% | 8.32E+08 | | | | |
| 12 | 0%
| 3.10E+06 | | | | |
| 13 | 10% | -2.91E+06 | | | | |
| 14 | 20% | -2.08E+06 | | | | |
|
|
| | | | |
| 21 | 90% | -6.27E+04 | | | | |
| 22 | 100%
| -5.05E+03 | | | | |
| 23 | 110% | 3.78E+04 | | | | |
| 24 | 120% | 6.99E+04 | | | | |
|
|
| | | | |
| 32 | 200% | 1.55E+05 | | | | |
<tbody>
</tbody>
Formulas:
B2: =NPV(A2, $A$94:$BG$94)
E2: =IRR($A$94:$BG$94, D2)
F2: =(1+E2)^12 - 1
Copy the formulas down their columns
The inflection points (changes of sign) in column B suggest that there
might be an IRR between -40% and -30%, 0% and 10%, and 100% and 110%. The midway "guesses" in column D demonstrate that Excel IRR does indeed return reasonable results.
FYI, the following table shows the "NPV curve" for the data in AJ94:
BF94.
| H | I | J | K | L | M |
|---|
| 1 | %discnt | npv | | guess | monthly irr | annl irr |
| 2 | -99% | 2.76E+121 | | 5% | 1.58% | 20.66% |
| 3 | -90% | 3.04E+63 | | 10% | 1.58% | 20.66% |
| 4 | -80% | 1.18E+46 | | 105% | 101.04% | 435732.67% |
|
|
| | | | |
| 11 | -10% | 1.09E+09 | | | | |
| 12 | 0%
| 3.61E+06 | | | | |
| 13 | 10% | -2.91E+06 | | | | |
| 14 | 20% | -2.08E+06 | | | | |
|
|
| | | | |
| 21 | 90% | -6.27E+04 | | | | |
| 22 | 100%
| -5.05E+03 | | | | |
| 23 | 110% | 3.78E+04 | | | | |
| 24 | 120% | 6.99E+04 | | | | |
|
|
| | | | |
| 32 | 200% | 1.55E+05 | | | | |
<tbody>
</tbody>
Formulas:
I2: =NPV(H2, $A$94:$BF$94)
L2: =IRR($A$94:$BF$94, K2)
M2: =(1+L2)^12 - 1
Copy the formulas down their columns
The inflection points (changes of sign) in column I suggest that there
might be an IRR between 0% and 10%, and 100% and 110%. The midway "guesses" in column K demonstrate that Excel IRR returns reasonable results.
