IRR showed #NUM!

Hi. I looked at this a little more.
I may of been mistaken on the Derivative issue. This is not a hard
problem at all!!
I think there is a "bug" or Logic issue in the algorithm used by Excel.
I used the "divided difference" technique and set it equal to Solver's
in another program. I've found that it doesn't have to be small to
quickly converge to a solution. (usually only 7-10 loops are required)

Here is a quick-n-dirty version in Excel. I have the data in A1:A10.
=MyIrr(A1:A10) converged to the solution in about 7 loops.


Function MyIRR(rng)
Dim v
Dim R
Dim k
Dim J As Long

R = 0.1 'Initial guess
Const dd = 0.000000001

'Make 1-Dim
v = WorksheetFunction.Transpose(rng)

For J = 1 To 20
k = MyPv(v, R)
R = R - k / ((MyPv(v, R + dd) - k) / dd)
Next J
MyIRR = R
End Function

Private Function MyPv(v, R) As Double
Dim J As Long
Dim Pv

For J = 1 To UBound(v)
Pv = Pv + v(J) / (1 + R) ^ (J - 1)
Next J
MyPv = Pv
End Function


I think there is a logic bug in the routine that is not allowing IRR to
converge within 20 loops. This is a problem especially when one gives
the actual solution as an initial guess!!

- - - -
Dana DeLouis



Ron Rosenfeld wrote:
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

If anyone is interested. This problem should converge to a solution
quickly even if we (or Excel) are not accurate in our derivative. I
believe there is a logic bug somewhere in Excel's IRR algorithm.
However, the nature of this particular problem does allow us to have a
more exact derivative. Here is one method where we can be more exact...

=MyIRR(A1:A10)

Function MyIRR(Rng)
Dim v
Dim dr()
Dim R
Dim OldRte
Dim Ct As Long
Dim J As Long

With WorksheetFunction
'Make 1-Dimensional
v = .Transpose(Rng.Value)

'Make an exact Derivative
ReDim dr(1 To UBound(v) - 1)
For J = 1 To UBound(dr)
dr(J) = -J * v(J + 1)
Next J

R = 0.1 'initial guess
Do
OldRte = R
R = R - .SeriesSum(1 + R, 0, -1, v) / _
.SeriesSum(1 + R, -2, -1, dr)
Ct = Ct + 1
Loop While OldRte R And Ct = 40
End With
MyIRR = R
End Function

= = =
Dana DeLouis

snip

Dana DeLouis wrote...
If anyone is interested. *This problem should converge to a solution
quickly even if we (or Excel) are not accurate in our derivative. *I
believe there is a logic bug somewhere in Excel's IRR algorithm.
....

Given the problem with this particular use of IRR (seems to fail due
to larger than usual values), my money would be on an unfortunate use
of Single rather than Double somewhere along the way. It's hard to
believe there'd be overflow. I suppose the bug could come from
bracketing the range in which the IRR lies.

On Sun, 14 Dec 2008 10:22:16 -0500, Dana DeLouis
wrote:

If anyone is interested. This problem should converge to a solution
quickly even if we (or Excel) are not accurate in our derivative. I
believe there is a logic bug somewhere in Excel's IRR algorithm.
However, the nature of this particular problem does allow us to have a
more exact derivative. Here is one method where we can be more exact...

=MyIRR(A1:A10)

Thanks, Dana
--ron

