top of page

MS Excel: XIRR for annualized rate of return

  • Writer: Fakhriddinbek
    Fakhriddinbek
  • May 1
  • 2 min read

In real-world finance, investments and cash flows rarely happen on a fixed monthly or annual schedule. Payments and returns often occur at irregular intervals, making simple IRR calculations unreliable.


This is where Excel’s XIRR function comes in—a powerful tool for computing the true annualized rate of return when cash flows do not occur at regular periods.


Excel interface showing a "Function Arguments" dialog for XIRR in front of a spreadsheet. Toolbar and formula instructions visible.

The XIRR function calculates the internal rate of return (IRR) for a series of cash flows that occur at non-periodic (irregular) intervals.


Unlike the standard IRR function, which assumes evenly spaced periods, XIRR accounts for the exact dates of each transaction and provides a more accurate annualized return.


Syntax


=XIRR(values, dates, [guess])


Argument Breakdown:


Argument

Description

values

A range of cash flows (at least one negative and one positive)

dates

A range of dates corresponding to each cash flow

guess

(Optional) Your estimate for the return. Default is 10% (0.1)


Output: The annualized internal rate of return as a decimal (e.g., 0.084 = 8.4%)


Example: Real-World Investment


Scenario: You invest $10,000 on January 1, 2024, then receive:

  • $3,000 on July 1, 2024

  • $4,000 on January 1, 2025

  • $5,000 on April 1, 2025


Excel Formula:


=XIRR({-10000, 3000, 4000, 5000}, {DATE(2024,1,1), DATE(2024,7,1), DATE(2025,1,1), DATE(2025,4,1)})


Result: 0.1542 or 15.42% annualized return


This reflects the effective return, considering exact dates and compounding annually.


When to Use XIRR


Use Case

Why It’s Useful

Venture capital & private equity

Irregular capital calls and returns

Real estate investments

Uneven rental income and sale proceeds

Project finance & capex modeling

Non-linear cash flows

Bond ladders or loan repayments

Inconsistent payment schedules


Related Functions


Function

Description

IRR

Calculates IRR for regular periods

XNPV

Calculates NPV for irregular cash flows

NPV

Net present value for regular intervals

RATE

Calculates interest rate per period (for annuities)


Use XIRR when your cash flows do not occur at regular intervals and you need an accurate, annualized return.


Tips and Common Errors


Tip

Reason

Include at least one negative and one positive cash flow

Required for the IRR calculation

Match values and dates arrays

Must be the same length and order

Use XNPV with XIRR for discounted cash flow analysis

Keeps your models consistent

Watch for #NUM! errors

Try changing the guess parameter if Excel can’t find a solution


Example with a custom guess:


=XIRR(values, dates, 0.2) // 20% guess


Behind the Scenes: How It Works


XIRR solves this equation numerically:


Excel screenshot showing the "Function Arguments" dialog for the XIRR function. The sheet is labeled "Sheet1" with formula icons visible.

Where:

  • CiC_iCi​ = Cash flow at date did_idi​

  • d0d_0d0​ = First cash flow date

  • rrr = Internal rate of return (what Excel solves for)


This discounts each cash flow based on actual days elapsed since the initial investment—yielding an accurate compound annual growth rate (CAGR).


Summary Table


Feature

Details

Function Name

XIRR

Purpose

Calculates IRR with actual dates

Supports Irregular Dates

✅ Yes

Output

Annualized rate of return

Common Use Cases

VC, real estate, project finance, DCF


Final Thoughts


The XIRR function is essential for evaluating the real return on investments with non-periodic cash flows. It brings time-value-of-money accuracy to complex investment models and supports better decision-making in finance.


Whether you're:

  • Evaluating an investment's performance,

  • Modeling cash flows in Excel,

  • Comparing ROI across deals,

…XIRR gives you the true picture of what your money is earning annually.

Комментарии


bottom of page