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.
Sep 08 2023 10:05 PM
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"
Thanks in advance for your time and favour
Sep 10 2023 05:34 AM
Sep 10 2023 09:54 AM
To start new discussion open this page https://techcommunity.microsoft.com/t5/excel/bd-p/ExcelGeneral and click on Start New Discussion button.