SOLVED

Help with #NUM error in IRR formula

Copper Contributor

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? IRR.PNG

19 Replies
best response confirmed by hasan ahmed (Copper Contributor)
Solution

@hasan ahmed 

IRR requires at least one negative value (as initial cost of business), see at https://support.office.com/en-us/article/IRR-function-64925EAA-9988-495B-B290-3AD0C163C1BC

@hasan ahmed 

Hi Hasan,

While calculating IRR you need to input outflow with negative sign.

I have edited it, please refer the attached file,

Thanks,

Tauqeer

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

 

    
   

 

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!






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

HansVogelaar_0-1684837496238.png

 

 

@Hans Vogelaar 

so what should I do?

@mah2023  wrote:  ``what should I do?``

 

First, you should take a critical look at the Excel file that @Hans Vogelaar provides to be sure that he understood your cash flows correctly.

 

In particular, he considers only the first 33 cash flows that you numbered in your question to Sergei Baklan.

 

But in your question to hasan ahmed, you have 35 numbered cash flows.

 

Nevertheless, if we enter the additional cash flows into A34:A35 and calculate =IRR(A1:A35), the formula has no problem returning -0.486775222080371%, even without a "guess" parameter.

 

So....

 

-----

Second, you should present the data in a spreadsheet.

 

Ideally, attach an Excel file that demonstrates the problem.

 

Alternatively, provide a link to the Excel file that you uploaded to a file-sharing website that does not require that we log in to download the file.

 

The devil might be in details that we cannot see in the text that posted.

 

And most importantly, you should show us the formula that returns a #NUM error.

 

Is it truly =IRR(A1:A33), as @Hans Vogelaar assumes, tacitly?

 

Or is it =IRR(A1:A35), as I assume?  (If so, the data is not as it appears in your postings.)

 

Or is it something else entirely?

@Joe User

 35 cash flows is correct(a1:a35), but unfortunately, an error appears after entering the formula(as the attach excel file) .

@Joe User 

I had tried 35 cash flows too, but also got #NUM!

@mah2023's data are not exactly as displayed:

HansVogelaar_0-1685349391648.png

@Hans Vogelaar  wrote:  ``I had tried 35 cash flows too, but also got #NUM!  mah2023's data are not exactly as displayed``

 

"Had tried" or "now tried"?

 

We didn't have access to the exact cash flows until now.  Right?

 

Anyway, amazing what a little detail will do! (wink)

@Joe User 

I did try it with the data as originally posted, but my memory is admittedly a bit hazy.

@mah2023  wrote: ``35 cash flows is correct(a1:a35), but unfortunately, an error appears after entering the formula(as the attach excel file)``

Amazing what a little detail will reveal!  Please keep that in mind for all future questions.

Refer to the image below, and see the attached Excel file for details.

JoeUser_0-1685394491554.png

 

 

In general, when Excel IRR returns #NUM and there is nothing wrong with the data, we need to provide a "guess" (optional second parameter) in order to the Excel algorithm.

 

Unfortunately, there is no "good" way to choose a good guess.

 

The NPV curve in columns S:T suggests that there might be IRRs between -10% and -5% and between -5% and 0%, since the NPV changes signs in T23:T25.

 

In fact, Solver (not shown) finds IRRs at about -5.1703457688% and -0.486775222%.

 

Nevertheless, Excel IRR cannot find those IRR, even with a good "guess".

 

There are several factors that might contribute to that "failure".

 

The major factor seems to be the magnitude of the cash flows, which is extremely large.

 

With integer parts of 10 to 13 digits, they are already near the limits of 64-bit binary floating-point precision.

 

If we scale the cash flows down by 1E+06 (1 million), Excel IRR has no problem finding the IRRs with very little loss in precision relatively.

 

The IRR formulas are:

O40: =IRR(O4:O38/1000000)

O41: =IRR(O4:O38/1000000, -10%)

 

