XIRR in Excel (Table of Contents)
XIRR in Excel
The XIRR function is under as financial functions category and will calculate the internal rate of return (XIRR) for a series of cash flows that may not be periodic by assigning specific dates to each individual cash flow. The main benefit of using XIRR function is that these unevenly timed cash flows can be accurately modeled.
In financial the XIRR function is useful in determining the value of an investment or understanding the feasibility of a project without periodic cash flows. It helps us to understand the rate of return earned on an investment. Hence, it is commonly used in finance, particularly when choosing between investments.
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, a 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 and therefore it can help to 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.
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 internal rate of interest so we consider 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, 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 amount for EMI. Then we can use the function XIRR for 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. The amount invested should be represented by a ‘minus’ sign. 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 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.
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 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
You can download this XIRR function Excel template here – XIRR Function Excel Template
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 –