XIRR for Irregular Cash Flows: The Complete Guide (Excel & Google Sheets)

Quick Answer

XIRR calculates an annualized internal rate of return using actual dates. Use XIRR when cash flows are irregular (not evenly spaced)—which is typical for real estate, private deals, and personal portfolios. The formula in Excel/Google Sheets is:
=XIRR(values, dates)
where values include at least one negative (money out) and one positive (money in), and dates are real date values.

Why XIRR Exists (What IRR Gets Wrong)

The standard IRR function is built for clean, periodic cash flows: one cash flow per period, equally spaced in time. That’s fine for textbook examples and some bonds. But real life isn’t periodic.

Examples of irregular timing:

• You buy a property on Jan 12, pay a contractor on Feb 3 and Mar 18, then start receiving rent on May 1.
• You invest in a private deal with a capital call in month 0, another capital call in month 7, a distribution in month 19, then a big exit in month 43.
• You contribute to a brokerage account whenever you have surplus cash (not monthly), and withdraw for a big purchase.
• You refinance at an unexpected time and pull cash out mid-hold.

If you use IRR in those cases, you’re forcing the model to pretend time is evenly spaced. That can produce returns that are directionally wrong, not just slightly off.

XIRR vs IRR: The Clear Decision Rule

Use IRR if:

• Cash flows occur at equal intervals (every month, every year) and
• They truly are one cash flow per period with consistent spacing.

Use XIRR if:

• Cash flow dates are irregular (most real-world cases), or
• You have a purchase date and a sale date (not exactly aligned to periods), or
• You have renovations/capital calls/refinances that happen on specific days.

Simple test

If your data includes a “Date” column, you should almost always be using XIRR.

How XIRR Works (In Plain English)

XIRR is still an “IRR.” It’s the rate that makes NPV equal to zero. The difference is that instead of assuming each cash flow happens one fixed period apart, XIRR discounts each cash flow by the actual time difference from the start date.

Conceptually:

• Cash flows that occur sooner “count more” because they’re discounted less.
• Cash flows that occur later “count less” because they’re discounted more.
• XIRR finds the annual rate that makes the present value of positives equal the present value of negatives.

Why this matters in practice

In irregular cash flows, small timing shifts can move XIRR noticeably: a renovation payment delayed by 60 days changes how long your money is deployed, and that changes the annualized rate. XIRR captures that.

Correct Setup (Values + Dates): The Rules

Rule 1: Values and dates must be paired 1:1

Each cash flow amount must have a corresponding date. If you have 25 values, you need 25 dates.

Rule 2: You need at least one negative and one positive cash flow

Negative = money out (investment). Positive = money in (return). If you forget the initial negative cash flow, XIRR can’t compute a meaningful return.

Rule 3: Dates must be actual date values, not text

This is the #1 reason XIRR returns #VALUE!. Make sure your spreadsheet recognizes dates as dates (you can usually tell because the cell aligns and formats like a date).

Rule 4: Order should be chronological

Some spreadsheets can handle unordered rows, but you should sort by date anyway. It prevents subtle errors, makes auditing easier, and helps you debug quickly.

Rule 5: Don’t “compress” months into one line unless it’s intentional

You can aggregate monthly cash flows into quarterly or annual totals, but then you must use dates that match those totals. If you aggregate, be consistent. Aggregation changes the timing and can slightly change XIRR.

Excel XIRR: Step-by-Step (With a Clean Template)

Step 1: Create two columns

• Column A: Cash flow (values)
• Column B: Date

Step 2: Enter cash flows with correct signs

Example pattern (real estate-ish):

• -80,000 on 2026-01-15 (down payment + closing)
• -12,000 on 2026-02-05 (renovation)
• -5,000 on 2026-03-12 (renovation)
• +650 on 2026-05-01 (net cash flow month 1)
• ... more monthly cash flows ...
• +95,000 on 2028-06-20 (net sale proceeds)

Step 3: Use the formula

=XIRR(A1:A30, B1:B30)

Step 4: Format as percent

Excel returns a decimal. Format as percent.

Step 5: Validate

If the result is extremely high or negative unexpectedly, verify: (1) signs, (2) sale proceeds are net of selling costs and loan payoff, and (3) dates are correct.

Google Sheets XIRR: Step-by-Step

Step 1: Same structure (values + dates)

Column A = values, Column B = dates.

Step 2: Use XIRR()

