May 12 2022 11:26 AM
May 12 2022 01:03 PM - edited May 12 2022 01:11 PM
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.