Mazars – welcome to our Digital Classrooms! We are opening our Digital Classrooms to individual registrations, making our world-leading financial...
Excel IRR Function and Other Ways to Calculate IRR in Excel
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:
In this tutorial we will discuss the possible methods to calculate the IRR:
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.
IRR is the discount rate for which NPV equals zero, and could be calculated by a trial and error process.
The trial and error process is as follows:
Using the example in the workbook:
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.
IRR(CF1, CF2, …)
We could calculate IRR using Excel function IRR(), but similar to NPV(), it has some limitations:
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.
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.
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.
There are numerous other tutorials and free resources related to financial modelling in the Corality Financial Modelling Campus.
Some of the more popular courses that relate to this topic include:
This website cannot function properly without these cookies.
Analytical cookies help us enhance our website by collecting information on its usage.
We use marketing cookies to increase the relevancy of our advertising campaigns.