Numerical Methods - The Newton-Raphson Method (Part 2)

In the previous post, we talked about several root finding techniques. In this post, we're going to see how we can use Microsoft Excel VBA to find the roots using the Newton-Raphson Method.

Advertisement

Figure 1: The Newton-Raphson method rapidly converges on a solution using the function's derivative.

The Logic

As we know, the Newton-Raphson Method is the most widely used of all root-locating formulas. It uses the slope (first derivative) of the function to find the root. That means, in the VBA code, we have to calculate the first derivative of the function.

We already discussed how to find the first derivative using numerical methods, and we will incorporate that logic here.

Figure 2: Geometric interpretation: The tangent line points to the next approximate root.

The Iterative Formula:

The method uses the Taylor series to approximate the next position (x_N) based on the current position (x₀):

x_N = x₀ - f(x₀) / f'(x₀)

The Algorithm

1. Guess the initial value of the root → select x₀.
2. Calculate the next guess (x_N) using the formula above.
3. Check convergence: Is the error abs(x_N - x₀) within the allowable tolerance?
4. If yes, stop. If no, set x₀ = x_N and repeat.

Advertisement

The VBA Code

Copy and paste the following code into a Module in your Excel VBA Editor (Alt+F11). This code includes the function to solve, the numerical derivative calculator, and the Newton-Raphson loop.

' ================================================
' Created by Suparerg Suksai
' Mechanical Design Handbook
' The Newton-Raphson Method - Numerical Methods
' ================================================

' 1. Define the function you want to solve here
' Example: e^(-x) - x = 0
Function f(x As Double) As Double
    f = Exp(-x) - x
End Function

' 2. Calculate Numerical Derivative (Central Difference)
Function fDeriv(x As Double) As Double
    Dim h As Double
    h = 0.000001 ' Small step size
    fDeriv = (f(x + h) - f(x - h)) / (2 * h)
End Function

' 3. Main Newton-Raphson Algorithm
Function RootNewtonRaphson(x0 As Double) As Double
    Dim x_new As Double
    Dim x_old As Double
    Dim tolerance As Double
    Dim error_val As Double
    Dim max_iter As Integer
    Dim i As Integer
    
    x_old = x0
    tolerance = 0.000001
    max_iter = 100 ' Prevent infinite loops
    
    For i = 1 To max_iter
        ' Apply formula: x_new = x_old - f(x)/f'(x)
        x_new = x_old - (f(x_old) / fDeriv(x_old))
        
        ' Check if we found the root
        error_val = Abs(x_new - x_old)
        If error_val < tolerance Then
            RootNewtonRaphson = x_new
            Exit Function
        End If
        
        ' Update for next iteration
        x_old = x_new
    Next i
    
    RootNewtonRaphson = x_new
End Function

Figure 3: Paste the code into a new Module in the VBA Editor (Alt+F11).

How to Use It

To calculate the root of e^-x - x = 0:

1. Go to any cell in your Excel spreadsheet.
2. Type =RootNewtonRaphson(1). (Here, 1 is your initial guess).
3. The program will calculate and return the result: 0.567143290409784.

Verification

We can recheck the calculation result by plugging the answer back into the original formula in Excel:

=EXP(-0.567143...) - 0.567143...

The result is 0. This confirms that 0.567143... is indeed the root of the equation.

This method is incredibly powerful for engineering calculations where analytical solutions are impossible to find.