‘Enable Iterative Function’ Error

%3CLINGO-SUB%20id%3D%22lingo-sub-3366721%22%20slang%3D%22en-US%22%3E%E2%80%98Enable%20Iterative%20Function%E2%80%99%20Error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3366721%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EI%20am%20attempting%20to%20complete%20an%20IRR%20function%20but%20the%20%E2%80%98%23NUM!%E2%80%99%20Error%20keeps%20showing%20up.%20I%20selected%20the%20%E2%80%98Enable%20iterative%20calculation%2C%E2%80%99%20but%20the%20error%20persists.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EHelp%3F%20Please%3F%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1388722%22%20target%3D%22_blank%22%3E%40Meltoncc%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3366721%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3367082%22%20slang%3D%22en-US%22%3ERe%3A%20%E2%80%98Enable%20Iterative%20Function%E2%80%99%20Error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3367082%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1388722%22%20target%3D%22_blank%22%3E%40Meltoncc%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMSFT%20documentation%20is%20wrong%3A%26nbsp%3B%20enabling%20Iterative%20Calculation%20has%20nothing%20to%20do%20with%20Excel%20IRR%2C%20RATE%20and%20XIRR.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20not%20enable%20Iterative%20Calculation%20unless%20you%20purposely%20use%20circular%20references%20(not%20a%20good%20idea%2C%20IMHO).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20Excel%20IRR%2C%20RATE%20and%20XIRR%20return%20%23NUM%2C%20often%20it%20means%20that%20they%20require%20the%20%22guess%22%20parameter%20to%20help%20their%20internal%20algorithm.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUnfortunately%2C%20there%20is%20no%20simple%20way%20to%20determine%20a%20good%20%22guess%22.%26nbsp%3B%20And%20sometimes%2C%20even%20a%20%22good%22%20guess%20fails%20to%20resolve%20the%20problem%20--%20due%20to%20weaknesses%20in%20the%20internal%20implementation%2C%20IMHO.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20try%20to%20find%20good%20guesses%20for%20IRRs%20(there%20might%20be%20more%20than%20one!)%20by%20setting%20up%20a%20table%20of%20the%20%22NPV%20curve%22.%26nbsp%3B%20That%20is%2C%20I%20calculate%20the%20NPV%20with%20varying%20discount%20rates%20(typically%20-99%25%20and%20-95%25%20to%20%2B100%25%20in%205%25%20increments)%20and%20look%20for%20changes%20in%20sign%20or%20direction.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20for%20%22real%20world%22%20problems%2C%20often%20the%20%23NUM%20error%20and%20the%20need%20for%20a%20%22guess%22%20arises%20because%20the%20cash%20flow%20model%20is%20incorrect.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20further%20assistance%2C%20I%20suggest%20that%20you%20attach%20an%20Excel%20file%20that%20demonstrates%20the%20problem.%26nbsp%3B%20Click%20on%20%22browse%22%20near%20the%20bottom%20of%20the%20reply%20window.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20cannot%20attach%20a%20file%20(some%20people%20claim%20the%20forum%20does%20not%20permit%20it)%2C%20upload%20the%20Excel%20file%20to%20a%20file-sharing%20website%2C%20and%20post%20the%20download%20URL.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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

1 Reply

@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.