# VBA MIRR Function

HomeVBA Functions LIST (Category Wise)How to use the VBA MIRR Function (Syntax + Example)

## What is VBA MIRR Function

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.

## how to use it

To use VBA's MIRR function you need to understand its syntax and arguments:

### Syntax

MIRR(ValueArray, FinanceRate, ReinvestRate)

### Arguments

• ValueArray: An array of cash flow which 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 use MIRR Function in VBA

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

Below are some important points which you need to take care while using MIRR function in VBA.

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