XIRR

Formulas / XIRR
Calculate the internal rate of return for an irregular cash flow.
=XIRR(Values, Dates, [Guess])
  • values - required, a series of values
  • dates - required, a series of dates that corresponds to the values argument

Examples

  • =XIRR(A3:A7, B3:B7, 0.1)

    For example, this formula returns the internal rate of return for a series of cash flows that is not necessarily periodic. The function takes into account the date of each cash flow and calculates the rate of return based on the net present value of the investment.

  • XIRR(A3:A7, B3:B7, 0.1)

    For instance, let’s say you have a series of cash flows (A3:A7) that you want to calculate the internal rate of return for. You can use the XIRR function to do so. This example takes your cash flow series as its first two arguments, and the third argument is an estimate of the rate of return.

  • XIRR(A3:A7, B3:B7, 0.1)

    Another example of using the XIRR function is if you have a series of cash flows in a non-periodic format. For example, this will calculate the internal rate of return for a series of cash flows that is not necessarily periodic. The function takes into account the date of each cash flow and calculates the rate of return based on the net present value of the investment.


Summary

The XIRR function is an advanced tool for calculating the internal rate of return for a set of cash flows that may not follow a regular pattern. It can be used to calculate the rate of return for investments that are not consistent. However, it will throw an error if the dates provided are invalid.

  • The XIRR function calculates the internal rate of return for a series of cash flows that occur at irregular intervals by iterating to arrive at a result.
  • The arguments for the XIRR function are values, dates, and guess. The values argument represents a series of cash flows over a period of time and the dates argument represents a schedule of dates that correspond to cash flows for a financial instrument. The guess argument is optional.

Frequently Asked Questions

What is the XIRR function?
The XIRR function is a financial calculation that calculates the internal rate of return for a schedule of cash flows that is not necessarily periodic. It works similarly to the IRR function, which calculates the internal rate of return for a series of periodic cash flows.
When should I use the XIRR function?
The XIRR function is useful when you want to calculate the internal rate of return for a series of cash flows that occur at irregular intervals. It can also be used to compare two investments of different lengths.
What are the inputs of the XIRR function?
The XIRR function requires two inputs: the cash flow amounts and the dates on which the cash flows occur. The cash flow amounts can be positive or negative, and the dates must be in descending order.
How does the XIRR function work?
The XIRR function uses a numerical approximation algorithm to calculate the internal rate of return for a series of cash flows. It starts with an initial guess of the internal rate of return, and then iteratively adjusts the guess until the present value of the cash flows is close to zero.
What are the advantages of using XIRR?
  • It can be used to compare investments of different lengths.
  • It is more accurate than the IRR function when the cash flows are not periodic.
  • It is easy to use and understand.

Related Functions

Sourcetable Logo

Work smarter

Al is here to help. Leverage the latest models to
analyze spreadsheets, enrich data, and create reports.

Drop CSV