MIRR — How It Works

From our experience, it appears that Internal Rate of Return (IRR) is the metric of choice for many, if not most, real estate investors. However, you may be aware that there are a few issues with IRR that can cause you some vexation: If you expect a negative cash flow at some point in the future, then the IRR computation may simply fail to come up with a unique result; and with your positive cash flows, IRR may be a bit too optimistic about the rate at which you can reinvest them.

For these reasons, a variation on IRR, called Modified Internal Rate of Return (MIRR), can be very important. When you see how it works, then you’ll also see that it gives you the opportunity to deal with IRR’s shortcomings.

Our support folks have had a number of calls from users of our Real Estate Investment Analysis software asking for guidance in using and understanding MIRR. How does it work, and how do I choose the “safe” and “reinvestment” rates that it asks for?

Let’s start with some definitions:

“safe rate The interest rate obtainable from relatively risk-free investments, such as U.S. Government Treasury Bonds.” source: The Complete Real Estate Encyclopedia by Denise L. Evans, JD & O. William Evans, JD; 2007, McGraw-Hill (btw, this is an excellent reference book)

“reinvestment rate When analyzing the value of an income producing property, it is the rate an investor is assumed to be able to earn on intermediate cash flows. …” Ibid

There is an alternative name sometimes used for the safe rate — “finance rate” — and the rather opaque definition given in the Excel help for MIRR doesn’t seem particularly helpful: “…the interest rate you pay on the money used in the cash flows.” Frankly, I’m not sure I understand what that is supposed to mean, but I believe if you focus on the term “safe rate,” you will be able to follow this discussion easily. The reinvestment rate also sports an alias — “risk rate” — which seems clear enough, but I believe again that you will find it easier to stick with the more common term, “reinvestment.”

Let’s begin with the safe rate and pose the question, “Why and when does the safe rate come into play?” The answer has to do with negative cash flows. Usually, you expect an investment to put cash into your pocket (positive cash flow), but sometimes it pulls money out of your pocket instead (negative cash flow). In real life, you can’t leave a negative cash flow sitting there and just move on to the next year. The property has to pay its bills, so you as the investor have to pick up the tab. In other words, you have to make an additional cash investment in the property. Herein lies the key.

Hold that thought for a moment while we consider what happens to an IRR computation when it encounters negative cash flows. Let’s say you invest $100,000 to purchase a property and have these cash flows:

Day 1, Initial investment: -100,000
Year 1: 1000
Year 2: 1000
Year 3: 2,000
Year 4: 1,000
Year 5: 140,000

Notice that your initial investment is considered a negative cash flow because it is money out of your pocket. Now notice how many times the sign changes in this series of cash flows: once, going from negative (initial investment) to Year 1.

Now let’s says you expect Year 3 to show a negative cash flow instead:

Day 1, Initial investment: -100,000
Year 1: 1000
Year 2: 1000
Year 3: -2,000
Year 4: 1,000
Year 5: 140,000

How many sign changes? You go from minus 100,000 to plus 1,000; then to minus 2,000; then to plus 1,000. That’s three changes of sign.

Conventional mathematical wisdom has it that IRR can have “non-unique” solutions for a series of cash flows. In fact, there can be as many solutions as there are sign changes. Hence there are probably three different IRRs that would describe this series. Often, some of those solutions may seem unreasonable, like 0% or 100%. That is why the IRR function in Excel asks you to enter a “guess rate” — a rate you believe would be reasonable. If one of the several answers that Excel comes up with for a series like the one above is within shouting distance of your guess, then it should display that as the answer.

MIRR deals with several shortcomings in IRR and one of those is the problem of negative cash flows. Let’s get back to that thought you were holding from the discussion above. A negative cash flow is really an additional cash investment you must make. The assumption you make in MIRR is that you will put the money aside on Day 1 so that you will have it available to soak up the negative cash flow when it occurs. In the case above, you need $2,000 in Year 3; you will invest that money on Day 1 so that you will have it when you need it in Year 3.

You could simply put the $2,000 under your mattress and pull it out three years later. However, you are a prudent investor and so you put your money somewhere safe and where it will earn interest. That’s where your safe rate comes in. You don’t really need to invest $2,000 on Day 1. You will invest whatever amount necessary that will grow at the safe rate to give you exactly $2,000 in three years. To put it another way you will shift the timing of your $2,000 investment from Year 3 to Day 1 by discounting at the safe rate.

Let’s say you find a short-term T-Bill or other secure vehicle, like a Certificate of Deposit, that will pay you 2% per year for the next three years. If you discount the required $2,000 at 2%, you find that you have to set aside just $1,884.64 and let it grow. You have effectively added that amount to your initial investment while at the same time zeroing out the negative cash flow in Year 3:

Day 1, Initial investment: -101,885
Year 1: 1000
Year 2: 1000
Year 3: 0
Year 4: 1,000
Year 5: 140,000

What MIRR really does is to rearrange all the cash flows so that it ultimately has only two: “Day 1” and the final year when you dispose of the property. It uses that safe rate, as we’ve seen, to zero out all of the expected negative cash flows. Then it takes the “reinvestment rate” you specify to zero out all of the positive cash flows by compounding them forward.

Choosing this reinvestment rate can be a bit trickier, because you have to decide what rate you could earn on the property’s positive cash flows. You may not be able to earn as much from smaller cash flows as you do from the property itself unless the cash flows are large enough to allow you to buy another, similar property. In that case, your reinvestment rate might be comparable to the return you’re achieving with this property. In any event, you’re not going to assume that you will use cash to make a risk-free investment as you do with the safe rate, so this rate will necessarily be higher (read: riskier).

You’ll also want to assume that it will be a rate you could achieve, on average, over the holding period of the investment. If the cash flows are too small to use to acquire another property, then perhaps you’ll use the money to buy stocks or bonds. You need to make a judgment as to what kind of return you might reasonably expect reinvesting your cash flows in those vehicles.

MIRR will take the rate you choose (say 7%, for example) and compound all positive cash flows forward, adding the results to the total cash flow in the final year. That means the other positive cash flow years are now all at zero, leaving, as I said above, just the initial investment and the proceeds in the sale year. You have one negative cash flow — Day 1 — and one positive cash flow — the final year. That means you have only one sign change, so the MIRR function can then perform a standard IRR-style computation and derive a single result.

Day 1, Initial investment: -101,885
Year 1: 0
Year 2: 0
Year 3: 0
Year 4: 0
Year 5: 143,606

MIRR 7.11%

Unlike IRR, MIRR will require that you make some judgment calls in selecting the two rates. Safe rate is, well, pretty safe because it is fairly objective. Where can you put your cash in a risk-free vehicle, and what can it earn there? Reinvestment rate is far more subjective. The end result, however, is that your MIRR will often give you a reading that is more conservative and perhaps more realistic.