XIRR Function in Excel
XIRR function is also one of the financial functions in excel which is used to calculate the Internal Rate of Return (IRR) in any series of intervals. The good thing about XIRR Function is, it considers only irregular series of cash flow or installments intervals, whereas most of the function in excel gives fine result when regular interval series is selected. XIRR function works when we assign the dates of obtained intervals whether it is regular or irregular and calculates the internal rate of return on that array of cash flow.
XIRR is popular in spreadsheet analysis because, unlike the XIRR function, it allows for uneven periods of time between compounding dates. XIRR function is especially useful in financial structures because the timing of the initial investment within the month can have a meaningful effect on the XIRR. For instance, large equity investment on the 1st of the month produces a much lower XIRR than the same sized equity investment on the last day of the same month. By allowing for variable periods of compounding, the XIRR Function allows for the exact number of days in the first period. Therefore, it can help assess the effect of a delay in the partnership formation date within the same month.
XIRR Function also has a few of its own unique flaws. Because it uses the internal actual date logic in excel, it is impossible to use the XIRR function with even monthly periods consistent with 30/360 loan interest calculations. Also, a well-known bug with the XIRR function as of this writing is that it cannot handle a set of cash flows that start with zero as its initial value.
XIRR Formula in Excel
Below is the XIRR Formula.
How to Open XIRR Function?
Click on Formula Tab > Financial > Click on XIRR
We Get A New Function Windows Showing In Below Mention Picture.
Then we have to enter the details of cash value and dates.
The shortcut of using the XIRR Formula
Click on the cell where you want the result value, then put the formula as below mention
= XIRR (cash flow value range, dates value range) > Enter
XIRR formula has the following arguments:
- Values (Required Argument) –It is the array of values that represent the series of cash flows. Instead of an array, it can be a reference to a range of cells containing values.
- Dates (Required Argument) – It is a series of dates that correspond to the given values. Subsequent dates should be later than the first date as the first date is the start date, and subsequent dates are future dates of outgoing payments or income.
- Guess (Optional Argument) –It is an initial guess of what the IRR would be. If omitted, excel takes the default value that is 10%.
The format of the result
This is the internal rate of interest, so we consider it as percent (%) number format.
How to use the XIRR Function in Excel?
This XIRR function is very simple and easy to use. Let us now see how to use XIRR in Excel with the help of some examples.
Calculating XIRR value using XIRR Formula =XIRR(H3: H9, G3: G9)
The answer will be 4.89%
In the above table, the interest inflows are irregular. Hence, you can use the XIRR function in excel to compute
The internal rate of interest on these cash flows, the investment amount is showing a minus sign. When you get the result, then change the format with %.
Suppose you are given a loan, details are mentioned in the below +table, loan of rupees 6000 which showing a minus sign(-), and receive date is 2 Feb-18, after that rest date and the amount for EMI. Then we can use the function XIRR for the internal rate return of this amount.
Calculating XIRR value using XIRR Formula =XIRR(A3: A15, B3: B15)
The answer will be 16.60%
In an excel sheet, first, enter the original amount invested. A ‘minus sign should represent the amount invested. In the following cells, enter the returns received during each period. “remember to include the ‘minus’ sign whenever you invest the money.
Now, find XIRR using the values that refer to a series of cash flows that correspond to a schedule of payments in dates. The first payment refers to the investment made at the beginning of the investment period and must be a negative value. All succeeding payments are discounted based on a 365-day-year. The series of values must contain at least one positive and one negative value.
The date stands for the day where the first investment was made and when the returns were received. Each date should correspond to its respective investment made, or income received, as shown in the above table. Dates should be entered in a ‘DD-MM-YY (date-month-year)’ format as errors can occur if the format is not followed. If any number in the dates is invalid, or the format of dates is inconsistent, XIRR will reflect the “#value!” Error.
If we discuss error problems, this is also an important part. If our data is not correct, we will face such a type of issue as below mention.
- #Num! Occurs if either:
- The supplied values and dates arrays have different lengths.
- The supplied values array does not contain at least one negative and at least one positive value;
- Any of the supplied dates proceeds the first supplied date
- The calculation fails to converge after 100 iterations.
- #value! : – occurs if any of the supplied dates can’t be recognized as valid excel dates.
- If you attempt to input dates in text format, there is a risk that excel may misinterpret them, depending on the date system or date interpretation settings on your computer.
This has been a guide to XIRR Function. Here we discuss the XIRR Formula and how to use XIRR Function along with practical examples and downloadable excel templates. You can also go through our other suggested articles –