MS Excel: XIRR for annualized rate of return
- 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.

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:

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.
Комментарии