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.

7 comments on “MIRR — How It Works

  1. ogunsakin gbenga on

    This is the best article on mirr that i have seen and read.pls., keep it up.you guys know how to pass knowledge across.should i have any other questions,how can i get in contac with you?have a lovely day

  2. Benny on

    Hi, This is a good article explaining how Mirr works. One question remains to me after reading the article of Mirr:

    What would happen if you would borrow the 2000 dollars in year 3 instead of putting an additional cash investment of 1885 dollars in year 0 (so that it would soak up the negative cash flow of 2000 dollars in year 3) .
    If you would borrow this extra 2000 in year 3 you wouldn,t need to put any money aside in year 0 .
    How would the below excel model look like in this case of borrowing the additional 2000 you need in year 3?

    Day 1:
    Year 1:
    Year 2:
    Year 3:
    Year 4:
    Year 5:

    I would appreciate your reply.

    Thanks
    Benny

  3. Frank Gallinelli on

    Benny — That’s a very good question.

    One of the purposes of discounting back all negative cash flows to Day 1 and compounding all positives to the end is to try to reduce the income stream to just two cash flows: Day 1, which is negative (since it represents the money that went out at the time of acquisition) and the final cash flow, which you hope is positive since it represents the final disposition of the property. With just one negative and then one positive you end up with just one change of sign. IRR typically will return as many valid answers as there are sign changes. So, with just one change, you can do an IRR computation on these two cash flows and expect to get just one result.

    Of course, discounting a negative cash flow back to day one means you’re adding to the amount invested. What you’re suggesting would also zero out the negative cash flow in a future year, but would do so by increasing debt. What you would need to do in your Excel model now is to take into account the annual debt service from this new debt — essentially reducing your year-to-year cash flow. You would also need to reduce your final cash flow — the proceeds of selling the property — by whatever part of this new debt is still outstanding when you sell. If you take these steps, and have no other negative cash flows as a result, then you should be able to do a successful IRR calculation on the series of cash flows because there will be only one change of sign that occurs throughout the entire stream.

    It would be interesting to see which scenario — coming up with more cash on Day 1, or borrowing money when you have the negative cash flow — results in the higher rate of return. It would depend entirely on the projected cash flows and sale proceeds of the particular property, and could favor either scenario.

  4. Jacob Wilderger on

    Frank,

    Great info. As I progress in my career and continue to analyze properties it’s invaluable to my clients and my reputation to be able to convey the meaning of these metrics! Thanks!

  5. Amir Ashkan on

    Dear Gallinelli

    Special thanks for your post, it’s the best description of MIRR that I’ve read. it very helped me. I hope you be successful.

  6. Steve K on

    One question I’ve always had: is it appropriate to measure IRR/MIRR and show a cash flow based on how the property actually throws it off, or should the cash flow be based on distribution timing? I know some firms that only take cash annually at the end of the year which I imagine would have a lower IRR than a monthly calculation.

    Also, is there a follow up article on XIRR?

  7. Frank Gallinelli on

    Hi Steve — Good question, and I think the answer might be, “It depends.” (No, I am not running for office.) If you were looking, as an investor, at an actual rather than a projected series of cash flows then I think it would make sense to use the distribution timing. In other words, if you really know exactly how much you will receive and when you will receive it, then what you would be measuring is how well your cash investment is performing for you in real life. On the other hand, a typical real estate investment pro forma deals with projected cash flows. It usually does so on an annualized basis because it is seldom realistic to hope that one can make credible forecasts on a month-by-month basis (or, at least, the effort required would dwarf any benefit to accuracy). Hence, I believe it makes sense in that situation to use annualized cash flow projections regardless of when you might expect the distributions. Sometimes, trying to too fine a point on calculations that are based on projections leads to lesser rather than greater accuracy.

    Always looking for suggestions for article topics. Hadn’t thought about XIRR. Thanks for the tip!

    Frank

Leave a Reply

Your email address will not be published. Required fields are marked *