Categorized | tip sheet

The Spreadsheet Formulas Every Entrepreneur Should Know, Part Three: Modified Internal Rate of Return

Pen and Excel bar graph
The Series
Introduction
1.) Internal Rate of Return
1a.) Modified Internal Rate of Return
2.) Net present value
3.) Payback
4.) Average
5.) Standard deviation
Bonus!) Excel Solver

Are you ready for another exciting chapter in our supreme geek-a-thon survey of spreadsheet formulas? I know it’s not balloon boy, but too many entrepreneurs, small business owners, and managers have these spreadsheet thingies hanging around their hard drive and the most they use them for is to file numbers and letters in neat little spreadsheet boxes. So settle back in your ergonomic chair for yet another adrenaline-filled lesson in unlocking the math tools that every entrepreneur and business owner should know how to use.

A few sharp-eyed folks who read our last post on calculating an internal rate of return for an investment or project — and actually kept awake through the whole production — sent me emails saying something on the order of, “You’re assuming that the returns on the investment are being paid out to the investors.”

Ummm, yes. In fact, that’s the example I gave.

That, my worthies, is the problem with IRR’s. If you were to bring on an equity investor, chances are that all the returns over the life of the investment would be plowed back into the business. There’s also a pretty good chance that the venture or project will lose money are require a future infusion of much-needed cash, which, as anyone with a credit card can tell you, costs money. The solution is . . . the Modified Internal Rate of Return — or MIRR, if you’re Julie Newmar — and it’s going to burn up your computer screen with Grand Theft Auto excitement over the next few minutes.

What is a Modified Internal Rate of Return
In case you forgot (and I don’t blame you), here’s my definition of an Internal Rate of Return:

The internal rate of return measures the profitablility of an investment by calculating the equivalent compounded interest rate of the investment over the period for which the investment is made.

The standard IRR assumes that all the returns are paid out as they come in. It also assumes that, if the project has negative returns, you won’t need to go out and borrow money at some hefty interest rate (so there’s no cost to borrowing money in the standard IRR).

The example I gave (a true example) was an equity investment in a restaurant in which first and second year losses are fully covered in the initial investment and in which the managers began paying out profits in Year 3, so the investor was banking cash as well as building up equity.

A large number of business investments — particularly if you’re on the prowl for an equity investor — involve reseeding the venture or project with ongoing returns. An even larger number lose money for a year or two and require additional capital in the form of a loan, line of credit, or yet more equity investments. In those cases, the Modified Internal Rate of Return assumes that all returns are reinvested and then paid out at the end of the project or investment period. The MIRR also allows you to figure in the cost of new capital if, like business ventures everywhere, you lose money.

How do you calculate an MIRR?
Now remember, the only difference between an IRR and an MIRR is that, for the former, returns are paid out as they’re realized and, for the latter, returns are reinvested and then returned in one big lump sum at the end. So if you know how to calculate an IRR using a spreadsheet, then, happily, you know nearly everything you need to know to calculate an MIRR.

Except . . . (don’t you love that word) . . . an MIRR takes into account the cost of capital as well as the fact that reinvested funds will earn a different rate of return than the original investment. So the MIRR adds two additional variables to the cash flows: the “finance rate” (that is, the cost of capital) and the “reinvestment rate” (an assumed rate of return on the reinvested cash).

The variables
As for an IRR, you’ll need cash flows, but you also need some idea of how much you would have to pay for capital to make up for negative cash flows (your “finance rate” or “cost of capital”) and how much the reinvested funds will return (the “reinvestment rate”).

Just like an IRR, the most important thing to consider about entering cash flows into a spreadsheet to calculate an MIRR is that timing is everything. If an investment delivers returns monthly, then the returns have to be entered into the spreadsheet for every month. If you invest a pile of money at the beginning of the year, a pile more six months later, and another pile two years later, you have to place those investments in the proper relationship to the returns (in this case, in intervals of half a year). If returns or investments are variable (say, once in April, once in May, and once in November), then you have to choose the lowest common unit of time, in this case, monthly — most months, of course, will have cash flows of zero, but April, May, and November will have cash flows).

And remember, if you set up returns on a weekly, monthly, or yearly basis, your spreadsheet will calculate the MIRR based on those returns being paid at the end of the period specified. Variability in the realization of returns will affect the accuracy of the calculation.

Let’s use the exact same cash flows we used when discussing an IRR (remember, the IRR was 4.17% on those cash flows) In the original example, the managers were generously shoveling out profits to the equity investor every year, which the investor could spend on Vin Diesel DVDs.

The deal here is different. The investor still gets a 20% equity stake in the restaurant for a 2.5 million dollar investment. Starting in year 3, the investor will get a sizable portion of the profits with this catch: these profits will be reinvested in the business and the investor will get the total profit shares from years 3-8 at the end of year 8. Starting in year 9, the investor will receive the 20% in profits equal to his share in the restaurant (which we will not be including in our calculations).

Here’s the scenario we used last time:

In year 0, the cash flow is negative 2.5 million dollars (money going in to the investment is always a negative cash flow).
In year 1, the cash flow is 0 to the investor.
In year 2, the cash flow is 0 to the investor.
In year 3, the return to the investor is $385000, but it’s reinvested
In year 4, the return to the investor is $421000, but it’s reinvested
In year 5, the return to the investor is $457000, but it’s reinvested
In year 6, the return to the investor is $582000, but it’s reinvested
In year 7, the return is $634000, but, you guessed it
In year 8, the return is $691000 plus all the Year 3-Year 7 returns in one big, honking check

