Excel XIRR Function
XIRR is an Excel function that lets us calculate internal rate of return without the inherent assumption of cash flows occurring at the end of each period of equal duration. XIRR let us enter the date on which the cash flows occur and calculates the present values using fractional years between two dates based on a 365-days year.
The standard Excel IRR function require that the cash flows occur at the peroid end and there is equal duration between each cash flow. XIRR is a better function to calculate internal rate of return because it allows accounting for exact date on which each cash flow occur.
Syntax
XIRR syntax is:
XIRR(values, dates, [guess])
Values argument refers to the stream of cash flows, dates argument refers to the corresponding schedule of dates. There must be a 1-on-1 correspondence between cash flows and dates. [guess] is an optional function.
XIRR and XNPV are sister functions, they both are flexible versions of IRR and NPV.
Example
The following example illustrates application of both functions:
Please note that even though the project duration is five years, a slight change in the exact timing of cash flows has resulted in different NPV and IRR.
Even though XIRR is flexible than IRR it still doesn’t address the problem with internal rate of return technique in general—that of reinvestment assumption at the IRR. If we believe the reinvestment rate must be lower than the IRR, we turn to MIRR—the modified internal rate of return.
by Obaidullah Jan, ACA, CFA and last modified on
