IRR versus XIRR Excel Functions [Tutorial]

The IRR (Internal Rate of Return) Function is used to calculate the internal rate of return for a series, of given periodic cash flows. These cash flows do not have to be even, though they should include the initial input investment and the subsequent values that denote the net income. The internal rate of return is utilized, in order to give an indication of the profitability of a potential investment.

Read More: NPV versus XNPV Excel Functions Tutorial

The higher the internal rate of return for a particular investment, the more likely it is that the investment will be profitable. The IRR is similar to the NPV (we have already seen how to utilize the NPV Function and the XNPV Function in a previous tutorial). The IRR however, works on the premise that the NPV is 0.  The IRR input values are spaced at regular, equal intervals. The XIRR Function is used to calculate the internal rate of return for cash flows that are not periodic. The XIRR value is closely related to the XNPV function and is the internal rate of return calculated, for a corresponding XNPV of 0.

The syntax of the IRR function is:

IRR Excel function syntax

The syntax of the XIRR function is:

XIRR Excel function syntax

So, let’s get started with a simple example to illustrate what IRR and XIRR are used for, and the main difference between them.

Calculating IRR

A small business owner starts a business, at a cost of $80,000. The income for the first year is $20,000. The income for the second year is $22,000. The income for the third year is $28,000 and the income for the fourth year is $31,000. All income values refer to the net income.

IRR Function Excel Image 2

1) In order to calculate the IRR, in Cell B8 we input the following formula:

=IRR (B3: B7)

IRR Function Excel Image 3

2) Upon pressing CTRL-ENTER, a value of 9.2 % is returned.

IRR Function Excel Image 4

The IRR of a potential project can be utilized as a comparative benchmark value, if the IRR meets the criteria of being higher than a company’s required rate of return, the company proceeds with the commencement of the project. If not, the company does not proceed with the project. The IRR can also be utilized to compare the potential profitability of multiple prospective projects, and if the cost of investment is equal for all the projects, then the project with the highest IRR would be considered to be the most attractive for investment.

Read More: The PV, FV and PDURATION Excel Functions Tutorial

Calculating XIRR

A small business owner starts a construction business that has irregular cash flows, at a cost of $85,000 on the 2 January 2015. The income for the 6 June 2015, is $15, 000. The income for 7 October 2015 is $10,000. The income for 10 July 2016 is $18,000. The income for 10 January 2017 is $19,000.

XIRR Function Excel Image 1

1) In order to calculate the XIRR, in Cell B8 we input the following formula:

=XIRR (B3: B7, A3: A7)

XIRR Function Excel Image 2

2) Upon pressing CTRL-ENTER, a negative value of -21.0% is returned.

This indicates a potential lack of profitability, for the time periods given.

Read More: Calculating the Rate an Investment Achieves Using the RRI Function

Conclusion

The main difference between the IRR and the XIRR Functions is that XIRR is utilized for irregular cash flows. Both give an indication of the potential profitability of a project or investment and can be utilized as a comparative benchmark. The IRR and XIRR Functions, like the NPV and XNPV can be used as indicators of the attractiveness of a potential investment or project. The IRR and XIRR functions, however, rate attractiveness as a percentage versus an actual dollar amount (as in the case of the NPV and XNPV Functions)

Please feel free to comment and tell us what projects/investments, you use IRR and XIRR for.

Download working file

IRRVersusXIRRFunction

Useful Links

The IRR Function

The XIRR Function

What is the formula for calculating internal rate of return (IRR) in Excel?

What is IRR and XIRR and how to Calculate it?

Taryn is a Microsoft Certified Professional, who has used Office Applications such as Excel and Access extensively, in her interdisciplinary academic career and work experience. She has a background in biochemistry, Geographical Information Systems (GIS) and biofuels. She enjoys showcasing the functionality of Excel in various disciplines.

In her spare time when she’s not exploring Excel or Access, she is into graphic design, amateur photography and caring for her two pets, Pretzel and Snoopy.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *

Solve the Math * Time limit is exhausted. Please reload CAPTCHA.