If you work with the rate of returns for a series of cash flows, then the** Excel XIRR function** might come in handy. The Excel **XIRR** is a financial function that calculates the internal rate of return (IRR) for a series of cash flows that is not periodic. In financial modeling, the **XIRR function** might help estimate the value of investment or understand the feasibility of a project. In this article, we will learn how to use the **Excel XIRR function **easily. Here’s an overview of the function;

**Table of Contents**hide

**Download Practice Workbook**

Download this practice book to exercise the task while you are reading this article.

**Introduction to the XIRR Function**

**Function Objective:**

Excel XIRR is a **financial function** that returns the internal rate of return (IRR) for a schedule of cash flows that occurs at irregular intervals. It uses iteration to arrive at a result. It also assigns specific dates to each cash flow. The XIRR function is related to the Excel **XNPV** function. The rate returned by XIRR is the interest rate when **XNPV** = 0.

**Syntax:**

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

**Argument Explanation:**

Argument | Required/Optional | Explanation |
---|---|---|

values |
Required | An array of references to cells that contain cash flows. |

dates |
Required | Dates that correspond to cash flows, in any order. |

guess |
Optional | A guess for expected IRR. Default is 0.1(10%) |

**Return Parameter:**

Calculated internal rate of return (IRR)

**3 Suitable Methods to Use XIRR Function in Excel**

A small real estate company started a construction business that has irregular cash flows, for $20,000 on 1 January 2022. The income for 26 December 2022 is $4,000, the income for 10 April 2023 is $9,000, the income for 12 September 2024 is $8,000, the income for 6 June 2025 is $9,000, and the payment for 24 November 2026 is $5,000. We will use the Basic XIRR function in Excel, the Complete form of the function with our guess for the initial rate of return, and the **XIRR** function for monthly cash flow.

**1. Basic XIRR Function in Excel**

**Step 1:**

- In order to calculate the basic
**XIRR**, we have written the following formula in cell**F4**:

**
**

`=XIRR(B5:B10,C5:C10)`

**Step 2:**

- Upon pressing
**ENTER**, a value of**13.56%**is returned.

This indicates substantial profitability, for the periods given.

**2. Complete Form of the XIRR Function with Initial Guess**

If you have a clear idea about the rate of return from the investment or have an expectation or guess about it, you can use your expectation as a guess. It is especially helpful when a correct **XIRR** formula throws a **#NUM!** Error.

For the data shown below, an XIRR formula without the guess as the third argument returns an error:

`=XIRR(B5:B10,C5:C10)`

**Step 1:**

- If we can guess the initial rate of return, then using it will help us avoid this error. We will get the initial rate of return calculated by the
**XIRR**In the case above, our**guess**for the initial rate of return is**-20%**. This**guess**is our third argument. - We have written the formula below with our guess for the initial rate of return as the
**third argument**of the**XIRR**

`=XIRR(B5:B10,C5:C10, -20%)`

Here,

** -20%** = **Guess** = Our Guess for the initial rate of return as the** third argument **of the function.

**Step 2:**

- Upon pressing the
**ENTER**, a value of**-16.52%**is returned. - The anticipated return rate (-20%) used as
**a**argument helps the*guess***XIRR**function to arrive at the result.

**Read More:** **How to Use RATE Function in Excel (3 Examples)**

**Similar Readings**

**How to Use SLN Function in Excel (3 Examples)****Use NPV Function in Excel (3 Easy Examples)****How to Use Excel PMT Function (4 Quick Examples)****Use Excel PPMT Function (3 Suitable Examples)****How to Use FV Function in Excel (4 Easy Examples)**

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

Whatever cash flows you are calculating, the **XIRR** function in Excel produces an **annual rate of return**.

We can prove this by calculating IRR for the same series of cash flows (**B5:B10**). But one occurs monthly and another one yearly (the dates are in **C5:C10**):

`=XIRR(B5:B10,C5:C10)`

As you can see in the image above, the IRR goes from **10.30%** for annual cash flows to about **232%** for monthly cash flows! The difference seems too large to be justified only by the time value of the money factor.

To find an approximate **monthly XIRR**, we can use the below calculation.

**Step 1:**

- We have written down the below formula in cell
**F15**. Here cell**F13**stores the result of the regular XIRR function. In our case, it is**231.93%**.

`=(1+F13)^(1/12)-1`

- Another way is to insert the
**XIRR**formula directly in the equation:

`=(1+XIRR(E5:E10,F5:F10))^(1/12)-1`

- As the result of these calculations, we get a monthly
**XIRR**of**51%**

**Step 2:**

- To be certain, let’s use the
**IRR****function**for the same cash flows. Note that,**IRR**will also compute an approximate rate because it assumes that all the time periods are equal:

`=IRR(E5:E10)`

**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.
- All subsequent dates must be later than the starting date or else the
**XIRR**function will return the**#NUM!**Error value.

**Conclusion**

In this article, we have learned to use the** XIRR **function in Excel to calculate the internal rate of return for a series of cash flows occurring at irregular intervals. I hope now you have a clear idea of how to use the **XIRR **function to calculate the internal rate of return for such cash flows. If you have any queries or recommendations about this article, please do leave a comment below. Have a great day!!!