How do you calculate IRR and equity IRR in Excel?

Syntax

IRR(values, [guess])

The IRR function syntax has the following arguments:

• Values    Required. An array or a reference to cells that contain numbers for which you want to calculate the internal rate of return.

  • Values must contain at least one positive value and one negative value to calculate the internal rate of return.

  • IRR uses the order of values to interpret the order of cash flows. Be sure to enter your payment and income values in the sequence you want.

  • If an array or reference argument contains text, logical values, or empty cells, those values are ignored.

• Guess    Optional. A number that you guess is close to the result of IRR.

  • Microsoft Excel uses an iterative technique for calculating IRR. Starting with guess, IRR cycles through the calculation until the result is accurate within 0.00001 percent. If IRR can't find a result that works after 20 tries, the #NUM! error value is returned.

  • In most cases you do not need to provide guess for the IRR calculation. If guess is omitted, it is assumed to be 0.1 (10 percent).

  • If IRR gives the #NUM! error value, or if the result is not close to what you expected, try again with a different value for guess.

Remarks

IRR is closely related to NPV, the net present value function. The rate of return calculated by IRR is the interest rate corresponding to a 0 (zero) net present value. The following formula demonstrates how NPV and IRR are related:

NPV(IRR(A2:A7),A2:A7) equals 1.79E-09 [Within the accuracy of the IRR calculation, the value is effectively 0 (zero).]

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Data

Description

-$70,000

Initial cost of a business

$12,000

Net income for the first year

$15,000

Net income for the second year

$18,000

Net income for the third year

$21,000

Net income for the fourth year

$26,000

Net income for the fifth year

Formula

Description

Result

=IRR(A2:A6)

Investment's internal rate of return after four years

-2.1%

=IRR(A2:A7)

Internal rate of return after five years

8.7%

=IRR(A2:A4,-10%)

To calculate the internal rate of return after two years, you need to include a guess (in this example, -10%).

The internal rate of return (IRR) is a common source of error in a financial model. This tutorial covers how to calculate an IRR in Excel, and assumes that the reader is already familiar with the mathematical concept of the IRR.

The IRR can be defined as a discount rate which, when applied to a series of cash flows, generates a nil net present value (NPV). There may be more than one IRR in certain situations; additionally, Excel makes this calculation deceptively simple, at the risk of errors.

The NPV is the discounted value of a stream of cash flows generated from a project or investment. IRR computes the break even rate of return for which the NPV equals to zero. It is an indicator of the efficiency or quality of an investment, as opposed to the NPV which indicates value or magnitude.

The IRR is the annualised effective compounded return rate, denoted by ‘r’. Mathematically, it can be formulated as:

What is the best way to calculate the IRR of a set of cash flows?

In this tutorial we will discuss the possible methods to calculate the IRR:

• Using a trial and error method (to assist understanding)
• Using a 1-dimension data table
• Calculating IRR using Excel function NPV()
• Calculating IRR using XIRR

The downloadable Excel workbook above has been prepared to demonstrate the IRR calculation. For ease of reference, we recommend you download the workbook while reading this tutorial.

 Trial and error method for IRR calculations in Excel

IRR is the discount rate for which NPV equals zero, and could be calculated by a trial and error process.

• NPV(IRR(…), …) = 0
• XNPV(XIRR(…), …) = 0

The trial and error process is as follows:

• Start with a guess of the discount rate ‘r’
• Calculate NPV using the ‘r’ – refer to our tutorials on how to calculate an NPV with or without Excel formulae
• If the NPV is close to zero, then ‘r’ is the IRR
• If the NPV is positive, increase ‘r’
• If the NPV is negative, decrease ‘r’
• Continue the process until NPV reaches zero

Using the example in the workbook:

• Refer to screenshot 1: The NPV calculated at a discount rate of 10% is $19.31 million, hence we know that the IRR should be greater than 10%

• Refer to screenshot 2: Let us now try ‘r’ of 18%. The NPV is -$0.87 million. It is negative, but the NPV is closer to zero this time.
• Therefore, we could guess that the IRR should be slightly lower than 18%.

• The trial and error process can be more tedious than calculating an NPV itself.
• Next, we will show you the approach of guessing the IRR with the help of a 1-dimension data table.

Using a 1-dimension data table to spot the root

We recommended presenting NPV at various discount rates using a quick 1-dimension data table, with discount rates as the vertical parameter as shown in screenshot 3.

Let us plot the above data table in a chart – see screenshot 4. It becomes clear that the IRR is between 17.50% and 18.00%. To be precise, the IRR is 17.53%, which we could get using the Excel function.

Using IRR() in Excel

IRR() syntax:

IRR(CF1, CF2, …)

We could calculate IRR using Excel function IRR(), but similar to NPV(), it has some limitations:

• The cash flows (CFi) must be equally spaced in time and occur at the end of each period
• The CFi must be entered in the correct sequence

Using XIRR() function In Excel

Due to its limitation, the IRR function (without the X) is best avoided. The more robust function would be XIRR(). It returns the internal rate of return for a schedule of cash flows that is not necessarily periodic.

XIRR() is an added-in function in Excel and the syntax is: XIRR(CFi, dates)

As demonstrated in screenshot 5, the calculated IRR is 17.53%. We could double check the calculation by feeding the IRR back into the NPV calculation as a discount rate, for which NPV equals zero.

Capital budgeting: Investment decision tool

IRR is a metric to decide whether a single project is worth investing in. Theoretically, a simple decision making benchmark could be set to accept a project if the IRR exceeds the cost of capital, and rejected if this IRR is less than the cost of capital.

You should be aware of the limitation of the IRR, such as a project with multiple IRRs or no IRR. In addition, IRR neglects the size of the project, and assumes that cash flows are reinvested at a constant rate.

Tariff optimisation

IRR is commonly used in optimising the toll/tariff regime in project finance transactions, such as in public, private partnerships (PPP) schemes. This could be done during the bidding process based on the expected IRR. Alternatively, a tariff could be adjusted in order to provide a minimum IRR threshold for the concessionaire.

Corality Academy: Corality Financial Modelling Campus

There are numerous other tutorials and free resources related to financial modelling in the Corality Financial Modelling Campus.

How do you calculate project IRR and equity IRR in Excel?

How do you calculate equity IRR?

What is the formula for calculating IRR in Excel?

What is difference between project IRR and equity IRR?