Excel was originally created to facilitate calculations in many areas, including business. Using its capabilities, you can quickly perform complex calculations, including for forecasting the profitability of certain projects. For example, Excel allows you to easily calculate the IRR of a project. How to do this in practice, this article will tell.
What is IRR?
This abbreviation refers to 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 rate
Having found out the GNI of the project, you can decide on its launch or abandon it. For example, if you intend to open a new business and it is planned to finance it with a loan taken from a bank, then 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 IRR value with their value will make it possible to make an informed decision on the feasibility of launching a project. The cost of more than one source of financing is calculated according to the so-called formula of weighted arithmetic mean. It is called “Cost of Capital” or “Price of Advance Capital” (denoted by SS).
Using this indicator, we have:
If:
- IRR> SS, then the project can be launched;
- IRR = SS, then the project will bring neither profit nor loss;
- IRR <SS, the project is obviously unprofitable and should be abandoned.
How to calculate IRR manually
Long before the advent of computers, GNI was calculated by solving a rather complicated equation (see below).
It includes the following values:
- CFt - cash flow over time t;
- IC - financial investments at the project launch stage;
- N is the total number of intervals.
Without special programs, the IRR of a project can be calculated using the method of successive approximation or iteration. To do this, you must first select the barrier rates in such a way as to find the minimum values ​​of the NPV modulo, and carry out the approximation.
Successive approximation solution
First of all, you have to switch to the language of functions. In this context, IRR will be understood as such a value of profitability r, at which NPV, being a function of r, becomes equal to zero.
In other words, IRR = r such that when substituted into the expression NPV (f (r)), it is zeroed.
Now we solve the formulated problem by the method of successive approximations.
By iteration it is customary to understand the result of repeated application of a particular mathematical operation. In this case, the value of the function, calculated in the previous step, during the next becomes its own argument.
The calculation of the IRR indicator is carried out in 2 stages:
- IRR calculation at extreme values ​​of normal returns r1 and r2 such that r1 <r2;
- calculation of this indicator at r values ​​close to the IRR value 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 below.
It follows from this that in order to obtain the IRR value, one must first calculate the NPV for different values ​​of the% rate.
Between indicators NPV, PI and SS there is the following relationship:
- if 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 <CC and PI <1.
Graphical method
Now that you know what IRR is and how to calculate it manually, it is worth getting acquainted with another method for solving this problem, which was one of the most popular before computers appeared. This is a graphical version of the definition of IRR. To plot, it is necessary to find the NPV value by substituting various values ​​of the discount rate into the formula for calculating it.
How to calculate IRR in Excel
As you can see, manually finding the GNI is quite difficult. This requires some mathematical knowledge and time. It’s much easier to learn how to calculate IRR in Excel (see example below).
For this purpose, the well-known table processor Microsoft has a special built-in function for calculating the internal discount rate - the 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 designated as VVD (Values; Assumption).
Let's consider its syntax in more detail:
- Values ​​are understood as an array or a reference to cells that contain numbers for which it is necessary to calculate the IRR, taking into account all the requirements indicated for this indicator.
- The assumption is a value that is known to be close to the IRR result.
In Microsoft Excel, it uses the iteration method described above to calculate the IRR. It starts from the value "Assumption", and performs cyclic calculations, until the result is obtained with an accuracy of 0.00001%. If the built-in VSD function does not produce a result after making 20 attempts, then the table processor generates an error value, indicated as "#NUMBER!".
As practice shows, in most cases there is no need to set a value for the value of "Assumption". If it is omitted, then the processor considers it equal to 0.1 (10%).
If the built-in VSD function returns the error "#NUMBER!" or if the result does not meet expectations, then you can perform the calculations again, but with a different value for the argument "Assumption".
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 in advance that are listed in an Excel spreadsheet.
| A | B | C | D | E |
1 | Period (year) T | Initial cost | Cash income | Cash expense | Cash flow |
2 | 0 | 200 000 p. | - R. | 200000 p. | 200000 p. |
3 | 1 | - R. | 50,000 p. | 30000 p. | 20,000 p. |
4 | 2 | - R. | 60,000 p. | 33000 p. | 27000 p. |
5 | 3 | - R. | 45000 p. | 28000 p. | 17000 p. |
6 | 4 | - R. | 50,000 p. | 15000 p. | 35000 p. |
7 | 5 | - R. | 53000 p. | 20,000 p. | 33000 p. |
8 | 6 | - R. | 47000 p. | 18000 p. | 29000 p. |
9 | 7 | - R. | 62000 p. | 25000 p. | 37000 p. |
10 | 8 | - R. | 70,000 p. | 30 000 p. | 40,000 p. |
eleven | 9 | - R. | 64000 p. | 33000 p. | 31000 p. |
12 | | IRR | 6% |
The formula "= VSD (E3: E2)" is entered in the cell with the address E12. As a result of its use, the table processor produced a value of 6%.
Decisions in Excel: option 2
According to the data in the previous example, calculate the IRR through the add-on “Search for solutions”.
It allows you to search for the optimal value of the IRR for NPV = 0. For this, it is necessary to calculate the NPV (or NPV). It is equal to the amount of discounted cash flow over the years.
| A | B | C | D | E | F |
1 | Period (year) T | Initial cost | Cash income | Cash expense | Cash flow | Discount cash flow |
2 | 0 | 200000 p. | - R. | 200000 p. | 200000 p. | |
3 | 1 | - R. | 50,000 p. | 20 000 p. | 20,000 p. | 20,000 p. |
4 | 2 | - R. | 60,000 p. | 20,000 p. | 27000 p. | 27000 p. |
5 | 3 | - R. | 45000 p. | 20,000 p. | 17000 p. | 17000 p. |
6 | 4 | - R. | 50,000 p. | 20,000 p. | 35000 p. | 35000 p. |
7 | 5 | - R. | 53000 p. | 20,000 p. | 33000 p. | 33 000 p. |
8 | 6 | - R. | 47000 p. | 20,000 p. | 29000 p. | 29000 p. |
9 | 7 | - R. | 62000 p. | 20,000 p. | 37000 p. | 37000 p. |
10 | 8 | - R. | 70,000 p. | 20,000 p. | 40,000 p. | 40,000 p. |
eleven | 9 | - R. | 64000 p. | 20,000 p. | 31000 p. | 31000 p. |
12 | | NPV | 69000 p. |
| | IRR | |
Discounted cash flow is calculated by the formula "= E5 / (1 + $ F $ 11) ^ A5".
Then for NPV we get the formula "= SUM (F5: F13) -B7".
Next, you need to find, based on optimization through the “Decision Finding” add-in, the value of the IRR discount rate at which the NPV of the project becomes zero. To achieve this goal, you need to open the "Data" section in the main menu and find the "Search for Solutions" function there.
In the window that appears, fill in the lines “Set Target Cell”, indicating the address of the NPV calculation formula, ie + $ F $ 16. Then:
- select a value for this cell "0";
- the + $ F $ 17 parameter is entered into the “Cell Changes” window, that is, the value of the internal rate of return.
As a result of optimization, the tabular processor fills an empty cell with address F17 with the value of the discount rate. As can be seen 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, it is required to calculate a modified internal rate of return. It reflects the minimum GNI of the project in the event of a reinvestment. The formula for calculating MIRR is as follows.
Where:
- MIRR - internal rate of return on an investment project;
- COFt - cash outflow from the project during time periods t;
- CIFt - inflow of finance;
- r is the discount rate, which is equal to the weighted average cost of invested capital WACC;
- d -% reinvestment rate;
- n is the number of time periods.
MIRR calculation 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, the table processor provides a special built-in function of the Ministry of Internal Affairs. Take the same example already under consideration. How to calculate the IRR on it has already been considered. For MIRR, the table is as follows.
| A | B | C | D | E |
1 | The size of the loan as a percentage | 10 % | | |
2 | Reinvestment rate | 12% | | |
3 | Period (year) T | Initial cost | Cash income | Cash expense | Cash flow |
4 | 0 | 200000 p. | - R. | 200000 p. | 200000 p. |
5 | 1 | - R. | 50,000 p. | 30000 p. | 20,000 p. |
6 | 2 | - R. | 60,000 p. | 33000 p. | 27000 p. |
7 | 3 | - R. | 45000 p. | 28000 p. | 17000 p. |
8 | 4 | - R. | 50,000 p. | 15000 p. | 35000 p. |
9 | 5 | - R. | 53000 p. | 20,000 p. | 33000 p. |
10 | 6 | - R. | 47000 p. | 18000 p. | 29000 p. |
eleven | 7 | - R. | 62000 p. | 25000 p. | 37000 p. |
12 | 8 | - R. | 70,000 p. | 30000 p. | 40,000 p. |
thirteen | 9 | - R. | 64000 p. | 33000 p. | 31000 p. |
14 | | MIRR | 9% |
The formula for MIRR "= MVSD (E3: E13; C1; C2)" is entered into cell E14.
Advantages and disadvantages of using the internal rate of return indicator
The method of assessing the prospects of projects by calculating the IRR and comparing with the value of the cost of capital is not perfect. However, it has certain advantages. These include:
- The ability to compare various investment projects in terms of their attractiveness and the effectiveness of the use of invested capital. For example, a comparison can be made with profitability in the case of risk-free assets.
- The ability to compare different investment projects having a different investment horizon.
At the same time, the disadvantages of this indicator are obvious. These include:
- inability of the indicator of internal rate of return to reflect the size of reinvestment in the project;
- the complexity of forecasting cash payments, as their value is influenced by many risk factors, an objective assessment of which is of great difficulty;
- inability to reflect the absolute amount of income (cash generated) from the size of the investment.
Note! The last drawback was resolved by maintaining the MIRR indicator, which was described in detail above.
How IRR can be useful for 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). Any borrower can independently calculate it. To do this, he will have 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 they are daily, then T = 365) without rounding.
Now, if you know what the internal rate of return is, therefore, if you are told: “For each of the projects listed below, calculate the IRR”, you will not have any difficulties.