IRR returning "#NUM!"

[rydo22]

Hello,



I am having issues with the excel IRR function. My current cashflows are currently returning "#NUM!". I have tried adjusting the iterations and maximum change and my guess. Currently my formula looks like "=IRR(B4:B87,-0.3)" and my cashflows are the following:

Nov-08	(5,754,636)
Dec-08	-
Jan-09	-
Feb-09	-
Mar-09	-
Apr-09	-
May-09	-
Jun-09	-
Jul-09	1,424,449
Aug-09	-
Sep-09	-
Oct-09	-
Nov-09	-
Dec-09	-
Jan-10	-
Feb-10	-
Mar-10	-
Apr-10	-
May-10	-
Jun-10	(143,866)
Jul-10	-
Aug-10	-
Sep-10	-
Oct-10	-
Nov-10	-
Dec-10	-
Jan-11	-
Feb-11	-
Mar-11	-
Apr-11	-
May-11	-
Jun-11	-
Jul-11	-
Aug-11	-
Sep-11	-
Oct-11	-
Nov-11	-
Dec-11	-
Jan-12	-
Feb-12	-
Mar-12	-
Apr-12	-
May-12	-
Jun-12	-
Jul-12	-
Aug-12	-
Sep-12	-
Oct-12	-
Nov-12	-
Dec-12	-
Jan-13	-
Feb-13	-
Mar-13	-
Apr-13	-
May-13	-
Jun-13	-
Jul-13	-
Aug-13	-
Sep-13	-
Oct-13	-
Nov-13	-
Dec-13	-
Jan-14	-
Feb-14	-
Mar-14	-
Apr-14	-
May-14	-
Jun-14	-
Jul-14	-
Aug-14	-
Sep-14	-
Oct-14	-
Nov-14	-
Dec-14	-
Jan-15	-
Feb-15	-
Mar-15	-
Apr-15	-
May-15	-
Jun-15	-
Jul-15	-
Aug-15	-
Sep-15	-
Oct-15	-

<colgroup><col style="width: 147px;"><col style="width: 97px;"></colgroup><tbody>
</tbody>

Where the cells with "-" contain zeros and the flows are cells B4:B87. The part that is extremely frustrating is when I add as little as a penny (.01) as the cashflow for the last entry, October 1st of 2015, the formula works and I am getting a return that makes sense.



Also, as a sidenote: I have also tried the XIRR formula to find the same results.



Any help would be much appreciated!



Thanks,

Ryan

 

[BiocideJ]

Both the IRR and XIRR formulas require a series of cashflows, but they also require final distribution value (that would zero out the account) to calculate how much is actually IN the account at the end of the time period.

Simplistically speaking, this is normally signed opposite of the inital investment. (i.e. if the initial investment is negative the final value should be positive and vice-versa.)

So for this to work, add a new row for 2015-11 and put the current account balance (positive number) and you should find the calculation works correctly.

Also, I have never needed to include a value for the guess argument.

 

[rydo22]

BiocideJ,

For this particular CashFlow it wound up being a negative return. So there was an investment, then one positive flow, then one negative, and then it was over. So I am unsure as to what the positive account balance could be. Is there a way around this or a different formula that would work better?

Thanks

 

[BiocideJ]

The way your cash flows read is that 5,754,636 was invested 11/2008, then 1,424,449 was withdrawn 07/2009 and then 143,866 was re-invested on 6/2010 and then that investement went to 0.

If the final balance is actually 0, then the return is simple... -100%. But that seems unlikely, so I'm a bit confused.


More likely, there is X in the account right now. Let's call it 4,000,000. So basically, 4.5M was invested and only 4.0M remains. This 4,000,000 value needs to be put as a cash flow (for the purposes of IRR) for today.
It is basically saying if I am no longer invested and liquidated all of my assets today, what is my annualized return since inception.

 

[BiocideJ]

This is a poorly document function (amazing, right?), but this line in the Help document explains what I am trying to say.

IRR is closely related to NPV, the net present value function. The rate of return calculated by IRR is the interest rate corresponding to a 0 (zero) net present value. The following formula demonstrates how NPV and IRR are related:

This is basically saying, you need to tell it what cash flow will bring the net present value to 0, which conveniently is done by adding the value for the current value to the last row of the table.

 

[rydo22]

This helped out a lot, thanks BiocideJ!

 

[BiocideJ]

No problem. I fortunately (or unfortunately) have had a lot of experience using the IRR and XIRR functions.

 

[Houston_Guy]

BiocideJ, so if in the end of an investment the remaining value goes to $0, the XIRR is always -100%? I keep running scenarios and as soon as the remaining value falls below 50% of the previous estimated value, the XIRR function returns #NUM .

 

[joeu2004]

If the final balance is actually 0, then the return is simple... -100%.

That is incorrect, in general.

For example, most loans are structured to have zero balance in the end. Some annuities, as well.

If we have a loan of $100,000 with 24 periodic payments of $4500 and zero end-balance (i.e. no "balloon" payment), then the IRR in C1 is
=RATE(24,45000,-100000), which returns 0.625074652888644%.

Alternatively, enter -100000 into B1 and 4500 into B2:B25. Then the IRR in C2 is =IRR(B1:B25), which returns 0.625074652890989%.

The small difference can be explained. But the point is, both =NPV(C1,B1:B25) and =NPV(C2,B1:B25) return "close to zero", about -2.02E-10 and -2.86E-08 respectively.

-----

The IRR of a series of cash flows ending in some number of zero cash flows is mathematically equal to the IRR of that series of cash flows truncated after the last non-zero cash flow.

So, in rydo22's case, the IRR of B4:B87 should be the same as the IRR of B4:B23 (Jun-10).

To demonstrate, if we replace B23 with 6,000,000, note that IRR(B4:B23) = IRR(B4:B87), which is 1.53340711898509%.

Mathematically, the reason is: Excel NPV is calculated by the following (yes, sigh, Excel NPV discounts the first term):

=CF1/(1+r)^1 + CF2/(1+r)^2 + ... + CF20/(1+r)^20 + 0/(1+r)^21 + ... + 0/(1+r)^84

Note that zero divided by any non-zero value is zero. So the latter zero-terms do not contribute to the calculation of the NPV.

Apparently, Excel NPV is not clever enough to realize that if the numerator is zero, the discounted cash flow is zero (unless r=-100%). Instead, first it tries to calculate the denominator, which exceeds the computational limitations of 64-bit binary floating-point for some values of "r".

So in general (but sadly, not rydo22's case), we might have better luck with IRR(B4:B23) than with IRR(B4:B87).

For example, when "r" in C4 is 1E+10 (1000000000000% !), NPV(C4,B4:B87) returns a #DIV/0 error, whereas NPV(C4,B4:B23) returns
about -5.75E-04, "close to zero".

 

[joeu2004]

Errata....

If we have a loan of $100,000 with 24 periodic payments of $4500 and zero end-balance (i.e. no "balloon" payment), then the IRR in C1 is
=RATE(24,45000,-100000), which returns 0.625074652888644%.

Alternatively, enter -100000 into B1 and 4500 into B2:B25. Then the IRR in C2 is =IRR(B1:B25), which returns 0.625074652890989%.

45000 is an obvious typo. Shoulda been 4500.