IRR - what is it and how to calculate in Excel?

The Excel program was originally created to facilitate settlements in many areas, including business. Using its capabilities, you can quickly make complex calculations, including for forecasting the profitability of certain projects. For example, Excel allows you to easily calculate IRR project. How to do it in practice, this article will tell.

What is IRR

This abbreviation denotes the internal rate of return (GNI) of a particular investment project. This indicator is often used to compare proposals for the prospect of profitability and business growth. In numerical terms, IRR is the interest rate at which the present value of all cash flows necessary for the implementation of the investment project is zeroed (denoted by NPV or NPV). The higher the GNI, the more promising is the investment project.

How to evaluate

Having found out the GNI project, you can decide whether to launch it or abandon it. For example, if you are going to open a new business and are supposed to finance it with a loan taken from a bank, the IRR calculation allows you to determine the upper acceptable limit of the interest rate. If the company uses more than one source of investment, then comparing the value of IRR with their value will make it possible to make an informed decision about the feasibility of launching the project. The cost of more than one funding source is calculated by the so-called weighted arithmetic average formula. It was called the "Cost of Capital" or "The Price of Advance Capital" (denoted by the SS).

Using this indicator, we have:

If:

• IRR> CC, then the project can be launched;
• IRR = SS, then the project will not bring any profit or loss;
• IRR

How to calculate IRR manually

Long before the advent of computers, GNI was calculated by solving a fairly complex equation (see below).

It includes the following values:

• CFt - cash flow for a period of time t;
• IC - financial investments at the stage of project launch;
• N is the total number of intervals.

Without special programs, the IRR of the project can be calculated using the method of successive approximation or iterations. To do this, it is first necessary to select barrier rates in such a way as to find the minimum values of the NPV modulo, and carry out approximation.

The solution by the method of successive approximations

First of all, it is necessary to switch to the language of functions. In this context, the IRR will be understood as the value of the yield r, at which NPV, being a function of r, becomes zero.

In other words, IRR = r is such that when it is substituted into the NPV expression (f (r)) it is reset.

Now we solve the formulated problem by the method of successive approximations.

Under the iteration it is customary to understand the result of the repeated application of a mathematical operation. In this case, the value of the function computed in the previous step becomes the same argument during the next step.

Calculation of the IRR is carried out in 2 stages:

• Calculation of IRR at extreme values of normal profitability r1 and r2 such that r1
• Calculation of this indicator at values of r close to the value of IRR, obtained as a result of previous calculations.

When solving the problem, r1 and r2 are chosen so that NPV = f (r) inside the interval (r1, r2) changes its value from minus to plus or vice versa.

Thus, we have the formula for calculating the IRR indicator in the form of the expression presented below.

It follows that in order to obtain the IRR value, it is required to first calculate the NPV for different values of the% bet.

Between the indicators NPV, PI and CC there is the following relationship:

• If the NPV value is positive, then IRR> CC and PI> 1;
• If NPV = 0, then IRR = CC and PI = 1;
• If the NPV value is negative, then IRR

Graphical method

Now that you know what an IRR is and how to calculate it manually, it's worthwhile to get acquainted with one more method of solving this problem, which was one of the most sought-after ones before computers appeared. This is a graphic version of the definition of IRR. For the construction of graphs, it is required to find the value of NPV, substituting in the formula for its calculation various values of the discount rate.

How to calculate IRR in Excel

As you can see, manually finding GNI is quite difficult. For this, certain mathematical knowledge and time are required. It is much easier to learn how to calculate IRR in Excel (see example below for an example).

For this purpose, the Microsoft table processor has a special built-in function for calculating the internal discount rate - IRR, which gives the desired IRR value in percentage terms.

Syntax

IRR (what it is and how to calculate it is necessary to know not only specialists, but also ordinary borrowers) in Excel is denoted as VSD (Values, Assumption).

Let's consider its syntax in more detail:

• A value is an array or a reference to cells that contain numbers for which it is necessary to compute the IRR, taking into account all the requirements specified for this indicator.
• The assumption is a value that is known to be close to the IRR result.

In Microsoft Excel, the IAS method described above uses the iteration method described above. It starts with the value "Assumption", and performs the cyclic calculations, until the result is obtained with an accuracy of 0.00001%. If the built-in IRR function does not produce a result after making 20 attempts, then the table processor outputs an error value, indicated as "# NUMBER!".

As practice shows, in most cases there is no need to set a value for the value "Assumption". If it is omitted, then the processor considers it to be 0.1 (10%).

If the built-in IRR function returns a "# NUMBER!" Error or if the result does not match the expectations, you can perform calculations again, but with a different value for the "Assumption" argument.

Solutions in Excel: option 1

Let's try to calculate the IRR (what it is and how to calculate this value manually you already know) using the built-in IRR function. Suppose we have data for 9 years ahead, which are recorded in an Excel spreadsheet.

	A	B	C	D	E
1	Period (year) T	Initial Costs	Monetary income	Cash flow	Cash flow
2	0	200 000 р.	- R.	200000 р.	200000 р.
3	1	- R.	50000 р.	30000 р.	20000 р.
4	2	- R.	60000 р.	33000 р.	27000 р.
5	3	- R.	45000 р.	28000 р.	17000 р.
6th	4	- R.	50000 р.	15000 р.	35000 р.
7th	5	- R.	53000 р.	20000 р.	33000 р.
8	6th	- R.	47000 р.	18000 р.	29000 р.
9	7th	- R.	62000 р.	25000 р.	37000 р.
10	8	- R.	70000 р.	30 000 р.	40000 р.
eleven	9	- R.	64000 р.	33000 р.	31000 р.
12				IRR	6%

