IRR (internal rate of return) is the discount rate at which a project’s net present value (NPV) equals zero: all future cash flows, converted to today’s money at that rate, exactly pay back the investment.
A project is worth doing if its IRR is above the required return — the cost of money for you or your investor.
For example, a €150,000 investment returning €30,000–60,000 a year for five years has an IRR of 15.8%.
Below: what IRR tells you in plain terms, the formula and a worked example, how to calculate IRR in Excel and Google Sheets, what to compare it with, where IRR misleads, and how to use it for a project in Spain and for a startup investor. There is an IRR, NPV and payback calculator on the page.
What IRR means in plain terms
IRR answers the question: what annual rate is the money invested in the project really earning?
If you put the same €150,000 into an account paying 15.8% a year and withdrew exactly what the project returns, after five years the account would be at zero.
Hence the rule: IRR is compared with the required return — the discount rate.
That is the cost of capital: the interest on a loan, the return you could earn elsewhere at the same risk, or the minimum return an investor will accept.
- IRR above the rate — the project earns more than the money costs; NPV is positive.
- IRR equal to the rate — the project only covers the cost of money.
- IRR below the rate — the alternative is the better use of the money.
Note: IRR is not the same as a simple return. “We invested €100,000 and made €40,000” is a 40% gain over the whole period, but IRR depends on when the money arrived: over one year that is 40% a year, over four years about 8.8%.
The IRR formula
IRR is the rate r that satisfies:
NPV = −I₀ + CF₁ / (1 + r)¹ + CF₂ / (1 + r)² + … + CFₙ / (1 + r)ⁿ = 0
- I₀ — the investment now (year 0);
- CF₁…CFₙ — each year’s net cash flow: receipts minus payments, after tax;
- n — the number of years;
- r — the rate we are looking for, the IRR.
For more than two years the equation has no closed-form solution: IRR is found numerically — in Excel, with a calculator or by manual interpolation.
The exception is a single investment and a single payout at the end: then IRR = (payout / investment)^(1/n) − 1.
How to calculate IRR: a worked example
A hypothetical example: a company invests €150,000 in a new line of business and expects net cash flows of €30,000, €40,000, €50,000, €60,000 and €60,000 over the first five years. The required return is 12%.
| Rate | Project NPV | What it means |
|---|---|---|
| 12% | €16,439 | The project earns more than the required return |
| 15% | €3,344 | Still positive |
| 16% | −€674 | Now negative — IRR lies between 15% and 16% |
| 17% | −€4,534 | More negative |
Manual interpolation between 15% and 16%: IRR ≈ 15% + 3,344 / (3,344 + 674) × 1% ≈ 15.83%. An exact search gives the same: 15.8%. IRR is above 12%, so the project passes.
IRR, NPV and payback calculator
Enter your investment, rate and yearly cash flows — the calculator works out IRR, NPV, the profitability index and the simple and discounted payback period, and shows the cumulative cash flow by year.
IRR in Excel and Google Sheets
| What you calculate | Excel (English) | Excel (Spanish) | Excel (Russian) | Google Sheets |
|---|---|---|---|---|
| IRR, equal periods | IRR | TIR | ВСД | IRR |
| IRR by payment dates | XIRR | TIR.NO.PER | ЧИСТВНДОХ | XIRR |
| Modified IRR | MIRR | TIRM | МВСД | MIRR |
| NPV | NPV | VNA | ЧПС | NPV |
For the example above: cells A1:A6 hold −150,000, 30,000, 40,000, 50,000, 60,000, 60,000, and =IRR(A1:A6) returns 15.8%. Points to know:
- The range must contain at least one negative and one positive value, otherwise you get #NUM!. The same error appears if the search does not converge within 20 iterations: add the second argument, a guess, such as 0.2.
- IRR assumes equal intervals between flows. For irregular payments use XIRR with dates.
- Excel’s NPV function discounts even the first value, as if it arrived a year from now. So the year-0 investment is added separately:
=NPV(12%, A2:A6) + A1.
What is a good IRR?
There is no universal benchmark: an IRR is good if it beats your discount rate with a margin for risk.
The riskier the project, the higher the required return — and the higher the IRR at which the project is worth doing.
So 15% can be excellent for expanding an established business with proven demand and weak for a pre-revenue startup.
The rate for a given project is set by whoever’s money it is: the owner by their alternative, a bank by its interest and risk, an investor by the expected return of their portfolio.
How the rate and risks go into a model is covered in our series “Financial model of a project”.
IRR, NPV, payback and multiple compared
| Metric | What it shows | Where it misleads | When to use it |
|---|---|---|---|
| IRR | The project’s annual return in percent | Ignores scale; can have several values if flows change sign; assumes reinvestment at the same rate | Compare with the cost of capital; explain the return to an investor |
| NPV | How much value, in today’s euros, the project creates above the required return | Depends on the chosen rate | Choose between projects; decide go / no-go |
| Payback period | How many years until the investment comes back | Ignores flows after payback; the simple version ignores the cost of money | Gauge risk and the strain on cash flow |
| ROI | Profit relative to the investment over the whole period | Ignores timing | Quick estimates, short projects |
| Multiple (MOIC) | How many times the money grew | Ignores timing: 3× over 3 years and over 10 years look the same | Venture and private investment — alongside IRR |
Where IRR misleads
It ignores scale
Project A: invest €10,000, get €13,000 a year later — IRR 30%. Project B: invest €100,000, get €120,000 — IRR 20%.
By IRR, A wins, but at a 12% rate project A creates €1,607 of NPV and project B €7,143. If you can only do one, NPV decides.
Several IRRs
If the cash flows change sign more than once — for example, large closing costs at the end of a project — the equation can have several solutions.
The flows −100, +230, −132 have two IRRs: 10% and 20%. There is no way to say which is “right”; NPV, meanwhile, is unambiguous.
Reinvestment at the same rate
IRR implicitly assumes that interim receipts are reinvested at the same return. For a project with a 40% IRR that is usually too optimistic.
Modified IRR (MIRR) fixes this with two rates: the rate at which money is raised and the rate at which receipts are actually reinvested.
Project IRR is not the investor’s return
IRR is calculated on the whole project’s cash flows.
The owner’s or investor’s return is different: debt and interest, taxes, fees and the manager’s share all change it.
If a project is partly funded with a loan, calculate the IRR on equity separately.
IRR for a project in Spain
For a Spanish company, IRR is calculated on cash flows after tax:
- Corporate tax is deducted from the flows at the 2026 rates: 25% general, 23% with turnover under €10m, 19% on the first €50,000 and 21% above with turnover under €1m, and 15% for a new company in its first profitable year and the next. Depreciation reduces tax even though it is not paid in cash.
- VAT is left out of the flows: it is customers’ money that the company passes on to the tax agency. But the timing gap between collecting VAT and paying it affects working capital and can cause a cash gap.
- If the project is funded with a loan, interest and repayments change the owner’s cash flow — a separate IRR is calculated for them.
How to build taxes and their payment dates into a model is covered in the section “Taxes in the financial model of a Spanish company” of “Financial model calculations”.
IRR for a startup investor
A venture investor usually gets money back once — when selling the stake. Then IRR follows from the multiple and the time: IRR = multiple^(1/years) − 1.
| Multiple | Time to exit | IRR |
|---|---|---|
| 2× | 5 years | 14.9% |
| 3× | 5 years | 24.6% |
| 3× | 7 years | 17.0% |
| 5× | 5 years | 38.0% |
So for an investor both the entry valuation and the time to exit matter: the same amount seven years out gives a noticeably lower IRR.
How the entry valuation is set and what stake the investor gets is covered in “How to value a startup”; its VC method relies on exactly this expected return.
Frequently asked questions
What is IRR in simple terms?
It is a project’s annual return that takes into account when the money arrives: the rate at which all future receipts, converted to today’s money, exactly pay back the investment.
What is a good IRR?
One that is above your discount rate — the cost of money adjusted for risk. The riskier the project, the higher the IRR should be.
Can IRR be negative?
Yes: if total receipts are less than the investment, IRR is negative — the project loses money even before the cost of money is counted.
What is the difference between IRR and NPV?
IRR shows the return as a percentage; NPV shows how many euros the project creates above the required return. When choosing one of several projects, NPV is more reliable, because IRR ignores scale.
What is the difference between IRR and ROI?
ROI is profit relative to the investment over the whole period, ignoring time. IRR accounts for when the money arrives: the same ROI over one year and over five years gives very different IRRs.
How do you calculate IRR in Excel?
With the IRR function (TIR in Spanish Excel) on the cash flows, with the year-0 investment as a negative number. For irregular payment dates use XIRR with dates.
Why does IRR return #NUM!?
The flows do not include at least one negative and one positive value, or the search did not converge within 20 iterations. Check the signs and add a guess as the second argument, such as 0.2.
What is MIRR?
Modified IRR: instead of assuming receipts are reinvested at the IRR itself, you set two realistic rates — financing and reinvestment. The Excel function is MIRR (TIRM in Spanish).
Key points on IRR
- IRR is the rate at which a project’s NPV is zero — the annual return, taking timing into account.
- A project passes if its IRR is above the discount rate — the cost of money adjusted for risk.
- IRR ignores scale and can have several values — when choosing between projects, NPV decides.
- In Excel use IRR (TIR); for irregular payments, XIRR; enter the year-0 investment as a negative number.
- For a Spanish company, cash flows are taken after corporate tax and without VAT.
- For a startup investor IRR = multiple^(1/years) − 1: time to exit matters as much as the multiple.
Sources
- Microsoft — IRR function; XIRR; MIRR; NPV; TIR (Spanish).
- Google Sheets — IRR, XIRR, MIRR.
- Spanish Corporate Income Tax Act (Ley 27/2014) — art. 29, transitional provision 44 (2026 rates).
