How to Use XIRR Function in Excel (3 Examples)

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.

• 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.

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.

• 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.

• 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.

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 is 31% instead of 15%, we will get the XNPV value as 0.

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

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.