Can anybody tell me why this implementation of the secant method does not work?

[xfu14]

I want to implement the secant method in order to find the internal rate of return of an investment.

Public Function irr(payment As Double, endValue As Double, T As Integer) As Double
    
    Dim x0 As Double
    Dim x1 As Double
    Dim f0 As Double
    Dim f1 As Double
    Dim temp As Double
    Dim i As Integer

    'We will use the Secant method'

    x0 = -0.03
    x1 = 0.1
    f0 = f(x0, endValue, payment, T)
    f1 = f(x1, endValue, payment, T)

    For i = 1 To 100
        
        If  f1 = f0 Then
            Exit For
        End If

        temp = x1
        x1 = x1 - f1 * (x1 - x0) / (f1 - f0)
        x0 = temp
        f0 = f1
        f1 = f(x1, endValue, payment, T)
        
        If Abs(f1)<0.00001 Then
            Exit For
        End If

    Next i
        
    irr = r1
    
End Function

If I run this code it always returns the initial value of x1, in this case 10%. But if I run this code in any other programming language such as R or Python, it works and returns the correct internal rate of return.

The function f is derived from the geometric sum with monthly investments and implemented as follows:

Private Function f(r, value, prem, T) As Double

    Dim y As Double
    
    y = prem * ((1 + r) ^ (T + 1 / 12) - 1) / ((1 + r) ^ (1 / 12) - 1) - value 
    
End Function

 

[6StringJazzer]

Please provide a reference to the algorithm for the Secant Method. This is not a usual thing to do in Excel since there is a built-in IRR function, and there may be very few people familiar with that algorithm.

 

[xfu14]

Please provide a reference to the algorithm for the Secant Method. This is not a usual thing to do in Excel since there is a built-in IRR function, and there may be very few people familiar with that algorithm.

The principle is the same as here: Secant Method Pseudocode

 

[KenU]

In your private function, you need to set the return value of the function. Prior to the End Function you should add the line

f = y

Note that I did not check the full algorithm; just looking for the right pieces.

Hope that helps.

Regards,
Ken

 

[xfu14]

In your private function, you need to set the return value of the function. Prior to the End Function you should add the line

f = y

Note that I did not check the full algorithm; just looking for the right pieces.

Hope that helps.

Regards,
Ken

Thank you, it works now.