Dana, thanks for this posting - At first, I was getting Error 1004 (Unable to call the "SeriesSum" worksheet function), but then attempted it with the variants changed to doubles, and 41 iterations (dunno why, I'm not financial, just programmatical..). Outcome is, it is a tidy function, which works unfailingly, and which is able to help verify APR's for clients. I just want you to know that your help is appreciated - thankyou.



Dana DeLouis wrote:

If anyone is interested.
14-Dec-08

If anyone is interested. This problem should converge to a solution
quickly even if we (or Excel) are not accurate in our derivative. I
believe there is a logic bug somewhere in Excel's IRR algorithm.
However, the nature of this particular problem does allow us to have a
more exact derivative. Here is one method where we can be more exact...

=MyIRR(A1:A10)

Function MyIRR(Rng)
Dim v
Dim dr()
Dim R
Dim OldRte
Dim Ct As Long
Dim J As Long

With WorksheetFunction
'Make 1-Dimensional
v = .Transpose(Rng.Value)

'Make an exact Derivative
ReDim dr(1 To UBound(v) - 1)
For J = 1 To UBound(dr)
dr(J) = -J * v(J + 1)
Next J

R = 0.1 'initial guess
Do
OldRte = R
R = R - .SeriesSum(1 + R, 0, -1, v) / _
.SeriesSum(1 + R, -2, -1, dr)
Ct = Ct + 1
Loop While OldRte R And Ct = 40
End With
MyIRR = R
End Function

= = =
Dana DeLouis

snip

Previous Posts In This Thread:

On Thursday, December 11, 2008 2:14 AM
bakbu wrote:

IRR showed #NUM!
I am trying to use IRR function for my string of cash flows for 10 years:
Year | Cash Flows
2009 | -272,895,028,812
2010 | -207,524,139,910
2011 | -185,716,940,803
2012 | -9,306,121,279
2013 | 326,372,394,532
2014 | 245,782,146,138
2015 | 501,268,808,310
2016 | 497,663,783,563
2017 | 493,577,645,807
2018 | 488,974,344,794

The formula I am using is simply:
=IRR("Range of Data")

I am getting the #NUM! error...any assistance please?

Thanks!

On Thursday, December 11, 2008 2:33 AM
Mike Middleton wrote:

IRR showed #NUM!
bakbuk -

With the years in A2:A11 and the cash flows in B2:B11, in another cell
=IRR(B2:B11) returns 34%.

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

On Thursday, December 11, 2008 2:49 AM
bakbu wrote:

It worked?
It worked? oww...I wonder why mine did not...
I will try to troubleshoot then, thanks for the result

"Mike Middleton" wrote:

On Thursday, December 11, 2008 3:25 AM
bakbu wrote:

I still have the problem with this matter.
I still have the problem with this matter. I tried it at my friend's computer
and the result is still the same (#NUM!). I also tried by creating a new
excel file but contain the same value, and it happened again. Both of us use
Office2007, saved and tried the file in .xls and .xlsx format. Is there
something wrong? thanks again.

"bakbuk" wrote:

On Thursday, December 11, 2008 6:37 AM
Niek Otten wrote:

I get #NUM too.Your numbers are quite big.
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

On Thursday, December 11, 2008 7:33 AM
bakbu wrote:

Oh I see...so the problem is that the numbers are big...
Oh I see...so the problem is that the numbers are big...
I followed your step but divide by 1000...yup, it returned 26.43%. They're
big because they're in IDR currency. This really helped me, thanks a lot!
Now, I have better knowledge to handle big numbers. Phew...case closed then.
Thanks again, really!

Best Regards,

"Niek Otten" wrote:

On Thursday, December 11, 2008 8:11 AM
Ron Rosenfeld wrote:

IRR showed #NUM!
On Thu, 11 Dec 2008 12:37:44 +0100, "Niek Otten" wrote:


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

On Thursday, December 11, 2008 9:13 AM
Dana DeLouis wrote:

Why do you think these "large" numbers result in the #NUM error (whichHi.
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




Ron Rosenfeld wrote:

On Thursday, December 11, 2008 9:26 AM
Niek Otten wrote:

Thanks, Dana!
Thanks, Dana!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

On Thursday, December 11, 2008 10:22 AM
Ron Rosenfeld wrote:

IRR showed #NUM!
On Thu, 11 Dec 2008 09:13:07 -0500, Dana DeLouis wrote:


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

On Saturday, December 13, 2008 2:00 PM
Dana DeLouis wrote:

Hi. I looked at this a little more.
Hi. I looked at this a little more.
I may of been mistaken on the Derivative issue. This is not a hard
problem at all!!
I think there is a "bug" or Logic issue in the algorithm used by Excel.
I used the "divided difference" technique and set it equal to Solver's
in another program. I've found that it doesn't have to be small to
quickly converge to a solution. (usually only 7-10 loops are required)

Here is a quick-n-dirty version in Excel. I have the data in A1:A10.
=MyIrr(A1:A10) converged to the solution in about 7 loops.


Function MyIRR(rng)
Dim v
Dim R
Dim k
Dim J As Long

R = 0.1 'Initial guess
Const dd = 0.000000001

'Make 1-Dim
v = WorksheetFunction.Transpose(rng)

For J = 1 To 20
k = MyPv(v, R)
R = R - k / ((MyPv(v, R + dd) - k) / dd)
Next J
MyIRR = R
End Function

Private Function MyPv(v, R) As Double
Dim J As Long
Dim Pv

For J = 1 To UBound(v)
Pv = Pv + v(J) / (1 + R) ^ (J - 1)
Next J
MyPv = Pv
End Function


I think there is a logic bug in the routine that is not allowing IRR to
converge within 20 loops. This is a problem especially when one gives
the actual solution as an initial guess!!

- - - -
Dana DeLouis



Ron Rosenfeld wrote:

On Sunday, December 14, 2008 10:22 AM
Dana DeLouis wrote:

If anyone is interested.
If anyone is interested. This problem should converge to a solution
quickly even if we (or Excel) are not accurate in our derivative. I
believe there is a logic bug somewhere in Excel's IRR algorithm.
However, the nature of this particular problem does allow us to have a
more exact derivative. Here is one method where we can be more exact...

=MyIRR(A1:A10)

Function MyIRR(Rng)
Dim v
Dim dr()
Dim R
Dim OldRte
Dim Ct As Long
Dim J As Long

With WorksheetFunction
'Make 1-Dimensional
v = .Transpose(Rng.Value)

'Make an exact Derivative
ReDim dr(1 To UBound(v) - 1)
For J = 1 To UBound(dr)
dr(J) = -J * v(J + 1)
Next J

R = 0.1 'initial guess
Do
OldRte = R
R = R - .SeriesSum(1 + R, 0, -1, v) / _
.SeriesSum(1 + R, -2, -1, dr)
Ct = Ct + 1
Loop While OldRte R And Ct = 40
End With
MyIRR = R
End Function

= = =
Dana DeLouis

snip

On Monday, December 15, 2008 7:54 PM
Ron Rosenfeld wrote:

IRR showed #NUM!
wrote:


Thanks, Dana
--ron

On Tuesday, December 16, 2008 2:16 AM
Harlan Grove wrote:

IRR showed #NUM!
Dana DeLouis wrote...
....

Given the problem with this particular use of IRR (seems to fail due
to larger than usual values), my money would be on an unfortunate use
of Single rather than Double somewhere along the way. It's hard to
believe there'd be overflow. I suppose the bug could come from
bracketing the range in which the IRR lies.


Submitted via EggHeadCafe - Software Developer Portal of Choice
WPF Customized Find Control for FlowDocuments
http://www.eggheadcafe.com/tutorials...ind-contr.aspx

Dana, thanks for this posting -
At first, I was getting Error 1004 (Unable to call the "SeriesSum" worksheet function),
but then attempted it with the variants changed to
doubles, and 41 iterations (dunno why, I'm not
financial, just programmatical..).

Outcome is, it is a tidy function, which works
unfailingly, and which is able to help verify
APR's for clients.

I just want you to know that your help is appreciated - Thankyou.





Stephen Mackley wrote:

Thanks for this IRR function
01-May-10

Dana, thanks for this posting - At first, I was getting Error 1004 (Unable to call the "SeriesSum" worksheet function), but then attempted it with the variants changed to doubles, and 41 iterations (dunno why, I'm not financial, just programmatical..). Outcome is, it is a tidy function, which works unfailingly, and which is able to help verify APR's for clients. I just want you to know that your help is appreciated - thankyou.

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Distributed Data Grids - Share Objects Between Windows Service and ASP.NET
http://www.eggheadcafe.com/tutorials...ta-grids-.aspx

Hi Stephen. Thanks for the feedback.
I'm glad you like it. It is one of my favorite programs that I wrote.
It sure doesn't look like it would work though, does it. :)

I am not sure why changing variables to double would remove an Error
1004. Hmmm.
Anyway, thanks for the positive feedback. :)

= = = = = =
Dana DeLouis

On 5/1/10 3:12 AM, Stephen Mackley wrote:
Dana, thanks for this posting - At first, I was getting Error 1004 (Unable to call the "SeriesSum" worksheet function), but then attempted it with the variants changed to doubles, and 41 iterations (dunno why, I'm not financial, just programmatical..). Outcome is, it is a tidy function, which works unfailingly, and which is able to help verify APR's for clients. I just want you to know that your help is appreciated - thankyou.



Dana DeLouis wrote:

If anyone is interested.
14-Dec-08

If anyone is interested. This problem should converge to a solution
quickly even if we (or Excel) are not accurate in our derivative. I
believe there is a logic bug somewhere in Excel's IRR algorithm.
However, the nature of this particular problem does allow us to have a
more exact derivative. Here is one method where we can be more exact...

=MyIRR(A1:A10)

Function MyIRR(Rng)
Dim v
Dim dr()
Dim R
Dim OldRte
Dim Ct As Long
Dim J As Long

With WorksheetFunction
'Make 1-Dimensional
v = .Transpose(Rng.Value)

'Make an exact Derivative
ReDim dr(1 To UBound(v) - 1)
For J = 1 To UBound(dr)
dr(J) = -J * v(J + 1)
Next J

R = 0.1 'initial guess
Do
OldRte = R
R = R - .SeriesSum(1 + R, 0, -1, v) / _
.SeriesSum(1 + R, -2, -1, dr)
Ct = Ct + 1
Loop While OldRte R And Ct= 40
End With
MyIRR = R
End Function

= = =
Dana DeLouis

snip

Previous Posts In This Thread:

On Thursday, December 11, 2008 2:14 AM
bakbu wrote:

IRR showed #NUM!
I am trying to use IRR function for my string of cash flows for 10 years:
Year | Cash Flows
2009 | -272,895,028,812
2010 | -207,524,139,910
2011 | -185,716,940,803
2012 | -9,306,121,279
2013 | 326,372,394,532
2014 | 245,782,146,138
2015 | 501,268,808,310
2016 | 497,663,783,563
2017 | 493,577,645,807
2018 | 488,974,344,794

The formula I am using is simply:
=IRR("Range of Data")

I am getting the #NUM! error...any assistance please?

Thanks!

On Thursday, December 11, 2008 2:33 AM
Mike Middleton wrote:

IRR showed #NUM!
bakbuk -

With the years in A2:A11 and the cash flows in B2:B11, in another cell
=IRR(B2:B11) returns 34%.

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

On Thursday, December 11, 2008 2:49 AM
bakbu wrote:

It worked?
It worked? oww...I wonder why mine did not...
I will try to troubleshoot then, thanks for the result

"Mike Middleton" wrote:

On Thursday, December 11, 2008 3:25 AM
bakbu wrote:

I still have the problem with this matter.
I still have the problem with this matter. I tried it at my friend's computer
and the result is still the same (#NUM!). I also tried by creating a new
excel file but contain the same value, and it happened again. Both of us use
Office2007, saved and tried the file in .xls and .xlsx format. Is there
something wrong? thanks again.

"bakbuk" wrote:

On Thursday, December 11, 2008 6:37 AM
Niek Otten wrote:

I get #NUM too.Your numbers are quite big.
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

On Thursday, December 11, 2008 7:33 AM
bakbu wrote:

Oh I see...so the problem is that the numbers are big...
Oh I see...so the problem is that the numbers are big...
I followed your step but divide by 1000...yup, it returned 26.43%. They're
big because they're in IDR currency. This really helped me, thanks a lot!
Now, I have better knowledge to handle big numbers. Phew...case closed then.
Thanks again, really!

Best Regards,

"Niek Otten" wrote:

On Thursday, December 11, 2008 8:11 AM
Ron Rosenfeld wrote:

IRR showed #NUM!
On Thu, 11 Dec 2008 12:37:44 +0100, "Niek wrote:


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

On Thursday, December 11, 2008 9:13 AM
Dana DeLouis wrote:

Why do you think these "large" numbers result in the #NUM error (whichHi.
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




Ron Rosenfeld wrote:

On Thursday, December 11, 2008 9:26 AM
Niek Otten wrote:

Thanks, Dana!
Thanks, Dana!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

On Thursday, December 11, 2008 10:22 AM
Ron Rosenfeld wrote:

IRR showed #NUM!
On Thu, 11 Dec 2008 09:13:07 -0500, Dana wrote:


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














--ron

On Saturday, December 13, 2008 2:00 PM
Dana DeLouis wrote:

Hi. I looked at this a little more.
Hi. I looked at this a little more.
I may of been mistaken on the Derivative issue. This is not a hard
problem at all!!
I think there is a "bug" or Logic issue in the algorithm used by Excel.
I used the "divided difference" technique and set it equal to Solver's
in another program. I've found that it doesn't have to be small to
quickly converge to a solution. (usually only 7-10 loops are required)

Here is a quick-n-dirty version in Excel. I have the data in A1:A10.
=MyIrr(A1:A10) converged to the solution in about 7 loops.


Function MyIRR(rng)
Dim v
Dim R
Dim k
Dim J As Long

R = 0.1 'Initial guess
Const dd = 0.000000001

'Make 1-Dim
v = WorksheetFunction.Transpose(rng)

For J = 1 To 20
k = MyPv(v, R)
R = R - k / ((MyPv(v, R + dd) - k) / dd)
Next J
MyIRR = R
End Function

Private Function MyPv(v, R) As Double
Dim J As Long
Dim Pv

For J = 1 To UBound(v)
Pv = Pv + v(J) / (1 + R) ^ (J - 1)
Next J
MyPv = Pv
End Function


I think there is a logic bug in the routine that is not allowing IRR to
converge within 20 loops. This is a problem especially when one gives
the actual solution as an initial guess!!

- - - -
Dana DeLouis



Ron Rosenfeld wrote:

On Sunday, December 14, 2008 10:22 AM
Dana DeLouis wrote:

If anyone is interested.
If anyone is interested. This problem should converge to a solution
quickly even if we (or Excel) are not accurate in our derivative. I
believe there is a logic bug somewhere in Excel's IRR algorithm.
However, the nature of this particular problem does allow us to have a
more exact derivative. Here is one method where we can be more exact...

=MyIRR(A1:A10)

Function MyIRR(Rng)
Dim v
Dim dr()
Dim R
Dim OldRte
Dim Ct As Long
Dim J As Long

With WorksheetFunction
'Make 1-Dimensional
v = .Transpose(Rng.Value)

'Make an exact Derivative
ReDim dr(1 To UBound(v) - 1)
For J = 1 To UBound(dr)
dr(J) = -J * v(J + 1)
Next J

R = 0.1 'initial guess
Do
OldRte = R
R = R - .SeriesSum(1 + R, 0, -1, v) / _
.SeriesSum(1 + R, -2, -1, dr)
Ct = Ct + 1
Loop While OldRte R And Ct= 40
End With
MyIRR = R
End Function

= = =
Dana DeLouis

snip

On Monday, December 15, 2008 7:54 PM
Ron Rosenfeld wrote:

IRR showed #NUM!
wrote:


Thanks, Dana
--ron

On Tuesday, December 16, 2008 2:16 AM
Harlan Grove wrote:

IRR showed #NUM!
Dana wrote...
...

Given the problem with this particular use of IRR (seems to fail due
to larger than usual values), my money would be on an unfortunate use
of Single rather than Double somewhere along the way. It's hard to
believe there'd be overflow. I suppose the bug could come from
bracketing the range in which the IRR lies.


Submitted via EggHeadCafe - Software Developer Portal of Choice
WPF Customized Find Control for FlowDocuments
http://www.eggheadcafe.com/tutorials...ind-contr.aspx