Internal rate of return of the project. Net Present Value NPV (NPV) and IRR (IRR) in MS EXCEL

Internal Rate of Return (in English)... Internal rate of return)- This metric is used to measure the ROI of a potential investment.

IRR is the discount rate that equates NPV project to zero.

Formula

IRR is calculated by equating the sum of the present value of future cash flows minus the initial investment to zero. Formula:

As you can see, the only unknown variable in the equation is IRR. When evaluating a potential project, company management knows how much capital will be required to launch the project and there will also be a reasonable estimate of future cash flows. In this way, it is necessary to solve the equation forIRR.

Example

Bob is considering buying a new plant, but he is not sure if this is the best use of the company's funds for this moment... With the acquisition of a plant at a cost$ 900KBob's company will generate$ 300K, $ 400K and $ 500K cash flow.

Let's calculate minimum rate Tom. Since it is difficult to determine the discount factor manually, let's start with an approximate rate of 7%.

$ 300K / (1 + 7%) 1 + $ 400K / (1 + 7%) 2 + $ 500K / (1 + 7%) 3 - $ 900K = $ 137.9K

The final NPV is not zero. Since $ 137.9K is a positive number in this case, you need to increase the estimated internal rate. Let's increaseIRRup to 14.51% and recalculate.

$ 300K / (1 + 14.51%) 1 + $ 400K / (1+14.51 %) 2 + $ 500K / (1+14.51 %) 3 - $ 900K = $ 0

Thus, the IRR of the project is 14.51% ... Bob can now compare the receivedIRRwith other investment opportunities to determine if it makes sense to spend $ 900,000 to buy a new plant or invest in another project.

SignificanceIRR

The internal rate of return is much more useful when it is used for benchmarking purposes rather than in isolation as a single value. The higher the IRR of the project, the more attractive the project is from an investment point of view. IRR is a uniform metric for comparison different types investments, and therefore IRR values ​​are often used to rank several promising investments. If the investment is equal between the projects under consideration, the project with the highest IRR is considered the best..

The IRR of any project is calculated taking into account the assumptions:

1. Intermediate cash flows will be reinvested under the same IRR.

2. All cash flows are of a periodic nature, the time intervals between receipt of cash flows are equal.

Required rate of return

Required rate of return (eng... Required rate of return)Is the minimum return that an organization can expect from an investment. Most organizations adhere to a specific barrier rate. Any project with an internal rate of return that exceeds the barrier rate is considered profitable. While this is not the only basis for considering an investment project, RRR is an effective mechanism for project selection. Typically, the project that has the highest difference between RRR and IRR is considered best project for investment.

Ate Internal Rate of Return> Required Rate of Return - Attractive Project

Ate Internal Rate of Return< Required rate of return - reject project

Disadvantages of IRR

The problem arises in situations where the initial investment gives a low IRR. This happens in projects that generate profits at a slower rate, but these projects can subsequently increase the overall value of the corporation at the expense ofNPV.

A similar problem is that the project demonstrates quick results in a short period of time. Small project can be cost effective in a short time by showing high IRR but low NPV. Therefore, sometimes it is more profitable to invest in projects with lowIRRbut high absolute valueNPV.

Cash flows coming from new projects cannot always be reinvested underIRR.Therefore, in the calculations, the project may turn out to be more profitable., than when implementing it.

Excel was originally created to facilitate calculations in many areas, including business. Using its capabilities, you can quickly perform complex calculations, including for predicting the profitability of certain projects. For example, Excel makes it fairly easy to calculate the IRR of a project. This article will tell you how to do this in practice.

What is IRR

This abbreviation means internal norm profitability (IRR) of a specific investment project... This metric is often used to compare offers based on profitability and growth prospects for a business. Numerically, IRR is 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 IRR, the more promising the investment project is.

How to rate

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

Using this indicator, we have:

