Calculate IRR in Excel

The Internal Rate of Return (“IRR”) is a common source of error in a financial model. This document 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 cashflows generates a nil 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/investment. IRR thus 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”, and can be formulated mathematically using the formula below.

r = IRR, %

n = time period of the project / investment

What is the best way to calculate an IRR of a set of cashflows?

We will discuss in this tutorial the possible method to calculate the IRR:

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

An excel workbook has been prepared to demonstrate the IRR calculation, it would be best to download the workbook whilst reading this document.

Trial and Error method

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 previous tutorial on how to calculate an NPV.
• 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 discount rate of 10% is $20.8 million and hence we know that the IRR should be greater than 10%.
• Refer to Screenshot 2. Let us now try r of 15%. The NPV is -$0.7 million. It is negative but the NPV is closer to zero this time.
• We could guess that the IRR should be slightly lower than 15%

Screenshot 1: Project NPV at r = 10%

Screenshot 2: Project NPV at r = 15%

The trial and error process could 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 1D data table to spot the root

In our previous tutorial, 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. 

Screenshot 3: Data table of NPV at various discount rates

Let us plot the above data table in a chart as shown in Screenshot 4. It becomes clear that the IRR is between 14.75% and 15.00%. To be precise, the IRR is 14.78% which we could get using the Excel function.

Screenshot 4: NPV chart at various discount rates

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 CFi values 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() 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)

Screenshot 5: Using XIRR function in Excel

As demonstrated in Screenshot 5, the calculated IRR is 14.78% (cell E99) and we could double check the calculation by feeding the IRR back into the NPV calculation as a discount rate (cell E101) for which NPV equals zero.

Common applications of IRR

Capital budgeting: Investment decision tool

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

One 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 optimization

IRR is commonly used in optimizing the toll/tariff regime in project finance transactions such as in PPP (Public, Private Partnerships) schemes.

This could be done during the bidding process based on the expected IRR, or the IRR stipulated in the concession agreement is often regarded by the concessionaire as a threshold for initiating any tariff adjustment.

This is done by performing simulation on the toll/tariff versus the expected IRR using the carefully built financial model, which necessitates the identification of key parameters and risk factors.

Want more Tutorials?

See All Resources Get the latest Project Finance Tutorials and Blog Posts...