XIRR Function

What Is The Excel XIRR Function?

The XIRR function stands for an extended internal rate of return. If you want to determine your rate of return on investments, projects, and cashflows, you will use the XIRR function. However, you would use the function if your cashflows are not periodic.

Periodic cash flows are a business's money coming in and out. When they are periodic, an equal amount of money is regularly coming in and out of business. For example, if you have a business and your business is earning forty dollars every month for a year, it can be considered a periodic cash flow. 

XIRR will not use a periodic cash flow; instead, it will calculate returns from different times. For example, cash can come in one month, three months, and two months later. 

If a business is interested in investing, consider calculating the extended internal rate of return. Finding the XIRR can help them contribute to their decision of whether to invest or not.

Understanding The XIRR Function

XIRR function will be used by people who know about Excel and finance modeling. In finance modeling, people will often use the extended internal rate of return to determine the return on their business investments. The function can also help determine if an investment is worth investing in. 

An example of how the XIRR works could be pretending you own a company. Your company wants to invest but has two options for where to invest. The investment gives you an XIRR of twenty-seven percent. 

This tells us that the extended internal rate of the return would be by twenty percent. The second investment gives you a total internal rate of return of ten percent. Judging by the XIRR, the best investment option is the one with the highest XIRR.

XIRR will consider the money of each date in the data set.

XIRR Function Formula

The syntax for the extended internal rate of return is 

When entering the formula in Excel, always start by joining an equals sign. If you do not, the formula may not show up. 

When you go on to Excel, the formula for the function should appear as follows:

Instead of typing the formula, you can find it in the Finance section. You can find this section under the Formulas tab. It will be your third option. Once you select the finance section, you can scroll down to see the formula XIRR. 

On Excel, you will always be able to either enter the formula manually or find it under the formulas tab.

The formula tells you to enter the following arguments: values, dates, and guesses. 

• Values: For this required argument, you will enter the cash flows in your data that belong to the varying dates. 
• Dates: These are the required times the cash flow was recorded.
• Guess: For this argument, it is a guess or estimate that can be close to the result of the XIRR and is optional. 

The argument value and dates in the formula are required. The guess argument is optional.

There are other things to look out for when entering the arguments in the formula. Excel sometimes has some exceptions when it comes down to its formulas. 

When entering the values on your formula, the first cash flow from your data is optional to include. It is optional because the first cash flow is usually tied to a cost or payment at the beginning of the investment. In case it is, it will be entered as a negative value. 

For the dates that are required, they can be entered in any order. There is no need for them to be in order. However it is recommended that when entering a date on your data, the DATE function enters the date in Excel.

There are a few things to remember when using the extended internal rate of return on Excel. 

1. The numbers you enter into the dates will be cut short to whole numbers. 
2. The function XNPV function is nearly correlated to the function XIRR. 
3. Since the guess argument is primarily optional, the guess will be assumed as ten percent if you do not enter the argument.

How To Use The XIRR Function In Excel

We will use cells from the "A" and "B" columns. The numbers under the value section will be our cash flows. Under the cell named dates, there are dates on which the cash flows were recorded. 

Notice how the first cash flow on the data is entered as unfavorable because this was the investment's first cash flow correlated with a payment or cost.

Next, you will click on the cell where you want your formula. After selecting the cell, begin typing your formula; when you reach the value entry, like all the cells with cash flows, you want to include in your calculations.

Next, type a comma after the selected values and select the dates that correspond to the values. 

Now this next step, as mentioned, is optional. After the date selection, type in another coma if you include the guess. If we decide to enter the guess entry, our guess will be ten percent in a decimal form of 0.10. 

If your number came out in decimal form, you could change it into a percentage. To change it to a rate, you can right-click on your mouse or click your pad with two fingers if you use a Macbook. 

From the screens options, you will pick format cells. Next, you will select percentage, and you can choose the amount of decimal placement you prefer and select okay. 

The following percentage is our XIRR for the data. 

Let's keep our same example but try it without the guess entry. You will follow the same steps, but instead of typing in a guess, we will end our formula with the date entry. 

Notice how we still got the same result for the XIRR. This is because Excel will automatically assume that the guess is ten percent when it is not manually entered. 

Errors while using XIRR

Sometimes, when working on Excel, we can make a few errors. Errors can happen when a formula is incorrectly entered or when values and dates are incorrectly entered. When working with the extended internal rate of return, there are a few possible errors to look out for. 

Sometimes errors are just part of small mistakes that we make without noticing. Excel ensures it lets us know when we forget something or are entering something wrong. Some errors occur for the following reasons:

1. Forgetting to add a negative sign. 

This is a mistake that is sometimes made without notice. If there is no negative cash flow, the result will return as a #NUM! Error, as shown below. 

When working on entering the values into the formula, there needs to be at least one negative and positive cash flow. People must remember that adding a negative sign to the first cash flow is a common mistake.

2. False Date 

If a number within the date entry is mistakenly entered and is not a valid date, it will result in an error. The error message you will see is #NUM!

3. Former Dates

An error will also occur if a date is before the first starting date. For example, if the first date was February 4, 2021, and another date later was entered for January 2021, it will result in an error. 

4. Unrecognizable entry

If Excel does not recognize a number or date, it can cause an error. The error message you will most likely see is #VALUE!

A thing about Excel errors is sometimes it can be hard to determine which step was the one that caused the error. Therefore, carefully reviewing your work is always best to distinguish where the error comes from.

XIRR Vs. IRR Excel Function

There is a function very similar to the function of extended internal rate of return, and it is essential to avoid getting confused. The other function that you should know of is the IRR function. 

The IRR formula stands for the internal rate of return. These functions are very similar because they both compute the internal rate of return. 

Both functions use similar formulas as well. It is easy to get them confused due to the very identical attributes they both have. However, it is essential to know their difference to get the best results for your data. 

If you want to use the function calculated over an annual period, that is best to use the IRR function. In excel, to enter the formula, you type the following: 

=IRR(values,[guess])

Both formulas will give you different results, so it is essential to know which one suits your needs the best.  

Let's use our previous model to find the IRR as an example of what we mentioned above. 

As you see, we get different results, and that is because, again, the IRR will roll all the dates together to calculate them annually. Unlike the extended internal rate of return, it is estimated considering the date.

In the example, we noticed the results between IRR and XIRR were quite different. The extended internal rate of return is higher than the internal rate.

The reason why the extended internal rate of return is higher is because of the cash flows. The way the cash flows are being calculated, one of the functions appears lower than the other. 

If you noticed, as we used the IRR function, it did not ask us for the dates, and this is because, again, it did not consider each time recorded.

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• MIRR Function
• NPV Function
• PMT Function
• QUARTILE Function
• XIRR Function