Internal Rate Of Return
Internal rate of return (IRR) is the discount rate that makes an investment's NPV zero. Formula, worked example, Excel IRR and XIRR, MIRR and pitfalls.
Contents
The internal rate of return (IRR) is the annual return an investment is expected to earn, measured as the discount rate at which the present value of its future cash flows exactly equals its upfront cost. Put another way, it is the rate that brings net present value (NPV) to zero. Businesses compare a project's IRR with their required return to decide whether it is worth funding.
IRR is one of the main tools of capital budgeting because it expresses a project's return as a single percentage that is easy to compare with a cost of capital or a loan rate. Private equity, venture capital and real estate investors use it to report deal and fund performance, and for a bond bought at its market price, the IRR is its yield to maturity.
IRR formula
0 = Σ [CFt ÷ (1 + IRR)t] − C0
- CFt is the net cash flow in period t.
- C0 is the initial investment at time zero.
- t is the period number.
- IRR is the rate that solves the equation.
Except in the simplest cases, the equation cannot be solved algebraically. IRR is found by trial and error, which is what financial calculators and spreadsheets do automatically.
How to calculate IRR
- List the cash flows by period, with the initial investment as a negative number.
- Choose a trial discount rate and calculate the NPV.
- If the NPV is positive, try a higher rate; if it is negative, try a lower one.
- Repeat until the NPV is close to zero, or interpolate between a rate with a positive NPV and one with a negative NPV.
IRR example
A manufacturer is considering a $100,000 machine expected to produce net cash flows of $30,000, $35,000, $40,000 and $25,000 over four years. At an 8% discount rate its NPV is $7,914, so the IRR must be higher than 8%.
At 11% the NPV is $1,150, and at 12% it is −$953, so the IRR lies between them. Linear interpolation gives 11% + [1,150 ÷ (1,150 + 953)] × 1% ≈ 11.55%, and solving exactly gives an IRR of 11.54%. Discounting the four cash flows at that rate gives $26,896 + $28,131 + $28,823 + $16,150 = $100,000, which equals the cost.
If the company's required return is 8%, the machine clears the hurdle by roughly 3.5 percentage points and should be accepted, which matches its positive NPV.
How to calculate IRR in Excel
Enter the cash flows in one column, starting with the initial investment as a negative number, then use =IRR(B1:B5). For the machine, with −100,000 in B1 and the four inflows in B2:B5, it returns 11.54%. Unlike the NPV function, IRR must include the year-zero outflow, because it needs at least one negative and one positive value. An optional second argument provides a starting guess, which helps when the function fails to converge.
IRR assumes equally spaced periods. When cash flows fall on irregular dates, use =XIRR(values, dates), which returns an annualized rate based on the actual dates. Google Sheets supports both functions.
IRR decision rule
- IRR above the hurdle rate: accept. The project earns more than the cost of the capital that funds it, typically the weighted average cost of capital (WACC) or a project-specific required rate of return.
- IRR equal to the hurdle rate: the project just covers its cost of capital, and NPV is zero.
- IRR below the hurdle rate: reject, because NPV is negative.
The rule applies to conventional projects, where an outflow occurs first and inflows follow. For financing-type cash flows, such as a loan where cash comes in first and goes out later, a lower IRR is better.
IRR vs. NPV
For a single conventional project, IRR and NPV give the same accept-or-reject answer. They can rank competing projects differently because IRR ignores scale. At a 10% hurdle rate, a one-year $10,000 project returning 40% has an NPV of $2,727, while a one-year $1 million project returning 15% has an NPV of $45,455. IRR also favors projects that return cash rapidly. When the two methods conflict, choose the project with the higher NPV, because NPV measures the dollars of value created.
Modified internal rate of return (MIRR)
IRR is often criticized because it implicitly treats interim cash flows as if they were reinvested at the IRR itself, which can overstate the return on high-IRR projects. MIRR instead compounds positive cash flows forward at a chosen reinvestment rate, typically the cost of capital, and discounts negative cash flows at the financing rate:
MIRR = (FV of positive cash flows at the reinvestment rate ÷ PV of negative cash flows at the finance rate)1/n − 1
For the machine, compounding the four inflows at 8% to the end of year 4 gives $37,791 + $40,824 + $43,200 + $25,000 = $146,815. MIRR = (146,815 ÷ 100,000)1/4 − 1 = 10.08%, below the 11.54% IRR. In Excel, use =MIRR(B1:B5, 8%, 8%), where the second and third arguments are the finance and reinvestment rates.
Limitations of IRR
- Multiple IRRs. When cash flows change sign more than once, there can be more than one IRR. A project costing $100,000 today, returning $230,000 in year 1 and requiring a $132,000 cleanup payment in year 2 has IRRs of both 10% and 20%, so comparing either one with the hurdle rate can mislead. Use NPV instead.
- No IRR at all. Some cash flow patterns never give an NPV of zero at any rate.
- Scale blindness. A percentage says nothing about how much value a project adds.
- Reinvestment assumption. Very high IRRs on short projects can overstate the return realistically available.
- One hurdle rate. IRR is compared with a single rate, so it cannot accommodate a cost of capital that changes over time.
Frequently asked questions
What is a good IRR?
A good IRR is one that exceeds the investment's hurdle rate by enough to cover forecasting risk. There is no universal figure: a 12% IRR may be attractive for a low-risk equipment upgrade but too low for a venture investment.
Can IRR be negative?
Yes. For a conventional project, a negative IRR means the investment returns less cash in total than it costs, so it loses money even before the time value of money is considered.
What is the difference between IRR and ROI?
Return on investment (ROI) compares total gain with cost and ignores when the cash arrives. IRR accounts for timing and gives an annualized rate, so two investments with the same ROI can have very different IRRs.
What is the difference between IRR and XIRR?
IRR assumes cash flows arrive at equal intervals, such as annually. XIRR uses the actual date of each cash flow and returns an annualized rate, which makes it the better choice for real investment records.
Is IRR the same as the discount rate?
No. The discount rate is the return you require, set before the analysis; IRR is the return the project's cash flows imply. A conventional project is attractive when its IRR exceeds the discount rate.