The VBA MIRR Function

Related Function:
VBA IRR

Description

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:

MIRR( ValueArray, FinanceRate, ReinvestRate )

Where the function arguments are:

ValueArray -

An array of cash flows, representing a series of payments and income, where:

  • Negative values are treated as payments;
  • Positive values are treated as income.
This array 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.


VBA MIRR Function Example

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 $100
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 = MIRR( cashFlows, 0.055, 0.05 )
' 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%).

Note that:


VBA MIRR Function Error

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:

Run-time error '5': Invalid procedure call or argument

VBA Run Time Error 5 Message Box