Irr

Irr(values, I, guess)

Computes the Internal Rate of Return (IRR) for a series of equally spaced cash flows («values») along the index «I». The internal rate of return is a widely used criteria for comparing alternative capital investment opportunities. Intuitively, it is the effective compounded rate of return (per time step) on invested capital that is implied by the investment's future cash flows.

IRR is an indicator of the efficiency or quality of an investment, but gives no information about the magnitude of the return. An investment with an IRR exceeding an organization's cost of capital adds value to the organization.

The IRR is formally defined as the discount rate at which the Net Present Value of the cash flows is equal to zero. For many "normal" capital investments, a large up-front outlay is followed by a stream of revenue into the future eventually totaling an amount greater than the initial investment. For these cash flows, as discount rate increases, NPV decreases, eventually passing from positive to negative NPV. The discount rate where NPV passes through zero, the IRR, is the discount rate at which the project's value of future earnings is break-even with the initial investment cost.

In general, the IRR is not uniquely defined -- there may be multiple discount rates that produce a zero NPV. There are also cases where no IRR exists. For example, if all cash flows have the same sign (i.e., the project never turns a profit), then no discount rate will produce a zero NPV. When the cash flows change sign repeatedly, there could theoretically be as many IRR solutions as there are sign changes. When multiple IRR solutions exist, care may be required to decide which IRR value (if any) is appropriate to your decision context. The third optional parameter to Irr, «guess», provides a starting guess for the search Analytica uses to find the Irr solution. When there are multiple solutions, Analytica will usually return the Irr solution closest to the indicated «guess».

Unlike the Npv function, the Irr function applies equally to an array where the first value occurs in the current time period, as well as to an array where the first cash flow occurs in the next time period.

When you have a series of cash flows at unequally spaced periods, use XIrr.

Relation to the NPV-criteria

An Internal Rate of Return criteria for capital budgeting can be compared to a Net Present Value criteria. An IRR approach avoids the need to determine your appropriate discount rate, a step fraught considerable subjectivity. It is this fact that makes it highly attractive as a decision criteria, and explains its widespread use. However, IRR as a criteria comes with a collection of its own problems. In many circumstances, Irr can produce highly misleading results and counter-intuitive decision criteria, and many people have been unwittingly fooled by Irr. One normally assumes that an investment with a higher Irr is a higher-quality investment, but in some situations, an investment that dominates another (is strictly better) will have a lower Irr value. These types of situations often occur when negative cash flows occur late in the series. Irr also tends to unwittingly introduce several biases. It can make bad projects look better, and good ones look great, to inflate the true value of high-return projects, and to give smaller projects an advantage over larger ones. (Kelleher and MacCormack (Oct 2004), "Internal Rate of Return: A Cautionary Tale", The McKinsey Quarterly, CFO.com).

Finance textbooks and academics often warn about the perils of using Irr, and usually suggest Npv as a superior measure for comparing capital investment projects; however, despite this, Irr remains the most commonly used metric by executives at corporations for comparing investments.

Uncertainty Modeling and Irr

Irr is very poorly behaved when used in conjunction with explicit models of uncertainty. The Expected Internal Rate of Return (EIrr) usually does not exist in a mathematical sense, and will often result in a NaN value when you attempt to compute it via Monte Carlo simulation in Analytica. It is easy to see why this is, since most uncertain investments have some non-zero probability of never turning a profit -- a case where the Irr does not exist, so that the mean Irr does not exist.

One way around these problems is to use a Median(Irr(...)) rather than a Mean(Irr(...)) criteria, which usually will exist (assuming a >50% probability that a profitable scenario at some discount rate exists). Using percentiles such as the median at least allows results to be obtained from a Monte Carlo Irr analysis. However...

Even when the mean or median Irr can be obtained, both are highly highly non-linear and tend to be very misleading. They both exhibit numerous counter-intuitive properties. For example, let's compare the following four investments using Expected- and Median-Irr:

