XIRR
AdvancedXIRR is a spreadsheet function that calculates the internal rate of return for a series of cash flows that occur on irregular dates, rather than at evenly spaced intervals.
The standard internal rate of return function assumes cash flows happen at regular intervals, such as yearly. Real investments often have cash flows on specific, uneven dates, such as investments and distributions in a private equity fund or irregular contributions to a portfolio.
XIRR takes each cash flow with its exact date and finds the annual rate of return that makes the net present value of all cash flows equal to zero, using the actual time between dates.
For example, an investor puts in 10,000 on 15 January, adds 5,000 on 3 August and receives 17,000 on 20 December of the following year. XIRR calculates the annualized return accounting for exactly when each amount was invested and received.
XIRR is widely used in private equity, real estate and personal investing. It is available in most spreadsheet programs, along with XNPV, which calculates net present value for irregular dates. Like the standard IRR, it can give misleading results for unusual cash flow patterns and assumes reinvestment at the calculated rate.
For worked examples and full lessons, visit notesbysara.org.