XIRR in Excel (Table of Contents)

• XIRR in Excel
• XIRR Formula in Excel
• How to Use XIRR in Excel?

XIRR in Excel

The Xirr function in excel 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 the XIRR function in excel is that these unevenly timed cash flows can be accurately modeled.
In financial the XIRR function in Excel is useful in determining the value of an investment or understanding the feasibility of a project without periodic cash flows. It helps us understand the rate of return earned on an investment. Hence, it is commonly used in finance, particularly when choosing between investments.

Returns

XIRR is popular in spreadsheet analysis because, unlike the XIRR function in Excel, it allows for uneven periods of time between compounding dates. XIRR in Excel is especially useful in financially 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 in Excel allows for the exact number of days in the first period and therefore can help assess the effect of a delay in the partnership formation date within the same month.
XIRR in Excel 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 in excel 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 in Excel

How to Open XIRR Function in Excel

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 in Excel

Click on the cell where you want to result value then put the formula as below mention

= XIRR (cash flow value range, date value range) > Enter

XIRR formula in excel 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 in excel is very simple easy to use. Let us now see how to use XIRR in Excel with the help of some examples.

XIRR In Excel Example #1

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 %.

XIRR In Excel Example #2

Suppose you are given a loan, details are in below mention table, loan of rupees 6000 which showing a minus sign(-), and receive date is 2 Feb-18, after that rest date and amount for Emei. 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 in Excel 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 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 errors 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 precedes 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

Recommended Articles

This has been a guide to XIRR Function in Excel. Here we discuss the XIRR Formula in Excel and how to use XIRR in Excel along with practical examples and downloadable excel templates. You can also go through our other suggested articles –

1. COLUMN in Excel
2. LOOKUP Function in Excel
3. Excel Shortcuts – Selection, data, and formula
4. CHOOSE in Excel

The post XIRR In Excel appeared first on EDUCBA.