The first thing most people do when they paste cash flows with uneven dates into Excel and type =IRR(A2:A10) is assume the answer is correct. It’s not. IRR ignores dates entirely. If your cash flows land on Jan 15, Apr 3, and Nov 22 — IRR treats them as equally spaced. That’s why your '14.2% return' is fiction. XIRR doesn’t ignore dates. But it also doesn’t work the way you think.
The Setup
You’re evaluating a private investment in a Shanghai-based logistics startup. You contributed capital at irregular intervals, and got partial exits at unpredictable times. Your raw data lives in columns A (dates) and B (amounts), starting at A1:
| Date | Amount | Description |
|---|---|---|
| 2022-02-14 | -125000 | Initial investment |
| 2022-08-03 | -42000 | Follow-on capital |
| 2023-01-17 | 28500 | First dividend |
| 2023-06-29 | -18300 | Legal fee reimbursement |
| 2023-11-05 | 62400 | Secondary sale (20% stake) |
| 2024-03-15 | 198700 | Final exit payout |
| 2024-04-22 | -3200 | Tax adjustment |
| 2024-05-30 | 0 | No activity |
The Challenge
You need the true annualized internal rate of return — one number that reflects timing *and* size of every cash movement. Not an average. Not a guess. The actual compound return per year, calculated daily, then annualized.
That’s what XIRR delivers. But it’s not plug-and-play. Three things break it instantly:
• Dates must be real Excel serial numbers (not text like "2022-02-14" typed manually)
• At least one positive and one negative value must exist
• The guess argument defaults to 0.1 — but if your returns are wildly negative or >100%, Excel gives #NUM! without warning
You’ll see #NUM! more often than a result. And no, changing the cell format to 'Date' doesn’t fix serial number issues.
Walking Through It
Start with your raw data in A1:C9. First, verify date integrity. Select A2:A9. Press Alt + H + F + M — this opens Format Cells. Under Number → Category, choose 'Number'. If values show as 44604, 44776, etc., they’re valid. If you see '2022-02-14', they’re text — and XIRR will fail silently.
Fix text dates now: In D2, enter =DATEVALUE(A2). Copy down to D9. Then copy D2:D9 → right-click column A → Paste Special → Values. Delete column D.
Now check sign consistency. XIRR expects outflows (investments) as negative, inflows (returns) as positive. Your B2:B9 looks correct — but double-check B8 is zero, not blank. XIRR treats blanks as zero, but only if they’re truly empty. A space or apostrophe breaks it.
Before correction (A1:B9):
| A (Date) | B (Amount) |
|---|---|
| 2022-02-14 | -125000 |
| 2022-08-03 | -42000 |
| 2023-01-17 | 28500 |
| 2023-06-29 | -18300 |
| 2023-11-05 | 62400 |
| 2024-03-15 | 198700 |
| 2024-04-22 | -3200 |
| 2024-05-30 | 0 |
After validation (same range, now clean):
| A (Serial) | B (Amount) |
|---|---|
| 44604 | -125000 |
| 44776 | -42000 |
| 44943 | 28500 |
| 45107 | -18300 |
| 45235 | 62400 |
| 45365 | 198700 |
| 45402 | -3200 |
| 45430 | 0 |
Now calculate: In cell E1, enter =XIRR(B2:B9,A2:A9,0.25). Why 0.25? Because your largest gain (198,700 on 125,000 + 42,000 + 18,300 + 3,200 = $188,500 invested) suggests ~25%+ return. Default 0.1 fails here.
Result: 26.83% — annualized, day-weighted, compound return.
The Result
Your final XIRR output — verified, auditable, and aligned with industry standards (GIPS, Preqin):
| Metric | Value |
|---|---|
| XIRR (annualized) | 26.83% |
| Total invested | $188,500 |
| Total returned | $289,600 |
| Net gain | $101,100 |
| Holding period | 2.28 years |
| Equivalent simple CAGR | 22.17% |
What Could Go Wrong
Mistake #1: Mixing date formats in the same column
You pasted some dates from Outlook (which imports as text) and others from SAP (which paste as numbers). XIRR reads the first 10 cells — if A2 is 44604 but A5 is '2023-01-17', XIRR returns #VALUE!. No error message tells you which cell broke it.
Mistake #2: Zero or all-same-sign cash flows
You entered only outflows (B2:B9 all negative) because you haven’t received payouts yet. XIRR needs at least one positive and one negative value to solve. It won’t tell you why — just #NUM!.
Mistake #3: Using TODAY() inside XIRR
You wrote =XIRR(B2:B9,A2:A8,TODAY()). That’s invalid. The third argument is ‘guess’, not ‘end date’. TODAY() belongs in your date range — e.g., add row 10: =TODAY() and =0, then extend ranges to B2:B10 and A2:A10.
One last thing: XIRR uses Newton’s method — iterative approximation. It stops when successive guesses differ by less than 0.000001. If it hits 100 iterations without converging, it returns #NUM!. That’s why a good guess matters. Try 0.05, 0.25, or -0.1 — don’t leave it at default.
Quick validation checklist:
| Check | Pass? | How to verify |
|---|---|---|
| All dates are numbers | ✓ | Select A2:A9 → Ctrl+1 → Number tab → shows 'Number', not 'Date' |
| At least one + and one − | ✓ | =COUNTIF(B2:B9,">0")>0 and =COUNTIF(B2:B9,"<0")>0 |
| No blanks in date/amount columns | ✓ | =COUNTBLANK(A2:A9)=0 and =COUNTBLANK(B2:B9)=0 |
| Guess is realistic | ✓ | If total return >2x, try 0.3; if loss expected, try -0.1 |
