Quick Answer

IRR Function

IRR tells you the annual return rate of an investment given its cash flows (initial cost + periodic returns). If IRR exceeds your required return, the investment is worthwhile.

Syntax

IRR

(values, [guess])

Parameters

Parameter	Description	Required
values	An array or range of cash flows. Must include at least one negative value (investment/cost) and one positive value (return/income).	Required
guess	An estimated starting value for the iterative calculation. Default is 0.1 (10%). Usually unnecessary.	Optional

Basic Example

Calculate the annual return rate of a $100,000 investment that returns $25,000 each year for 5 years

=IRR({-100000, 25000, 25000, 25000, 25000, 25000})

Result7.94%

The initial investment of -$100,000 (negative = cost) and 5 years of $25,000 returns (positive = income) yield an IRR of ~7.94%.

Advanced Examples

Example 1: Real estate investment IRR

Property investment

Evaluate a property purchase with varying annual rental income and a final sale

=IRR(B2:B8)

Result: 12.3%

B2:B8 contains: -$500K (purchase), $30K, $32K, $35K, $38K, $40K (rental income), $650K (sale price + final rent). IRR is 12.3%.

Example 2: IRR with irregular cash flows

Venture capital

Calculate return for an investment with highly variable yearly returns

=IRR({-200000, 0, 50000, 80000, 150000, 300000})

Result: 15.6%

Year 1 has no return, years 2-5 have increasing returns. IRR still computes the annualized rate that makes NPV=0.

Example 3: Compare IRR vs required return

Investment decision

Use IF to compare IRR against a hurdle rate for investment decisions

=IF(IRR(B2:B7)>C1, "Invest", "Reject")

Result: Invest (if IRR > hurdle rate)

C1 contains the required minimum return (hurdle rate). If IRR exceeds it, the investment meets the threshold.

How IRR Works

IRR uses an iterative algorithm to find the discount rate that makes the Net Present Value (NPV) of all cash flows exactly zero. Think of it this way: if you discount every future cash flow back to today at the IRR rate, the total exactly equals your initial investment. The calculation starts with the guess value (default 10%) and iteratively adjusts until NPV ≈ 0. Cash flows must be equally spaced in time (annual, monthly, etc.) and include at least one negative (cost) and one positive (return) value.

1

List all cash flows

Arrange cash flows in chronological order: initial investment (negative), then each period's return (positive or negative).

2

Enter values in cells

Put the cash flows in a column: B1 = initial cost (-$100K), B2-B6 = yearly returns ($25K each).

3

Calculate IRR

Type =IRR(B1:B6) to get the annualized return rate.

4

Compare to hurdle rate

If IRR > your required return, the investment is worth making.

Important Notes & Limitations

IRR assumes equally-spaced time periods. For irregular timing, use XIRR instead.
IRR can produce multiple valid rates if cash flows change sign more than once (non-conventional cash flows).
IRR doesn't account for the scale of the investment — a 50% IRR on $1K is different from 50% on $1M.
The iterative calculation may fail to converge if the guess is far from the actual rate.

Common Errors & Fixes

#NUM!IRR cannot find a solution after 20 iterations, or values contain no negative/positive numbers

Fix: Provide a guess closer to the expected rate, or ensure values include at least one negative and one positive value.

Multiple IRRsCash flows change sign more than once (e.g., cost, profit, then cost again)

Fix: For non-conventional cash flows, use MIRR (Modified IRR) which gives a single, meaningful rate.

Misleading IRRIRR ignores investment scale and reinvestment rate assumptions

Fix: Use NPV alongside IRR for better decision-making. NPV accounts for scale and is more reliable.

Download Practice File

Practice IRR with Real Data

Download a sample CSV file with pre-populated data and practice exercises for the IRR function. Works in both Excel and Google Sheets.

Works in Google SheetsCompatible with ExcelIncludes exercises

File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps

Related Functions

ACCRINT

Calculates accrued interest for a security that pays periodic interest.

ACCRINTM

Calculates accrued interest for a security that pays interest at maturity.

AMORDEGRC

Calculates depreciation for a specific period using the French declining balance method.

AMORLINC

Calculates depreciation for a specific period using the French straight-line method.

COUPDAYBS

Calculates the number of days from the beginning of the coupon period to the settlement date.

COUPDAYS

Calculates the number of days in the coupon period that contains the settlement date.

Frequently Asked Questions

What's the difference between IRR and NPV?

NPV tells you the dollar value of an investment at a given discount rate. IRR tells you the discount rate that makes NPV=0. Use both: IRR for rate comparison, NPV for absolute value.

When should I use XIRR instead of IRR?

Use XIRR when cash flows happen at irregular dates (not evenly spaced). XIRR takes specific dates for each cash flow, while IRR assumes equal time periods.

What is MIRR and when should I use it?

MIRR (Modified IRR) fixes IRR's unrealistic reinvestment assumption. It assumes reinvestment at your finance rate and return at your reinvestment rate, giving a single, more realistic result.

Why is IRR returning #NUM!?

Most common causes: (1) no negative values in the range (no initial cost), (2) no positive values (no returns), (3) the algorithm can't converge. Try providing a guess value.

Is a higher IRR always better?

Not necessarily. A 100% IRR on a $100 investment earns less than a 20% IRR on a $1M investment. Always compare IRR alongside NPV for informed decisions.