Table of Contents
Standard return formulas assume your cash flows happen at equal intervals. Monthly. Quarterly. Annual. But real investments don’t follow neat schedules.
The XIRR formula in excel solves this. It calculates the internal rate of return for cash flows that occur on any dates you specify. This guide shows you the exact syntax, setup steps, and a working example.
What is the XIRR formula in Excel?
The XIRR function returns an annualized rate of return for a series of cash flows with irregular timing. Here’s the syntax:
=XIRR(values, dates, [guess])
Values — A range of cells containing your cash flows. Must include at least one positive and one negative number.
Dates — The corresponding dates for each cash flow. Excel needs to recognize these as actual dates, not text.
Guess — Optional. Your estimate of the return rate. Excel defaults to 10% if you leave this blank.
The function works by finding the rate that makes the net present value of all cash flows equal zero. Excel runs up to 100 iterations to get within 0.000001% accuracy.
Compatibility: XIRR works in Excel for Microsoft 365, Excel for the web, and Excel 2016 through 2024 (including Mac versions).
How to use XIRR formula in Excel
Step 1: Set up your data
Create two columns. One for dates, one for cash flows.
Step 2: Enter your cash flows
Follow this rule: money out is negative, money in is positive.
Your initial investment goes in as a negative number. Returns, dividends, and sale proceeds go in as positive numbers. The final row should be your ending value or sale price.
|
Date |
Cash Flow |
|---|---|
|
1/15/2023 |
-10,000 |
|
4/22/2023 |
500 |
|
9/8/2023 |
750 |
|
2/14/2024 |
600 |
|
6/30/2024 |
12,500 |
Step 3: Enter the XIRR formula in Excel
Click an empty cell. Type:
=XIRR(B2:B6, A2:A6)
The first argument references your cash flows. The second references your dates.
Step 4: Format as percentage
Excel returns a decimal like 0.2584. Select the cell, go to Home > Number, and click the percent button.
Result: 25.84% annualized return.
XIRR vs IRR: when to use which
Both functions calculate internal rate of return. The difference is timing.
|
Function |
Use when… |
|---|---|
|
IRR |
Cash flows occur at equal intervals (monthly, annual) |
|
XIRR |
Cash flows occur on specific, irregular dates |
IRR assumes exactly 12 months between each value. XIRR uses actual calendar dates. For most real-world scenarios—investments, projects, rental properties—XIRR gives you accurate results.
Common XIRR errors and fixes
#NUM! error
This happens when Excel can’t find a solution after 100 tries. Three common causes:
- Missing sign: You need at least one positive and one negative value
- Bad guess: Try adding a guess parameter closer to your expected return
- Impossible math: The cash flows may not have a valid IRR
#VALUE! error
Excel doesn’t recognize your dates. Check that:
- Dates are formatted as dates, not text
- No blank cells exist in your date range
- All dates use a consistent format
Quick fix: Select your date column and apply a date format from Home > Number > Date.
Get started today!
XIRR works best with current data. Stale numbers from last month’s export won’t cut it for active investment tracking.
Coefficient connects Excel directly to your accounting and financial systems. Pull live data from QuickBooks, NetSuite, or Salesforce into your spreadsheet. Your XIRR calculations update automatically as new transactions hit the source system.
No more manual exports. No more copy-paste errors. Just accurate return calculations built on real-time data.
Get started with Coefficient and put your financial analysis on autopilot.
