2
\$\begingroup\$

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) > 0 And Opt(Cash, Dates, rate) > tolerance Then
        rate = rate + 0.0000001
    ElseIf Opt(Cash, Dates, rate) < 0 And Opt(Cash, Dates, rate) < 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.

\$\endgroup\$
  • \$\begingroup\$ What is a "good rate"? \$\endgroup\$ – Mathieu Guindon Jul 11 at 18:27
  • \$\begingroup\$ One quick comment for speed-up is that you're calling the Opt function four times with the exact same parameters when you should only call it once. Set up a variable Dim sum As Double; sum = Opt(Cash, Dates, rate), then use that result in the If statement: If sum > 0 And sum > tolerance Then.... Additionally, we could help a bit more if you can supply a table of test data to run against so we know what to look for. \$\endgroup\$ – PeterT Jul 11 at 18:38
  • \$\begingroup\$ Just to point out that I improved optimization (root finder) through bisection method. \$\endgroup\$ – Newbie Jul 12 at 19:44
3
\$\begingroup\$
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) > 0 And Opt(Cash, Dates, rate) > tolerance Then
    rate = rate + 0.0000001
ElseIf Opt(Cash, Dates, rate) < 0 And Opt(Cash, Dates, rate) < 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 > 0 And optResult > tolerance Then
    rate = rate + 0.0000001
ElseIf optResult < 0 And optResult < 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.

\$\endgroup\$
  • \$\begingroup\$ thank very much for the feedbacks. I really appreciated it. \$\endgroup\$ – Newbie Jul 12 at 19:41
1
\$\begingroup\$

I want to understand this because it seems like an interesting problem, but it's difficult because the parameters are in Spanish and I don't really understand the business domain.

Some things to consider: are you sure there isn't a plugin somewhere that does this calculation?

If not, avoid repeat calculations. Instead of calling Opt four times, store it once to a variable and use that instead.

Also, try to think of a way you can store previous results in general. Do users keep calculating the same things over and over again? Could you store that in a table instead?

Could you maybe attach a spreadsheet with sample data that does the calculation?

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.