XIRR function

The XIRR function in Excel is used to calculate the internal rate of return (IRR) for a series of cash flows that are not necessarily periodic. This is especially useful for financial analysis involving irregular cash flows, such as investments or loans.

Syntax:

XIRR(values, dates, [guess])

Arguments:

1. values: An array of cash flows corresponding to the payment schedule. The first value is typically negative (representing an investment or payment), and subsequent values are positive (returns or receipts).
2. dates: A series of dates corresponding to the cash flows in values. These must match the order of the values array.
3. guess (optional): An initial guess for the IRR. If omitted, Excel defaults to 10% (0.1).

Important Notes:

• Cash flows must include at least one positive and one negative value.
• Dates must be valid and sequential, but they don’t need to have equal intervals.
• The function iteratively calculates the IRR, and it might return a #NUM! error if a solution can’t be found or the inputs are invalid.

Example:

Suppose you have the following cash flow data:

Formula:

=XIRR(B2:B5, A2:A5)

Where:

• B2:B5 contains the cash flows.
• A2:A5 contains the corresponding dates.

Result:

Excel will return the annualized IRR as a percentage, representing the rate of return for the given cash flows.

Common Errors:

• #NUM!: Occurs if there are invalid inputs or no solution is found.
• #VALUE!: Happens if the dates are not valid or non-numeric data is present in values.