VBA IRR and errors
 

I'm using IRR in a function, but sometimes IRR can't find a solution. I
want to provide IRR a guess that, if IRR still doesn't find a solution,
the guess will change and be provided to IRR again until IRR finds a
solution or this is repeated a certain number of times.

I think my error trapping isn't working properly, but I'm not really
sure what's going on. It only loops once and I then get #Value! in the
cell using my function.

Any help would be greatly appreciated.

Below is my program in its entirety.


Code:
--------------------

Function getIRR(BegVal As Double, CashFlows As Range, EndVal As Double, Optional Guess As Double = 0) As Double

Dim AllFlows() As Double 'holds all flows in a single array (negative BegVal, negative CashFlows, and positive EndVal)
Dim x As Integer 'increment counter
Dim Periods As Integer 'number of periods used
Dim i As Integer 'increment counter

'Get the number of periods used
Periods = CashFlows.Cells.Count

'Enter all values into a single array
On Error GoTo errorFlows:
ReDim AllFlows(Periods + 1)
AllFlows(1) = -1 * (BegVal + CashFlows.Cells(1))
For x = 2 To Periods
AllFlows(x) = -1 * CashFlows.Cells(x)
Next x
AllFlows(Periods + 1) = EndVal

TryAgain: 'Return here problem with getIRR

'Find the IRR (DWR)
On Error GoTo errorIRR
getIRR = (1 + IRR(AllFlows(), Guess)) ^ Periods - 1
GoTo endd:

'For when there is a problem getting the Flows
errorFlows:
getIRR = -0.8888

'For when there is a problem calculating getIRR
errorIRR:
i = i + 1
If i 20 Then
getIRR = -0.9999
GoTo endd:
Else
Guess = -1.1 + i * 0.1
GoTo TryAgain
End If
endd:
End Function

--------------------


--
ericsh
------------------------------------------------------------------------
ericsh's Profile: http://www.excelforum.com/member.php...o&userid=26149
View this thread: http://www.excelforum.com/showthread...hreadid=511848