A McKinsey article on IRR (Internal Rate of Return) and why it is EVIL. (My words)
Project and opportunity evaluation has two main types of indicative metrics: value and efficiency. Value is typically expressed as Net Present Value or NPV. There are to typical ways of expressing efficiency: as a percent return on investment or as a ratio. The most commonly used percent function is Internal Rate of Return.
First a little background. Present value methods are based on the idea that most people and companies would rather receive one dollar today, than that same dollar one year from today. The preference is usually expressed as a cost of capital or discount factor (percentage) and should equal an indifference value. For example, if it is the same to you whether you receive $1 today, or $1.10 in a year, your discount factor is said to be 10%. In most cases, it represents either a cost or a lost opportunity.
In project or investment analysis, typically an analyst will generate a set of cash flows over time, incorporating everything he knows about future economic conditions, prices, costs, taxes, etc. The NPV is calculated by discounting the the yearly cash flow at the discount rate back to the present day. To use a simple example, if you have a discount rate of 10%, receiving $1 today would be the same as receiving $1.0 a year from today. They would both have an NPV of $1. Internal rate of return uses a similar methodology, but instead of presupposing a discount rate, IRR calculates the rate at which the NPV is 0. It works well only is there is a stream of negative cash flow followed by a stream of positive.
That seems pretty straightforward, what makes it evil?
Actually, there is really nothing wrong with the calculation, as with many things it's how you interpret the facts.
There are two things that make IRR misleading. First, short term projects that pay-out reasonably can have misleadingly high IRRs. Second, and related to the first, IRRs tend to not reflect what a project or investment is worth in the overall context of the company, it makes no assumptions about what happens with the cash that a project spins off.
So a three year project with a 50% rate of return needs to take the cash that it spins out and reinvest it. Unless all the firm's projects are 50% return projects, the reinvestment rate will be much lower. Probably in many cases less than 12%. If you were to put the project on a 30 year timeline, the same as your 15% infrastructure project, and make an explicit assumption about the rate at which the cash will multiply, it is possible that the firm would prefer the 12% project to the 50% project because at the end of the timeframe, you would have more money with the longer term project. I recognize that I am ignoring risk and a number of other factors, but I am trying to illustrate the concept. This is why IRR is evil; people take it out of context and think that it represents a real rate of return to the firm and that it can be useful for comparison of dissimilar projects.
Luckily, Excel allows for the growth rate of return calculation. It is called MIRR. It is sometimes called Growth Rate of Return.
Some of the ratio metrics are also useful. In particular the benefit-cost ratio (BCR) will give the same relative ranking as MIRR. It is calculated simply as the NPV divided by the maximum cumulative discounted negative exposure of the opportunity. Sometimes 1 is added to make the number have a value something like 1.2, meaning that for every PV dollar invested you are receivng 1.2 PV dollars paid back. If a project has multiple phases, sometimes it will have additional negative net cash flows in later years. Those must also be brought back as present values into the denominator.