Quick Navigation

IRR Function

Excel 2007+

Summary

The Excel IRR function calculates the internal rate of return for a series of cash flows occurring at regular intervals. It determines the discount rate that makes the net present value of all cash flows equal to zero, helping investors evaluate project profitability.

Syntax

IRR(values, [guess])

Parameters

Parameter	Type	Required	Description
values	`Array/Reference`	Yes	Range or array containing cash flow values (must include at least one positive and one negative value)
guess	`Number`	No	Estimated rate close to actual IRR (defaults to 0.1 or 10%)

Using the IRR Function

IRR is essential for capital budgeting, helping determine whether an investment meets required return thresholds. Enter negative values for outflows (investments) and positive values for inflows (returns), maintaining proper chronological order.

Common IRR Examples

Basic Investment Analysis

=IRR(A2:A7)

Calculates IRR for a 5-year investment with initial outflow of $70,000 and subsequent positive cash flows. Returns 8.7%.

Short-term Project with Guess

=IRR(A2:A4,-0.1)

For two-year project requiring guess parameter due to negative IRR. Returns -44.4%.

Array Formula Usage

=IRR({-10000,3000,4000,5000,6000})

Direct array input for quick IRR calculation without cell references.

Frequently Asked Questions

Occurs when Excel can't converge after 20 iterations. Try different guess values (0.1, 0.5, -0.1) or verify cash flows have both positive and negative values.

Yes, assumes regular intervals (monthly, quarterly, annually). Use XIRR for irregular dates.

Excel defaults to 10%. Provide when expecting unusual rates (negative IRR, very high returns) to help convergence.

Common Errors and Solutions

#NUM!

Cause: No solution found after 20 iterations

Solution: Adjust guess parameter, ensure mixed positive/negative values, verify chronological order

#VALUE!

Cause: Values contains non-numeric data

Solution: Clean data range, use numeric values only

Unexpected result

Cause: Incorrect cash flow order

Solution: Arrange outflows first (negative), then inflows chronologically

Notes

Cash flows must include ≥1 positive and ≥1 negative value
Order matters: initial investment first, then periodic returns
Ignores text, logical values, empty cells in range
Related to NPV: NPV(IRR(values),values) ≈ 0
Maximum 20 iterations for convergence

Compatibility

Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365

Not available in:

Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+