IRR returns NaN when it fails to converge on a result

CharlesBowman
Contributor

IRR returns NaN when it fails to converge on a result

This item has been identified as an outstanding known issue. A workaround is provided until the issue is officially fixed.

 

Issue

IRR returns NaN when some of the following conditions are satisfied:

  • There a small number of cashflows (less than 5)
  • The difference between the numbers is large (>10,000)
  • The resulting IRR value is large (over 500)

The current implementation will fail to calculate IRR when the above situations are satisfied and it will return NaN. The reason for this is because of the iterative nature of the function fails to converge the large void between the numbers. 

 

Examples:

CharlesBowman_0-1607945227535.png

CharlesBowman_1-1607945290912.png

 

 

 

Workaround

  1. If the values have a lot of significant figures, using ROUND on the cashflows can help as it changes the numbers. This will cause a slight loss of accuracy though and the rounding value will have to be adjusted on a case by case basis.
  2. Create a read/write connection in the Anaplan Excel Add-in. Use Excels XIRR function to calculate the value and then write the data back to Anaplan.

If this workaround is not suitable in your use case, please contact Support at support@anaplan.com.

 

---

Internal Reference: CALC-2252