Forum Discussion
‘Enable Iterative Function’ Error
Hello,
I am attempting to complete an IRR function but the ‘#NUM!’ Error keeps showing up. I selected the ‘Enable iterative calculation,’ but the error persists.
Help? Please? Meltoncc
I am not able to start a new discussion hence I had to start with a reply to similar problem by other communit member, sorry for that. am getting error message "XIRR attempted to compute the internal rate of return for a series of cash flows, but it was not able to"
Please help
Thanks in advance for your time and favour
- please
1. guide how to start a new discussion on Microsoft tech community
2. how to fix problem of Excel opened on Android on Google sheets and notes created there convert to comments
MSFT documentation is wrong: enabling Iterative Calculation has nothing to do with Excel IRR, RATE and XIRR.
Do not enable Iterative Calculation unless you purposely use circular references (not a good idea, IMHO).
When Excel IRR, RATE and XIRR return #NUM, often it means that they require the "guess" parameter to help their internal algorithm.
Unfortunately, there is no simple way to determine a good "guess". And sometimes, even a "good" guess fails to resolve the problem -- due to weaknesses in the internal implementation, IMHO.
I try to find good guesses for IRRs (there might be more than one!) by setting up a table of the "NPV curve". That is, I calculate the NPV with varying discount rates (typically -99% and -95% to +100% in 5% increments) and look for changes in sign or direction.
But for "real world" problems, often the #NUM error and the need for a "guess" arises because the cash flow model is incorrect.
For further assistance, I suggest that you attach an Excel file that demonstrates the problem. Click on "browse" near the bottom of the reply window.
If you cannot attach a file (some people claim the forum does not permit it), upload the Excel file to a file-sharing website, and post the download URL.
