Financial
IRR Function in Excel
Returns the internal rate of return for a series of cash flows.
Syntax
- =IRR(values, [guess])
Arguments
- values (required): Array or range containing cash flows (must include at least one positive and one negative)
- guess (optional): Initial guess for the rate (default 0.1 or 10%)
Examples
- =IRR({-100000, 30000, 35000, 40000, 45000}) - IRR of investment - Result: 17.1%
- =IRR(A1:A5) - IRR from cash flow range - Result: Internal rate
Excel IRR function documentation
- IRR calculates internal rate of return for a series of cash flows — guess optional.
- Syntax: =IRR(values, [guess]). Values must include at least one negative and one positive cash flow.
- Use in capital budgeting, project ROI, and investment comparison models.
- Related: [NPV](/functions/npv/), [XIRR](/functions/xirr/), [MIRR](/functions/mirr/), [RATE](/functions/rate/).
IRR syntax and cash flow layout
- values: range of cash flows in chronological order — initial investment usually negative first cell.
- guess (optional): starting estimate; default 0.1 (10%). Change if #NUM! appears.
- IRR assumes equal periods between flows — use XIRR for irregular dates.
- Blank cells treated as zero; text causes #VALUE!.
Step-by-step: project IRR from yearly cash flows
- Step 1 — Year 0 investment -100000 in B2, years 1–5 returns in B3:B7.
- Step 2 — =IRR(B2:B7) returns periodic rate.
- Step 3 — Compare to hurdle rate in cell E1: =IF(IRR(B2:B7)>E1,"Accept","Reject").
- Step 4 — Cross-check with [NPV](/functions/npv/) at discount rate.
- Step 5 — Irregular dates → [XIRR](/functions/xirr/) with date column.
IRR vs XIRR vs MIRR
- IRR — equal spacing between periods.
- XIRR — actual dates for each flow.
- MIRR — reinvestment rate assumptions; more conservative in some models.
- Category: [Financial functions](/categories/financial/).
Worked examples to copy
- Basic IRR: =IRR(-100000,20000,25000,30000,40000,50000).
- With guess: =IRR(B2:B10,0.15).
- Annual to monthly mindset — IRR is per period in range, not auto-annualized.
- NPV sanity: =NPV(0.1,flows)+initial at 10% discount.
People also ask
- IRR #NUM!? — No sign change in cash flows or guess fails — try different guess.
- IRR vs ROI? — IRR is rate that zeros NPV; ROI is simpler return ratio.
- Multiple IRR problem? — Non-conventional flows may have multiple solutions — use MIRR.
- IRR annual or monthly? — Matches period spacing of your values range.
Common errors
- #NUM! if no solution found
- #VALUE! if values don't contain positive and negative
Use cases
- Investment analysis
- Project evaluation
- Capital budgeting
- ROI calculation
Frequently asked questions
- What is a good IRR? A good IRR depends on your cost of capital. Generally, if IRR exceeds your required rate of return (hurdle rate), the investment is considered worthwhile. For most businesses, an IRR above 15-20% is considered good.
- How is IRR different from ROI? ROI is a simple percentage return, while IRR accounts for the time value of money. IRR considers when cash flows occur, making it more accurate for comparing investments with different timing patterns.
Editorial review
- Reviewed by Excel.Directory Editorial Team. Updated May 2026.
When to use IRR
- Investment analysis — common Financial scenario for IRR.
- Project evaluation — common Financial scenario for IRR.
- Capital budgeting — common Financial scenario for IRR.
- ROI calculation — common Financial scenario for IRR.
IRR in the Financial category
- Browse all Financial functions at /categories/financial/ for related formulas.
- IRR syntax: =IRR(values, [guess])
- values (required): Array or range containing cash flows (must include at least one positive and one negative)
- guess (optional): Initial guess for the rate (default 0.1 or 10%)
- Confirm IRR arguments match the syntax shown above before filling down.
- Lock table and range references with $ when copying formulas across rows or sheets.
Formula checklist before you copy down
- Confirm IRR arguments match the syntax shown above before filling down.
- Lock table and range references with $ when copying formulas across rows or sheets.
- If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
- Spot-check three known input rows manually against expected output.
Related Excel functions
- NPV (/functions/npv/): Calculates the net present value of an investment based on a discount rate and future cash flows.
- XIRR (/functions/xirr/): Returns the internal rate of return for a schedule of cash flows with specific dates.
- MIRR (/functions/mirr/): Returns the modified internal rate of return for a series of periodic cash flows.
Errors to watch for
- #NUM! if no solution found — review causes on linked error pages in the directory.
- #VALUE! if values don't contain positive and negative — review causes on linked error pages in the directory.
Copy-paste audit workflow
- Enter IRR on three test rows with known expected output documented on a QA tab.
- Fill down only after absolute references are locked on lookup tables and rate tables.
- Compare against manual calculation or a calculator for financial and statistical functions.
- Search this directory for comparison guides when choosing between similar functions in the same category.
IRR worked examples to copy
- =IRR({-100000, 30000, 35000, 40000, 45000}) — IRR of investment. Expected result: 17.1%.
- =IRR(A1:A5) — IRR from cash flow range. Expected result: Internal rate.
IRR reference summary for crawlers and offline review
- IRR belongs to the Financial category in Excel. Returns the internal rate of return for a series of cash flows.
- Full syntax: =IRR(values, [guess]). Open /functions/irr/ for parameters, FAQs, and related pages.
- Common mistakes: #NUM! if no solution found; #VALUE! if values don't contain positive and negative
- Pair this function with comparison guides when another Excel formula might fit the same task better.
- Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.