#num! Error in IRR formula
pls help me to sort out above #num! Error in IRR FUNCTION
Forum Discussion
If I3 is less (more negative) than about -87,509, there is no solution.
IRR tries to find the rate for which the NPV of the amounts in B3:I3 is 0, but there is no such rate: the NPV never becomes 0 or positive. In the chart below, I used I3 = -87.510.
The x-axis is the rate, and the y-axis is the NPV. As you can see, the NPV doesn't touch the x-axis.
For I3=-1,57,249 it is much worse:
The NPV doesn't even come near the x-axis.
HansVogelaar wrote: ``If I3 is less (more negative) than about -87,509, there is no solution``
An astute and interesting observation.
We might also note that it has little to do with the small changes in row 3.
For the cash flows in B2:I2, there is also no solution if I2 is -87509 or less (more negative). With -87509, the NPV appears to be asymptotic near -8.1665 with an IRR near -34.544364960805%, again never crossing NPV=0.
-----
Usually, bizarre results like this are due to an erroneous cash flow model. In your case, it is unusual for the first and last cash flows to have the same sign.
We might be able to help you with that if you provide some context.
Explain the nature of the cash flows. What do they represent?
And provide an Excel file (or a link to it) that demonstrates the context and the derivation of the cash flows.
It might also help if you provide an explanation of the assignment verbatim. Ideally, a PDF (or a link to it) or a screenshot, not your interpretation of it.