• IRR> CC, then the project can be launched;
• IRR = CC, then the project will not bring any profit or loss;
• IRR< СС, то проект заведомо убыточный и от него следует отказаться.

How manually

Long before the advent of computers, VND was calculated by solving a rather complicated equation (see below).

It includes the following quantities:

• 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 you can calculate the IRR of the project using the method of successive approximation or iterations. For this, it is first necessary to select barrier rates in such a way as to find minimum values NPV modulo, and an approximation is performed.

Solution by the method of successive approximations

First of all, you have to switch to the language of functions. In this context, IRR will mean such a value of the return 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 will 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 becomes its argument during the next one.

The calculation of the IRR indicator is carried out in 2 stages:

• calculation of IRR at extreme values ​​of normal returns r1 and r2 such that r1< r2;
• calculation of this indicator at values ​​of r close to the IRR value obtained as a result of the previous calculations.

When solving the problem, r1 and r2 are chosen so that NPV = f (r) within 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 from it that in order to obtain the IRR value, it is necessary to pre-calculate the NPV at different values ​​of the% rate.

There is the following relationship between NPV, PI and CC:

• if NPV is positive, then IRR> CC and PI> 1;
• if NPV = 0, then IRR = CC and PI = 1;
• if NPV is negative, then IRR< СС и 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. It is about the graphical version of the definition of IRR. To build graphs, you need to find the NPV value by substituting various values ​​of the discount rate into the formula for its calculation.

How to calculate IRR in Excel

As you can see, it is quite difficult to find GNI manually. This requires a certain amount of mathematical knowledge and time. It is much easier to learn how to calculate IRR in Excel (see example below).

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

Syntax

Let's take a closer look at its syntax:

• The values ​​mean 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 specified for this indicator.
• The guess is the amount known to be close to the IRR result.

Microsoft Excel uses the above-described iteration method to calculate IRR. It starts with the "Guess" value, and performs loop calculations until the result is accurate to 0.00001%. If the built-in function IRR does not return a result after 20 attempts, then the spreadsheet will return an error value denoted as “#NUM!”.

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

If the built-in IRR function returns the error "#NUM!" or if the result does not meet expectations, then you can perform the calculations again, but with a different value for the "Guess" argument.

Excel Solutions: Option 1

Let's try to calculate 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 is entered into an Excel spreadsheet.

The formula "= VSD (E3: E2)" has been entered into the cell with the address E12. As a result of its application, the table processor returned a value of 6%.

Excel Solutions: Option 2

Based on the data in the previous example, calculate the IRR using the Solver add-in.

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

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

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

Next, it is necessary to find, on the basis of optimization using the "Search for solutions" add-in, such a value of the IRR discount rate, at which the NPV of the project becomes equal to 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 formula for calculating NPV, ie + $ F $ 16. Then:

• select the value for this cell "0";
• enter the parameter + $ F $ 17, that is, the value of the internal rate of return, into the "Cell Changes" window.

As a result of optimization, the spreadsheet processor will fill the empty cell with the address F17 with the value of the discount rate. 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

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

Calculating MIRR in a Tabular Processor

Having become familiar 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 function MVSD is provided in the spreadsheet processor. Let's take the same example already being considered. How to calculate IRR on it has already been considered. For MIRR, the table looks like this.

In cell E14 you enter the formula for MIRR "= MVSD (E3: E13; C1; C2)".

Advantages and Disadvantages of Using the Internal Rate of Return

The method for assessing the prospects of projects by calculating IRR and comparing with the value of the cost of capital is not perfect. However, it does have 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, you can make a comparison with the return in the case of risk-free assets.
• The ability to compare different investment projects with different investment horizons.

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

• the inability of the indicator of internal rate of return to reflect the amount of reinvestment in the project;
• the complexity of forecasting cash payments, since their value is influenced by many risk factors, the objective assessment of which is very difficult;
• inability to reflect the absolute amount of income (proceeds) from the size of the investment.

