EXCEL XIRR FUNCTION
Excel XIRR Function Introduction
The XIRR function is classified as a financial function since it computes the Internal Rate of Return (IRR) for a sequence of cash flows that may or may not be periodic by assigning precise dates to each individual cash flow. The primary advantage of utilising the XIRR Excel function is that these irregularly timed cash flows may be correctly predicted. To understand more, see Why XIRR is Always Better Than IRR in Excel Modeling. The XIRR function in financial modelling is useful for assessing the worth of an investment or analysing the viability of a project without monthly cash flows. It aids us in understanding the rate of return on an investment.As a result, it is frequently utilised in finance, particularly when deciding between assets.
Description of Excel XIRR Function
Syntax :
=XIRR (values, dates,[guess])
Parameters :
1. Values (mandatory parameter) – The array of values representing the sequence of cash flows. It can be a reference to a range of cells containing values instead of an array.
2. Dates (mandatory parameter) – It is a list of dates that match to the values supplied. Because the first date is the commencement date and subsequent dates represent future dates of outgoing payments or revenue, subsequent dates should be later than the first date.
3. [guess] (optional argument) – This is an initial estimation of the IRR. If this field is left blank, Excel uses the default value of 10%. For computing XIRR, Excel use an iterative method. XIRR goes through the computation using a changing rate (beginning with [guess]) until the result is correct to 0.000001 percent.
Step By Step Guide of XIRR Function
Example : Assume a project began on January 1, 2018. The project generates cash flows in the middle of the first year, after 6 months, then at the end of 1.5 years, the second year, the third year, and yearly after that. The following information is provided:
Key Points
1. Date numbers are reduced to integers.
2. XNPV and XIRR are related terms. The interest rate equivalent to XNPV = 0 is the rate of return determined by XIRR.
3. Enter dates as references to cells holding dates or values returned by Excel formulae.
4. #NUM! error – Occurs if any of following conditions is met:
1. The values and dates arrays are of different lengths;
2. The given arrays lack at least one negative and one positive value;
3. Any of the given dates before the first date provided; or
4. The computation fails to converge after 100 iterations.
5. #VALUE! error – Occurs when any of the dates provided is not recognised as a valid date by Excel.
Excel XIRR Function Template/Exercise
- Download XIRR Function Template
Related Functions
HLOOKUP Function
HLOOKUP Function
Explore NowVLOOKUP Function
VLOOKUP Function
Explore NowINDEX Function
INDEX Function
Explore NowMATCH Function
MATCH Function
Explore NowDATE Function
DATE Function
Explore NowIF Function
IF Function
Explore NowOR Function
OR Function
Explore NowAND Function
AND Function
Explore NowRANK Function
RANK Function
Explore NowSUMIF Function
SUMIF Function
Explore NowXIRR Function
XIRR Function
Explore NowWORKDAYS Function
WORKDAYS Function
Explore Now
Share this Content
Excel Functions
Excel Formulas
Excel Data Analytics
- AND
- FALSE
- IF
- IFEROR
- IFNA