=XIRR(A1:A30, B1:B30)

Step 3: Watch for date parsing issues

Google Sheets can misread dates depending on locale (MM/DD/YYYY vs DD/MM/YYYY). If your XIRR seems wrong, confirm that the dates are interpreted correctly.

Step 4: If you get #VALUE!

Check for text dates or blank cells. Convert text to dates using DATEVALUE if needed, or re-enter dates in a consistent format.

Real Estate XIRR Example: Purchase → Renovation → Rent → Sale

Real estate is the perfect XIRR use case because the cash flows are inherently irregular. Here’s how to build a realistic cash flow list for XIRR:

1) Acquisition (cash out)

• Down payment (negative)
• Closing costs (negative)
• Initial repairs/renovations (negative)

2) Stabilization (often delayed)

Many properties don’t generate net positive cash flow immediately:

• Lease-up period (vacancy)
• Upfront repairs delay rent
• Higher early maintenance/turnover costs

Those early negatives matter. They reduce XIRR because they increase cash out early and delay cash in.

3) Operating cash flow (monthly or quarterly)

Use net cash flow after: mortgage (if levered), taxes, insurance, repairs, property management, vacancy allowance, and reserves if you treat them as cash out.

4) Exit (sale) — use net proceeds

The sale cash flow line should be net:

• Sale price
• minus selling costs (agent commissions, closing fees)
• minus remaining loan payoff
• minus any taxes you are modeling
• = net cash to equity (positive)

5) Refinance cash-out (if applicable)

If you refinance and pull cash out, that’s a positive cash flow on the refinance date. Refinance fees are negatives. This can boost XIRR because it returns capital earlier—but also changes risk.

Levered vs unlevered XIRR

XIRR can be computed for:

• Unlevered cash flows (property-level, no debt)
• Levered cash flows (equity-level, after debt service)

Don’t compare unlevered XIRR to an equity hurdle rate or vice versa. Keep the level consistent.

Portfolio XIRR: Your “Personal IRR” for Investing Contributions and Withdrawals

XIRR is also useful for personal finance: you contribute to investments over time, you withdraw sometimes, you may change contribution amounts, and you want to know your annualized performance considering real timing.

How to structure portfolio XIRR

• Contributions (deposits) = negative cash flows
• Withdrawals = positive cash flows
• Final portfolio value = positive cash flow on “today’s” date (as if you sold everything today)

Why this is powerful

It accounts for the reality that you invested different amounts at different times. Two people can own the same fund but have different XIRR because they contributed at different times (buying more before a rally vs after).

Important note

Portfolio XIRR reflects your personal timing (money-weighted return). It is not the same as the fund’s time-weighted return. That’s not a bug—just be clear about what you’re measuring.

Common Mistakes That Break XIRR (Or Make It Wrong)

1) Dates stored as text

Looks like a date, acts like text → XIRR fails or returns nonsense. Fix: re-enter dates, change format, or convert using date functions.

2) Wrong signs

Deposits must be negative; distributions and sale proceeds must be positive. The most common error is accidentally making the purchase positive.

3) Missing net sale proceeds

If you use gross sale price (and ignore selling costs and loan payoff), your XIRR is inflated. XIRR can’t correct missing costs. Only accurate cash flows can.

4) Mismatched ranges

Values range and dates range must be the same length. If you copy/paste and accidentally include an extra blank date or value, XIRR returns errors.

5) Aggregating cash flows inconsistently

If you combine 12 months into one annual cash flow but still label it as a month date, timing becomes distorted. Aggregation is fine, but match dates to what the number represents.

6) Cash flows with multiple sign changes

Complex deals can produce multiple IRR solutions. XIRR might fail or return a weird number. In that case, use NPV at a hurdle rate and/or MIRR as a backup.

Troubleshooting XIRR Errors: #NUM! and #VALUE!

#VALUE! (usually formatting)

Typical causes:

• Dates are text (not date values)
• Non-numeric values in the cash flow range
• Ranges are different sizes

Fixes:

• Convert dates to real dates
• Remove blanks and non-numeric characters
• Make ranges identical length

#NUM! (usually math/convergence)

Typical causes:

• No sign change (all negative or all positive)
• XIRR can’t converge with default guess
• Cash flows produce multiple IRRs
• Extreme cash flow patterns

Fixes:

