The internal rate of return (IRR) is a financial metric used to evaluate the profitability of an investment project. It represents the discount rate at which the net present value (NPV) of future cash flows becomes zero, meaning the investment breaks even, with the higher the IRR, the more attractive the investment opportunity (To learn how to calculate the NPV in Excel click here).
The difference between the two functions, IRR and XIRR, relates to the way they handle the timings of the cash flow in the calculation.
Download File
If you would like to follow along download the attachment below.
What is IRR
The Internal Rate of Return (IRR) is the discount rate that makes the net present value (NPV) of a series of cash flows equal to zero. It assumes equal time intervals (e.g. monthly, yearly) between cash flows.
The IRR is a complicated calculation:
Thankfully Excel significantly simplifies this allowing you to calculate NPV with the following function:
=IRR(values, [guess])
values: An array or range of cash flows (including the initial investment as a negative value).
guess (optional): An initial estimate of the IRR (If left empty it defaults to 10%)
How does the IRR Function Work
To use the IRR function you will need a dataset showing a cash flow, you will not need to include dates as the function will default to assuming the cash flows occur at regular intervals.
To use the IRR function to calculate the IRR for this dataset all that is needed is to select the cash flows in cells A2 to A8.
This calculation will return an IRR of 5%.
What is XIRR
The XIRR (Extended Internal Rate of Return) function accounts for cash flows occurring at irregular intervals. This is needed to calculate the IRR for investments where there is not an equal time period between cash flows.
The syntax for the function is:
=XIRR(values, dates, [guess])
values: An array of cash flows.
dates: A corresponding array of dates.
guess (optional): An initial estimate of the IRR (If left empty it defaults to 10%).
How does the XIRR function work
Unlike the IRR function it is compulsory to enter a date range into the XIRR function.
This is because the IRR function assumes equal time periods between cash flows, but the XIRR needs to know the non-continuous dates you want to use in the calculation.
Since XIRR considers actual dates, it provides a more accurate IRR calculation when cash flows are unevenly spaced.
To use the XIRR function you will need a dataset showing a cash flow, as well as the corresponding dates.
To use the XIRR function to calculate the IRR for this dataset the cash flows in cells B2 to B8 will need to be selected in the values segment of the function, and cells A2 to A8 will need to be selected in the dates segment.
This calculation returns an IRR of 21%.
Conclusion
Both the IRR and XIRR function calculate the internal rate of return, but treat the time intervals differently, with the IRR function assuming equal time intervals between cash flows, and the XIRR function needing specific dates to be provided for the cash flows.
When choosing between the two, use IRR for projects with consistent cash flow timing and XIRR when cash flows are spaced unevenly.
Comentários