Calculate the Internal Rate of Return (IRR) in Excel VBA

Steps before you start:

1. Prepare the Data: Enter the cash flows in an Excel column (for example, from cell A2 to A7).
2. Add the VBA Code: Open the VBA editor by pressing Alt + F11, then insert a new module (via Insert > Module).

VBA Code to Calculate IRR

Function InternalRateOfReturn(flux As Range) As Double
    Dim guess As Double
    Dim rate As Double
    Dim npv As Double
    Dim tolerance As Double
    Dim iteration As Integer
    Dim maxIterations As Integer
    ' Initializing variables
    guess = 0.1 ' Starting guess rate (10%)
    maxIterations = 100 ' Maximum number of iterations
    tolerance = 0.00001 ' Tolerance for determining the precision of the result
    ' Start finding the rate that makes NPV close to zero
    For iteration = 1 To maxIterations
        npv = 0 ' Reset NPV at each iteration
        ' Calculate NPV for the current rate
        For i = 1 To flux.Count
            npv = npv + flux.Cells(i).Value / (1 + guess) ^ (i - 1)
        Next i      
        ' If NPV is close enough to zero, we have found our IRR
        If Abs(npv) < tolerance Then
            InternalRateOfReturn = guess
            Exit Function
        End If      
        ' Adjust the rate depending on the direction of NPV
        guess = guess - npv / Derivative(flux, guess)
    Next iteration
    ' If no solution is found, return an error
    InternalRateOfReturn = CVErr(xlErrNA)
End Function

Function Derivative(flux As Range, guess As Double) As Double
    ' Function to calculate the derivative of NPV with respect to the rate
    Dim epsilon As Double
    Dim npv1 As Double
    Dim npv2 As Double
    Dim derivative As Double
    epsilon = 0.00001 ' Small value to compute the derivative
    npv1 = 0
    npv2 = 0
    ' Calculate NPV for two rates slightly different
    For i = 1 To flux.Count
        npv1 = npv1 + flux.Cells(i).Value / (1 + guess) ^ (i - 1)
        npv2 = npv2 + flux.Cells(i).Value / (1 + guess + epsilon) ^ (i - 1)
    Next i
    ' Calculate the derivative using finite difference
    derivative = (npv2 - npv1) / epsilon
    Derivative = derivative
End Function

Code Explanation

1. InternalRateOfReturn Function:
   • This function takes a range of cells containing cash flows as input.
   • The IRR is calculated using an iterative approach (Newton-Raphson method), where the rate is adjusted until the net present value (NPV) is close to zero.
   • The initial guess (guess) is set arbitrarily at 10% and can be adjusted as needed.
   • The tolerance determines how precise the result should be (here set to 0.00001).
   • The maximum number of iterations is set to 100 to avoid infinite loops in case the calculation doesn’t converge.
2. Derivative Function:
   • This function calculates the derivative of the NPV with respect to the rate. It is used to adjust the rate during the iterations. The derivative is calculated using finite differences, which is done by evaluating the NPV at two values close to the current rate.

How to Use the Code in Excel

1. Enter your cash flows in an Excel column (for example, from A2 to A7).
2. In any empty cell, use the custom InternalRateOfReturn function you created in VBA. For example, if your cash flows are in the range A2:A7, you can enter the following formula in any cell:

=InternalRateOfReturn(A2:A7)

Example:

If your cash flows are as follows:

• Year 0 (Initial Investment): -1000 €
• Year 1: 300 €
• Year 2: 400 €
• Year 3: 500 €
• Year 4: 600 €

The cash flows in Excel would look like this:

A2: -1000
A3: 300
A4: 400
A5: 500
A6: 600

By entering the formula =InternalRateOfReturn(A2:A6) in an empty cell, you will get the corresponding IRR.

Things to Check:

• If the IRR doesn’t converge (for example, if the cash flows are too complex), the algorithm might not find a solution. You can try modifying the initial guess (guess) or adjust the tolerance for better convergence.

This code provides a basic structure for calculating IRR in VBA, but it can be adapted for more complex cases such as irregular cash flows or other financial models.