Forum Discussion

hasan ahmed's avatar
hasan ahmed
Copper Contributor
Jul 13, 2019
Solved

Help with #NUM error in IRR formula

In the attached image and file (See tab B&C, Cell B15,) I'm having difficulties with the IRR formula as it is displaying a #NUM error instead of a numerical value. What seems to be the issue here? ...
excel
Formulas
Formulas and Functions
  • mah2023's avatar
    mah2023
    Copper Contributor
    May 23, 2023
    I have the same problem. please check the table and help me.
    cash flow
    1372000000000 1
    0 2
    (77777777778) 3
    (73009451097) 4
    (68241124416) 5
    (63472797735) 6
    (58704471055) 7
    (53936144374) 8
    (49167817693) 9
    (1416399491012) 10
    (39631164332) 11
    42914940127 12
    42914940127 13
    42914940127 14
    42914940127 15
    40076609987 16
    37238279847 17
    34399949707 18
    31561619568 19
    28723289428 20
    25884959288 21
    23046629148 22
    20208299008 23
    17369968868 24
    17369968868 25
    17369968868 26
    17369968868 27
    15439972327 28
    13509975786 29
    11579979246 30
    9649982705 31
    7719986164 32
    5789989623 33
    3859993082
    1929996541

    IRR #NUM!






    • HansVogelaar's avatar
      HansVogelaar
      MVP
      May 23, 2023

      mah2023 

      IRR uses an iterative algorithm to find the rate. Apparently, the iteration failed.

      I tried several methods using the Solver add-in to find the rate. GRG Non-Linear and Simplex LP failed. Evolutionary came close, but was still a bit off.

      So I set up a simulation - see the attached workbook.

      It shows that the NPV switches from + to - near the limit of Excel's precision. Note the repetitions in the NPV column.

       

       

      • mah2023's avatar
        mah2023
        Copper Contributor
        May 29, 2023

        HansVogelaar 

        so what should I do?

Resources