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

xfu14

New Member
Joined
Jun 15, 2022
Messages
3
Platform
  1. Windows
I want to implement the secant method in order to find the internal rate of return of an investment.
VBA Code:
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:
VBA Code:
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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.
 
Upvote 0
In your private function, you need to set the return value of the function. Prior to the End Function you should add the line

VBA Code:
f = y

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

Hope that helps.

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

VBA Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top