Excel makes IRR calculation straightforward with two built-in functions: IRR for equal-interval cash flows and XIRR for irregular dates. This guide shows you exactly how to use both, with ready-to-copy formulas, calculation tables, and solutions to common errors.
=IRR(values) where
"values" is a range containing your cash flows (initial investment as negative, returns as
positive). For irregular dates, use =XIRR(values, dates).
No Excel? Use our free online IRR calculator — just enter your cash flows and get instant results.
The IRR Function: Basic Syntax
Excel's IRR function calculates the internal rate of return for a series of cash flows that occur at regular intervals (e.g., annually, monthly, quarterly).
| Argument | Description | Required? |
|---|---|---|
values |
A range of cells containing cash flows. Must include at least one negative (investment) and one positive (return) value. | Yes |
guess |
Your estimate of the IRR. Default is 10% (0.1). Use this if you get a #NUM! error. | No |
Example 1: Simple Investment IRR
Let's calculate the IRR for a $10,000 investment that returns cash over 5 years:
| A | B | |
|---|---|---|
| 1 | Year | Cash Flow |
| 2 | 0 (Initial) | -$10,000 |
| 3 | 1 | $2,000 |
| 4 | 2 | $2,500 |
| 5 | 3 | $3,000 |
| 6 | 4 | $3,500 |
| 7 | 5 | $4,000 |
| 8 | IRR | =IRR(B2:B7) |
| 9 | Result | 13.45% |
You can also download our ready-made Excel IRR Calculator here:
Download Excel IRR Calculator (.xlsx)Step-by-Step Instructions
- Enter your cash flows in a column (B2:B7 in this example)
- Initial investment must be negative (-$10,000)
- Returns are positive ($2,000, $2,500, etc.)
- In an empty cell, type:
=IRR(B2:B7) - Press Enter — Excel returns 0.1345 (13.45%)
The XIRR Function: Irregular Cash Flow Dates
Real-world investments rarely have perfectly annual cash flows. Dividends arrive quarterly, you might add funds mid-year, or sell part of an investment at any time. That's where XIRR comes in.
| Argument | Description | Required? |
|---|---|---|
values |
Range of cash flows (negative for investments, positive for returns) | Yes |
dates |
Range of dates corresponding to each cash flow | Yes |
guess |
Optional estimate. Default is 10%. | No |
Example 2: Real-World XIRR Calculation
Calculate XIRR for an investment portfolio with irregular contributions and withdrawals:
| A | B | C | |
|---|---|---|---|
| 1 | Date | Cash Flow | Description |
| 2 | 1/15/2023 | -$25,000 | Initial investment |
| 3 | 4/10/2023 | -$5,000 | Additional investment |
| 4 | 7/22/2023 | $1,200 | Dividend received |
| 5 | 10/5/2023 | -$3,000 | Additional investment |
| 6 | 1/8/2024 | $1,500 | Dividend received |
| 7 | 6/30/2024 | $2,000 | Partial sale |
| 8 | 11/15/2024 | $38,500 | Final sale |
| 9 | XIRR | =XIRR(B2:B8,A2:A8) | |
| 10 | Result | 17.75% | Annualized return |
IRR vs XIRR: When to Use Which
Use IRR when: Cash flows occur at equal intervals (e.g., every year, every month). You're modeling a standard loan, bond, or simple investment.
Use XIRR when: Cash flows have specific dates. Intervals are irregular. You're tracking a real portfolio with dividends, additions, or partial sales.
Common Excel IRR Errors & How to Fix Them
#NUM! Error
Solutions:
- Provide a
guessargument:=IRR(B2:B7, 0.2) - Check that you have both negative AND positive cash flows
- Verify values aren't text (use VALUE() to convert)
#VALUE! Error
Solutions:
- Ensure date cells are formatted as dates
- Use DATEVALUE() to convert text dates
- Check regional date format settings
IRR Returns 0% or Unexpected Result
Solutions:
- Initial investment should be negative (cash outflow)
- Returns should be positive (cash inflow)
- Check for hidden spaces or formatting issues
Advanced Tips for IRR in Excel
Convert Monthly IRR to Annual
If you calculated IRR on monthly cash flows:
Calculate MIRR (Modified IRR)
MIRR addresses the reinvestment rate assumption issue:
Example: =MIRR(B2:B7, 5%, 8%) — assumes 5% borrowing cost and 8% reinvestment rate.
Verify IRR with NPV
To verify your IRR is correct, calculate NPV at that rate — it should be close to zero:
Free Excel IRR Template
Here's a simple template structure you can copy into Excel:
| A | B | C | |
|---|---|---|---|
| 1 | Date | Cash Flow | Notes |
| 2 | [Enter date] | [Enter amount] | [Description] |
| 3 | ... | ... | ... |
| 10 | |||
| 11 | IRR (annual) | =IRR(B2:B9) | For equal intervals |
| 12 | XIRR | =XIRR(B2:B9,A2:A9) | For specific dates |
| 13 | Total Invested | =SUMIF(B2:B9,"<0") | Sum of investments |
| 14 | Total Returned | =SUMIF(B2:B9,">0") | Sum of returns |
Key Takeaways
- IRR function:
=IRR(values)— for equal-interval cash flows - XIRR function:
=XIRR(values,dates)— for specific dates - Initial investment should be negative (money going out)
- Returns should be positive (money coming in)
- #NUM! errors: Add a guess parameter or check cash flow signs
- XIRR is generally better for real-world investment tracking
Now that you know how to calculate IRR in Excel, explore these related topics: