ASM Arman
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;
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 guess argument helps the 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!!!