A $100 investment today yields $100 in time-period 1.
A $100 investment today randomly yields either $90 or $110 at time period 1, each with 50%.
A $100 investment today randomly yields either $90 or $100 at time period 2, each with 50%.
A $100 investment today randomly yields either $90 or $100 at time period 1/2, each with 50%.

(Note: to compute time period 1/2, you would need to use the XIrr function)

All four investments clearly have the same "expected return" of 0%. However, the mean (and median) Irr for each is, respectively, 0%, 0%, -0.125% and +1%. (M. Whetten & W. Jin (Mar 2005), "CDO Equity, Correlation and IRR", NOMURA).

The bottom line is that the expected IRR (as well as other statistics of IRR) do not result in what we would intuitively consider to be the expected return (or other stat-return) of the investment. A more reliable measure for the expected return would be the discount rate at which the Expected Net Present Value (ENPV) is zero. This measure is obtained using:

Irr(Mean(values), T)

The fact that this expression computes the point at which the ENPV is zero is not directly obvious, but can be proved with a little algebra. While Mean(Irr(values, T)) is called the Expected IRR, and usually doesn't exist, this metric could be called the Irr of the expected cash flow, and is more naturally what we mean if we say "the expected return of an uncertain cash flow."

Downsides

Textbooks warn of many downsides to the user of Irr.

Because multiple solutions may exist when cash-flows change sign more than once, the measure has an inherent ambiguity.

When you apply Irr to an exceptionally attractive investment, you will probably not be able to re-invest money received from interm cash flows at the same rate of return. This creates a distortion, since it makes it seem like you would be able to achieve this same rate of return when those interm returns are re-invested. This can make equally attractive investments look substantially different due to the timing of their interm cash flows. Whenever a project's Irr is substantially greater than a company's cost-of-capital, you should be cautious that the Irr is giving an overly optimistic picture, which could distort the relative merits of competing alternatives and create unrealistic expectations for return among stakeholders. This distortion is particularly pernicious since it occurs precisely for the projects managers tend to view as most attractive, while not being felt evenly across all projects.

Examples

A $100K investment produces $10K per year for the next 15 years.

Index T := 0..15 Do Irr(If T = 0 then $-100K else $10K, T) → 5.56%

$1000 down yields $2 next year, $4 the next, doubling each year for 10 years.

Index Y := 0..10 do Irr(If Y = 0 then -1K else 2^Y, Y) → 8.35%

A 10-year treasury bill with $100 face value and 5.5% coupon rate makes 20 semi-annual payments of 2.75% starting 6 months from its date of issue, and ending on its maturity date 10 years later, on which date the final coupon payment plus face value is paid. If this treasury bill is offered at $102.32, what is its yield to maturity?

Index period := 0..20;

Var faceValue := $100;

Var price := $102.32;

Var couponRate := 5.5%;

Var couponPmt := couponRate/2*faceValue;

Var cashFlow := If period = 0 Then - price

If period = 20 Then couponPmt + faceValue

Else couponPmt;

(Irr(cashFlow, period) + 1)^2 - 1 → 5.267%

In this example, since each period is 6 months, Irr computes the yield per six-months. To express this as yield-per-year, we compound for two periods, i.e. (1 + Irr)² - 1.

Multiple solutions

A $1.5B nuclear power plant investment with a 29-year lifetime will generate $200M per year, and then cost $5B to decommission on the 30th year. The NPV-curve for this investment looks like this:

Let:

Variable Time := 0..30

Variable CashFlow := If Time = 0 Then -1.5B

Else if Time = 30 Then -5B

Else 200M

Index Discount_rate := Sequence(0, 20%, 0.1%)

Npv_plot := Npv(discount_rate, CashFlow, Time)*(1 + discount_rate)

Then:

Irr(CashFlow, Time) → 11.15%

Irr(CashFlow, Time, guess: 2%) → 1.58%

Irr

Contents