Financial

XIRR Function in Excel

Returns the internal rate of return for a schedule of cash flows with specific dates.

Syntax

  • =XIRR(values, dates, [guess])

Arguments

  • values (required): Cash flow values
  • dates (required): Dates for each cash flow
  • guess (optional): Initial guess (default 0.1)

Examples

  • =XIRR(A1:A5, B1:B5) - IRR with dates - Result: Annual rate

Excel XIRR function documentation

  • XIRR calculates internal rate of return for cash flows on irregular dates.
  • Syntax: =XIRR(values, dates, [guess]). Requires one negative and one positive flow.
  • Essential for real investment timelines, PE cash flows, and loan schedules with uneven payments.
  • Related: [IRR](/functions/irr/), [XNPV](/functions/xnpv/), [NPV](/functions/npv/), [RATE](/functions/rate/).

XIRR syntax and date alignment

  • values and dates must be same-length ranges — each flow paired with its date.
  • dates must be valid Excel dates — sort order not required but flows must include sign change.
  • guess optional — try 0.1 if #NUM! on unusual patterns.
  • Text dates or mismatched range lengths cause #NUM! or #VALUE!.

Step-by-step: IRR with actual payment dates

  • Step 1 — Amounts in B2:B20, dates in C2:C20.
  • Step 2 — Initial investment negative on first date.
  • Step 3 — =XIRR(B2:B20, C2:C20).
  • Step 4 — Annualized rate — XIRR returns annual rate by default based on 365-day basis.
  • Step 5 — Validate with [XNPV](/functions/xnpv/) at hurdle rate.

XIRR vs IRR vs MIRR

  • XIRR — irregular calendar dates.
  • IRR — equal periods only.
  • MIRR — explicit finance/reinvest rates.
  • Financial modeling: [/categories/financial/](/categories/financial/).

Worked examples to copy

  • Basic: =XIRR(B2:B10, C2:C10).
  • With guess: =XIRR(flows, dates, 0.2).
  • Compare projects: rank XIRR holding same date granularity.
  • Sanity: all dates unique not required; duplicate dates allowed.

People also ask

  • XIRR #NUM!? — No sign change or bad guess — adjust guess or check flows.
  • XIRR vs IRR difference? — Date spacing; XIRR for real-world calendars.
  • XIRR monthly flows? — Dates can be any spacing; result annualized.
  • XIRR Google Sheets? — Sheets uses XIRR with similar syntax.

Common errors

  • Needs at least one positive and one negative value

Use cases

  • Irregular cash flows
  • Investment analysis
  • Project returns

Frequently asked questions

  • What is the difference between IRR and XIRR? IRR assumes equal periods between cash flows (monthly, yearly). XIRR uses actual dates, making it accurate for irregular cash flows. For real investments with varying timing, XIRR gives the true annualized return.
  • How do I calculate my investment return with XIRR? List all cash flows (negative for money out, positive for money in) with their dates. Include initial investment as negative, any additions as negative, withdrawals and final value as positive. =XIRR(cashflows, dates) gives your annualized return.
  • Why does XIRR return #NUM! error? XIRR needs at least one positive and one negative value. It also may not converge for unusual cash flow patterns. Try providing a guess closer to expected return: =XIRR(values, dates, 0.15). Check that dates are valid and in chronological order.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use XIRR

  • Irregular cash flows — common Financial scenario for XIRR.
  • Investment analysis — common Financial scenario for XIRR.
  • Project returns — common Financial scenario for XIRR.

XIRR in the Financial category

  • Browse all Financial functions at /categories/financial/ for related formulas.
  • XIRR syntax: =XIRR(values, dates, [guess])
  • values (required): Cash flow values
  • dates (required): Dates for each cash flow
  • guess (optional): Initial guess (default 0.1)
  • Confirm XIRR arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm XIRR arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • IRR (/functions/irr/): Returns the internal rate of return for a series of cash flows.
  • XNPV (/functions/xnpv/): Returns the net present value for a schedule of cash flows with specific dates.
  • NPV (/functions/npv/): Calculates the net present value of an investment based on a discount rate and future cash flows.

Errors to watch for

  • Needs at least one positive and one negative value — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter XIRR on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

XIRR worked examples to copy

  • =XIRR(A1:A5, B1:B5) — IRR with dates. Expected result: Annual rate.

XIRR reference summary for crawlers and offline review

  • XIRR belongs to the Financial category in Excel. Returns the internal rate of return for a schedule of cash flows with specific dates.
  • Full syntax: =XIRR(values, dates, [guess]). Open /functions/xirr/ for parameters, FAQs, and related pages.
  • Common mistakes: Needs at least one positive and one negative value
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.