The XIRR function in Google Sheets is a powerful tool that calculates the internal rate of return for a series of cash flows that occur at uneven intervals. This function is especially useful for financial analysts, investors, and anyone dealing with varied investment transactions over time, as it provides a more accurate assessment of profitability compared to regular interval calculations.

Syntax

XIRR(cash_flows, dates, [guess])

• cash_flows: An array or range of cash flow values corresponding to the investments or returns received.
• dates: An array or range of dates associated with each cash flow. These dates must be in chronological order.
• guess: (Optional) A guess for the expected internal rate of return. If omitted, a default of 0.1 (or 10%) is used.

Example #1

=XIRR(A2:A6, B2:B6)

This function calculates the internal rate of return for the cash flows listed in cells A2 to A6, using the corresponding dates in B2 to B6. For instance, if A2:A6 contained the values {-5000, 1500, 1800, 2000, 2200} and B2:B6 had the dates {01/01/2020, 01/01/2021, 01/01/2022, 01/01/2023, 01/01/2024}, the function would yield a return of approximately 6.54%.

Example #2

=XIRR({-10000, 2500, 5000, 7000}, {"2020-01-01", "2020-06-30", "2021-01-15", "2022-06-30"})

Here, the internal rate of return is calculated for an investment of $10,000 with cash inflows at different times. Using these values, the function would return approximately 31.73%.

Example #3

=XIRR(A1:A5, B1:B5, 0.05)

In this case, cash flows and their respective dates are referenced from A1:A5 and B1:B5, respectively. The guess for the rate is set at 5%. If the cash flows were {-1200, 400, 300, 500} with respective dates spaced irregularly, the internal rate of return would be approximately 14.82%.

Error handling

• NUM!: This error indicates that the function cannot find a valid result for the internal rate of return. This usually occurs when the cash flows and dates are not compatible or lead to a mathematically impossible calculation.
• VALUE!: This error suggests that the function has encountered a non-numeric value in either the cash flows or dates range. Ensure all entries are valid and numeric where expected.
• DIV/0!: This occurs if there is not enough cash flow data to calculate the rate. Ensure that there is at least one positive and one negative cash flow.

Conclusion

In summary, the XIRR function is an essential tool for calculating the internal rate of return for investments with irregular cash flows. By utilizing this function, users can gain valuable insights into the returns of their investments, making it easier to make informed financial decisions based on accurate data.