The VBA MIRR function calculates the Modified Internal Rate of Return for a supplied series of periodic cash flows (i.e. a series of payments and returns).
The syntax of the function is:
Where the function arguments are:
An array of cash flows, representing a series of payments and income, where:
|FinanceRate||-||The interest rate paid on the money used in the cash flows.|
|ReinvestRate||-||The interest rate received on the reinvested cash flows.|
In the following example, the VBA MIRR function is used to calculate the modified internal rate of return for an initial investment of $100, that generates a series of cash returns over 5 years. The finance rate is 5.5% and the reinvestment rate is 5.0%
' Calculate the modified internal rate of return of an initial investment
' of $100, that generates a series of cash returns over 5 years.
Dim cashFlows(0 to 5) As Double
Dim mirrVal As Double
cashFlows(0) = -100 ' Initial investment of $100mirrVal = MIRR( cashFlows, 0.055, 0.05 )
cashFlows(1) = 18.0 ' Return from year 1
cashFlows(2) = 22.5 ' Return from year 2
cashFlows(3) = 28.0 ' Return from year 3
cashFlows(4) = 35.5 ' Return from year 4
cashFlows(5) = 45.0 ' Return from year 5
' mirrVal is calculated to be 0.1000268752662.
The above VBA code calculates the modified internal rate of return for the investment to be 0.1000268752662 (10.0%).
If the ValueArray that is supplied to the VBA MIRR function does not contain at least one negative value and at least one positive value, you will get the following error: