IRR function

Returns the internal rate of return for a series of regular periodic cash flows, measuring annualized investment profitability.

=IRR(values, [guess])

Generate a IRR formula

Describe what you need. The generator will reach for IRR where IRR is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How IRR reads its arguments
valuesrequiredguessoptionalIRR →
ArgumentRequiredDescription
valuesRequiredRequired. A range or array containing cash flows in chronological order with at least one negative value (investment/cost) and one positive value (return/revenue).
guessOptionalOptional. A starting estimate for the IRR calculation (e.g., 0.1 for 10%). Helps convergence when a solution is hard to find; Excel defaults to 0.1 if omitted.

Returns

A decimal number representing the annualized discount rate (typically formatted as a percentage) that makes the net present value of cash flows equal to zero.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Calculate return rate for a churn scenario

CustomerPlanMRRSignup DateChurn Date
TechStartPro$300Jan 2024Jun 2024
DataCorpEnterprise$2,000Jan 2024Active
SmallBizStarter$100Mar 2024Aug 2024
=IRR({-500, 300, 300, 300, 300, 300})

Result: 0.2009 (or 20.09%)

TechStart invested $500 in acquisition costs and earned $300/month MRR for 6 months before churning. The IRR of 20% represents the effective annualized return on that acquisition investment, accounting for the timing of cash recovery. This IRR shows the business model is profitable—you recoup acquisition costs quickly.

2. Compare IRR across customer lifetime differences

CustomerPlanMRRSignup DateChurn Date
TechStartPro$300Jan 2024Jun 2024
DataCorpEnterprise$2,000Jan 2024Active
SmallBizStarter$100Mar 2024Aug 2024
=IRR({-500, 300, 300, 300, 300, 300, 300, 300, 300, 300})

Result: 0.3558 (or 35.58%)

If the same $500 acquisition cost retained the customer for 9 months instead of 6, the IRR jumps to 36%. Longer customer lifetime dramatically improves acquisition ROI because you collect more monthly revenue relative to the fixed acquisition spend. This illustrates why retention improvements directly increase profitability.

3. Use guess parameter to assist convergence

CustomerPlanMRRSignup DateChurn Date
TechStartPro$300Jan 2024Jun 2024
DataCorpEnterprise$2,000Jan 2024Active
SmallBizStarter$100Mar 2024Aug 2024
=IRR({-500, 300, 300, 300, 300, 300}, 0.15)

Result: 0.2009 (or 20.09%)

The guess parameter (0.15, or 15%) provides a starting point for Excel's iterative solver, helping it find the true IRR faster. The result matches Example 1 because the answer is the same; the guess simply accelerates convergence. Use this when IRR returns #NUM! or recalculation is slow.

Common errors

Which IRR error are you seeing?
IRR returned an error#NUM!
Ensure your range includes both negative cash flows (costs/investments) and positive ones (revenue/returns). If convergence fails despite mixed signs, try =IRR(range, guess) with a different starting estimate like 0.3 or 0.05.
#VALUE!
Remove headers and text from the cash flow range. If C2:C8 includes a header row, use C3:C8 instead. Verify all cells contain only numeric values.
#DIV/0!
Provide an explicit guess parameter to avoid problematic calculation zones: =IRR(values, 0.05) or =IRR(values, 0.5). If the error persists, use XIRR for irregular cash flow timing.
ErrorWhy it happensHow to fix it
#NUM!All cash flows have the same sign (all positive or all negative), or the iterative solver cannot converge to a solution within 20 attempts.Ensure your range includes both negative cash flows (costs/investments) and positive ones (revenue/returns). If convergence fails despite mixed signs, try =IRR(range, guess) with a different starting estimate like 0.3 or 0.05.
#VALUE!The values array contains non-numeric data, text labels, or cell references that resolve to text rather than numbers.Remove headers and text from the cash flow range. If C2:C8 includes a header row, use C3:C8 instead. Verify all cells contain only numeric values.
#DIV/0!In some spreadsheet implementations, IRR fails during iteration due to division by zero when certain internal calculations occur near boundary rates.Provide an explicit guess parameter to avoid problematic calculation zones: =IRR(values, 0.05) or =IRR(values, 0.5). If the error persists, use XIRR for irregular cash flow timing.

Tips and when to use something else

  • IRR assumes equally-spaced time periods (months, quarters, years). For cash flows on irregular dates, use XIRR instead—it accounts for actual day counts.
  • IRR requires both positive and negative values. A range of all income or all expenses will return #NUM!. Always structure: initial investment (negative) + periodic returns (positive).
  • High IRR doesn't always mean high profit—it means fast capital recovery relative to investment. Cross-check with NPV and XNPV to validate business impact.
  • If you need the rate at which NPV equals a specific target (not zero), use RATE with a target loan amount, or combine NPV with Goal Seek instead.

Related functions

XIRRXIRR returns the annualized internal rate of return for cash flows at irregular date intervals, letting you evaluate project or investment profitability over time.NPVNPV returns the net present value of a series of cash flows discounted to their present value today, helping you decide if an investment creates value.RATERATE calculates the interest rate per period for an investment or loan given regular periodic payments and present/future values.PVReturns the present value of an investment based on a series of regular payments and a constant interest rate.FVFV returns the future value of a series of regular cash flows at a constant interest rate, modeling compound growth over time.

Frequently asked questions

What's the difference between IRR and XIRR?
IRR assumes cash flows occur at regular intervals; XIRR accepts actual dates and handles irregular spacing. Use XIRR if billing dates vary (e.g., customers sign up mid-month). For recurring monthly billing on fixed dates, IRR is simpler.
How do I use IRR to decide which customers to keep or drop?
Calculate IRR for each customer's cash flows (acquisition cost + monthly revenue). Customers with IRR above your company's cost of capital are value-creating; those below it destroy shareholder value. Use this to prioritize retention spend and churn reduction efforts.
Can IRR be negative?
Yes. Negative IRR means you lose money on the investment. For example, a $500 acquisition generating only $200 total revenue over 6 months has negative IRR. This signals the business model or customer segment is unprofitable.
Why should I use IRR instead of just calculating total profit?
IRR accounts for timing—it shows the annualized return on capital and lets you compare investments fairly. A customer returning 20% IRR over 12 months outperforms one returning 20% IRR over 24 months because capital cycles faster. Use IRR for apples-to-apples comparison across different customer segments and plans.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17