Where:
NPV — net present value of the project's cash flows;
r is the discount rate equal to IRR.
This formula demonstrates that to 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 singapore business email list 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:
A year of project life Initial investment, RUB Planned income for the project, RUB. Planned project expenses, RUB. Financial results of the project, RUB.
1st 100,000 0 100,000 – 100,000
2nd 0 40,000 30,000 10,000
3rd 0 50,000 35,000 15,000
4th 0 45,000 30,000 15,000
After creating this table, you need to use the IRR formula to calculate the internal rate of return.
Make sure that in the cell intended for the value of the IRR formula, you have specified the range of amounts from the column with the financial results of the project.
In real practice, investment projects do not always have stable cash flows. There is a possibility of pauses: freezing of projects, their temporary suspension for various reasons, etc. In this situation, another formula is used, designated in the Russian version of Excel as ЧИСТВНОДХ. The main difference between this formula and the previous one is that, in addition to the financial results of the project, it is necessary to specify the time intervals (dates) in which specific financial results are formed.
Formula for calculating GNI in Excel
Source: shutterstock.com
To calculate the internal rate of return of a project using the Solution Finder tool, you need to supplement the table of project values with a column reflecting the annual discounted financial results.
Then, in a separate cell, you should indicate that the NPV calculation will be performed here, and enter the appropriate formula, which will refer to another empty cell where the internal rate of return will be calculated.
Please note! In the "Set Target Cell" line, you must specify a reference to the cell containing the NPV formula. Then you must set that the target cell must have a zero value.
In the "Changing cell value" section, you need to specify a reference to an empty cell in which the indicator we need will be calculated. After that, you should use the "Search for solutions" function to find the interest rate value at which NPV becomes zero.
Formula for calculating GNI in Google Sheets
You can calculate your GNI in Google Sheets in just a few steps:
select an empty cell;
in the menu, select the items “Insert — Functions — Financial functions — IRR”;
Select the required cells or add new ones if you want to perform calculations manually: start with the negative value of investment costs and end with the last period of cash flows;
To calculate the second project, repeat the same process.
After this, all that remains is to compare the profitability of all projects.
To ensure the safety and profitability of the business, it is important to use formulas, calculations and terms associated with both Russian and foreign abbreviations. It is necessary to apply calculations, a practical approach and analytical thinking - this is the basis for successful business.
Recommended articles on this topic:
Examples of Marketing Strategies from Apple