VBA MIRR Function (Syntax + Example)

The VBA MIRR function is listed under the financial category of VBA functions. When you use it in a VBA code, it calculates the Modified Internal Rate of Return for a supplied series of periodic cash flows. As it’s a financial term you learn more about it from here.

Syntax

MIRR(ValueArray, FinanceRate, ReinvestRate)

Arguments

• ValueArray: An array of cash flow that represents the payments and income. Payments would be in negative values and incomes would be in positive values [It must contain at least one negative and at least one positive value].
• FinanceRate: The interest rate paid on the money used in the cash flows.
• ReinvestRate: The interest rate received on the reinvested cash flows.

Example

To practically understand how to use VBA MIRR function, you need to go through the below example where we have written a vba code by using it:

Sub example_MIRR()
Dim cF(0 To 9) As Double
cF(0) = -1000
cF(1) = 213.6
cF(2) = 259.22
cF(3) = 314.6
cF(4) = 381.79
cF(5) = 463.34
cF(6) = 562.31
cF(7) = 682.42
cF(8) = 828.19
cF(9) = 1005.09
Range("A1").Value = MIRR(cF, 0.09, 0.08)
End Sub

In the above code, we have used MIRR to get the interest rate of return from an array (cF) in which we have an initial investment of -1000 and then all the cash returns in the next 9 years and we have also specified the finance rate and reinvest rate and it has returned 22% in the result.

Notes

• If the array we supplied doesn’t have one value that is negative and one value is positive or not able to find results after 20 iterations, VBA will return a run-time 5 error.