MIRR
Motivated by e-mail from Dean A.

There's this animal called MIRR

You borrow $A to start some company (or some project).
The Financing rate is F%. (F% is the interest charged on the loan.)
Profits from the company are Invested at a rate I%.

Expected profits are $B after 1 year and $C after 2 years.
(These you intend to invest at that annual rate of I%.)

Then comes the BIG question:
Is $A too much to pay for the company?

The profits will be worth (at the end of n years):
P = B (1+I)n-1 + C (1+I)n-2.

Suppose the company is worth $K, at the end of n years.
The total value of profits + company will then be their sum, P + K.

So what "return" are you getting on your enterprise?
There's some magic rate of return (which we'll call MIRR).
Our A would be worth A (1+MIRR)n (after n years at this magic return).

Setting that equal to P + K we'd get the equation:
A (1+MIRR)n = B (1+I)n-1 + C (1+I)n-2 + K.

Now all we have to do is solve for MIRR, eh?
To buy that pizzeria, suppose we get MIRR = 8%.
For the burger stand we get MIRR = 7%.
Then we buy the pizzeria.

Note:
If A is the only loan, then MIRR ain't got nothin' to do with the Finance rate F.
However, if there are other loans, we gotta calculate their present value (at the Finance rate F%) and add all these present values to A.
If that gave A', then we'd solve for MIRR from:
A' (1+MIRR)n = B (1+I)n-1 + C (1+I)n-2 + K.
'course, there may be other profits as well as B and C, so ... uh ... I reckon y'all can take it from here.
If an initial loan of A0 is followed by subsequent loans (after 1, 2, ... years) of A1, A2 ... and the Financing rate is F

and annual profits (after 1, 2, ... years) are B1, B2 ... which are invested at an Investment rate I then define:

PV = A0 + A1/(1+F)1 + A2/(1+F)2 + ...
That's a Present Value
FV = B1(1+I)n-1 + B2(1+I)n-2 + ...
That's a Future Value

then MIRR is defined so that:
PV (1+MIRR)n = FV
So the PV gives FV after n years with annual return MIRR.

Solving:

MIRR = [ FV / PV ] 1/n - 1

I forgot to mention that one (often) takes the PV as a series of negative cash flows, so - PV is (often) used in the formula for MIRR
... just so you get a positive ratio: -FV/PV.
Further, if I = F = IRR, then MIRR ain't no different than IRR.
I might also mention that there's FMRR.
In the above analysis, all cash flows are discounted to Present Value at the same rate.
With FMRR, there are a couple of rates for this ritual ... and the discounting is not (necessarily) to the "present" value.

To play with MIRR (and compare with IRR), there's a spreadsheet:

>Can I play?
Be my guest. Just click on the picture to download the spreadsheet.

gRR

In the derivation of MIRR, we calculated the Present Value of all the loans using the financing rate F.
That seems strange, to me.

>Why?
Okay, suppose we make a bunch of loans and, after n years, we owe $B.
Our invested profits (at the end of n years) are worth $A.
Let's assume that A > B.

How to calculate some "rate of return"?
I started with $0, made some loans and ended up with a profit of A - B. That's a return of infinity.
Obviously, starting with $0 ain't good for calculating some annual return. We need an initial bankroll.
MIRR does that by calculating a Present Value at the F% rate. That seems kind of arbitrary, to me.

How about if I imagine starting with an initial (fictitious) bankroll which is just sufficient to cover the actual loans.
Since I can (presumably) invest at the Investment rate I, that (fictitious) initial bankroll would be the Present Value of all the loans, discounted by the annual rate I:
[1]       P = A0 + A1/(1+I)1 + A2/(1+I)2 + ...
Having $P initially, I would be able to pay off each loan at times t = 0, 1, 2, etc..

Now I have an initial $P in my pocket and, after n years, the profits (invested at I%) are worth:
[2]       Q = B1(1+I)n-1 + B2(1+I)n-2 + ...
(Note that Q is the same as FV, above.)

So now how would I calculate some rate of return? I got me some initial bankroll, right?
I reckon I'd just write:
[3]       P (1+R)n = Q   or   R = [ Q / P ] 1/n - 1

>Looks familiar ... hey! That's just MIRR, right?
Not quite. In fact, MIRR calculates the Present Value of the loans using the Finance rate F.
That's what I don't like ... so I'm using the Investment rate I.

>So what's this rate called ... this R rate?
I call it gIRR.
>And it's something new, eh?
Uh ... I doubt it.
Note that, although IRR requires solving some fancy equation, gIRR doesn't. In fact, the Finance rate doesn't even appear.

>But no Finance rate? What if it's a jillion percent?
Okay, we need some initial bankroll to calculate a return. Let's do it this way:
  • We pretend to have enough initial money, $M, to pay off the final value of the loan(s).
    What's the final value of the loan(s)? That's:
    [4]       L = A0(1+F)n + A1(1+F)n-1 + A2(1+F)n-2 + ...
    What's the necessary value of $M so that after n years (invested at I%) it's worth $L? That's:
    [5]       M = L/(1+I)n
  • After n years, we pay off that loan and have what left in our portfolio?
    We have the invested profits, namely Q (from [2], above.)
  • That'd mean, at some magic annual return, "x", we'd start with $M and end up with $Q -- so:        
    M(1+x)n = Q so we got us yet another annual return which (for obvious reasons) we'll call:
    gRR = [ Q / M ] 1/n - 1

Picture taken from the
private collection of pjPonzo

There's a spreadsheet y'all can play with. Make up your own Rate of Return, eh?