• Ensure at least one negative and one positive cash flow
• Try a different guess (see below)
• Check for multiple sign changes; consider NPV/MIRR
• Audit the largest cash flow lines (purchase, refinance, sale)

The Guess Parameter: When You Need It (and How to Use It)

XIRR is solved iteratively. The guess is a starting point. Most of the time you don’t need it. But when you see #NUM!, adding a guess can help.

Example

=XIRR(A1:A30, B1:B30, 0.1) starts near 10%.

What guess should you use?

• If you expect a normal investment return, try 0.05 to 0.15
• If you expect a negative return, try -0.1
• If the deal is a fast flip, try 0.3 or 0.5

If you get a weird answer after guessing

That can indicate multiple IRRs. Try another guess and see if it returns a different answer. If answers vary widely, rely on NPV at a hurdle rate instead of “the IRR.”

Multiple IRRs and Weird Cash Flow Patterns (When XIRR Becomes Unreliable)

Cash flows that change sign multiple times (negative → positive → negative → positive) can produce multiple IRRs. XIRR is not “wrong” here—there may simply be more than one solution.

Common causes in real estate

• Large mid-hold renovations after initial stabilization
• Balloon payments or major debt events
• Refinance cash-out followed by major capital injection

What to do

• Use NPV at your hurdle rate as the decision metric
• Consider MIRR (modified IRR) with realistic reinvestment rate
• Split the project into phases (if appropriate) and analyze separately

Sanity Checks (So You Don’t Trust a Wrong XIRR)

Sanity check #1: Does the story make sense?

A normal investment pattern is: big negative upfront, then small positives, then a big positive at exit. If you see many positives early with no initial negative, something’s wrong.

Sanity check #2: Compare total dollars

XIRR can be high if money returns quickly even if total profit is small. Check total profit and/or equity multiple.

Sanity check #3: Compare to NPV at a hurdle rate

If XIRR is above your hurdle rate, NPV at that hurdle rate should generally be positive (assuming standard cash flows). If that relationship doesn’t hold, audit the data.

Sanity check #4: Remove one line at a time

If XIRR behaves weirdly, remove the sale line, then refinance line, then big CapEx lines. This often reveals a wrong sign or a misdated entry.

Copy/Paste Templates (Excel / Sheets)

Template 1: Real estate XIRR skeleton

Column A = Cash flow, Column B = Date, Column C = Description (optional)

• A1: -[Down payment + closing] | B1: [Purchase date] | C1: Purchase
• A2: -[Reno cost] | B2: [Date] | C2: Renovation
• A3: -[Reno cost] | B3: [Date] | C3: Renovation
• A4: [Net cash flow] | B4: [Date] | C4: Operations
• ...
• A_last: [Net sale proceeds] | B_last: [Sale date] | C_last: Sale (net)

Formula:
=XIRR(A1:A_last, B1:B_last)

Template 2: Portfolio XIRR (money-weighted return)

• Deposits: negative on deposit dates
• Withdrawals: positive on withdrawal dates
• Final portfolio value: positive on today’s date

Formula:
=XIRR(values, dates)

Checklist (Print This)

• ✅ Use XIRR when dates are irregular (default for real estate)
• ✅ Values and dates ranges are same length
• ✅ Dates are real date values (not text)
• ✅ At least one negative and one positive cash flow
• ✅ Correct signs: money out = negative, money in = positive
• ✅ Sale line is net proceeds (selling costs + loan payoff removed)
• ✅ Big CapEx/reno entries have correct dates and signs
• ✅ If #NUM!: try a guess, check multiple sign changes, use NPV/MIRR as backup
• ✅ Sanity-check with total profit/equity multiple

Related links

Frequently Asked Questions

Bottom Line

If your cash flows happen on real calendar dates (which is most investments), use XIRR. IRR assumes equal spacing and can be wrong when timing is irregular. XIRR isn’t just “more advanced”—it’s the correct tool for real estate purchases, renovations, delayed rent starts, refinances, and sale exits. The key is setup: correct signs, real dates, net sale proceeds, and matching ranges. Once you calculate XIRR, validate it with sanity checks and pair it with NPV or equity multiple to avoid being fooled by timing effects.

Methodology and assumptions

Educational only. XIRR/IRR are mathematical summaries of cash flow assumptions and timing. For irregular dates, XIRR is typically more appropriate. Always include real-world costs (fees, selling costs, loan payoff) and run scenario tests.