In the cell with the address E12 the formula "= VSD (E3: E2)" was introduced. As a result of its use, the table processor produced a value of 6%.

Solutions in "Excel": option 2

According to the data shown in the previous example, calculate the IRR using the "Find solutions" add-on.

It allows you to search for the optimal IRR for NPV = 0. To do this, calculate the NPV (or NPV). It is equal to the amount of discounted cash flow by years.

	A	B	C	D	E	F
1	Period (year) T	Initial Costs	Monetary income	Cash flow	Cash flow	Discount cash flow
2	0	200000 р.	- R.	200000 р.	200000 р.
3	1	- R.	50000 р.	20 000 р.	20000 р.	20000 р.
4	2	- R.	60000 р.	20000 р.	27000 р.	27000 р.
5	3	- R.	45000 р.	20000 р.	17000 р.	17000 р.
6th	4	- R.	50000 р.	20000 р.	35000 р.	35000 р.
7th	5	- R.	53000 р.	20000 р.	33000 р.	33 000 р.
8	6th	- R.	47000 р.	20000 р.	29000 р.	29000 р.
9	7th	- R.	62000 р.	20000 р.	37000 р.	37000 р.
10	8	- R.	70000 р.	20000 р.	40000 р.	40000 р.
eleven	9	- R.	64000 р.	20000 р.	31000 р.	31000 р.
12					NPV	69000 р.
					IRR

Discounted cash flow is calculated using the formula "= E5 / (1 + $ F $ 11) ^ A5".

Then for NPV the formula "= SUMM (F5: F13) -B7" is obtained.

Next, you need to find, based on the optimization by means of the "Find Solutions" add-on, the value of the IRR discount rate, in which the NPV of the project will be zero. To achieve this, you need to open the "Data" section in the main menu and find the "Find solutions" function.

In the window that appears, fill out the lines "Set target cell", specifying the address of the NPV calculation formula, ie + $ F $ 16. Then:

• Select the value for this cell "0";
• The "Change cell" window is entered with the parameter + $ F $ 17, that is, the value of the internal rate of return.

As a result of optimization, the table processor will fill an empty cell with address F17 with the discount rate value. As you can see from the table, the result is 6%, which completely coincides with the calculation of the same parameter, obtained using the built-in formula in Excel.

MIRR

In some cases, you need to calculate the modified internal rate of return. It reflects the minimum IRR of the project in the case of reinvestment. The formula for calculating the MIRR is as follows.

Where:

• MIRR - internal rate of return on the investment project;
• COFt - outflow from the project of funds in time periods t;
• CIFt - the inflow of finance;
• R - the discount rate, which is equal to the weighted average cost of the invested capital WACC;
• D -% rate of reinvestment;
• N is the number of time periods.

Calculating MIRR in a table processor

Having become acquainted with the properties of IRR (what it is and how to calculate its value graphically you already know), you can easily learn how to calculate the modified internal rate of return in Excel.

For this purpose, a special built-in MIRFD function is provided in the table processor. Take the same, already considered example. How to calculate the IRR on it, has already been considered. For MIRR, the table looks like this.

	A	B	C	D	E
1	Amount of loan in percent		10 %
2	Level of reinvestment		12%
3	Period (year) T	Initial Costs	Monetary income	Cash flow	Cash flow
4	0	200000 р.	- R.	200000 р.	200000 р.
5	1	- R.	50000 р.	30000 р.	20000 р.
6th	2	- R.	60000 р.	33000 р.	27000 р.
7th	3	- R.	45000 р.	28000 р.	17000 р.
8	4	- R.	50000 р.	15000 р.	35000 р.
9	5	- R.	53000 р.	20000 р.	33000 р.
10	6th	- R.	47000 р.	18000 р.	29000 р.
eleven	7th	- R.	62000 р.	25000 р.	37000 р.
12	8	- R.	70000 р.	30000 р.	40000 р.
13	9	- R.	64000 р.	33000 р.	31000 р.
14				MIRR	9%

In cell E14, a formula is introduced for MIRR "= MVDS (E3: E13; C1; C2)".

The advantages and disadvantages of using the internal rate of return

The method of estimating the prospects of projects, by calculating the IRR and comparing it with the value of capital, is not perfect. However, it has certain advantages. These include:

• The possibility of comparing different investment projects in terms of their attractiveness and efficiency of using the invested capital. For example, you can compare with the yield in the case of risk-free assets.
• Opportunity to compare different investment projects having a different horizon of investment.

At the same time, the shortcomings of this indicator are obvious. These include:

• Inability of an indicator of the internal rate of return to reflect the amount of reinvestment in the project;
• The complexity of predicting cash payments, since their magnitude is affected by a variety of risk factors, an objective assessment of which is of great complexity;
• Inability to reflect the absolute amount of income (the proceeds of money) from the value of the investment.

Note! The last drawback was resolved by maintaining the MIRR, which was described in detail above.

How the ability to calculate IRR can be useful to borrowers

According to the requirements of the Russian Central Bank, all banks operating in the territory of the Russian Federation are required to indicate an effective interest rate (EPS). It can independently calculate any borrower. To do this, he will need to use a table processor, for example, Microsoft Excel and select the built-in IRR function. To do this, the result in the same cell should be multiplied by the payment period T (if they are monthly, then T = 12, if daytime, then T = 365) without rounding.

Now, if you know what the internal rate of return is, so if you are told: "For each of the following projects, calculate the IRR", you will not have any difficulties.