IRR showed #NUM!
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
|