To confirm the results, the NPV formulas are:

Q40: =NPV(O40, $O$4:$O$38)

Q41: =NPV(O41, $O$4:$O$38)

 

The NPVs of -8.61E-04 and 5.63E-04 are sufficiently close to zero, IMHO.

Thank you. According to your explanation, this cash flow has a negative IRR. So why does my Excel not show the negative number and gives an error?
I mean: Is there a problem with my Excel settings?

@mah2023  wrote:  ``why does my Excel not show the negative number and gives an error?``

@mah2023wrote:  ``Is there a problem with my Excel settings?``

 

It seems that you still have not learned from past mistakes, namely:  "with great details come great answers", to paraphrase Uncle Ben's advice to Spider-Man. (wink)

 

You say that Excel does "not show the negative number", and it still "gives an error".

 

Again, attach an Excel that demonstrates those problems.

 

What is your formula(s)?  What is the data, if not the same as before?

 

Exactly what does Excel display?  Show us a screenshot, or attach a JPG file with a screenshot.

 

Using your previous Excel file attachment, when I enter the formula =IRR(O4:O38/1000000) into O40 and =IRR(O4:O38/1000000, -10%) into O41, as I suggested, this is what I see:

 

JoeUser_0-1685428607434.png

 

I changed the format for O4:O38 to display 2 decimal places and to separate 1000s with commas.

 

(Aside.... Many of the values in O4:O38 have more than 2 decimal places, up to 5 decimal places.  And all of the values have what I call "floating-point residuals":  additional precision beyond 15 significant digits, which Excel does not format.)

 

The point is:  I do not see any Excel error.

 

Instead, O40 displays red 0.00, and O41 displays red (0), even though the cell values are negative.

 

That is because of the cell formats:  0.00;[Red]0.00 for O40, and 0_);[Red](0) for O41.

 

Change the formats to Percentage with 2 decimal places to see the negative cell values (rounded), namely -0.49% in O40 and -5.17% in O41.

 

Increase the number of decimal places (up to 15) to see the greater precision of the cell values.

 

-----

If that does not answer your question, follow all of the instructions above to improve our understanding of the problem(s).

 

If you need instruction on how to set the cell format, I'm afraid that varies for different versions of Excel.

 

So, let us know what version of Excel you are using, and on what device (desktop, laptop, tablet, smartphone, etc).

 

For my version of Excel, I can select the cell(s), right-click, click Format Cells and the Number tab, then select the desired format.

 

Alternatively, I can select a limited set of format options from the Home "ribbon".

@Joe User 

hi- Thank you for your explanation

Information : Excel 2016 -device: pc (desktop)-  File: It is the same as the initial file.

I know the basics of working with Excel, such as formatting and... (wink).

My main question: When I enter the yield formula into the cell it gives NUM error. Why should I reduce the unit (divide by 1,000,000) when the cash flow amounts are the same numbers? (see attach file- sheet IRR2)

@mah2023 

As @Joe User has explained very patiently, the cash flows are so large that the calculations involved approach or even exceed the limit of what Excel's 64-bit floating point engine can handle.

By dividing the values by 1000000, they become smaller, making the calculation manageable. And since all values are reduced in proportion, the result is valid for the original numbers too.

By the way, it does not have to be 1000000. 10 would have worked too, but 1000000 is safer.

See the attached workbook.

@Hans Vogelaar  wrote:  ``As @Joe User has explained very patiently [....]``

 

Thanks.  I tried to be.

 

@Hans Vogelaar  wrote:  ``exceed the limit of what Excel's 64-bit floating point engine can handle``

 

I amended my explanation later.  Such large values strain the limits of 64BFP precision.

 

Of course, 64BFP "can handle" much larger values, up to about 1.8E+308.

@Joe User 

OK, thanks for the correction.

1 best response

Accepted Solutions
best response confirmed by hasan ahmed (Copper Contributor)
Solution