If you want to know how to use the **XIRR** function in Excel, you are in the right place. Here, we will walk you through **3 **usages of the **XIRR** function in Excel.

The** XIRR** function belongs to the financial functions category in Excel. This function finds out the Internal Rate of Return (IRR) for a stream of a non-periodic cash flow. This function is commonly used in financial analysis, investment planning, and capital budgeting.

Here, we will also describe the difference between **XIRR** and** IRR **functions. Also, we will explain the difference between **XIRR** and **XNPV** functions.

In addition to this, we describe what you will do when the **XIRR** function will not work.

In the following overview image, you can see how the** XIRR** function works. So letâ€™s dive with us to explore the **XIRR** function.

## Introduction to The XIRR Function

**Function Objective:**

The Excel **XIRR** function is a financial tool that calculates the internal rate of return (IRR) for a series of cash flows. The cash flows are received at varying time intervals. It uses iteration to arrive at a result. It also assigns specific dates to each cash flow.

There is a connection between the** XIRR** and **XNPV** functions in Excel. Specifically, the **XIRR** function calculates the interest rate at which the net present value (XNPV) of a series of cash flows becomes zero.

**Syntax:**

**=XIRR(values, dates, [guess])**

**Â ****Argument Explanation:**

** **

**Return Parameter:**

**Â **Calculated internal rate of return (IRR).

**Available In**

The** XIRR **function is available in **Excel 2007** and all the higher versions of Excel.

## Important Things to Know About XIRR Function

- To use the XIRR function, it is necessary to have at least one instance of incoming cash (positive cash flow) and one instance of outgoing cash (negative cash flow). If not, the function will return an error marked as
**#NUM!** - If any of the values entered as dates are not recognized as valid dates, the function will generate an error marked as
**#VALUE!** - XIRR returns the
**#NUM!**error value if any following date is prior to the starting date. - If there is a discrepancy in the number of values and dates entered, the function will generate an error marked as
**#NUM!** - The XIRR function in Excel is specifically created to determine the internal rate of return for cash flows that occur at different intervals. If the exact payment dates for periodic cash flows are unknown, the
**IRR**function can be utilized instead. - The XIRR function will consistently provide an annualized internal rate of return, even if the cash flows are occurring on a monthly or weekly basis.

## How to Use XIRR Function in Excel: 3 Examples

In the following dataset, you can see that we have the **Periods**,** Cash Flow**, and **Date** columns. Here, we have both cash inflow and cash outflow in the **Cash Flow **columns. Also, the dates are valid dates and in the right order.

Now, using this dataset we will go through **3 **examples to use the **XIRR** function in Excel.

Here, we used **Excel 365**. You can use any available Excel version.

### 1. Using Basic XIRR Function in Excel

Here, we will describe how you can use the basic XIRR function in Excel.

- First of all, we will type the following formula in cell
**D14**.

`=XIRR(C5:C12,D5:D12)`

- After that, press
**ENTER**.

Therefore, you can see the internal rate of return in cell **D14**.

### 2. Applying XIRR Function with Initial Guess

If you have a precise understanding of the investment’s rate of return or a well-informed prediction of it, you can utilize your forecast as an estimation. This approach can be particularly useful when the** XIRR **formula returns a **#NUM!** error.

Here, we have modified our previous dataset. For this dataset, the XIRR function without the guess of the initial rate of return, returns an error.

If we can guess the initial rate of return, then using it will help us avoid this error.

Here, our guess for the initial rate of return is** -20%**. This guess is our third argument.

- Now, in cell
**D14**, we have written the formula below with our guess for the initial rate of return as the third argument of the XIRR.

`=XIRR(C5:C10,D5:D10,-20%)`

Here,

-20%Â is the Guess, which is our Guess for the initial rate of return as the third argument of the function.

- After that, we press
**ENTER**.

Then, a value of –**12%** is returned in cell** D14**.

The anticipated return rate (-20%) used as a guess argument helps the XIRR function to arrive at the result.

### 3. Use of XIRR Function for Monthly Cash Flows

In the following dataset, you can see that the cash flow is month based instead of year based.

You can see when we try to find out the internal rate of return in cells using the XIRR function in cell **D13**, it gives a value of **179%**.

Here, **179% **is actually an annual rate of return. Since the XIRR function cannot calculate the monthly rate of return, this happens.

Do not worry, we will go through **3** ways to get rid of the situation. And therefore, we will find out the return rate for a monthly cash flow dataset.

