IRR Calculation: Formula, Excel and Worked Example

What IRR measures

IRR (internal rate of return) is the annual rate at which an investment pays back the money put into it, given when each euro goes out and comes in. An IRR calculation answers a simple question: "what rate did my money earn?" An investment that returns €1,800 on €1,000 invested has an IRR of 12.5% if the money comes back after five years, and 21.6% if it comes back after three.

Private equity funds judge each deal on two numbers: IRR and MOIC (multiple on invested capital, the multiple between what they get back and what they put in). The two complement each other, and we will see below why either one alone misleads.

The IRR formula

IRR is the discount rate that brings the net present value (NPV) of the cash flows to zero. In other words, it is the rate r that solves the equation:

F₀ + F₁/(1+r) + F₂/(1+r)² + … + Fₙ/(1+r)ⁿ = 0

where F₀ is the year 0 cash flow (the amount invested, negative) and F₁ to Fₙ are the flows of the following years (dividends, repayments and the sale price, positive; further contributions, negative).

This equation has no direct solution once there are more than two cash flows. It is solved by successive approximation: try a rate, check whether the NPV is positive or negative, and adjust. Excel does this in one function, and that is the method to use. Only one case can be done by hand: an opening flow and a closing flow, with nothing in between. IRR is then (MOIC)^(1/n) − 1, where n is the number of years.

IRR calculation in Excel

Three functions cover almost every need. In the English version of Excel:

Function	Use	French Excel
=IRR(range)	Cash flows at regular intervals (one cell per year, in order)	TRI
=XIRR(values, dates)	Cash flows on actual, irregular dates	TRI.PAIEMENTS
=MIRR(range, finance_rate, reinvest_rate)	Modified IRR, with an explicit reinvestment rate	TRIM

A few rules for IRR to return the right result:

1. One cell per period, no gaps. A year with no cash flow is entered as 0, not left blank: Excel ignores empty cells and shifts every later year.
2. Signs matter. Outflows negative, inflows positive. A range that is entirely positive returns #NUM!.
3. The first cash flow is period 0. IRR assumes the first flow happens today and the second one year later.
4. Dated cash flows go through XIRR. A fund that calls capital in March and distributes in November does not have annual flows. XIRR calculates the rate on the exact number of days, and funds use it for their official IRR.

One check is worth adding: the NPV of the cash flows at the calculated IRR should be zero. Beware the NPV trap: this function discounts the first cell of a range as if it fell in one year. The correct formula is therefore =F0 + NPV(IRR, F1:Fn), with the year 0 flow added outside. If you pass the whole range into NPV, the result is off by a factor of (1 + IRR).

IRR and MOIC: time makes the difference

MOIC tells you how much you earn; IRR tells you how fast. The table below converts one into the other for an investment with no interim cash flows:

MOIC	3-year IRR	5-year IRR	7-year IRR
1.5x	14.5%	8.4%	6.0%
2.0x	26.0%	14.9%	10.4%
2.5x	35.7%	20.1%	14.0%
3.0x	44.2%	24.6%	17.0%

Two more years on the same 2.0x multiple take the IRR from 14.9% to 10.4%. That is why a fund often prefers a faster exit at a slightly lower price, and why a dividend recapitalisation (a dividend funded by new debt during the holding period) improves IRR without changing MOIC.

For a quick estimate in your head, the Rule of 72 is enough: money doubles in n years at a rate of about 72 ÷ n. Doubling in five years takes close to 14.4% a year (exactly 14.9%); in three years, 24% (26.0%). These benchmarks also help in a paper LBO, where you convert a multiple into an IRR without a calculator.

Worked example: same multiple, two IRRs

A fund invests €1,000k in an SME. Two scenarios return exactly the same sum, €1,800k, a MOIC of 1.8x.

Year	Scenario A (dividends, then sale)	Scenario B (everything at exit)
0	− 1,000	− 1,000
1	+ 80	0
2	+ 80	0
3	+ 80	0
4	+ 80	0
5	+ 1,480	+ 1,800
Total received	1,800	1,800
MOIC	1.8x	1.8x
IRR	14.0%	12.5%

In €k. In scenario A, the fund receives €80k of dividends a year, then €1,400k on sale; in B, nothing before the sale at €1,800k.

Scenario A earns 1.5 points more IRR for the same gain, because part of the money comes back sooner. At a hurdle rate of 10%, the NPV of scenario A is €172.6k and that of B is €117.7k: both create value, A more so. At a 15% hurdle rate, the NPV of A turns negative (− €35.8k): a fund targeting 15% turns this deal down despite a MOIC of 1.8x.

The same mechanism applies with a further contribution. A fund that puts in €2.0m in year 0, another €1.0m in year 2 and sells for €9.0m in year 5 gets a MOIC of 3.0x and an IRR of 28.1%. If it manages to pay itself €2.0m in year 4 and sells for €7.0m in year 5, MOIC stays at 3.0x and the IRR rises to 29.9%.

What IRR does not tell you

Size. An IRR of 30% on €100k earns less than an IRR of 18% on €5m. Between two projects of different size, NPV decides, not IRR.

The reinvestment assumption. IRR assumes every interim cash flow is reinvested at the same rate as the IRR. An investment that pays €150k a year for five years on €500k invested shows an IRR of 15.2%; if those €150k only find a home at 8%, the modified IRR (MIRR, with 8% as both finance and reinvestment rate) falls to 12.0%. The more weight the interim flows carry, the wider the gap.

Cash flows that change sign several times. An investment, then a receipt, then a new expense (a remediation, a warranty called) can give two mathematically exact IRRs. The flows − 100, + 230, − 132 have one IRR of 10% and another of 20%. Excel returns the one closest to its starting guess, without warning. In that case, the NPV at a given rate is the only reliable reading.

Fees. A fund's gross IRR, calculated at the level of its holdings, ignores management fees and the team's profit share (carried interest). Net IRR, the figure investors actually receive, is several points lower. Always ask which one you are being shown.

The rate to compare an IRR with depends on who is investing. For a fund, it is its target return, often between 15% and 25%. For a company choosing between projects, it is its cost of capital, whose order of magnitude comes from a WACC calculation.

Common mistakes

1. Dividing the gain by the duration. An 80% gain over five years is not 16% a year but 12.5%, because returns compound.
2. Forgetting the year 0 flow in the NPV check. See above: NPV starts discounting from the first cell.
3. Mixing annual and dated cash flows. IRR on flows spaced six months apart overstates the duration, and so understates the rate. Switch to XIRR.
4. Comparing a gross IRR with a net IRR. The two do not measure the same thing.
5. Reading an IRR without its MOIC. An IRR close to 50% obtained by reselling after ten months corresponds to a gain of only 40%. Always show both.