How to use IRR function in VBA?

In VBA (Visual Basic for Applications), used mainly in Microsoft Excel, the IRR function computes the Internal Rate of Return for a schedule of cash flows that is not necessarily periodic. To use the IRR function in VBA, you’ll need to have your cash flows stored in an array. Here’s how the IRR function works: 1. Open Excel and then press ALT + F11 to open the VBA editor. 2. Insert a new module if there isn’t already one available. 3. Write your VBA code within a subroutine in the module. Here’s an example of how you can use the IRR function in VBA:

Sub CalculateIRR()
    ' Create an array to store your cash flows
    Dim cashFlows(4) As Double
    ' Populate the array with cash flow data
    ' Example cash flows: an initial investment of -10000, followed by 4 yearly returns
    cashFlows(0) = -10000 ' Initial investment (negative because it's an outlay)
    cashFlows(1) = 2500   ' Return in year 1
    cashFlows(2) = 3000   ' Return in year 2
    cashFlows(3) = 3500   ' Return in year 3
    cashFlows(4) = 4000   ' Return in year 4
    ' Calculate the IRR
    Dim myIRR As Double
    myIRR = IRR(cashFlows)
    ' Output the IRR to the immediate window
    Debug.Print "The Internal Rate of Return is: "; Format(myIRR, "Percent")
    ' Alternatively, output the IRR to a cell in Excel
    ThisWorkbook.Sheets("Sheet1").Range("A1").Value = myIRR
End Sub
To run this subroutine, either press F5 while the cursor is within the subroutine code or go back to Excel and run the subroutine from there (e.g., by assigning it to a button). Note: The IRR function in VBA has an optional second argument for a guess (an estimate for what the IRR will be). The default value is 10% (0.1). When you’re providing cash flow values, be sure to have at least one positive and one negative value, or the IRR function won’t be able to calculate the rate of return. The IRR function uses iterative techniques to arrive at a solution. If the function does not find a result after 20 tries, the IRR returns the #NUM! error value. You might need to provide a better initial guess value to get a result, particularly if the function is returning such an error. Here’s an example with the guess parameter:

myIRR = IRR(cashFlows, 0.1) ' 0.1 as the guess, which is 10%
Always make sure to test your VBA code to ensure it’s working as expected with your specific data sets.

Unlock Your Potential


Basic - Advanced


Access Basic - Advanced

Power BI

Power BI Basic - Advanced

Help us grow the project

Leave a Reply

Your email address will not be published. Required fields are marked *