View source for MIrr

[[Category:Financial Functions]]

== 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 [[Irr|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 [[Npv|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 [[Npv|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<sup>th</sup>'' time point and one expense at the zeroth time, the rate of return is just 
:<code>MIrr = (gain/cost)^(1/n) - 1</code>

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>
\begin{array}{lcl}
\frac{gain}{(1+rr)^n} & = & \sum_{
\begin{matrix}
{i=0} \\
s.t. \\ 
{x_1}>0
\end{matrix}}^{n} \frac {x_1}{(1+rr)^i} \\
& = & npv(rr, x \cdot (x>0), I) \cdot (1+rr)\\
gain & = & npv(rr,x \cdot (x>0),I) \cdot (1+rr)^{n+1}
\end{array}
</math>


Similarly, to obtain the present-value equivalent of the negative flow stream, we obtain
:<code>cost = Npv(frate, -x*(x < 0), T)*(1 + frate)</code>

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>
MIrr(x,T,rr,fr) = \Bigg( \frac{Npv(rr,x \cdot (x>0), T\cdot(1+rr)^{n+1}}{Npv(fr,-x \cdot (x<0), T) \cdot (1+fr)} \Bigg) ^{1/n} -1
</math>

== Example ==
Consider the following cash-flows from two potential projects:

:<code>Index Year := 0..6</code>
:<code>Index Project := [1, 2]</code>
:<code>cashFlow := if Year = 0 then -200 else if Year = 6 then -900 else if Project = 1 then 200 else 190</code>

:{|class="wikitable"
!colspan=8|Year&#9654; 
|-
! Project &#9660; !! 0 !! 1 !! 2 !! 3 !! 4 !! 5 !! 6
|-
! 1 
| -200 || 200 || 200 || 200 || 200 || 200 || -900
|-
! 2 
| -200 || 190 || 190 || 190 || 190 || 190 || -900
|}

Clearly <code>Project 1</code> has a higher rate of return than <code>Project 2</code>.  However, their internal rates of returns are reversed:
:<code>Irr(cashFlow, Year) &rarr; [5.4%, 8.77%]</code>

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:
:<code>MIrr(cashFlow, Year, 5%, 7%) &rarr; [6.4%, 5.5%]</code>

== 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<nowiki>[</nowiki>[[Irr]]<nowiki>]</nowiki> is nonsensical, and for which Monte-Carlo analysis often leads to [[NaN]]s.

==History==
Introduced in [[Analytica 4.2]].

== See Also ==
* [[Irr]]
* [[XMIrr]]
* [[Npv]]
* [[XNpv]]
* [[Analytica_User_Group/Past_Topics#Internal_Rate_of_Return_.28IRR.29_and_Modified_Internal_Rate_of_Return_.28MIRR.29|Internal Rate of Return (IRR) and Modified Internal Rate of Return (MIRR)]] Webinar recording
* [[Financial functions]]