Excel's IRR for Uneven Cash Flows
Many real-world investments don't fit the neat pattern of fixed payments. A rental property generates variable income some months and costs money in others. A startup business burns cash early, then generates uneven profits. A stock portfolio receives dividends sporadically and is sold years later. Excel's IRR function (Internal Rate of Return) calculates the true return on these investments despite irregular timing and amounts, making it indispensable for anyone analyzing complex investment opportunities, evaluating business ventures, or managing real estate portfolios.
Quick Definition
The IRR function calculates the internal rate of return—the discount rate that makes the net present value (NPV) of a series of cash flows equal to zero. Unlike FV, PV, RATE, NPER, and PMT, which assume regular equal payments, IRR handles any sequence of cash flows at any timing. The function syntax is:
=IRR(values, guess)
Where values is a range of cells containing cash flows (typically in chronological order), and guess is an optional initial estimate for the rate (defaults to 10%). IRR returns an annual rate as a decimal (e.g., 0.15 for 15%).
Key Takeaways
- IRR is the discount rate that makes NPV (net present value) of all cash flows equal to zero
- IRR handles uneven cash flows, unlike FV and NPER which require regular equal payments
- The function assumes the first cash flow occurs at time zero (today) and subsequent flows occur at regular intervals (years, months, etc.)
- IRR is particularly useful for real estate (rental income + expenses + sale), startups (burn, then profits), and portfolio analysis
- A higher IRR is better; compare IRR to your required return (opportunity cost) to decide if an investment is worthwhile
- IRR may return multiple solutions or fail to converge for unusual cash flow patterns (mostly cash outflows, then one huge inflow)
- Common mistakes include using IRR when cash flows aren't in chronological order, misunderstanding what IRR represents, or comparing IRRs across projects with different time horizons
- For irregular timing (e.g., exact dates rather than just year-end), use XIRR instead
- Real-world applications include evaluating rental properties, assessing startup investments, measuring fund performance, and comparing business ventures
Understanding IRR and Net Present Value
Before calculating IRR, understand its relationship to Net Present Value (NPV). NPV is the sum of all future cash flows discounted to today at a chosen discount rate:
NPV = CF₀ + CF₁/(1+r) + CF₂/(1+r)² + ... + CFₙ/(1+r)ⁿ
If you choose r = 10% and NPV is positive, the investment beats a 10% opportunity cost (good). If NPV is negative, it underperforms (bad). IRR is the specific rate at which NPV = 0—the break-even return.
If an investment has IRR = 15%, that means:
- At 15% discount rate, NPV = 0 (break-even)
- At 10% discount rate, NPV > 0 (beats 10%)
- At 20% discount rate, NPV < 0 (underperforms 20%)
Compare IRR to your required return (opportunity cost): if IRR ≥ required return, the investment is worth considering. If IRR < required return, you're better off elsewhere.
The Mathematics Behind IRR
IRR solves for the rate r that satisfies:
0 = CF₀ + CF₁/(1+r) + CF₂/(1+r)² + ... + CFₙ/(1+r)ⁿ
This is a polynomial equation of degree n. For n > 2, there's no closed-form algebraic solution, so Excel uses iteration (Newton-Raphson method) similar to the RATE function. The process starts with a guess (default 10%), evaluates NPV at that rate, refines the guess, and repeats until NPV ≈ 0.
Understanding this helps explain edge cases: some cash flow patterns have no solution (impossible), multiple solutions (rare but possible), or slow convergence (extreme guesses).
Worked Example 1: Rental Property Investment
You're evaluating a rental property purchase. The cash flows are:
- Year 0: Pay $300,000 (initial investment)
- Year 1: Collect $25,000 (net rental income after expenses)
- Year 2: Collect $25,500 (income grows 2% annually)
- Year 3: Collect $26,010
- Year 4: Collect $26,530
- Year 5: Sell for $350,000, net of sale costs (total cash inflow: $350,000 + $27,061 net rental = $377,061)
Setup: List cash flows in chronological order:
| Year | Cash Flow |
|---|---|
| 0 | -$300,000 |
| 1 | $25,000 |
| 2 | $25,500 |
| 3 | $26,010 |
| 4 | $26,530 |
| 5 | $377,061 |
Formula:
=IRR(range of cash flows)
If your cash flows are in cells A1:A6 (containing -300000, 25000, 25500, 26010, 26530, 377061):
=IRR(A1:A6)
Result: 0.0847 or 8.47% annual IRR.
This property investment returns 8.47% annually. If your required return (opportunity cost) is 8% (comparable to stock market returns), the property is marginally attractive. If your required return is 10%, it underperforms alternatives. This analysis informs your decision: should you buy this property or invest in stocks instead?
Worked Example 2: Startup Investment
You're considering investing in a startup. The expected cash flows are:
- Year 0: Invest $100,000 (you pay this today)
- Year 1–3: The startup loses money; no cash return to you (or you contribute more)
- Year 4–6: The startup becomes profitable; you receive distributions
- Year 7: You exit (sell your stake) for a profit
Estimated cash flows:
- Year 0: -$100,000 (initial investment)
- Year 1: -$20,000 (you contribute more as it burns cash)
- Year 2: -$15,000
- Year 3: -$10,000 (burn rate decreases)
- Year 4: $0 (break-even)
- Year 5: $40,000 (profitable)
- Year 6: $50,000
- Year 7: $500,000 (exit, total sale proceeds)
Formula:
=IRR({-100000, -20000, -15000, -10000, 0, 40000, 50000, 500000})
Or, if in cells A1:A8:
=IRR(A1:A8)
Result: 0.182 or 18.2% annual IRR.
This startup has an 18.2% IRR despite years of losses, because the eventual exit provides a large return. Compare to your opportunity cost: if you could earn 10% in stocks, this startup's 18.2% is attractive (adjusted for higher risk). If you demand 25% returns for startup risk, it's unattractive.
Worked Example 3: Comparing Two Investment Opportunities
You're deciding between:
- Option A: Rental property (IRR 8.47%, from Example 1)
- Option B: Commercial property with different cash flows:
| Year | Option A | Option B |
|---|---|---|
| 0 | -$300,000 | -$250,000 |
| 1 | $25,000 | $20,000 |
| 2 | $25,500 | $22,000 |
| 3 | $26,010 | $24,000 |
| 4 | $26,530 | $26,000 |
| 5 | $377,061 | $360,000 |
IRR for Option B:
=IRR({-250000, 20000, 22000, 24000, 26000, 360000})
Result: 0.1134 or 11.34% annual IRR.
Option B has a higher IRR (11.34% vs. 8.47%), so if you require 10% returns, Option B is more attractive. However, Option B requires less capital ($250k vs. $300k) but also generates lower rental income initially. Your decision depends on capital availability, risk tolerance, and required return rate.
Worked Example 4: Evaluating a Business Acquisition
You're considering buying a small business. Cash flows (net earnings):
- Year 0: Pay $500,000 to buy the business
- Years 1–5: Earn $100,000 annually
- Year 6: Sell for $450,000 (net of sale costs)
Cash flows:
- Year 0: -$500,000
- Year 1–5: $100,000 each
- Year 6: $450,000 (sale price + final year earnings)
Formula:
=IRR({-500000, 100000, 100000, 100000, 100000, 100000, 550000})
Result: 0.0876 or 8.76% annual IRR.
The business acquisition has an 8.76% IRR. You're buying $500,000 in earnings power that yields $100k/year ($100k/$500k = 20% earnings-to-price initially), but the IRR is lower because of the modest sale price at the end. This is typical: even profitable businesses can have modest IRRs if you overpay initially.
Worked Example 5: Portfolio with Dividends and Sales
You invested $10,000 in a stock portfolio 5 years ago. You received dividends: Year 1–4: $500/year; Year 5: $600. You sold for $14,000 after 5 years.
Cash flows:
- Year 0: -$10,000 (initial investment)
- Year 1: $500 (dividend)
- Year 2: $500
- Year 3: $500
- Year 4: $500
- Year 5: $600 + $14,000 (dividend + sale) = $14,600
Formula:
=IRR({-10000, 500, 500, 500, 500, 14600})
Result: 0.0817 or 8.17% annual IRR.
Your portfolio earned 8.17% annually (in the ballpark of long-term stock market returns). This is a realistic return for a diversified portfolio with no exceptional winners or losers.
Real-World Examples
Example 1: Real Estate Flip You buy a house for $200,000, invest $30,000 in renovations (Year 1), and sell for $290,000 (Year 1).
Cash flows:
- Year 0: -$200,000
- Year 1: -$30,000 + $290,000 = $260,000
IRR:
=IRR({-200000, 260000})
Result: 0.30 or 30% annual IRR.
A real estate flip returning 30% in one year is excellent, but it ties up capital for a year and involves risk (property doesn't sell, renovations cost more). Compare to stock market returns (~10%) and other opportunities.
Example 2: Bond-like Investment You buy a 5-year certificate of deposit (CD) for $10,000 earning 4% annually, then sell it (or it matures). You receive $50/year in interest and $10,000 principal back.
Cash flows:
- Year 0: -$10,000
- Years 1–4: $400 (interest)
- Year 5: $400 + $10,000 = $10,400
IRR:
=IRR({-10000, 400, 400, 400, 400, 10400})
Result: 0.04 or 4% annual IRR.
The IRR equals the quoted rate, confirming the calculation. (This is expected because CDs are fixed-income instruments.)
Example 3: Project Evaluation A company evaluates a project:
- Year 0: Initial equipment cost $50,000
- Year 1–5: Generate $15,000 in net cash flow annually
- Year 5: Salvage value $10,000 (equipment sold at end)
Cash flows:
- Year 0: -$50,000
- Year 1–4: $15,000 each
- Year 5: $15,000 + $10,000 = $25,000
IRR:
=IRR({-50000, 15000, 15000, 15000, 15000, 25000})
Result: 0.1494 or 14.94% annual IRR.
If the company's cost of capital is 10%, this project has a positive NPV and should be undertaken. If it's 15%, the project barely breaks even.
Handling IRR Errors and Convergence Issues
#NUM! Error: IRR fails to find a solution. Causes include:
- No valid solution: The cash flows don't make financial sense (e.g., all positive except one massive outflow that doesn't make sense in sequence).
- Multiple solutions: Unusual patterns (large outflow mid-stream) can create multiple valid IRRs. Excel finds one, but which one depends on the guess.
- Slow convergence: Extreme guesses (e.g., 0.0001% or 500% returns) cause slow iteration. Provide a better guess.
Solution: Provide a reasonable guess as the second parameter:
=IRR(values, 0.15)
This tells Excel to start at 15% instead of the default 10%, which helps if the true IRR is far from 10%.
Unusual cash flow patterns: If most cash flows are outflows, IRR may struggle. Example:
- Year 0: -$100,000 (invest)
- Year 1: -$50,000 (contribute more)
- Year 2: $200,000 (get most back)
For such patterns, you may have two IRRs. Try different guesses:
=IRR(values, 0.05) // Try 5%
=IRR(values, 0.50) // Try 50%
If both return different results, you have multiple solutions.
IRR vs. Other Functions: When to Use What
IRR vs. RATE: RATE is for regular equal payments (loans, annuities). IRR is for irregular cash flows. If payments are equal and regular, RATE is simpler and faster.
IRR vs. NPV: NPV calculates present value given a known discount rate. IRR finds the break-even rate. Use NPV to evaluate an investment given your required return; use IRR to find the return an investment provides, then compare to your requirement.
IRR vs. XIRR: IRR assumes cash flows occur at regular intervals (year-end, quarter-end). XIRR accepts exact dates. For mortgages and bonds with regular periods, IRR is fine. For real estate with exact payment dates or portfolio analysis with real transaction dates, XIRR is more accurate.
Sensitivity Analysis: How Changes Affect IRR
Create a table showing how changes in purchase price and sale price affect IRR for a 5-year rental property generating $25,000/year net:
| Purchase Price \ Sale Price | $300,000 | $350,000 | $400,000 |
|---|---|---|---|
| $250,000 | 12.5% | 13.8% | 15.1% |
| $300,000 | 8.5% | 10.1% | 11.6% |
| $350,000 | 5.3% | 6.7% | 8.2% |
Insight: Buying cheaper or selling higher increases IRR (obviously). The table reveals how sensitive IRR is to these assumptions. A 10% change in purchase price or sale price substantially changes the IRR.
Common Mistakes and How to Avoid Them
Mistake 1: Cash flows not in chronological order IRR assumes the first cell is Year 0 (today), the second is Year 1, etc. If your cash flows are scrambled, you'll get a nonsensical result. Always arrange chronologically, with the initial investment (usually negative) first.
Mistake 2: Missing or zero-value years If an investment has no cash flow in Year 2, you must still include a 0 in that year's cell. IRR assumes cells represent consecutive periods.
Mistake 3: Mixing time units If some cash flows are annual and others are quarterly, or if you have irregular timing, standard IRR won't work correctly. Use XIRR for exact dates or ensure all cash flows are in the same period (all years, all months).
Mistake 4: Forgetting to include all cash flows If you sell an asset in Year 5, include the sale proceeds in the Year 5 cash flow. Don't forget the sale or exclude it "because it's not income."
Mistake 5: Misinterpreting IRR as a guarantee IRR is a mathematical return assuming the cash flows occur as projected. Real investments vary. A 12% projected IRR may become 8% if rental income is lower or repairs cost more.
Mistake 6: Comparing IRRs with different time horizons A 3-year project with 15% IRR may be less attractive than a 10-year project with 10% IRR (more compounding time, less reinvestment risk). IRR alone doesn't account for duration.
Mistake 7: Using IRR when NPV is more appropriate For a one-time decision (buy or don't buy this property?), NPV is often clearer. IRR is useful for comparing similar projects or understanding the break-even rate. For a single project with uncertain required return, NPV at different rates (sensitivity analysis) reveals more.
Flowchart: Choosing the Right Time-Value Function
Frequently Asked Questions
Q: Is a higher IRR always better? A: Generally yes, but IRR doesn't account for risk, time horizon, or capital requirements. A 25% IRR in a risky startup may be less attractive than an 8% IRR in a stable, liquid investment. Compare IRR to required return adjusted for risk.
Q: Can IRR be negative? A: Yes. If an investment loses money overall, IRR is negative. Example: you invest $100, get $50 back after 5 years. IRR ≈ -12% annually (you're losing money).
Q: What if IRR returns multiple values? A: Unusual cash flow patterns (multiple direction changes) can create multiple IRRs. Provide different guesses to find each. In practice, you'd evaluate the investment based on NPV at your required return rate, which is unambiguous.
Q: How do I use IRR to compare projects of different lengths? A: IRR alone is insufficient because it doesn't account for time horizon. Use NPV at your required return rate, or adjust IRR for reinvestment assumptions (MIRR, Modified Internal Rate of Return, a more complex metric).
Q: Is IRR the same as yield? A: For bonds, yes—yield-to-maturity is the IRR of the bond's cash flows. For stocks and other investments, "yield" might refer to dividend yield (annual dividend / stock price), which is not the same as IRR (total return including price appreciation).
Q: Can I use IRR for monthly or daily cash flows? A: Yes. If your range contains monthly cash flows (Month 0, Month 1, Month 2, etc.), IRR returns a monthly rate. Multiply by 12 to annualize. Be clear about your time unit.
Q: Should I use IRR or NPV? A: Both. Use NPV to evaluate an investment at your required return rate (clear yes/no decision). Use IRR to understand the break-even return and to compare similar investments. Together, they give a complete picture.
Related Concepts
Net Present Value (NPV) is the present value of all future cash flows at a chosen discount rate. Modified IRR (MIRR) adjusts IRR for reinvestment assumptions (a more nuanced metric for complex scenarios). Profitability Index is NPV divided by initial investment, answering "NPV per dollar invested." Payback period is the time to recover your initial investment (simpler but ignores time value of money).
Summary
Excel's IRR function calculates the internal rate of return—the discount rate that makes net present value zero—for any sequence of cash flows at regular intervals. Unlike FV, PV, RATE, NPER, and PMT, which require regular equal payments, IRR handles real-world irregular cash flows, making it essential for evaluating rental properties, startup investments, business acquisitions, and portfolio performance.
IRR is the return an investment provides; compare it to your required return (opportunity cost) to decide if it's worthwhile. Higher IRR is generally better, but consider risk, time horizon, and capital requirements—a 25% IRR in a risky venture may not be better than an 8% IRR in a stable, liquid investment.
Common errors include mixing up chronological order, forgetting year-zero cash flows, and misinterpreting IRR as a guarantee. For investments with exact transaction dates rather than regular periods, use XIRR. For comparing projects or evaluating overall investment performance, combine IRR analysis with NPV and other metrics for complete understanding.