# How to Use the XIRR Function in Excel (Explained Easy)

The name of this function might be a little spooky, I get it – but the applicability of the XIRR function is very practical and widespread.

The XIRR function in Excel is a powerful financial function of Excel that returns the internal rate of return for a series of cash flows, even if they occur at irregular intervals.⏳

The IRR of an investment helps you make informed financial decisions by evaluating the profitability of your investments and projects. This guide will answer all your questions about the XIRR function.

So, download your free practice workbook for this tutorial here and read till the end to learn all about the XIRR function of Microsoft Excel.

## What is the XIRR function

The XIRR function is an advanced version of the IRR function.

It is designed to calculate the Internal Rate of Return (IRR) for a series of cashflows occurring at irregular intervals.

You give it a series of cashflows, and it calculates the implied internal rate of return for them.💵

### Syntax

The syntax of the XIRR function is all about three arguments:

**Values:** The series of cashflows. To be supplied as a range of cell references containing the cashflows.

**Dates:** The dates when these cashflows occurred.

**Guess (Optional argument):** A guess of what you think the IRR for the given cashflows would be. If omitted, Excel assumes it to be 10%.

## Using the XIRR function of Excel

Time we see a practical example of running the XIRR function in Excel.

Suppose I invest in a business on 01 January 2024. Per my observations of the relevant industry and economy, I forecast the business to generate cashflows at multiple intervals over the coming 7 years.📆

Here is what the forecasted cashflows look like.

Take a while to note the irregularity in the periods when cashflows occur. The first cash flow occurs 3 years after the investment, the second after another year, the same goes for the third, the fourth after the lapse of six months there onwards, and so on. There’s no regular interval between these cashflows.

What is the rate of return earned by this investment?

Let’s calculate.

**Step 1)** Write the XIRR function as below.

**Step 2)** Refer to the cells containing the cashflows as the value argument.

**Step 3)** Refer to the range of cells containing the dates when the cash flows occur.

Ensure the dates you supply as the “dates” argument are appropriately formatted as dates.

**Step 4)** Leave the guess argument omitted.

**Step 5) **Press the Enter button to have the IRR computed.

If the answer comes out as a decimal number like above, don’t fret.

**Step 6)** Go to the Home tab> Number group > Click the percentage Icon.

This converts the IRR to a percentage.

So, we have the IRR computed as 12.53%.

Let’s quickly see if the IRR we calculated works as it should.

**Step 7)** Calculate the number of years between the first date of your cashflows (01 January 2024) and the date of each cash flow by using the following formula.

**Step 8)** Drag it down to the whole list.

Excel assumes the earliest date of the series of dates as today’s date and calculates the IRR accordingly.

**Step 9)** Calculate the present value factor for each cash flow by using the following formula.

**Step 10)** Use an absolute reference for the IRR and drag the formula down the whole list.

**Step 11)** Divide each cash flow by its PV factor.

We have all present values calculated.

We did not calculate the PV for the value of initial investment ($1000) as Excel assumes 01 Jan 2024 to be today’s date. As this cash flow occurs on 01 Jan 2024, it is already at its present (today’s) value.

**Step 12)** Sum them up.

See that? The IRR for an investment is meant to calculate a rate where the present value of its cash inflows equates to the present value of its outflows.

When netted, the answer must be zero.

The above math tells the XIRR returned precisely accurate results.

## XIRR vs. IRR

The only difference between the XIRR and IRR functions is the additional “dates” argument.

The XIRR function allows you to manually supply the dates for when the cashflows occurred hence, even if they occur at irregular intervals, it is catered to📅

However, the IRR function allows no such argument and assumes that all cash flows occurred at regular, periodic intervals.

To understand this through an example, I have redone the dates in our above example to bring them to periodic intervals.

Pay attention to note that all the dates are now periodically arranged at 1-year intervals.

**Step 1)** Calculate the IRR for these cashflows and dates using the XIRR function.

Comes out as 20.979%.

**Step 2)** Now calculate the IRR for these cashflows using the IRR function.

**Step 3)** Since the IRR function doesn’t have any date argument, this is it. Press Enter.

Comes out as **20.975%.**

Both the IRRs are the same (ignore the very minor 0.004% difference).

This tells us that if the cashflows occur periodically, the IRR and the XIRR function work out the same answer.⚖

However, the difference only comes when your cashflows occur at irregular intervals.

For example, if I calculate IRR using the IRR function for our example with cashflows occurring at irregular intervals, the results would have been:

While the XIRR function incorporates the irregularity in cashflow intervals and returns the IRR as **12.53%**, the IRR function fails to do so and still returns the IRR as **20.98%**.

## Conclusion

XIRR is a very smart and practical version of the IRR function that caters to the reality of irregular cashflows. It makes an important financial function that’s widely used by Excel users.

The IRR of any investment is a very important financial modeling metric, and it makes a prime part of investment-related decision-making.

Investors usually compare the IRR (Internal Rate of Return) of their investments with their Weighted Average Cost of Capital (WACC) to see if it is feasible to take up the investment or not. Other finance concepts commonly applied by investors include the concept of Net Present Value (NPV) and Compound Annual Growth Rate (CAGR).