Categorized | happenings

The Five Spreadsheet Formulas Every Entrepreneur Should Know: Internal Rate of Return

Pen and Excel bar graph

The Series
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

Many moons ago, I started a series of blog posts on the five spreadsheet formulas every entrepreneur should know and, well, the follow-up never really followed up. Over the next ten days, however, we’re going to bring this epic snoozerific spreadsheet series to a close by walking you through everything you need to know (and a bit you didn’t) to turn your spreadsheet into a startup business powerhouse.

So, without further ado, let’s get rocking with our first spreadsheet function that every entrepreneur should know: IRR. No, not a techno band, but “internal rate of return,” and it’s the standard measure that venture capitalists and others use when considering making an investment in your business. So listen up!

What’s an Internal Rate of Return?
Despite the confuserific definitions of IRR you’ll find on the Web and the even more obscure explanations lurking in the pages of murky finance textbooks, an IRR is actually very simple.

In the most basic sense, the internal rate of return is one of the most accurate measures of the profitability of an investment.

Let’s explore this for a second. There are several ways to measure an investment’s profitability. Some of them are pretty accurate while others are seriously (and dangerously) misleading. Chances are, you use the seriously and dangerously misleading kind when you calculate the value of your investments, including investments in your own business.

But if you want to get folks like venture capitalists and angel investors to fork over some serious equity investment in your business, you’ll need to use one of the more accurate ways of measuring investment profitability. And VC’s almost universally employ the Internal Rate of Return to determine whether or not a business is worth investing in.

So, if you’re an entrepreneur looking for equity financing, you need to run IRR’s on your current and prospective financials in relation to the payouts investors will receive — including you. A low IRR means no financing; a (believably) high IRR makes equity financing more likely.

That’s why you should know it.

So what makes an internal rate of return accurate? As opposed to the blissfully inaccurate ways the rest of us hoi polloi use to calculate the return on an investment?

You’re familiar with “return,” but chances are you use something called the “accounting rate of return.” You take the average investment and divide it by the average return. For instance, you invest one million dollars and five years later you get back two million dollars. What’s the accounting rate of return? 20% annual return or a 100% total return on your investment.

That’s a good return but . . . it’s not really 20%.

For one, the value of money declines every year. So the two million dollars you get five years from now isn’t worth two million dollars today.

More importantly, it doesn’t take compounding into account. If you put one million dollars into the bank at 20% interest (don’t you wish) and the interest compounded every year, at the end of five years, you’d have . . . $2,448,320 (if I punched the right numbers on my calculator, that is). When you take compounding into consideration, the more accurate return on an investment of one million dollars that paid back two million dollars five years from now is . . . 15%.

In other words, if you stuck one million dollars into a bank account and showed up five years later and, shazam, the account now had two million dollars, the actual interest you would have been earning was 15%. But the interest compounded every year. (Do the math, you’ll see I’m right.)

Here’s a surprise. If you calculate the IRR on the investment I described? That’s right: 15%.

Why should you consider compounding interest when calculating a return? Well, because your money and your gains are tied up for five years, no? You don’t get your two million dollars until five years from now — if you put the money in a bank, mutual fund, or a stock with a DRIP option, you’d be compounding your returns year after year (or month after month).

In other words, to accurately calculate your return, you have to consider that you should be making earnings off the earnings you make on your investment.

Finally, the accounting rate of return does not allow you to adjust the return relative to risk (more on this later). All I mean is this: when you take on risk, you may have a return that you absolutely require relative to that risk (say 10%). The accounting rate of return does not really tell you by how much you miss or exceed that hurdle. But more on this later.

Now that you know what the accounting rate of return is and isn’t, here’s a better, more thorough definition of the 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.

In other words, the internal rate of return converts all the cash flows (both negative and positive) of an investment into the equivalent of banking the investment money and earning a compounded interest on that money (or putting it into a stock in which all the dividends are reinvested). The internal rate of return tells you what that compounded rate actually is.

And the lovely thing about the IRR is that it adjusts itself to the timing of the investments and the timing of the cash flows. Obviously, if all the returns come at the end of the investment, the equivalent compounded interest rate (and IRR) would be considerably less. If the lion’s share of investments come towards the beginning of the investment period, the equivalent in compounded interest (and IRR) goes down. So, unlike an accounting rate of return — which is how most of us schmucks calculate a return on investments and is really no more than an average — the timing of investments and returns determines the real return. And an IRR takes the timing into consideration.

Now the typical finance textbook would define an IRR as follows: “the IRR is the compounded interest rate at which the net present value of the investment is zero.” Make sense? Of course it doesn’t. That’s why entrepreneurs don’t read muck-it-up nonsensopedia finance textbooks by folks with PhDs — which, as you probably know, stands for “Piled Higher and Deeper” (A BS degree stands for “Bull S$%*,” an MS degree stands for “More of the Same,” and a PhD? “Piled Higher and Deeper.”)