Note! The latter flaw was addressed by maintaining the MIRR, which was detailed above.

How the ability to calculate IRR can be useful for borrowers

According to the requirements of the Russian Central Bank, all banks operating in the Russian Federation are required to indicate the effective interest rate (EIR). Any borrower can independently calculate it. To do this, he will have to use a spreadsheet 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 daily, then T = 365) without rounding.

Now, if you know what the internal rate of return is, so if you are told, "Calculate the IRR for each of the projects below," you will have no problem.

In this article, we will consider what the internal rate of return is, what economic meaning does it have, how and by what formula to calculate the internal rate of return, consider some examples of calculation, including using MS Excel formulas.

What is Internal Rate of Return?

Internal rate of return(IRR - Internal Rate of Return) is one of the main evaluation criteria (return on a unit of invested capital): the discount rate at which the amount is equal discounted income for the project (positive cash flow) the discounted amount of investments (negative cash flow, the reduced volume of investments), i.e. when equal to zero.

In the financial and economic literature, you can often find synonyms for the internal rate of return:

• internal rate of return;
• internal rate of return;
• internal rate of return;
• internal rate of return;
• internal rate of return on investment.

Internal rate of return reflects as return invested capital in general, and the return on the initial investment. IRR is one that equates the amount of reduced income from an investment project to the value, i.e. investments pay off, but do not bring.

Thus, the analysis of the internal rate of return (profit) answers to main question investor: how much the expected cash flow from the project will justify the cost of investing in this project. Therefore, when evaluating projects, it calculates the IRR of each project and compares it with the required (), i.e. with.

This calculation is usually done by trial and error, by successively applying present values ​​to the net cash flow at various interest rates. The main rule: if the internal rate of return is less than the rate of return on invested capital required by the investor, the project is rejected, if more, it can be accepted.

The formula for calculating the internal rate of return

The internal rate of return is calculated using the following formula:

where
NPV IRR(Net Present Value) - the net present value calculated at the IRR rate;
CF t(Cash Flow) - cash flow in the time period t;
IC(Invest Capital) - investment costs for the project in the initial period (also cash flow CF 0 = IC).
t- time period.

or this formula can be represented as:

Practical Application of Internal Rate of Return

Internal rate of return is used to evaluate a project or to benchmark against other projects. For this, the IRR is compared with effective rate discounting, that is, with the required level of profitability of the project (r). For this level, in practice, they are often used.

It should be noted that instead of the WACC comparison criterion, any other rate of return can be used, for example, the rate of return on, rate on, etc. So, if the interest rate on the deposit is 17%, and the IRR of the investment project is 22%, then, obviously, money should be invested in an investment project, and not placed in a bank.

The graphical method for finding the internal rate of return

Let's say we are going to invest $ 10,000. monetary units, and we have options for investing them in 3 projects, each of which is supposed to generate certain cash flows over 5 years.

Let's discount the above cash flows for 3 projects at different interest rates (from 0 to 14%) and build a graph based on the results obtained.

The graph shows a clear relationship between the discount rate and net present value: the higher the discount rate, the lower the discounted value.

The internal rate of return, as follows from the definition indicated at the beginning of this article, is the level of the discount rate at which NPV = 0. In our example, the internal rate of return is determined at the points of intersection of the curves with the X-axis. In particular, for project # 1 IRR is 8.9%, for project # 2 IRR = 6.6% and for project # 3 IRR = 4.4% ...

Calculation of the internal rate of return (IRR) using MS Excel

The internal rate of return can be calculated quite easily using the built-in IRR financial function in MS Excel.

VSD function Returns the internal rate of return for a series of cash flows, represented by their numerical values. These cash flows do not need to be equal in magnitude (as is the case), but they do. at regular intervals, for example monthly or annually. In this case, the structure of cash flows must necessarily have at least one negative cash flow (initial investment) and one positive cash flow (net investment income).

