Internal Rate of Return vs XIRR: Key Differences Explained

IRR vs XIRR is the same idea with two different clocks. The table compares them on definition, timing, who uses each, legal and tax treatment, risk, liquidity and a typical example.

Both functions search for a rate. They try rates until the cash flows, each discounted back to the start, add up to zero. Money paid in counts as negative. Money received counts as positive. The only difference is the exponent, the power each payment is discounted by.

IRR

Sum of CFₜ ÷ (1 + r)ᵗ = 0, where t is 0, 1, 2 and so on, the row number. The row is the period.

XIRR

Sum of CFᵢ ÷ (1 + r)^((dᵢ − d₁) ÷ 365) = 0, where dᵢ − d₁ is the number of days since the first payment.

Microsoft describes XIRR as the rate at which XNPV, its net present value function for dated flows, equals zero. XNPV discounts every payment on a 365-day year (Microsoft Support). IRR’s help page says its cash flows “must occur at regular intervals, such as monthly or annually” (Microsoft Support).

One Fund, Three Answers

Here is one investor’s position in an illustrative fund, with $1,000,000 committed. The last line is the value still held at the end of 2025. A fund report treats it as if it were paid out on that date.

• XIRR on the real dates14.1% a year
• IRR on calendar-year totals15.6%, as if each year’s flows all landed on one day
• IRR on the seven rows as typed15.1%, as if each row were exactly one year after the last

The XIRR result is the right one for these dates. The yearly-total IRR packs the March and September 2020 calls into one moment and shortens the life of the fund. Both shifts make the money look as if it worked faster, so the rate comes out 1.5 percentage points too high.

In Excel, the syntax is =IRR(values, [guess]) and =XIRR(values, dates, [guess]). Both guesses default to 10%. Google Sheets uses the same two functions, and its help page says to use IRR when the dates are evenly spaced (Google).

• SignsBoth need at least one negative and one positive cash flow. Otherwise they return #NUM!.
• The first dateXIRR measures every date from the first one. A later row with an earlier date returns #NUM!. Put the earliest payment first.
• A leading zeroILPA’s guidance for its performance template warns that XIRR returns an error if the first value is zero.
• TriesXIRR makes up to 100 attempts and IRR up to 20. If neither finds an answer, try a different guess.
• Check the answerAt the XIRR rate, XNPV on the same flows returns zero. At the IRR rate, NPV does.

Microsoft’s own XIRR example has five flows: −$10,000 on January 1, 2008, then four receipts over 15 months. XIRR returns 37.34% (Microsoft Support).

The compound annual growth rate (CAGR) uses only two numbers: a starting value and an ending value. CAGR = (end ÷ start)^(1 ÷ years) − 1. Excel’s RRI function returns the same rate from the number of periods, the present value and the future value (Microsoft Support). So $100,000 that grows to $175,000 in five years has a CAGR of 11.8%.

With one payment in and one out, all three agree. IRR, XIRR and CAGR give the same yearly rate. They part ways when money moves in and out along the way. CAGR cannot see those flows. IRR sees them but ignores their dates. XIRR sees both.

Take the fund above. Its TVPI, total value over money paid in, is 1.75x, reached 5.8 years after the first call. Treated as one lump, that is a CAGR of 10.1%. The XIRR is 14.1%, because most of the money went in later than the first day and some came back early. Each measure is correct for its own question. Only XIRR answers how fast your actual money grew.

IRR

For

Simple, with no dates to manage. Exact for flows that truly are evenly spaced, such as a yearly forecast. Works in every spreadsheet and calculator.

Against

Gives a wrong answer for uneven dates, and nothing warns you. Monthly or quarterly rows return a rate per period, which is easy to misread as a yearly one.

Verdict: IRR is the tool for models built on equal periods, where each row is a year or a month by design. For real investments with real payment dates, it is an approximation that can be more than a percentage point off.

XIRR

For

Uses the true timing of each payment. Always returns a yearly rate. Matches the way fund returns are calculated under GIPS, with daily cash flows.

Against

Needs a date for every flow, in the right order. Errors out on a leading zero or a date before the first one. Like IRR, it treats the final value as cash.

Verdict: XIRR is the tool for any private fund, SPV or personal portfolio with irregular payments. It is also how you check a fund’s reported IRR against your own records.

A fund’s reported net IRR is a dated calculation on the cash flows between the fund and its investors, with the current value counted as a final inflow. ILPA’s standard template reports net IRR at the top, with and without the effect of a subscription credit line (ILPA, 2025). That is a loan the fund uses before it calls your money. It shortens the time your money is in the fund. When the fund is in profit, that lifts the IRR, most in the early years (ILPA, 2020).

An IRR measures speed. It does not tell you how much money came back. A fund can show a high IRR on a small multiple. So read it next to TVPI and the cash part of it, covered in DPI vs TVPI. The deal-level multiple is explained in MOIC. The pace of your payments into a fund is set by capital calls.

Common Mistakes

• Using IRR on uneven datesThe function assumes equal spacing. Use XIRR whenever payments fall on real calendar dates.
• Reading a monthly IRR as yearlyIRR on monthly rows returns a monthly rate. XIRR always returns a yearly one.
• Leaving out the current valueWithout the NAV as a final inflow, a fund that still holds companies shows a rate far too low.
• Comparing gross with netA deal IRR before fees is higher than your IRR after them. Compare like with like.
• Judging by IRR aloneA quick small gain can show a high rate. Check the multiple too.

IRR (internal rate of return)

The rate at which the present value of a set of cash flows equals zero. Spreadsheet IRR assumes equal periods.

XIRR

The spreadsheet function that returns a yearly IRR for cash flows on any dates, discounted on a 365-day year.

XNPV

Net present value for dated cash flows. It equals zero at the XIRR rate.

CAGR

Compound annual growth rate: the steady yearly rate that turns a start value into an end value. Excel’s RRI returns it.

Money-weighted return

A return that reflects the timing and size of money moving in and out. IRR and XIRR are examples.

Present value

What a future payment is worth today at a given rate.

NAV (net asset value)

What a fund’s holdings are worth at their estimated value, minus what it owes.

Subscription credit line

A loan a fund draws on before it calls investors’ money.

TVPI

Total value to paid-in: a fund’s value per dollar investors paid in. It ignores time.

MOIC

Multiple on invested capital: a deal’s value over its cost. It also ignores time.

Function descriptions and standards were last checked on October 29, 2026. Spreadsheet behavior is as documented by Microsoft and Google. GIPS and ILPA are industry standards without the force of law. The SEC Marketing Rule applies to US investment advisers. The fund example is illustrative and contains no market data.