XIRR Function
Calculates the internal rate of return for a schedule of cash flows that is not necessarily periodic.
Syntax
Parameters
| Parameter | Description | Required |
|---|---|---|
| values | Series of cash flows corresponding to the dates. | Required |
| dates | Series of dates corresponding to the cash flows. | Required |
| guess | Initial guess for the rate (default 10%). | Optional |
Basic Example
IRR for irregular cash flows
=XIRR(B2:B5, A2:A5)A2:A5 contains dates, and B2:B5 contains the corresponding cash flows.
Advanced Examples
Example 1: Add a guess
Non-converging IRRProvide a guess to help the solver converge
=XIRR(B2:B5, A2:A5, 0.2)How XIRR Works
XIRR solves for the rate that makes the net present value of the irregular cash flows equal to zero. It uses an annualized rate based on the 365-day year.
Important Notes & Limitations
Dates must be valid and in chronological order.
Requires at least one positive and one negative cash flow.
Result is an annualized rate.
Common Errors & Fixes
#NUM! errorThe solver cannot converge or the cash flows have the same sign.Fix: Add a guess or check that cash flows have both signs.
#VALUE! errorDates are not valid or do not align with values.Fix: Ensure dates are valid Excel dates and match the values array.
Download Practice File
XIRR
xirrPractice XIRR with Real Data
Download a sample CSV file with pre-populated data and practice exercises for the XIRR function. Works in both Excel and Google Sheets.
File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps