How to use XIRR formula in Excel?

The XIRR function in Excel calculates the internal rate of return for a series of cash flows that are not necessarily periodic. It's an essential tool for evaluating investments with irregular payment schedules.

How to Use XIRR Formula in Excel?

The XIRR formula in Excel helps you determine the annual internal rate of return for a series of cash flows, accounting for the exact dates of each payment. Unlike the simpler IRR function, XIRR handles situations where cash flows occur at irregular intervals, providing a more accurate annualized return.

Understanding the XIRR Formula Arguments

The XIRR function requires three arguments: values, dates, and an optional guess.

values (Required): This is a series of cash flows that correspond to a schedule of payments on specific dates.
- It must include at least one positive cash flow (inflow) and one negative cash flow (outflow).
- The initial investment is typically represented as a negative number.
- Subsequent cash receipts or payments are entered as positive or negative numbers, respectively.
- For instance, if you invest $10,000, this would be -10000. If you receive $2,000, this would be 2000.
dates (Required): This is a schedule of payment dates that corresponds to each cash flow in the values argument.
- Each date must correspond directly to its respective cash flow.
- The order of dates is crucial; they should be entered chronologically, with the earliest date first.
- Excel stores dates as sequential serial numbers, so ensure your cells are formatted as dates.
guess (Optional): This is a number that you estimate is close to the result of XIRR.
- If omitted, Excel uses a default value of 0.1 (10%).
- Providing a guess can sometimes help Excel find a solution, especially if your cash flows are unusual or if the function returns a #NUM! error. A reasonable guess might be between 0 and 1 (0% to 100%).

Step-by-Step Guide to Using XIRR

Follow these steps to calculate the XIRR for your investment:

1. Organize Your Data

Set up your cash flow data in two adjacent columns in an Excel worksheet: one for the dates and one for the corresponding cash flows.

Example Data Setup:

Date	Cash Flow
1/1/2023	-10000
6/30/2023	2500
12/31/2023	3000
6/30/2024	3500
12/31/2024	4000

Note: The initial investment (e.g., -10000) must be a negative value.

2. Enter the XIRR Formula

Once your data is organized, select an empty cell where you want the XIRR result to appear and type the formula.

Using the example data above, assuming:

Dates are in cells A2:A6
Cash Flows are in cells B2:B6

The formula would be:
=XIRR(B2:B6, A2:A6)

If you want to include a guess, for example, 10%:
=XIRR(B2:B6, A2:A6, 0.1)

3. Interpret the Result

The XIRR function will return a decimal value representing the annual internal rate of return.

To display it as a percentage, format the cell containing the XIRR formula as a "Percentage" style (e.g., using the % button on the Home tab).
For example, if the formula returns 0.0825, formatting it as a percentage will show 8.25%.

Practical Insights and Tips

Initial Investment: Always ensure the first cash flow (representing the investment) is a negative number. This is crucial for the formula to correctly identify an outflow.
Chronological Order: Dates must be in chronological order from earliest to latest. If they are not, XIRR might return an error or an incorrect result.
Error Handling (#NUM! or #VALUE!):
- #NUM! Error: This usually means XIRR cannot find a valid internal rate of return. This can happen if there isn't at least one negative and one positive cash flow, or if the guess is too far off. Try adjusting your guess value.
- #VALUE! Error: This typically occurs if any of your dates are invalid (e.g., text instead of a date) or if the values and dates ranges are not of the same length or do not correspond correctly.
Relationship to NPV: The XIRR is the discount rate at which the Net Present Value (NPV) of the irregular cash flows is zero. You can verify this using the XNPV function in Excel.
Comparison to IRR:
- IRR: Used for cash flows that occur at regular, periodic intervals (e.g., monthly, annually).
- XIRR: Used for cash flows that occur at irregular intervals, making it more flexible and accurate for real-world investment scenarios.

Example Walkthrough

Let's use the example data from above to calculate XIRR:

Cell	Date	Cash Flow	Description
A2	1/1/2023	-10000	Initial Investment
A3	6/30/2023	2500	First Cash Inflow
A4	12/31/2023	3000	Second Cash Inflow
A5	6/30/2024	3500	Third Cash Inflow
A6	12/31/2024	4000	Final Cash Inflow

In cell C2, enter the formula:
=XIRR(B2:B6, A2:A6)

The result will be approximately 0.08254. Formatting this cell as a percentage will display 8.25%. This means your investment generated an annualized return of 8.25%.

For more detailed information on Excel functions, you can refer to the Microsoft Support documentation.