- Next, we type the following formula in cell
**D14**to find the monthly IRR.

**=(1+D13)^(1/12)-1**

- After that, press
**ENTER**.

Thus, you can see the monthly IRR in cell **D14**.

Another way to find the monthly IRR is to insert the XIRR function directly in the equation.

- Here, to do so, we type the following formula in cell
**D15**.

`=(1+XIRR(C5:C10,D5:D10))^(1/12)-1`

- Then, press
**ENTER**.

Thus, you can see the monthly IRR in cell** D15**.

Finally, we will use the **IRR** function to find out the monthly IRR.

- Therefore, we will type the following formula in cell
**D16**.

`=IRR(C5:C10)`

- Moreover, when you press
**ENTER**, you will see the monthly IRR in cell**D16**.

## XIRR Function vs. IRR Function in Excel

Here, we will describe the difference between XIRR and IRR functions.

**XIRR Function: **The **XIRR **function enables you to specify a date for each separate cash flow. Thus, you can compute the IRR for cash flows that don’t occur at regular intervals.

**Â ****IRR Function: **The** IRR** function presumes that each period within a sequence of cash flows is of equal length. This function is suitable for computing the IRR for cash flows that occur regularly, such as on a monthly, quarterly, or annual basis.

In cases, when the precise dates of cash payments are known, it is recommended to use the** XIRR **function. This is because the **XIRR** function offers more accurate calculations.

When cash payments are made at fixed and consistent intervals, the results returned by the functions are similar.

For a non-periodic cash flow, there can be a substantial difference between the results produced by the functions.

## XIRR Function Vs. XNPV Function in Excel

The XIRR function computes the IRR for cash flows that don’t occur at regular intervals. On the other hand, the **XNPV** function finds out the present value of a set of cash flows at a fixed rate of return.

XIRR and XNPV are closely connected functions. The interest rate that corresponds to **XNPV = 0 **is the rate of return computed by XIRR.

Here, in the following picture, you can see the XIRR result in cell **D14**.

- We have used the following formula to find the IRR value in cell
**D14**.

`=XIRR(C5:C12,D5:D12)`

After pressing **ENTER**, we get the output in cell **D14**.

Now, it’s time to find out the **XNPV** result. We need a discount rate to find out the **XNPV**.

Here, we take a **15%** discount rate in cell **D15**.

- Thus, we type the following formula in cell
**D15**.

**=XNPV(D15,C5:C12,D5:D12)**

- After pressing
**ENTER**, we can see the**XNPV Result**in cell**D15**.

Now, if we use the **XIRR Result** as the discount rate, which i**s 31%** instead of **15%**, we will get the **XNPV** value as 0.

- To show this, we type the following formula in cell
**D16**.

`=XNPV(D14,C5:C12,D5:D12)`

Here, **D14** is the **XIRR Result**, which is **31%**.

- Then, we press
**ENTER**.

Thus, you can see the XNPV Result is **0**.

## XIRR Function is Not Working in Excel

If your XIRR function is not working, consider the following key factors to investigate.

**Â ****#NUM! Error**

The following reasons are responsible for a **#NUM!** error.

- The number of columns or rows for the Cash Flows and date ranges are not equal in length.
- The cash flow column does not contain at least one incoming (+) and one outgoing (-) cash flow.
- Any of the following dates are prior to the starting date.
- A solution is not reached within 100 iterations. In this case, try using an alternative guess.

**Â ****#VALUE! Error**

The following reasons are responsible for a** #VALUE!** error.

- Cash flow values are not numeric.
- Dates are not input as valid dates.

## Things to Remember

- The
**XIRR**function calculates the internal rate of return for a series of cash flows that occurred at irregular intervals. - The first payment is optional and corresponds to a cost or payment that occurs at the beginning of the investment. If the first value is a cost or payment, it must be a negative value.
- Payments are expressed as positive values. All succeeding payments are discounted based on a 365-day year.

**Download Practice Workbook**

You can download the Excel file from the link below.

## Conclusion

In this article, we describe how you can use the XIRR function in Excel. We extensively describe 3 examples of this.

We also describe important facts about the **XIRR** function which you will find effective. In addition to this, we describe the differences between **XIRR** and **IRR** functions, and** XIRR** and **XNPV** functions.

Along with that, we explain the reasons that are responsible when the **XIRR** function is not working.

We hope you find this article helpful. If you have any queries or suggestions, please let us know in the comment section.

**<< Go Back to Excel Functions | Learn Excel**