How It Works

IRR finds the discount rate that makes the Net Present Value of all cash flows equal to zero. In plain terms: it’s the annualized return your investment is actually generating, accounting for the timing of all money in and out.

Syntax

=IRR(values, [guess])

values: Range of cash flows (negative for money out, positive for money in)
guess: Optional starting estimate (default 0.1 or 10%)

Critical: Cash flows must be in chronological order and at regular intervals.

Example

Rental Property Investment:

Year	Cash Flow	Description
0	-$50,000	Down payment
1	$4,800	Net rental income
2	$5,000	Net rental income
3	$5,200	Net rental income
4	$5,400	Net rental income
5	$85,600	Net income + sale

Formula: =IRR(B2:B7)

Result: 15.2% annual return

Understanding the Cash Flow Signs

Money OUT = Negative

Initial investment
Additional contributions
Costs and expenses

Money IN = Positive

Dividends/distributions
Rental income
Final sale/withdrawal

Real Estate IRR Calculator

Year	Cash Flow	Notes
0	-50000	=-(down_payment + closing_costs)
1	4800	=rent - mortgage - expenses
2	5000
3	5200
4	5400
5	85600	=annual_income + sale_price - loan_payoff
IRR	=IRR(B2:B7)	15.2%

Investment Comparison

Use IRR to compare different opportunities:

Investment	Initial	Y1	Y2	Y3	Y4	Y5	IRR
Property A	-50000	4000	4200	4400	4600	80000	13.8%
Property B	-80000	8000	8200	8400	8600	120000	12.4%
Stock Fund	-50000	0	0	0	0	90000	12.5%

Formula: =IRR(B2:G2) for each row

Monthly Cash Flows

For monthly analysis, IRR returns a monthly rate. Annualize it:

=(1 + IRR(monthly_flows))^12 - 1

Example: Savings plan with monthly contributions:

Month	Flow
0	-1000
1-12	-500 each
12	+8500 (final value)

Monthly IRR: 0.8% Annual IRR: =(1.008)^12-1 = 10.0%

Handling Irregular Returns

IRR assumes regular intervals. For irregular dates, use XIRR instead:

=XIRR(cash_flows, dates)

Multiple IRR Problem

Some cash flow patterns have multiple valid IRRs (sign changes more than once). In these cases:

Use NPV analysis instead
Or use MIRR (Modified IRR)

=MIRR(cash_flows, finance_rate, reinvest_rate)

Example:

=MIRR(B2:B7, 5%, 8%)

finance_rate: Cost of borrowing for negative flows
reinvest_rate: Return earned on positive flows

Business Project Analysis

Evaluate whether a business investment makes sense:

Year	Investment	Revenue	Costs	Net Flow
0	-100000	0	0	-100000
1	0	40000	25000	15000
2	0	55000	30000	25000
3	0	70000	35000	35000
4	0	80000	40000	40000
5	0	90000	45000	45000

IRR: =IRR(E2:E7) = 22.4%

If your required return (hurdle rate) is 15%, this project exceeds it.

IRR vs. Other Metrics

Metric	Measures	Best For
IRR	Annualized return with cash flow timing	Comparing different-sized investments
ROI	Total return percentage	Quick comparison
NPV	Dollar value created	Absolute value determination
CAGR	Growth rate of a single amount	Simple growth analysis

Limitations of IRR

Assumes reinvestment at IRR rate - may be unrealistic for high-IRR projects
Multiple solutions possible - when cash flows change sign multiple times
Ignores scale - a small project with 50% IRR may create less value than a large project with 15% IRR
Requires regular intervals - use XIRR for irregular timing

Pro Tips

Compare to hurdle rate - IRR > required return means the investment is worthwhile
Don’t ignore scale - combine IRR with NPV for complete picture
Check assumptions - ensure cash flow projections are realistic
Sensitivity analysis - test how IRR changes with different scenarios
Use XIRR for real world - actual investments rarely have perfectly regular cash flows

Common Errors

#NUM! error: No solution exists - check that cash flows have at least one sign change
Wrong sign: Remember negatives for money out, positives for money in
Unrealistic result: Very high IRR (>50%) often indicates short holding period, not necessarily a better investment
Comparing apples to oranges: IRR is annualized, so a 20% IRR over 1 month isn’t comparable to 20% over 5 years

Internal Rate of Return (IRR)