IRR # NUM

[Tracy]

Hi all

I have a formula that works if I change some the final value in the array,
please tell me if this number is a limitation in Excel or I am doing
something wrong in the formula

the array contains the following numbers

-42,742,752
0
10,168,420
11,238,780
2,675,900
0
2,675,900
5,351,800
2,675,900
0
2,675,900
0
0
0
0
3,211,080
0
0
1,070,360
0
0
0
0
0
0
0
0
1,070,360
0
0
0
0
0
0
0
0
0
0
0
535,180
1,444,986
0
963,324
0
1,155,989
0
1,016,842
401,385
428,144
0
0
0
1,391,468
0
0
588,698
1,016,842
0
0
0
-12,443,805

The formula is
=(1+IRR(I96:I156,0.1))^12-1

I get the #NUM error
If I change the last value in the array to -9,400,000 I get a result, so I
know that my formula is correct, but anything over that number I get the #NUM
error.

Please let me know if there is something that can be done to make this
formula work with this number.

thanks
Tracy

 

[Harlan Grove]

Tracy wrote...
....

The formula is
=(1+IRR(I96:I156,0.1))^12-1

I get the #NUM error

....

There are simply some cashflow patterns for which there's no solution
to the IRR polynomial. Your cashflow pattern is one of those. This is
one of several problems inherent in using IRRs.

 

[Tracy]

This is what I figured, thanks Harlan for your speedy response.

Tracy