The IRR function
=IRR(values, [guess]) takes a range of cash flows in time order, one per period, starting with today's (year 0). The investment is negative and the returns are positive. Excel then searches for the rate that makes the NPV zero. The optional guess is only needed when Excel cannot find an answer and returns #NUM!.
In the example, a $250,000 investment returns between $60,000 and $80,000 a year for five years. =IRR(B3:G3) returns 13.69%.
Worked example: one project, four checks
The dates in row 2 are used only by XIRR. IRR and NPV assume exactly one year between cash flows.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Year 0 | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | |
| 2 | Date | 15 Jan 2026 | 31 Dec 2026 | 31 Dec 2027 | 31 Dec 2028 | 31 Dec 2029 | 31 Dec 2030 |
| 3 | Cash flow ($) | -250,000 | 60,000 | 75,000 | 80,000 | 80,000 | 70,000 |
| 4 | |||||||
| 5 | Cost of capital | 10.0% | |||||
| 6 | Reinvestment rate (for MIRR) | 8.0% | |||||
| 7 | IRR | 13.69% | |||||
| 8 | NPV at cost of capital | 24,740 | |||||
| 9 | Check: NPV at the IRR | 0 | |||||
| 10 | MIRR | 11.24% | |||||
| 11 | XIRR (actual dates) | 13.89% |
- B7
=IRR(B3:G3)Year 0 is inside the range - B8
=NPV(B5,C3:G3)+B3Year 0 is added outside NPV - B9
=NPV(B7,C3:G3)+B3Must be 0 - B10
=MIRR(B3:G3,B5,B6)Finance rate, reinvestment rate - B11
=XIRR(B3:G3,B2:G2)Cash flows and their dates
Always check IRR with NPV
The IRR is only meaningful next to your cost of capital. If the project's IRR (13.69%) is above the 10% hurdle, the NPV is positive: here =NPV(B5,C3:G3)+B3 gives $24,740. A good habit is to put the IRR itself back into the NPV formula. The result must be zero (row 9). If it is not, the cash flow range is wrong.
Note how the NPV formula is built: NPV discounts its first value by a full period, so the year-0 investment is added outside the function. IRR, on the other hand, includes year 0 in its range.
XIRR: when cash flows are not exactly one year apart
IRR assumes equal periods. Real cash flows rarely are: here the investment is paid on 15 January 2026, but the returns arrive each 31 December. =XIRR(B3:G3,B2:G2) uses the actual dates and returns an annual rate of 13.89%. It is higher than the plain IRR because every return arrives about two weeks earlier than IRR assumes: the first one after 350 days, not a full year. For investment funds, property deals or any irregular cash flows, XIRR is the right function.
MIRR: a more conservative reading
IRR implicitly assumes that every cash flow you get back is reinvested at the IRR itself. With a high IRR that is optimistic. =MIRR(values, finance_rate, reinvest_rate) lets you choose the reinvestment rate. At 8% it gives 11.24%, a more realistic picture of what the investor ends up with. MIRR also always has a single answer, which helps when the cash flows change sign more than once.
Monthly and quarterly cash flows
IRR returns the rate per period of your data. If the cash flows are monthly, the result is a monthly rate, and multiplying it by 12 understates the true annual return because it ignores compounding. Convert it with =(1+IRR(range))^12-1. A monthly IRR of 1.0% is 12.68% a year, not 12%. For quarterly data use the power of 4. Alternatively, give every cash flow a date and use XIRR, which always returns an annual rate.
The same logic applies to the cost of capital you compare against. Compare an annual IRR with an annual hurdle rate, and a monthly IRR with a monthly one, never a mix of the two.
IRR or NPV: which one decides?
For a single yes-or-no decision, IRR and NPV agree: IRR above the cost of capital means a positive NPV. When you must choose between mutually exclusive projects of different size or timing, they can disagree. A small project can have a high IRR but add fewer dollars of value. In that case follow NPV, because it measures the value created in dollars.
Common mistakes
- Putting year 0 inside NPV.
=NPV(B5,B3:G3)discounts the investment by a year too. Here that gives $22,491 instead of $24,740. - Leaving out the initial investment or entering it as a positive number. IRR needs at least one negative and one positive value, or it returns
#NUM!. - Monthly cash flows read as an annual rate. IRR on monthly data gives a monthly rate. Annualise it with
=(1+IRR(range))^12-1, or use XIRR with dates. - Ranking projects by IRR alone. A higher IRR does not mean more value. Compare NPVs for mutually exclusive choices.
- Cash flows that change sign several times (for example a large clean-up cost at the end). Such streams can have more than one IRR. Use NPV or MIRR.
- Blank cells in the range. IRR ignores empty cells, so a missing year silently shifts every later cash flow one period earlier. Enter 0 instead.
Frequently asked questions
What is a good IRR?
One that is clearly above the cost of capital for the project's risk. There is no universal number: a safe project can be fine at a lower IRR, while a risky venture needs a much higher one.
Why does Excel return #NUM! for IRR?
Either the range has no sign change (all positive or all negative), or Excel could not converge on an answer. Check the signs first, then try a guess, for example =IRR(B3:G3,0.1).
What is the difference between IRR and XIRR?
IRR assumes cash flows exactly one period apart. XIRR takes a date for each cash flow and returns an annual rate, so it is correct for irregular timing.
Does IRR assume reinvestment at the IRR?
The calculation implies it, because it discounts every cash flow at the same rate. MIRR lets you set a more realistic reinvestment rate instead.