The variables
To calculate an IRR using a spreadsheet, you don’t need to understand the math beyond the variables that go into the math. Once you know the variables, you can set up any spreadsheet to calculate your internal rate of return, whether you’re talking about a loan to your sister, an investment in a startup, the return on an equity investment in your business, or the return on your IRA.

And for an IRR, the only variables you need are cash flows and a “guess.” Forget the guess for now, let’s just concentrate on the variables.

The most important thing to consider about entering cash flows into a spreadsheet to calculate an IRR 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 IRR 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.

Okay, let’s get cracking. I will use prospective financials from a real-world investment. We’ll skip the nitty-gritty of the financials and rifle straight to the investment returns calculated based on those financials.

The deal the entrepreneur is offering the investor is this: 20% equity in the restaurant for a 2.5 million dollar investment. For the first few years of the investment, the entrepreneur will pay the investor a much more sizable portion of the profits, which are scheduled to kick in during year 3. Now the entrepreneur has already figured out the profits, he just has to jigger the numbers to give the investor a nice return on profits for five, eight, ten, or fifteen years. At the end of this period, the investor will receive the 20% in profits equal to his share in the restaurant.

Among the alternatives you come up with (and the financials contain about 30 different disbursements to the investor), you decide to give your investor 60% of the profits beginning in Year 3 (since your financials don’t show you making a profit in years one and two) and 70% of the profits in years 6-8. Based on your prospective financials, this gives the following cash flows to the investor:

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.
In year 2, the cash flow is 0.
In year 3, the return to the investor is $385000
In year 4, the return to the investor is $421000
In year 5, the return to the investor is $457000
In year 6, the return to the investor is $582000
In year 7, the return is $634000
In year 8, the return is $691000
(Throughout the period, the investor holds a 20% equity stake which they still hold at the end of year 8, so we need a business valuation for year 8, but let’s leave this out of the picture for now. Just the money flowing back to the investor.)

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 IRR.

First, choose a spreadsheet cell and add a sum function (Insert/Function/Math/Sum). This will sum up all the values (it’s always good to have a sum for reference). Sum of the cash flows for this particular financial projection and profit disbursement: $3,170,000. The average accounting return? About 3.35%. Now, this sum is completely unnecessary to calculating an IRR. It’s just good to know the number.

Highlight another cell and choose “Insert/Function” and find the IRR function in your spreadsheet’s function library (in Excel, it’s listed as “IRR” under “Financial”). You’ll get a dialog box that looks something like this:

IRR dialog box in Excel

You can see that the spreadsheet is going to ask you for two types of variables.

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).
Guess: don’t worry about it.

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 IRR for (and don’t worry if the cash flows are monthly or weekly, the spreadsheet will correctly calculate the IRR).

The next box says “Guess.”

Well, there’s one for the confuse-me-then-lose-me file.

All you need to know here is that to calculate an IRR, you have to start with a “guess” of what the final answer might be. The whole math process of calculating an IRR is like the guess-a-number game we used to play as kids. You know, the one where you ask, “Is it less than 500?” “No.” Second question is, “Is it less than 750?” “Yes.” Third question, “Is it more than 625?” And so on. That’s how the math behind an IRR works (only it’s more complicated). And if you want to know the PhD term for that kind of math function, it’s called an iterative mathematical function, just so’s you can impress people at your next party.

So the spreadsheet must start with a “guess” and run the math and, if the guess is off (which it is), keep refining the guess until it finally gets the answer. So any guess will do. So just plunk in a number or ignore the whole thing. Most spreadsheets with IRR functions will insert an automatic “guess” if you don’t (usually 0.1%).

Once you’re done, you click okay.

The result appears immediately. The IRR for these cash flows is 4.17%.

Ummm, you lose. Nobody is going to risk two and a half million bucks for a 4.2% return (of course, we haven’t factored in the future value of the 20% equity stake).

You may have noticed that the IRR function I just described does not take inflation or risk into account. Good call! We’ll talk about that in another post.

Be Sociable, Share!

3 Responses to “The Five Spreadsheet Formulas Every Entrepreneur Should Know: Internal Rate of Return”


  1. […] spreadsheet formulas 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 […]

  2. […] Should Know, Part Three: Modified Internal Rate of Return The Series Introduction 1.) Internal Rate of Return 1a.) Modified Internal Rate of Return 2.) Net present value 3.) Payback 4.) Average 5.) Standard […]

  3. […] formulas every entrepreneur should know, Part 5: What is payback? The Series Introduction 1.) Internal Rate of Return 1a.) Modified Internal Rate of Return 2.) Net present value 3.) Payback (What Is Payback?, […]

Leave a Reply

Shoestring Book Reviews

Shoestring Venture Reviews
Richard Hooker on Jim Blasingame

Shoestring Fans and Followers



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