How to use MIRR function in VBA?

The MIRR (Modified Internal Rate of Return) function is not built-in as a direct function in VBA, but you can use the `WorksheetFunction` object to access it from Excel’s function library since it’s a standard Excel function.

To use the MIRR function in VBA, you need to address three sets of cash flows: the initial investment, the positive cash flows, and the negative cash flows. Additionally, you have to specify a finance_rate and a reinvest_rate.

The MIRR function syntax in Excel is:

MIRR(values, finance_rate, reinvest_rate)

Here’s how you would use the MIRR function in VBA:

Sub CalculateMIRR()
    Dim cashFlows As Range
    Dim financeRate As Double
    Dim reinvestRate As Double
    Dim mirrValue As Double
    
    ' Set the range of cash flows (including the initial investment)
    Set cashFlows = Range("A1:A5") ' Example range where your cash flows are
    
    ' Specify the finance_rate (cost of borrowing)
    financeRate = 0.1 ' 10%
    
    ' Specify the reinvest_rate (return on reinvestment)
    reinvestRate = 0.12 ' 12%
    
    ' Use the WorksheetFunction object to access MIRR
    mirrValue = Application.WorksheetFunction.MIRR(cashFlows, financeRate, reinvestRate)
    
    ' Output the result
    MsgBox "The Modified Internal Rate of Return (MIRR) is: " & Format(mirrValue, "Percent")
End Sub

Remember to replace `Range(“A1:A5”)` with the actual range where your cash flow data is located. The cash flow data should be organized such that the initial investment is negative and subsequent cash inflows are positive, reflecting the typical cash flow scenario for projects or investments.

The `WorksheetFunction.MIRR` function will return a decimal, which you can then format as a percentage using `Format(mirrValue, “Percent”)` like in the example above, to display it in a more human-readable format.

Keep in mind that if there are any errors with the data (e.g., non-numeric values or insufficient data), the MIRR function may raise an error. You should handle such errors appropriately in your VBA code.

facebooklinkedin