On Thu, 11 Dec 2008 09:13:07 -0500, Dana DeLouis wrote:

Why do you think these "large" numbers result in the #NUM error (which

Hi. Here is my guess.
IRR has two limitations. 20 Tries, and a change of .00001

When I use Goal Seek, and a start value of 10%, I get an answer close to
zero of .0005. Goal Seek does not have to be that accurate. Although
not documented in Excel 2007(afaik) I believe Goal Seek is able to
iterate more than 20 times. (I thought it was documented in earlier
versions??)

I used a math program, and the Newton method to arrive at a full
precision value of 0.264282301113724
However, when I plug this into the original equation, I'm left with a
difference from zero of .00006. This is outside IRR's .00001 limit.
Therefore, I believe the #Num error is due to not converging below
.00001 within 20 tries.

What has me puzzled is if I enter this "guess" value into the IRR
equation, I still get a #Num error.
The Derivative of the NPV formulas shows a slope of about -2.4*10^12
near the solution. This is very high.

Therefore, I believe IRR's troubles here is a combination of Excel's
method of calculating a high derivative, a little loss of precision of
the underlying numbers, and a limit of only 20 tries.

= = =
Dana DeLouis



OK, in Excel 2007, when I use Goal Seek, after having set up my worksheet as
below, I get a result of:

26.4282301113724%

If I use the array formula:

=IRR(B2:B11/10)

I obtain the same answer to Excel's 15 digit precision level

They are slightly different, perhaps 5.33E-16

but close enough for gov't work <g.
















Ron Rosenfeld wrote:
On Thu, 11 Dec 2008 12:37:44 +0100, "Niek Otten" wrote:

I get #NUM too.
Your numbers are quite big. If I divide all numbers by 100, I get 26.43%.


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

Niek,

Why do you think these "large" numbers result in the #NUM error (which I get
also in Excel 2007).

I thought Excel uses an iterative technique to solve for IRR, finding the
interest rate for which the NPV is zero.

It must have something to do with the way Excel calculates IRR internally, but
....

I set up to solve the IRR iteratively.

I used the OP's original data in B2:B11

E3 will be my "guess" for the IRR.

I then set up these formulas:

E3: 10%
G2: =B2
G3: =B3*(1/(1+$E$3)^ROWS($1:1))

Fill down to G11

G13: =SUM(G2:G11)

I then used Goal Seek to set G13 to 0 by varying E3. And that comes up with
the proper answer. The answer is the same as the IRR answer to 15 decimals.

In other words, implementing what I thought was the IRR technique in a
different way did not result in any error, and returned the correct answer.
--ron
--ron