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

The Excel program was originally created forfacilitating 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

Such an abbreviation denotes an internal normprofitability (GNI) of a specific 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 required 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 on itsstart or abandon it. For example, if they are going to open a new business and are supposed to finance it with a loan taken from a bank, the calculation of the IRR 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 calculate IRR projectone can use 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 in 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 resultrepeated 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.

The 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 value of NPV 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 howto calculate it manually, it is worth to get acquainted with one more method of solving this problem, which was one of the most popular 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, Microsoft's known tabular 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 this 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 method of iteration described above. It starts with the value "Assumption", and performs a cyclic computation, 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 produces 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, the processor considers it to be 0.1 (10%).

If the built-in IRR function returns an error"# NUMBER!" Or if the result does not correspond to expectations, then you can make calculations again, but with a different value for the "Assumption" argument.

Solutions in Excel: option 1

Let's try to calculate IRR (what is this and howto calculate this value manually you already know) through the built-in IRR function. Suppose we have data for 9 years ahead, which are listed in the Excel table.

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-in.

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

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

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

Further it is necessary to find on the basis of optimizationby means of the "Find solutions" add-on, such a 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 processorfill the 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 calculatemodified 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 - discount rate, which is equal to the weighted average cost of WACC;
• d -% rate of reinvestment;
• n is the number of time periods.

Calculating MIRR in a table processor

Having become acquainted with the IRR properties (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.

To do this, a tabular processor providesa special built-in MIRF function. Take the same, already considered example. How to calculate IRR on it, has already been considered. For MIRR, the table looks like this.

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 forthe degree of their attractiveness and the efficiency of the use of 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 forecasting cash payments, since their magnitude is affected by a variety of risk factors, 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 the 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.