Excel Guides

How to Use the XIRR Function in Excel

The XIRR function in Excel is a great way to calculate the internal rate of return for a series of cash flows. The function can be used to calculate the IRR for a series of irregular cash flows, as well as for a series of regular cash flows. In this article, we'll take a look at how to use the XIRR function in Excel, and how to interpret the results.

How to Use the XIRR Function in Excel

To use the XIRR function in Excel, you need to have a data set that contains at least two columns: a column for the dates of the cash flows, and a column for the corresponding cash flow amounts. The dates can be entered as either dates or text strings that can be parsed as dates. The cash flow amounts can be positive or negative.

Once you have your data set up, you can enter the XIRR function into a cell. The syntax of the function is as follows:

XIRR(cash_flows, dates, [guess])

Where cash_flows is a range or array of cells that contain the cash flow amounts, dates is a range or array of cells that contain the corresponding dates, and [guess] is an optional guess for the IRR. If you omit the [guess] argument, Excel will use 0.1 (10%) as the default.

Once you've entered the function into a cell, you can press Enter to calculate the result. The result will be displayed as a percentage, and it represents the internal rate of return for your data set.

Example

To see how the XIRR function works in practice, let's take a look at an example. Suppose we have the following data set:

DateCash Flow ($)
1/1/2018-1000
2/1/2018200
3/1/2018600

This data set represents three cash flows: an initial investment of $1,000 on January 1st, 2018, followed by two additional payments of $200 and $600 on February 1st and March 1st.

To calculate the IRR for this data set using the XIRR function, we can enter the following formula into a cell:

=XIRR(B2:B4,A2:A4)

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.