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

    hasan ahmed 

    I have the same problem. please check the table and help me.

      cash flow 
      13720000000001
      02
      (77777777778)3
      (73009451097)4
      (68241124416)5
      (63472797735)6
      (58704471055)7
      (53936144374)8
      (49167817693)9
      (1416399491012)10
      (39631164332)11
      4291494012712
      4291494012713
      4291494012714
      4291494012715
      4007660998716
      3723827984717
      3439994970718
      3156161956819
      2872328942820
      2588495928821
      2304662914822
      2020829900823
      1736996886824
      1736996886825
      1736996886826
      1736996886827
      1543997232728
      1350997578629
      1157997924630
      964998270531
      771998616432
      578998962333
      3859993082

    34

      1929996541

    35

     IRR#NUM!

     

        
       

     

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

         

         

Resources