Also, for the correct calculation of the internal rate of return using the IRR function, the order of cash flows is important, i.e. if the cash flows differ in size by different periods, then they must be specified in the correct sequence.

The syntax for the VSD function is:

where
The values- this is an array or reference to cells containing numbers for which you want to calculate the internal rate of return, taking into account the requirements specified above;
Assumption is the value about which it is assumed that it is close to the result of the IRR:

• Microsoft Excel uses the iteration method to calculate IRR. Starting with the Guess value, the IRR function performs cyclic calculations until it gets a result with an accuracy of 0.00001 percent. If the IRR function cannot get the result after 20 attempts, then the #NUM! Error value is returned.
• In most cases, there is no need to set an Assumption for calculations using the IRR function. If the Assumption is omitted, then it is assumed equal to 0.1 (10 percent).
• If IRR returns the #NUM! Error value or if the result is not what you expected, you can try to perform the calculation again with a different value for the Guess argument.

An example of calculating the internal rate of return (based on data on cash flows for the three projects discussed above):

In particular, for project # 1, the IRR value = 8.9%.

Calculation of the internal rate of return in MS Excel at unequal time intervals for cash flows

Using the Exel function of the IRR, it is quite easy to determine the internal rate of return, however this function can only be applied if cash flows are received at regular intervals (for example, annually, quarterly, monthly). However, in practice, situations often arise when cash flows arrive at different time intervals. In such cases, you can use Excel's other built-in financial function, PERFORMANCE, which returns the internal rate of return for a graph of cash flows that are not necessarily periodic.

Syntax for the NETWORK function

where
The values- a series of cash flows corresponding to the payment schedule given in the Dates argument. The first payment is optional and corresponds to the cost or payment at the start of the investment. If the first value is a cost or payout, it must be negative. All subsequent payments are discounted on a 365-day year basis. A series of values ​​must contain at least one positive and one negative value.
Dates- a schedule of payment dates that corresponds to a series of cash flows. Dates can be in any order.
Assumption- a value presumably close to the PERFECT result.

Calculation example:

Modifiedinternal rate of return(Modified Internal Rate of Return, MIRR) - an indicator that reflects the minimum internal level of profitability of a project when implemented into a project. This project uses interest rates obtained from capital reinvestment.

The formula for calculating the modified internal rate of return:

where
MIRR- the internal rate of return of the investment project;
COF t- cash outflow during periods of time t;
CIF t- cash flow;
r- the discount rate, which can be calculated as weighted average cost capital WACC;
d- the interest rate of capital reinvestment;
n- the number of time periods.

MS Excel has a special built-in financial function MIA for calculating the modified internal rate of return.

The syntax for the MVSD function is:

where
The values- an array or reference to cells containing numeric values. These numbers represent a series of cash payments (negative values) and receipts (positive values) that occur at regular intervals.
Rate_finance- the rate of interest paid for money used in cash flows.
Rate_reinvest- the rate of interest received on cash flows upon their reinvestment.

Internal Rate of Return (IRR) Advantages and Disadvantages

The main advantages of IRR include:

1. the ability to compare various investment projects with each other in order to determine more attractive in terms of economic efficiency use of available capital. Comparison can be made with a certain conventional benchmark, for example, with the interest rate on deposits;
2. the ability to compare different investment projects with different investment horizons.

The main disadvantages of the Internal Rate of Return (IRR) are:

1. the difficulty of predicting future cash payments. The size of the planned payments is influenced by many risk factors, the influence of which is difficult to objectively assess;
2. the impossibility of determining the absolute funds from investment;
3. with an arbitrary alternation of cash inflows and outflows, in the case of one project, several IRR values ​​may exist. Therefore, it is impossible to make an unambiguous decision based on the IRR indicator;
4. IRR does not reflect the size of reinvestment in the project (this drawback is solved in the modified internal rate of return MIRR).

You have a great idea for a new product that will increase your bottom line or new system, which will reduce the costs of the company. But how can you be sure that this idea will pay off your investment? One of the main methods to find out is IRR analysis.

