Internal rate of return

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 of discounted income from the project (positive cash flow) is equal to the discounted amount of investments (negative cash flow, reduced volume of investments) , i.e. when it is 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.

The internal rate of return reflects both the return on 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 the main question of the investor: to what extent the cash flow expected from the project will justify the investment costs 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 of 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

The internal rate of return is used to evaluate a project or to benchmark against other projects. To do this, the IRR is compared with the effective discount rate, 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.

A graphical method for finding the internal rate of return

Suppose that we are going to invest 10 thousand 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 have to be equal in magnitude (as is the case), but they do. at regular intervals, for example monthly or annually. At the same time, the structure of cash flows must necessarily contain 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 cash flows differ in size in different periods, then they must be indicated 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, but this function can only be used 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 Is 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 time periods t;
CIF t- cash inflow;
r- discount rate, which can be calculated as the weighted average cost of capital WACC;
d- the interest rate of capital reinvestment;
n- the number of time periods.

MS Excel has a special built-in financial function of the Ministry of Internal Affairs 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 possibility of comparing various investment projects with each other in order to determine more attractive from the point of view of economic efficiency of using the 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) indicator 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).