Internal Rate of Return (IRR)

  • Tagged with:
  • Algebra,
  • Conditional formatting,
  • Data tables,
  • Excel,
  • IRR,
  • Spreadsheets,

Many people know the old rule of thumb that at a 10% annual return, money doubles every seven years. (Actually it takes 7.27 years, but I guess this wouldn’t be a great rule of thumb.)

People who work in private equity, venture capital, or any other area where investment time horizons are measured in years rather than days, weeks or months – are generally pretty interested in the IRR of their investments. You’ll often hear a VC say something like “… we need a 35% IRR to justify so and so investment.” There’s plenty of discussion and debate elsewhere about the relevance of IRR as an investment measure, and the benefits of using IRR versus NPV (net present value) – so I’ll avoid getting into this debate here.

The problem with the above rule of thumb is that it doesn’t help you work out the result you’re interested in for other combinations of inputs. This is because the relationship between the variables is exponential rather than linear. You either need a calculator, a ready reckoner, or a large brain. In any case, it is useful to first explore the relationship between IRR (r), years the investment is held (t), and the sale multiple achieved (m).

The mathematics is pretty simple:

m = (1+r)^t

So, if you invest $100 at a 10% compound annual rate of return for 7 years, you’ll get back $195 at the end. That is:

100 * (1+0.1)^7

Which is pretty close to 2x your money − the rule of thumb mentioned above. Alternatively, if you know the multiple you require/achieved and you know the number of years, you can calculate the IRR as follows:

r = m^(1/t)-1

If you’re into spreadsheets you can pretty quickly knock up a table to reflect this. You can do this by either writing the required formula in every cell of the table, or by writing the generic formula once and creating a data table to present all of the scenarios you’re interested in. Either way, the end result may look like this.

This table tells you the IRR for various combinations of Multiple and Years.

I’ve seen a table like this on the back of someone’s business card. If you’re in the investments industry, it’s a pretty cool idea. It’s useful – so the person you give your card to might actually bother to keep your card on their desk. It also conveys immediately what you do, and what you’re interested in.

An alternative representation would be to show the Multiple for various combinations of IRR and Years. This table looks like this.

Here I’ve highlighted in yellow the Multiples that are close to 2.

I achieved this using conditional formatting, so that I am able to play around with the value I am interested in and also the IRR and Years values I want in the table, without having to manually adjust the shading.

If you’re interested, here’s a link to the Excel spreadsheet I created for this.

1 Comment so far Join the Discussion

  1. dw

    13th October 2009 at about 10pm

    THANKS I am teaching a class on VC finance and your wonderful table and explanation will really help me explain the IRR to multiple concept

    MUCH APPRECIATED!

Join the discussion

Popup

Register your Interest

Please register to keep up to date with our developments.

Register your Interest

Please register to keep up to date with our developments.