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

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

8 Replies

@Meltoncc 

 

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.

 

 

@OliverScheurich

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

@SatyaGupta_In 

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 

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

@SatyaGupta_In 

To start new discussion open this page https://techcommunity.microsoft.com/t5/excel/bd-p/ExcelGeneral and click on Start New Discussion button.

Thank you very much