Key Takeaways
- XIRR accounts for the exact calendar date of every cash flow. Standard IRR treats all periods as equal length, which overstates or understates returns whenever your contributions are irregular.
- Using simple IRR instead of XIRR on a 5-year investment with uneven contributions can misstate your annualized return by 2 to 4 percentage points, potentially hiding a underperforming position.
- XIRR solves for the single discount rate that makes the net present value of all dated cash flows equal zero, then annualizes that rate on a 365-day basis.
- Tool: Run your own XIRR calculation with the CalcMoney Investment Calculator →
Put Your Money to WorkSPONSORED
Betterment builds and rebalances your portfolio automatically. No guesswork.
Why Standard IRR Fails Irregular Portfolios
IRR, the internal rate of return, assumes each period in your cash flow series is identical in length. Month one equals month two. Quarter one equals quarter two. That assumption holds in textbook bond analysis. It does not hold when you invest a lump sum in January, add money in March, skip April, and receive a dividend in September.
The error compounds over time. A 36-month investment with contributions on day 1, day 47, day 210, and day 890 has periods of 47 days, 163 days, 680 days, and 110 days. Standard IRR treats all four gaps as equal. That misrepresentation distorts your annualized return figure.
XIRR eliminates that distortion. It assigns a specific date to every cash outflow and inflow, then solves for the rate that reconciles all of them to a net present value of zero. The annualization uses actual day counts, not assumed period counts.
The XIRR Formula, Plainly Stated
XIRR solves this equation iteratively:
Sum of [ Cash Flow(i) / (1 + Rate) ^ ((Date(i) - Date(0)) / 365) ] = 0
Where:
- Cash Flow(i) is each payment or receipt, negative for money you put in, positive for money you take out or for the final portfolio value
- Date(i) is the calendar date of that cash flow
- Date(0) is the date of the first cash flow
- Rate is the annual return, the value the formula solves for
There is no closed-form algebraic solution. Spreadsheets and calculators use Newton-Raphson iteration, starting with a guess (typically 10%), then adjusting until the sum converges to zero within a tolerance of 0.000001%.
Sign Convention Matters
Outflows carry a negative sign. When you invest $50,000, that entry is -50,000. Inflows carry a positive sign. When you receive $8,000 in dividends or sell a position for $120,000, those entries are positive. Reversing the sign on even one cash flow produces a nonsense result.
Worked Example 1: The Real Estate Syndication
A limited partner invests in a private real estate syndication with the following cash flows:
| Date | Cash Flow | Description |
|---|---|---|
| 2021-03-15 | -$200,000 | Initial capital call |
| 2021-11-02 | -$30,000 | Second capital call |
| 2022-06-18 | +$14,500 | Preferred return distribution |
| 2023-01-09 | +$18,200 | Preferred return distribution |
| 2023-09-22 | +$22,800 | Preferred return distribution |
| 2024-08-31 | +$284,000 | Final liquidation proceeds |
Step one: Calculate day offsets from the first date (March 15, 2021).
- March 15, 2021: day 0
- November 2, 2021: day 232
- June 18, 2022: day 460
- January 9, 2023: day 665
- September 22, 2023: day 921
- August 31, 2024: day 1,265
Step two: Set up the XIRR equation with these six terms and solve iteratively.
The XIRR solution for this cash flow series is 14.87% annualized.
Now run the same numbers through standard IRR, treating each cash flow as arriving at the end of equal six-month periods. Standard IRR returns 17.3%. The 2.43-point gap is not rounding error. It reflects the reality that the investor waited 1,265 days (3.46 years) to receive the bulk of the return, not the 3.0 years that equal-period IRR assumes.
The 14.87% XIRR figure is the correct one. A real estate attorney comparing this deal to a benchmark should use 14.87%, not 17.3%.
Worked Example 2: The Dollar-Cost Averaging Portfolio
An investor contributes to a brokerage account on payday, twice per month, but payday dates shift slightly across months. Over 18 months, the account receives 11 contributions (three payroll delays caused missed auto-transfers) and finishes with a portfolio value of $98,400.
Representative cash flows:
| Date | Cash Flow |
|---|---|
| 2023-01-13 | -$5,000 |
| 2023-01-27 | -$2,500 |
| 2023-02-10 | -$2,500 |
| 2023-03-14 | -$2,500 |
| 2023-04-28 | -$2,500 |
| 2023-06-09 | -$5,000 |
| 2023-07-14 | -$2,500 |
| 2023-09-01 | -$5,000 |
| 2024-01-05 | -$2,500 |
| 2024-04-19 | -$2,500 |
| 2024-06-22 | -$2,500 |
| 2024-07-13 | +$98,400 |
Total invested: $35,000. Terminal value: $98,400. Gross gain: $63,400.
A simple return calculation, (98,400 - 35,000) / 35,000, gives 181.1%. That number is meaningless without time adjustment, and the time adjustment requires knowing exactly when each dollar entered the account.
XIRR on this series returns 47.23% annualized. A naive annualized simple return, dividing 181.1% by 1.5 years, suggests 120.7%. That figure ignores the fact that the earliest $5,000 had 18 months to compound while the final $2,500 had only 21 days.
The 47.23% XIRR figure correctly weights each dollar by its actual holding period.
How to Run XIRR in Excel or Google Sheets
Both applications include XIRR as a native function.
Excel syntax:
=XIRR(values, dates, [guess])
Google Sheets syntax:
=XIRR(cashflows, dates, [rate_guess])
Practical notes:
- Place all cash flow amounts in a single column. Outflows are negative. The final portfolio value is positive.
- Place the corresponding dates in an adjacent column. Dates must be formatted as date values, not text strings.
- The optional guess argument defaults to 0.1 (10%). If the function returns an error, try 0.2 or -0.5 as alternative starting guesses. Some cash flow profiles have multiple mathematical solutions.
- If the result appears as a decimal (0.1487), format the cell as a percentage.
XIRR fails or returns errors in three common situations: all cash flows share the same sign, no positive cash flow exists in the series, or the dates are not in chronological order. Check for these before troubleshooting the formula.
XIRR vs. CAGR: When to Use Each
CAGR, compound annual growth rate, fits a single lump sum with a single exit. One investment in, one liquidation out. The formula is straightforward: (End Value / Begin Value) ^ (1 / Years) - 1.
XIRR replaces CAGR the moment a second cash flow appears, whether a contribution, a withdrawal, or a dividend reinvestment. Even one additional cash flow on a non-anniversary date invalidates the CAGR approach.
Private equity, real estate, startup investments, and actively managed brokerage accounts all require XIRR. Buy-and-hold index fund investors who contribute on variable schedules also require XIRR for an accurate return figure.
The Modified Dietz Method as a Shortcut
For quick approximations, the Modified Dietz method adjusts returns for the timing of cash flows without iterative calculation. It weights each cash flow by the fraction of the period remaining after the flow occurs.
Modified Dietz Return = (End Value - Begin Value - Net Cash Flows) / (Begin Value + Sum of [Weight(i) x Cash Flow(i)])
This method produces reasonable estimates for short periods with modest cash flows. For multi-year investments or large capital events, use XIRR. The approximation error in Modified Dietz grows as the dollar magnitude of mid-period flows increases relative to the beginning portfolio value.
Common XIRR Mistakes and Their Cost
Mistake 1: Omitting the terminal value. XIRR requires at least one positive cash flow. If you list only contributions (all negative), the function has nothing to solve against. Always include the current portfolio value as a positive figure on today's date.
Mistake 2: Double-counting reinvested dividends. If dividends auto-reinvest, they never leave the portfolio. Do not add them as inflows. Only record cash that physically enters or exits the account.
Mistake 3: Using inconsistent date formats. A mix of MM/DD/YYYY and YYYY-MM-DD in the same column causes Excel to misread dates silently, producing a result that appears valid but is calculated on wrong day counts.
Mistake 4: Ignoring fees. Management fees, transaction costs, and advisory fees paid outside the portfolio reduce your actual return. Add those outflows to the XIRR series. A 1.0% annual fee on a $500,000 portfolio costs $5,000 per year. Excluding that from the cash flow series overstates net XIRR by a meaningful margin over a decade.
Run Your Numbers Now
The examples above are illustrative. Your actual cash flow series has different amounts, different dates, and a different terminal value. The only number that matters is yours.
The CalcMoney Investment Calculator accepts variable contribution schedules and calculates time-weighted returns against your actual entry dates. Input your cash flows, set the dates, and the calculator returns an annualized figure built on the same XIRR logic described here.
If you have been reporting portfolio performance using simple returns or CAGR on an account with multiple contributions, the gap between your stated return and your actual return may be substantial. Run the correct calculation before comparing your results to any benchmark.
Calculate your XIRR with the CalcMoney Investment Calculator →You Might Also Like
- How to Calculate Discounted Cash Flow Valuation on Any Investment
- Free Cash Flow vs. Earnings: Why the Number Wall Street Reports Is Often Wrong
- Current Dividend Yield vs Trailing Yield: You're Probably Using the Wrong Number
Results are estimates for informational purposes only. Consult a licensed financial professional before making financial decisions.
Put These Numbers to Work
Open a Fidelity brokerage account. $0 commissions, no account minimums, fractional shares available.
Affiliated. We may earn a commission.
Related Guides
Free Tools
Run the actual numbers
Stop estimating. Plug in your numbers and get a precise answer in seconds. Free, no signup required.
Open Free Calculators