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]

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 .

No, I was being emphatic. In the example given, he was putting 5M in and only getting 1M out and then it was like the other 4M just evaporated. REMEMBER, XIRR and IRR require CASHFLOWS, not balances.
So every time money is being INVESTED or WITHDRAWN needs to be captured any other adjustments due to interest, dividends, etc. should not be captured in the data because they will be calculated by the final value in relation to the other cashflows. It is difficult to ascertain what your problem is specifically, but based on the limited information you provided, it appears that you are trying to load balances instead of cashflows into the formula.


As for @joeu2004, umm OK.