Every time you propose capital expenditures, you can be sure that senior executives will want to figure out their ROI.

There are many methods you can use to calculate ROI - Net Present Value, Payback Period, Profitability Index, and Internal Rate of Return, or IRR.

Let's figure out how IRR works and in what cases it is better to use it.

What is Internal Rate of Return?

IRR is the rate at which the project reaches the breakeven level (i.e. pays for itself).

Typically this metric is used by financial analysts in combination with net present value or NPV. This is because both methods are similar but use different variables.

With NPV, you determine the discount rate for your company and then calculate the present value of the investment based on that rate ().

But for IRR, you calculate the actual income from the project's cash flows and then compare it to your company's barrier rate (i.e., your company's minimum expected rate of return)... If the IRR is higher, then the investment is profitable.

How is IRR calculated?

This is not a simple calculation. For example, suppose you are offering an investment of CU3,000 that will generate CU1,300. for each year of the next 3 years. You cannot simply use the total cash flow of CU3,900 (1,300 * 3) to determine the rate of return, as it extends over a period longer than those 3 years.

Instead, you will have to use an iterative process in which you try to use different barrier rates (or annual interest rates) until your NPV is zero.

You don't need to go deep into math to calculate this indicator - you can easily calculate it in Excel (IRR or IRR function) or on a financial calculator.

How are companies using it?

Companies typically use both NPV and IRR to value investments.

NPV tells you more about expected profitability, but financial analysts “ often rely on IRR in presentations to non-financial people».

This is because IRR is much simpler and more intuitive.

When you speak: “If I have a project where IRR is 14% and our corporate barrier rate is 10%”, your audience thinks: "Oh I understand. We get 4% more profit from this project ".

If you were to say that the NPV in this project is CU 2 million, your audience will very likely ask for a reminder of what NPV is, and it might get confused before you even partially explain the meaning of the fact that "The present value of the future cash flows from this investment using our 10% barrier rate exceeds our initial investment by CU 2 million.".

The disadvantage of this metric is that IRR is much more conceptual than NPV. Using NPV you estimate cash income Companies: Assuming all assumptions are correct, this project will generate CU2 million. IRR doesn't give you real monetary numbers.

Likewise, IRR does not address scale issues. For example, an IRR of 20% does not tell you anything about the amount of money you will receive. Is this 20% of CU 1 million? Or from CU 1? You don't have to be a mathematician to understand that there is a big difference between these numbers.

What mistakes do people make when using IRR?

The biggest mistake is to use IRR exclusively.

It is much better to analyze a project using at least one of the other methods - NPV and / or payback period.

Using only this metric can lead you to make poor decisions about where to invest your company's hard-earned money, especially when comparing projects with different timelines.

Let's say you have a one-year project with an IRR of 20% and a 10-year project with an IRR of 13%. If you base your decision on IRR alone, you can support a 20% IRR project. But that would be a mistake. You benefit from an IRR of 13% over 10 years than 20% over one year if your corporate hurdle rate is 10% over that period.

You should also be careful about how IRR accounts for the time value of money... IRR assumes that future cash flows from the project are reinvested in IRR and not in the company's cost of capital, and therefore this metric does not accurately reflect the relationship with capital and time value of money as NPV.

Modified Internal Rate of Return (MIRR), assuming that positive cash flows are reinvested in the firm's capital, more accurately reflects the cost and profitability of the project.

However, you should always use IRR in conjunction with NPV to get a fuller picture of the return on your investment.

Galtsev Dmitry Alexandrovich

The goal of any investment is profit. But any new project is inherently risky.

Therefore, tools are developed and used that allow for the initial calculation of risks and, to some extent, minimize the likelihood of investing in low-income or unprofitable projects.

Most often, when performing financial analysis, an indicator is used that operates with reduced cash flows. It is called the internal rate of return (another designation, the rate of return ( international designation, IRR).

What is IRR?

In the economic literature, the internal rate of return refers to such a discount rate, reaching which the total present value of investments in the project, otherwise referred to as cash flows or net present value (denoted by "NPV"), is equal to "0".

Speaking simple language, at such a rate, the investor investing in the project own funds, will be able, at best, to compensate them.

By doing the financial analysis the project is first considered (give the value total amount investments to the value "for today"). Only after that the value of the IRR rate of the investment project is determined. This indicator can be named in different ways (but, in any case, in the name the first word will be "internal"):

• internal rate of discount;
• internal coefficient of efficiency (option, payback);
• internal norm.

The internal rate of return, together with the NPV value, are considered as complementary criteria to assess the efficiency of the investment project implementation. The graphical relationship between them is shown in the figure.

The calculated IRR of the project under consideration is compared with similar values ​​of the market rate of return (at the time of comparison). At the same time, such factors as the timing of the project and possible risks are necessarily additionally taken into account.

The rate of the average is used as a reference value. bank deposit... A project that has an internal rate of return exceeding the average rates for investment projects (option, for deposits) will be considered profitable.

• These indicators, in the first case, are considered exclusively as net investment... At the initial stage of the implementation of any project, all of them are negative (the investor invests, but receives nothing). After a certain time, streams of a positive direction appear. For the entire remaining period of the project under consideration, the dynamics does not change.
• The second takes into account the indicators characterizing the mixed cash flows. Funds come in alternating positive and negative directions. In such cases, IRR is not used because it does not provide reliable data. It is necessary to refer to the modified indicator, MIRR.

Formula for calculating GNI

For any investment project, the following formula is used to calculate the NPV rate

IC is the amount of investment costs for the project under consideration that took place at the initial stage (cash flow of the zero stage at t = 0);

NPV is the NPV value;

CFt is a similar flow of stage t;

t - considered time period.

Formula for determining GNI

The result obtained is nominated as a percentage. Some experts consider this state of affairs to be an advantage of the method, while others attribute such an assessment to its disadvantages.

The value of the internal rate of return is considered as an indicator that makes it possible to assess the investment attractiveness of any projects by comparing with necessary level its profitability (r).

As r, the value of the indicator called WACC is used.

In this case, the following results can be obtained:

• IRR< WACC. Ожидаемая доходность менее понесённых первоначальных затрат. Подобные проекты нерентабельны;
• IRR = WACC. Indicates the comparability of such parameters as the internal rate of return and the cost of capital raised. The profitability of the project is almost zero (has the minimum acceptable values). Investing is possible after preliminary adjustment of the movement of money, an increase in total financial flows any intensity;
• IRR> WACC. The excess of GNI (rate of return) over the value borrowed money allows you to accept a similar project for further in-depth analysis.
• IRR1> IRR2 (the first of the projects under consideration has great potential over the second).

The barrier level, which is an indicator of investment costs, can change. Instead of WACC, it is permissible to calculate other criteria for comparing and evaluating discount rates.

When analyzing the profitability of an investment project, it is important to take into account the source of funds for the project. If this is the investor's personal funds, IRR should be considered as his probable income, which can be counted on by implementing the project. If the funds are borrowed (credit), then the profitability of the project is interpreted as the maximum allowable rate on loan obligations.

IRR calculation options

For work, the following calculation options can be used:

• Graphic. It is considered more convenient in cases where comparative analysis IRR of 2 or more projects, as it clearly shows the result;
• Arithmetic. In fact, such calculations of the investment project are not actually carried out manually, since Excel is used to calculate the indicator.

Calculation using Excel

Using the Excel program allows performing calculations using a special built-in function located at the following address - Formulas - Financial. This function is called the "rate of return" (meaning internal). Abbreviated designation, "VSD";

This option allows you to carry out the calculations of the investment project, to obtain the required result only if there is (at least) one negative and one positive cash flow. Otherwise, the calculated value of NPV ≠ 0, and "VSD" will start giving an error.

To get the correct result, it is imperative to take into account the sequence of receipt of funds (enter their values ​​according to the time of receipt).

When filling out the calculation form

the second line, “guess”, can usually be left blank. This does not affect the accuracy of calculations. The program considers the value of this indicator to be 10% by default.

The algorithm for performing calculations involves the use of the iteration method. Therefore, in some cases, the program may give an error, not keeping within the specified rate of cycles for recalculating the IRR of investment projects. This is usually the case when calculating cash flows received monthly over several years. To exclude a program crash in such a situation, it is recommended to put in the cell called "guess" the estimated value of the interest rate (monthly).

The program assumes that the internal rate of return is calculated taking into account the monthly cash flows in approximately equal time intervals. This allows you to get approximate values ​​of IRR rates for the period of interest (quarter, month, year).

If cash flows come in a "ragged" rhythm, this is also amenable to calculation. In such a situation, it is required to use the “PURE” function available in the program, and as the arguments to be taken into account, in addition to the cash flow cell, those in which the dates of receipt of each of them are entered are set.

Please note that this calculation must be performed exclusively using the "NETWORK" function, since the IRR will not take into account changes in time periods in the calculation process.

Graphical calculation method

It was this method that was used before the advent of the PC. Charts are built according to standard rules. The NPV value is determined by putting (in the formula for calculating it) different values ​​of the net present value and plotting them on the coordinate axis.

At the point of intersection of the constructed curve with the X-axis, we obtain the IRR for the project. With the simultaneous construction of charts for 2 or more projects, you can get a clear comparison of their investment attractiveness.

In our case, the internal rate of return obtained for project "A" is more interesting to the investor when the cost of the attracted capital is ≤ 13.09% (NPV is higher).

At a cost exceeding this value, option "B" becomes more interesting.

If you choose to use the IRR indicator as the only assessment criterion, then option “B” will be preferred. But the graph shows that this decision is wrong at the “point of indifference” (13.09%).

That is why, when evaluating an investment project, it is recommended to use IRR only as an additional criterion if it is required to evaluate two or more projects that are mutually exclusive.

Multiple GNI problems

The problem arises when assessing IRR for projects with non-ordinary NPVs (during the implementation period, they change sign more often than once a year). The sign change from positive to negative values ​​takes place at least once.

Formula for determining IRR:

An ordinary flow has only one real solution to such an equation, all the others are imaginary.

The extraordinary allows for two or more valid solutions, which creates the problem under consideration.

Substituting the corresponding values ​​in the formula, we get two solutions: 0.824254 and 0.050699. The project under consideration has two IRRs, 82.4254% and 5.0699%.

Let's represent it graphically.

If the internal rate of return is greater than the first and less than the second value, the project is not attractive to the investor. The rest of the range gives a positive value.

The choice of one of the obtained values ​​is carried out according to one of the following options;

• The NPV method is used;
• Instead of IRR, MIRR is considered, its modified version.

Modified IRR (MIRR)

It is possible to use the internal rate of return only when calculating the initial investment. If funds are re-invested, this indicator does not work.

For such cases, the MIRR option has been developed, which represents a discount rate that reflects the situation when the terminal value (as the future total value of flows entering the investment project is called) is reduced to the actual point in time. It is taken equal to the same value of the outgoing flows withdrawn from the project under consideration.

TV is the terminal cost mentioned above;

PV CONST - the current value of the total costs of the investment project;

N is the investment horizon.

For simplicity of calculations, the equation is simplified

CIF t - received profit (cash flows included in the project during time t);

COF t - expenses incurred for investment (outgoing flows);

n is the number of time intervals;

r - asset value (weighted average);

d is the interest rate used when reinvesting in this project.

Join over 3 thousand of our subscribers. Once a month, we will send to your email a digest of the best materials published on our website, on our LinkedIn and Facebook pages.