Internal Rate of Return (IRR)

How to Calculate IRR (Step-by-Step)

The internal rate of return (IRR) metric estimates the annualized rate of return that an investment is going to yield.

Unlike the multiple of money (MoM), another metric tracked by investors to measure their returns, the IRR is considered to be “time-weighted” because it accounts for the specific dates that the cash proceeds are received.

The higher the internal rate of return (IRR), the more profitable a potential investment will likely be if undertaken, all else being equal.

The manual calculation of the IRR metric involves the following steps:

• Step 1 → The future value (FV) is divided by the present value (PV)
• Step 2 → The amount is raised to the inverse power of the number of periods (i.e., 1 ÷ n)
• Step 3 → From the resulting figure, one is subtracted

IRR Formula

The formula for calculating the internal rate of return (IRR) is as follows:

Conceptually, the IRR can also be thought of as the rate of return wherein the NPV of the project or investment equals zero.

The alternative formulas, most often taught in academia, involve backing out the IRR for the equation to hold true (and require using a financial calculator).

• 0 = CF t = 0 + [CF t = 1 ÷ (1 + IRR)] + [CF t = 2 ÷ (1 + IRR)^2] + … + [CF t = n ÷ (1 + IRR)^ n]

Or, an alternative method to calculate the IRR is the following:

• 0 = NPV Σ CFn ÷ (1 + IRR)^ n

How to Calculate IRR in Excel: XIRR vs. IRR Function

The XIRR Excel function is preferable over the IRR function as it has more flexibility by not being restricted to annual periods. The drawback to the IRR function is that Excel assumes each cell is separated by precisely twelve months, which is not always the case.

Under XIRR, daily compounding is assumed, and the effective annual rate is returned. But for the IRR function, the interest rate is returned assuming a stream of equally spaced cash flows.

The XIRR function can handle complex scenarios that necessitate taking into account the timing of each cash inflow and outflow (i.e., the volatility of multiple cash flows).

Limitations of Internal Rate of Return (IRR)

The internal rate of return (IRR) cannot be singularly used to make an investment decision, as in the case of most financial metrics.

• Cash Flow Timing: The internal rate of return (IRR) metric is imperfect and cannot be used as a standalone measure due to being highly sensitive to the timing of the cash flows. Thus, the IRR can potentially be misleading in its portrayal of returns under certain circumstances wherein a greater proportion of the cash flows are received earlier.
• Shorter Holding Periods: The implied IRR from an investment could be impressively high, yet stem from the shorter holding period, which caused the returns to be artificially inflated and unsustainable if the holding period were to be hypothetically extended longer.
• Dividend Recap: If a private equity firm were to issue itself a dividend soon after a leveraged buyout (LBO), i.e. a dividend recapitalization (or recap), the payment would increase the IRR to the fund regardless of whether the multiple-of-money (MoM) meets the required returns hurdles – which can cause the IRR to be potentially misleading here.

LBO Internal Rate of Return Calculation Example

Let’s say a leveraged buyout (LBO) is anticipated to yield a 30% internal rate of return (IRR) if sold on the present date, which at first glance sounds great.

But from a more in-depth look, if the same investment achieves only 1.5x MoM, this makes the return far less impressive, and the high 30% IRR is attributed more towards a quicker return of capital rather than substantial growth in the size of the investment.

Nonetheless, the IRR tends to be the most benchmarked marketing metric for the performance of investment funds and influential with regard to the ability for firms to meet (or surpass) their capital raising efforts for their next fund from existing and new limited partners (LPs).

Because of that factor, most firms pay a significant amount of attention to their fund’s IRR.