MIrr

MIrr(values, I, financeRate, reinvestRate)

Computes the modified internal rate of return for series of cash flows («values») at equally-spaced intervals, where «financeRate» is the interest rate per period at which money is borrowed to finance the negative values (outflows) and «reinvestRate» is the interest rate per period received on earnings (positive «values») after they are realized.

The MIRR is an indication of the quality of an investment that overcomes some of the distortions inherent in the standard Internal Rate of Return (IRR) measure.

To compute the MIRR for a cash flow at arbitrary time points, use XMIrr.

Modified Internal Rate of Return

MIrr computes a measure expressed as a rate of return, by utilizing net present value in a way that makes it more akin to Npv in terms of its robustness than to Irr.

The idea is to split the original cash-flow into two streams -- the positive cash flows and the negative cash flows. Then we replace the positive cash flow stream with a single inflow gain occurring at the final time point, having the same net present value as the stream of inflows. Likewise, we replace all outflows with a single absolute-value outflow cost occurring now. With one gain at the n^th time point and one expense at the zeroth time, the rate of return is just

MIrr = (gain/cost)^(1/n) - 1

To obtain the present-value equivalent of the outflows, we assume that all inflows are reinvested at the «reinvestRate» until the final time period. Setting the present value of a single gain in the final time period equal to the present value of this series, we obtain:

[math]\displaystyle{ \begin{array}{lcl} \frac{gain}{(1+rr)^n} & = & \sum_{ \begin{matrix} {i=0} \\ s.t. \\ {x_1}\gt 0 \end{matrix}}^{n} \frac {x_1}{(1+rr)^i} \\ & = & npv(rr, x \cdot (x\gt 0), I) \cdot (1+rr)\\ gain & = & npv(rr,x \cdot (x\gt 0),I) \cdot (1+rr)^{n+1} \end{array} }[/math]

Similarly, to obtain the present-value equivalent of the negative flow stream, we obtain

cost = Npv(frate, -x*(x < 0), T)*(1 + frate)

We have to multiply by (1 + frate) to obtain the present value assuming the first event in x occurs at the zeroth time period.

Hence the full MIrr is equivalent to:

[math]\displaystyle{ MIrr(x,T,rr,fr) = \Bigg( \frac{Npv(rr,x \cdot (x\gt 0), T\cdot(1+rr)^{n+1}}{Npv(fr,-x \cdot (x\lt 0), T) \cdot (1+fr)} \Bigg) ^{1/n} -1 }[/math]

Example

Consider the following cash-flows from two potential projects:

Index Year := 0..6

Index Project := [1, 2]

cashFlow := if Year = 0 then -200 else if Year = 6 then -900 else if Project = 1 then 200 else 190

Year▶
Project ▼	0	1	2	3	4	5	6
1	-200	200	200	200	200	200	-900
2	-200	190	190	190	190	190	-900

Clearly Project 1 has a higher rate of return than Project 2. However, their internal rates of returns are reversed:

Irr(cashFlow, Year) → [5.4%, 8.77%]

The modified rate-of-return avoids these deficiencies. Using a «reinvestRate» of 5% and a finance rate of 7%, we obtain more reasonable rates-of-return:

MIrr(cashFlow, Year, 5%, 7%) → [6.4%, 5.5%]

Notes

MIrr is well-suited for Monte Carlo simulation. The Expected MIrr is robust and well-defined, as is Npv. This is in contrast with Irr, for which E[Irr] is nonsensical, and for which Monte-Carlo analysis often leads to NaNs.

History

Introduced in Analytica 4.2.

See Also

• Irr
• XMIrr
• Npv
• XNpv
• Internal Rate of Return (IRR) and Modified Internal Rate of Return (MIRR) Webinar recording
• Financial functions