How to Use XIRR Function in Excel (3 Methods)

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;

How to Use XIRR Function in Excel


Download Practice Workbook

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


Introduction to the XIRR Function

The XIRR Function in Excel

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.

How to Use XIRR Function in Excel


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)

Basic XIRR Function in Excel

Step 2:

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

How to Use XIRR Function in Excel

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)

Complete Form of the XIRR Function

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.

How to Use XIRR Function in Excel

Step 2:

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

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


Similar Readings


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)

Monthly Cash Flows

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

Monthly Cash Flows

  • Another way is to insert the XIRR formula directly in the equation:
=(1+XIRR(E5:E10,F5:F10))^(1/12)-1

Use of XIRR Function for Monthly Cash Flows

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

procedures


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!!!


Related Articles

ASM Arman

ASM Arman

Hi there! I am ASM Arman. I Completed B.Sc. in Naval Architecture and Marine Engineering. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations. Have a great day!!!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo