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:

As the initial investment of $100 is an outgoing payment, this is supplied to the function as a negative value;
As the returns during years 1-5 are incoming payments, these are supplied to the function as positive values;
The FinanceRate and ReinvestRate are supplied to the function as decimal values.

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

Return to the VBA Functions Page
Return to the Excel VBA Tutorial Page