The formula looks like this:
IRR = r, at which NPV = 0
Where:
NPV — net present value of the project's cash flows;
r is the discount rate equal to IRR.
This formula demonstrates that 1000 phone data o calculate the internal rate of return (IRR), you need to find a discount rate at which the net present value (NPV) of the project is zero. This can be done either manually, by experimenting with different discount rates, or by using specialized financial functions in spreadsheets, such as the IRR function in Excel.
IRR calculation formula
Example of the formula for IRR for an investment project. A complex formula for calculating IRR for an investment project looks like this:
IRR = r, at which ∑ CF_t/(1+r)^t = 0
Where:
CF_t — cash flow in period t;
t — periods (years) of project implementation;
r is the desired discount rate, equal to IRR.
Let's analyze what this could mean using a specific example: a client wants to evaluate the profitability of an investment project that involves investing 100 million rubles over three years with an annual income of 20 million rubles. The discount rate in this case is set at 10%.
Using the IRR formula, we find the internal rate of return of the project:
IRR = discount rate at which NPV = 0
NPV = income - investment.
NPV = 20 million rubles ─ 100 million rubles = ─ 80 million rubles.
In order to achieve a net profit of 20 million rubles, an investment of 100 million rubles and a waiting period of approximately five years are required.
IRR calculation formula
Source: shutterstock.com
A negative NPV (─ 80 million rubles) indicates that the project is not feasible, since the present value of the expected future cash flows does not reach the initial investment. Thus, this project is unprofitable, because 80 million rubles is the amount that investors can lose if they decide to invest 100 million rubles in this project.
Case: VT-metall
Find out how we reduced the cost of attracting an application by 13 times for a metalworking company in Moscow
Find out how
Calculating Internal Rate of Return in Excel and Google Sheets
Let's look at several formulas for calculating the IRR.
Formula for calculating GNI in Excel
There are two methods for calculating the internal rate of return of a project in Excel:
using built-in functions;
using the Solver tool.
Let's start with the functionality built into the system. To calculate the internal rate of return of the project, it is necessary to create a table with annual planned indicators, which will include several columns. It is important to reflect in this table such important data as initial investments and annual financial results of the project.
Please note that financial results presented annually must be presented in undiscounted format, i.e. without recalculating them to current prices.
For greater transparency, you can add a breakdown of the planned annual income and expenses at each stage, from which the final result of the project is formed.
Example: