Forum Discussion
Help with #NUM error in IRR formula
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
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
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!
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.
so what should I do?
mah2023 wrote: ``what should I do?``
First, you should take a critical look at the Excel file that HansVogelaar 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 HansVogelaar 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?
