XIRR and IRRinternal rate of return

The internal rate of return (IRR) is the discount rate at which the sum of an investment’s discounted cash flows equals zero. Put simply, it is the constant annual rate that turns your deposits into today’s portfolio value, taking into account when you put the money in.

XIRR is the version of IRR for cash flows at irregular intervals. Classic IRR assumes equal periods (e.g. monthly), while XIRR uses the exact dates and returns an annual rate. For an investment portfolio, XIRR is in practice the same as MWR, the money-weighted return.

XIRR in Excel and Google Sheets

• Put the dates in one column and the cash-flow amounts in another.
• Enter deposits into the portfolio as negative numbers and withdrawals as positive ones.
• In the last row, enter today’s date and the current portfolio value as a positive number.
• Use =XIRR(amounts, dates), e.g. =XIRR(B2:B10, A2:A10).

The result is an annual rate of return. If the function returns an error, check that the amounts include at least one negative and one positive value and that the dates are real dates, not text.

Limitations

• The result depends on the timing of deposits, so XIRR is not suited to comparing a portfolio with an index. Use TWR for that.
• Over short periods, the annual XIRR can be extremely high or low.
• With unusual cash flows (e.g. alternating deposits and withdrawals), the equation can have more than one solution.