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:
- Open Excel and then press `ALT` + `F11` to open the VBA editor.
- Insert a new module if there isn’t already one available.
- 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
Excel
Access
Power BI
