Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

‘Enable Iterative Function’ Error

Copper Contributor


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

8 Replies



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.




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


The reference in cell M66 is to a percentage rather than a date.


@Peter Bartholomew 

Thank you very much for your time and effort. Highly grateful to you.

@Peter Bartholomew please

1. guide how to start a new discussion on community

2. Excel opened on Android on Google sheets and notes created there convert to comments 

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


To start new discussion open this page and click on Start New Discussion button.

Thank you very much