So you would set that up as follows in your spreadsheet:

Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8
-2,500,000 0 0 385000 421000 457000 582000 634000 691000

Those are the cash flows you enter in the spreadsheet. Now you can calculate the MIRR.

Highlight a cell in the spreadsheet and choose “Insert/Function” and find the MIRR function in your spreadsheet’s function library (in Excel, it’s helpfully listed as “MIRR” under “Financial”).

Insert MIRR in Excel

You’ll get a dialog box that looks something like this:

MIRR dialog box in Excel

Values are the cash flows in order and properly spaced relative to their time (if the cash flows are yearly, there should be one cash flow value for every year — if no money goes in or comes out, then the cash flow value must be set to zero).
Finance rate: think of the “finance rate” as the interest you would have to pay on a small business loan or line of credit — that, for most small businesses and startups, is the alpha and omega of their “cost of capital.” The finance rate is meant to cover any negative cash flows during the life of the investment. If, for instance, you lose money in the first couple years, you’re going to have to find that money somewhere, right? That additional cost will diminish the total yield on the investment.
Reinvestment rate: This is the return you expect the reinvested capital to make. It could be higher, lower, or the same as the original investment (usually lower). Again, let’s guess for now and set the reinvestment rate at 10%. Why do we set a rate higher than the IRR of 4.17%? Because the original investment loses money in the first two years — profits don’t show up until year 3 and increase steadily until year 8, so the reinvested money obviously is doing better than the original $2.5 million). Returns aren’t negative, however, because we’ve structured the original investment to cover two years of projected losses — so the “profit” is 0 in those first two years.

With the cursor in the Values box, select all the investment cash flows from Year 0 to Year 8 in the spreadsheet, from the initial investment (-2,500,000) to the last profit disbursement (691,000). When you’re done, a selection box should be dancing around the cash flows and the variables in the Values box should read something like A2:I2 (which means: all the cells starting at A2 and ending at I2).

Those represent all the cash flows in order that you’re going to calculate an MIRR for (and don’t worry if the cash flows are monthly or weekly, the spreadsheet will correctly calculate the IRR).

Enter 10% or 0.1 for both the finance rate and the reinvestment rate.

MIRR dialog box in Excel with variables filled in

The MIRR is 6%, whereas the original IRR was 4.17% (it’s higher because we assumed a higher return on the reinvestment).

That’s a bit better, but it still sucks as a return on a highly risky investment (92% of restaurants fail in their first two years).

Try this: set the reinvestment rate at the same rate as the IRR, 4.17%. What’s the MIRR?

4.17%.

Do you see why?

Now, let’s say you use a credit card to finance your business, so your cost of capital is — holy Chase, Batman! — 25%. With your finance rate at 25% and your reinvestment rate at 10%, what’s your MIRR?

It’s still 6%.

Why?

There are no negative returns in our projected profits. If, for instance, in Year 1, we had a negative return of $100,000, we’d have to borrow that money on our charge card at 25% interest. That interest would decrease the value of the returns in the later years that we would use to pay off the charge card and its interest.

In that case, the MIRR would be 5% because future returns have to cover that interest.

See?

So, why would you need an MIRR?

If you have negative cash flows on the investment (very typical for a startup in the first year or so), that is, your business requires an injection of money, borrowed or otherwise, after the initial investment has been made, then you need to calculate your return using an MIRR instead of an IRR, because you have to adjust your return relative to the cost of that extra money.

Second, if your business venture or project intends to plow returns back into the business (as we do here in Shoestring Publishing) rather than pay them out immediately, the MIRR is the more accurate measure of a project’s financial attractiveness.

And that ends the fun for today!

Be Sociable, Share!

2 Responses to “The Spreadsheet Formulas Every Entrepreneur Should Know, Part Three: Modified Internal Rate of Return”

Trackbacks/Pingbacks

  1. [...] every entrepreneur should know, Part One The Series Introduction 1.) Internal Rate of Return 1a.) Modified Internal Rate of Return 2.) Net present value 3.) Payback 4.) Average 5.) Standard deviation Bonus!) Excel [...]

  2. [...] know, part four: Net Present Value The Series Introduction 1.) Internal Rate of Return 1a.) Modified Internal Rate of Return 2.) Net present value 3.) Payback 4.) Average 5.) Standard deviation Bonus!) Excel [...]


Leave a Reply

Shoestring Book Reviews

Shoestring Venture Reviews
Richard Hooker on Jim Blasingame

Shoestring Fans and Followers


Categories

Archives

Business Book: How to Start a Business

Shoestring Book

Shoestring Venture in iTunes Store

Shoestring Venture - Steve Monas & Richard Hooker

Shoestring Kindle Version # 1 for e-Commerce, # 1 for Small Business, # 1 for Startup 99 cents

Business Book – Shoestring Venture: The Startup Bible

Shoestring Book Reviews

Shoestring Venture Reviews

Invesp landing page optimization
Powered By Invesp
Wikio - Top Blogs - Business