XIRR Function

Get the Internal Rate of Return (IRR) for a series of cash flows that may not be periodic

What is the XIRR Function?

The XIRR function is categorized under financial functions and will calculate the Internal Rate of Return (IRR) for a series of cash flows that may not be periodic by assigning specific dates to each individual cash flow. The main benefit of using the XIRR Excel function is that these unevenly timed cash flows can be accurately modeled. To learn more, read why to always use XIRR over IRR in Excel modeling.

In financial modeling, the XIRR function is useful in determining the value of an investment or understanding the feasibility of a project without periodic cash flows. It helps us understand the rate of return earned on an investment. Hence, it is commonly used in finance, particularly when choosing between investments.

 

Formula

=XIRR(values, dates,[guess])

 

The formula uses the following arguments:

  1. Values (required argument) – It is the array of values that represent the series of cash flows. Instead of an array, it can be a reference to a range of cells containing values.
  2. Dates (required argument) – It is a series of dates that correspond to the given values. Subsequent dates should be later than the first date as the first date is the start date and subsequent dates are future dates of outgoing payments or income.
  3. [guess] (optional argument) – It is an initial guess of what the IRR would be. If omitted, Excel takes the default value that is 10%.

 

Excel uses an iterative technique for calculating XIRR. Using a changing rate (starting with [guess]), XIRR cycles through the calculation until the result is accurate within 0.000001%.

 

How to use the XIRR Function in Excel?

To understand the uses of the XIRR function, let’s consider a few examples:

 

XIRR Example

Suppose a project started on January 1, 2018. The project gives us cash flows in the middle of the first year, after 6 months, then at end of 1.5 years, 2nd year, 3.5th year and annually thereafter. The data given is shown below:

 

XIRR Function

 

The formula to use will be:

 

XIRR Function - Example 1

 

We will leave the guess as blank so Excel takes the default value of 10%.

We get the result below:

 

XIRR Function - Example 1a

 

Things to remember about the XIRR Function 

  1. Numbers in dates are truncated to integers.
  2. XNPV and XIRR are closely related. The rate of return calculated by XIRR is the interest rate corresponding to XNPV = 0.
  3. Dates should be entered as references to cells containing dates or values returned from Excel formulas.
  4. #NUM! error – Occurs if either:
    1. The values and dates arrays are of different lengths;
    2. The given arrays do not contain at least one negative and at least one positive value;
    3. Any of the given dates precedes the first date provided; or
    4. The calculation fails to converge after 100 iterations.
  5. #VALUE! error – Occurs when either of the dates given cannot be recognized by Excel as valid dates.

 

Click here to download the sample Excel file

 

Additional resources

Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources:

Free Excel Tutorial

To master the art of Excel, check out CFI's FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.  

Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!