Its calculates the internal rate of return (IRR) for a series of cash flows with irregular timing, providing the annualized effective return rate.
Syntax
XIRR(Values; Dates; [Guess])
Arguments
| Argument | Required | Description | Validation Rules |
| Values | Yes | Array of cash flows (positive for inflows, negative for outflows). | Must include at least one positive and one negative value. |
| Dates | Yes | Corresponding dates for each cash flow. | Dates must be in chronological order after the first date. |
| [Guess] | No | Initial estimate for IRR (default=10%). | Helps convergence in complex scenarios. |
Error Conditions
- #VALUE!: Invalid date format.
- #NUM!: If:
- Dates not chronological
- Mismatched array sizes
- No solution found (e.g., all positive/negative cash flows)
Background
XIRR extends the classic IRR by accommodating irregular intervals, using daily compounding based on a 365-day year. It solves for the discount rate that sets the net present value (NPV) of cash flows to zero:
Examples
- Consumer Loan Evaluation
Scenario:
- Washing machine: $599 (initial outflow, entered as -599)
- 12 monthly payments: $52.48 (inflows, entered as +52.48)
Calculation:
=XIRR({-599, 52.48, 52.48, …, 52.48}, {« 1/1/2023 », « 2/1/2023 », …, « 12/1/2023 »})
Result: 9.8% effective annual rate.
- Insurance Premium Financing
Scenario:
- Annual premium: $1,000
- Quarterly alternative: 4 payments of $262.50
Calculation:
=XIRR({737.50, -262.50, -262.50, -262.50, -262.50}, {« 1/1/2023 », « 4/1/2023 », « 7/1/2023 », « 10/1/2023 », « 1/1/2024 »})
Result: 14.2% implied annual cost.
Key Features
- Real-World Applicability:
- Evaluates loans, investments, or leases with flexible payment schedules.
- Adjusts for exact calendar days (e.g., 28-day vs. 31-day months).
- Comparison Tool:
- Compare financing options (e.g., lump-sum vs. installments).
- Annualizes returns for irregular cash flows (e.g., venture capital).
- Limitations:
- Requires at least one inflow and one outflow.
- May fail with highly irregular cash flows (adjust Guess if needed).