XIRR Calculates the internal rate of return of an investment based on a specified series of potentially irregularly spaced cash flows.
What Is Internal Rate of Return (IRR)?
The internal rate of return is a metric used in financial analysis to estimate the profitability of potential investments. The internal rate of return is a discount rate that makes the net present value (NPV) of all cash flows equal to zero in a discounted cash flow analysis. IRR calculations rely on the same formula as NPV does.
- IRR is the annual rate of growth investment is expected to generate.
- IRR is calculated using the same concept as NPV, except it sets the NPV equal to zero.
- IRR is ideal for analyzing capital budgeting projects to understand and compare potential rates of annual return over time.
Formula and Calculation for IRR
The formula and calculation used to determine this figure are as follows.
Sample Usage of XIRR
XIRR(B2:B25,C2:C25)
XIRR({-4000,200,250,300},{DATE(2012,01,01),DATE(2012,06,23),DATE(2013,05,12),DATE(2014,02,09)},0.09)
Syntax
XIRR(cashflow_amounts, cashflow_dates, [rate_guess])
cashflow_amounts – An array or range containing the income or payments associated with the investment.
cashflow_amounts must contain at least one negative and one positive cash flow to calculate the rate of return.
cashflow_dates – An array or range with dates corresponding to the cash flows in cashflow_amounts.
rate_guess – [ OPTIONAL – 0.1 by default ] – An estimate for what the internal rate of return will be.
Notes
If the days specified in cashflow_dates are at a regular interval, use IRR instead.
Each cell in cashflow_amounts should be positive if it represents income from the perspective of the owner of the investment (e.g. coupons) or negative if it represents payments (e.g. loan repayment).
XNPV will return zero if the discount is set to the result of XIRR using the same cash flow amounts and schedule.
See Also
- XNPV: Calculates the net present value of an investment based on a specified series of potentially irregularly spaced cash flows and a discount rate.
- PV: Calculates the present value of an annuity investment based on constant-amount periodic payments and a constant interest rate.
- NPV: Calculates the net present value of an investment based on a series of periodic cash flows and a discount rate.
- MIRR: Calculates the modified internal rate of return on an investment based on a series of periodic cash flows and the difference between the interest rate paid on financing versus the return received on reinvested income.
- IRR: Calculates the internal rate of return on an investment based on a series of periodic cash flows.
Examples