VBA root finder algorithm I did two simple functions in VBA (see below). 

When a user calls TirNoPer360 in Excel he must select cashflows and dates (as range), then he will get a rate (through iterations according to the second function TirNoPer360).

Function Opt(Caja, Fecha As Range, tasa As Double)

Dim i As Integer
Dim sum As Double
Dim Initial As Date

Initial = Fecha(1, 1)

For i = 1 To Caja.Rows.Count
    sum = sum + Caja(i) / ((1 + tasa) ^ (Application.WorksheetFunction.Days360(Initial, Fecha(i)) / 360))
Next i

Opt = sum

End Function


======================

Function TirNoPer360(Cash, Dates As Range, rate As Double)
Dim tolerance  As Double
tolerance = 0.00001

For Iteration = 1 To 100
    If Opt(Cash, Dates, rate) &gt; 0 And Opt(Cash, Dates, rate) &gt; tolerance Then
        rate = rate + 0.0000001
    ElseIf Opt(Cash, Dates, rate) &lt; 0 And Opt(Cash, Dates, rate) &lt; tolerance * -1 Then
        rate = rate - 0.0000001            
    Else
        rate = rate
    End If
    TirNoPer360 = rate
Next Iteration

End Function


When cashflows and dates increase I need more than 100 tries in order to get a good rate (approximated to four decimals) and time increases a lot. So, I would like to speed up my code. 
I think I must improve the root finder algorithm, but I don't know how exactly I can do it.
Can anyone help me?

PD: I read Excel uses Newton's algorithm to find the root for a function.
Function TirNoPer360(Cash, Dates As Range, rate As Double)



The function is implicitly Public, implicitly returns a Variant, and implicitly receives all its parameters ByRef; Cash is implicitly a Variant, and the name isn't making it obvious that...


If Opt(Cash, Dates, rate)



...


Function Opt(Caja, Fecha As Range, tasa As Double)
    '...
    For i = 1 To Caja.Rows.Count



...that Cash (and Caja) really needs to be a Range object, otherwise things can go all kinds of wrong.

Make these things explicit, pass parameters ByVal, and keep the same identifiers when concepts are the same:

Public Function TirNoPer360(ByVl Cash As Range, ByVal Dates As Range, ByVal rate As Double) As Double




Private Function Opt(ByVal Cash As Range, ByVal Dates As Range, ByVal rate As Double) As Double


Now it's much clearer that...


TirNoPer360 is the public UDF, and Opt is a private implementation detail of it.
Cash in TirNoPer360 is the same thing as what you had as Caja in Opt.
The function(s) return a Double




This block needs immediate attention:

If Opt(Cash, Dates, rate) &gt; 0 And Opt(Cash, Dates, rate) &gt; tolerance Then
    rate = rate + 0.0000001
ElseIf Opt(Cash, Dates, rate) &lt; 0 And Opt(Cash, Dates, rate) &lt; tolerance * -1 Then
    rate = rate - 0.0000001            
Else
    rate = rate
End If


The entire Else block is no-op and should be removed. rate is already equal to rate, but the biggest inefficiency here is the fact that, in the worst case, Opt is invoked 4 times, and it's a very expensive function call. Pull it into a local variable (as was already suggested) and call it once:

Dim optResult As Double
optResult = Opt(Cash, Dates, rate)


And now the conditionals become:

If optResult &gt; 0 And optResult &gt; tolerance Then
    rate = rate + 0.0000001
ElseIf optResult &lt; 0 And optResult &lt; tolerance * -1 Then
    rate = rate - 0.0000001            
End If


The tolerance looks like it should be an optional parameter, with a default value of 0.00001. You could amend the function's signature like this:

Public Function TirNoPer360(ByVl Cash As Range, ByVal Dates As Range, ByVal rate As Double, Optional ByVal tolerance As Double = 0.00001) As Double


Variable Iteration is not declared. I like that it's a fully spelled-out identifier and not just i, but given the variable has no use other than counting the number of iterations... so i wouldn't hurt at all. Just declare it - the fact that the code is allowed to run without this variable being declared indicates that Option Explicit is not specified at the top of the module, and that's a very dangerous thing, because any typo will merrily compile and run.. and produce the wrong results... and typos can be hard to spot. Spare yourself the trouble, and always put Option Explicit at the top of every module you ever write any code in.


  I need more than 100 tries in order to get a good rate


Your function needs to define a concept of what a "good rate" is, and bail out (Exit Function, or Exit For) when it gets one. Currently, the function performs 100 iterations every time, regardless of whether the 2nd or 99th iteration yielded a "good rate".


    TirNoPer360 = rate
Next Iteration



You're re-assigning the function's return value at every iteration. That's a redundant step, just assign it once, after the loop:

Next Iteration
TirNoPer360 = rate


Now, about that Opt function...


For i = 1 To Caja.Rows.Count
    sum = sum + Caja(i) / ((1 + tasa) ^ (Application.WorksheetFunction.Days360(Initial, Fecha(i)) / 360))
Next i



i should be declared as a Long, not an Integer. I don't know how many rows this function is iterating, but Caja being a Range means it's absolutely possible that there are more than 32,767 (the maximum value an Integer can take).

The function would be more efficient if you were iterating a single-dimensional Variant array rather than a Range.

Consider declaring Caja and Fecha parameters ByVal...As Variant, and passing them from TirNo360 like so:

Public Function TirNoPer360(ByVl Cash As Range, ByVal Dates As Range, ByVal rate As Double, Optional ByVal tolerance As Double = 0.00001) As Double

    Dim cashValues As Variant
    cashValues = Application.WorksheetFunction.Transpose(Cash.Value)

    Dim dateValues As Variant
    dateValues = Application.WorksheetFunction.Transpose(Dates.Value)

    Dim optResult As Double
    optResult = Opt(cashValues, dateValues, rate)


Now instead of this:


For i = 1 To Caja.Rows.Count
    sum = sum + Caja(i) / ((1 + tasa) ^ (Application.WorksheetFunction.Days360(Initial, Fecha(i)) / 360))
Next i



You'll have this (assuming the Caja parameter is now named cashValues):

Dim firstDate As Date
firstDate = dateValues(1)

For i = LBound(cashValues) To UBound(cashValues)
    sum = sum + cashValues(i) / ((1 + rate) ^ (Application.WorksheetFunction.Days360(firstDate, dateValues(i)) / 360))
Next


Now the only thing left to address, is the function names: I've no idea what TirNoPer360 actually stands for, and Opt is very cryptic. Give your functions meaningful names that ideally start with a verb, like, FindOptimalRate or something.