The internal rate of return shows. Determining the internal rate of return to assess the attractiveness of the project. Application of internal rate of return

Internal rate of return is one of the most commonly used measures to evaluate investments. Investments with a higher internal rate of return are considered more profitable than investments with a low internal rate of return. This free online tool will help you calculate IRR, it also generates a dynamic graph to show the relationship between NPV and discount rates.

Example 1 | Example 2 | Example 3

Data Entry Batch(enter or copy your details in the field below)

year	Cash flow in	Cash flow from (minus)	Flow Net Cash
(($index + 1))
general:

Reset + Add line

Using the internal rate of return (IRR) calculator

Internal rate of return (IRR)- IRR is the rate at which the NPV of an investment equals zero
initial investment- Initial investment in the first year
Cash-In- Annual cash flows
Cash-out- Annual cash flows
Flow Net Cash- Cash acceptance minus cash out

What is IRR (internal rate of return)

IRR is the rate of return that makes the NPV (net present value) equal to zero, IRR is also called the effective interest rate, or rate of return. It is used to evaluate an investment or project. Generally, the higher the IRR, the greater the feasibility of the project.

How to calculate IRR?

It's almost impossible to talk about IRR without mentioning NPV. The NPV formula is as follows:

Since IRR is the rate to NPV = 0, we get the following functions:

or
Benefit PV - Cost PV = 0

G is the IRR, once unknown, it can be solved using numerical or graphical analysis methods.

Let's look at an example:
An $85,000 investment returns $20,000 per year over a 5 year lifespan, what is the rate of return on the investment?
Solution:
20000 / (1 + I) + 20000 / (1 + I)^2 + 20000 / (1 + I)^3 + 20000 / (1 + I)^4 + 20000 / (1 + I)^5 = 85000
The IRR is 5.68%.

IRR vs NPV

IRR is a rate, percentage, while NPV is an absolute value. IRR is commonly used to calculate the return on investment or project. If the IRR exceeds the cost of capital, the investment or project can be accepted. Otherwise, it must be rejected. NPV is used to measure the total value that an investment will generate over a given period. If the NPV is greater than zero, the investment is generally considered acceptable.

Calculate IRR in Microsoft Excel

If you don't have Internet access or feel more comfortable working with Microsoft Excel,

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

Any time you propose a capital expenditure, you can be sure that senior managers will want to find out the return on investment (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 a project breaks even (i.e. pays for itself).

This metric is typically used by financial analysts in conjunction 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 cash flow income of the project and then compare it with your company's barrier rate (i.e. the minimum expected level of profitability of your company). If the IRR is higher, then the investment is profitable.

How is IRR calculated?

This is not a simple calculation. For example, let's say you offer an investment of CU3,000 that will yield 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 beyond those 3 years.

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

To calculate this indicator, you do not need to go deep into mathematics, - you can easily calculate it in Excel (VSD or IRR function) or on a financial calculator.

How do companies use it?

Companies typically use both NPV and IRR to evaluate 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 more simple and intuitive.

When you say: "If I have a project where the IRR is 14% and our corporate hurdle 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 of this project is CU2 million, your audience will very likely ask for a reminder of what NPV is and may become confused before you even partially explain the meaning of what “the present value of future cash flows from this investment using our 10% hurdle rate exceeds our initial investment by CU2 million.”.

The downside to this metric is that IRR is much more conceptual than NPV. Using NPV, you estimate the cash return of the company: assuming all assumptions are correct, this project will generate CU2 million. IRR doesn't give you real monetary numbers.

Likewise, IRR does not address issues of scale. For example, an IRR of 20% doesn't tell you anything about the amount of money you'll receive. Is it 20% of CU 1 million? Or from 1 unit? 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 the project using at least one of the other methods - NPV and/or payback period.

Using this metric alone can lead you to make poor decisions about where to invest your company's hard-earned money, especially when comparing projects that have 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 are better off with an IRR of 13% over 10 years than an IRR of 20% over one year if your corporate hurdle rate is 10% over that period.

You should also be careful about how IRR takes into account the time value of money. IRR assumes that future cash flows from a project are reinvested in IRR rather than in the company's cost of capital, and therefore it does not reflect the relationship to capital and the time value of money as accurately as NPV.

Modified internal rate of return (MIRR), which assumes 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 more complete picture of how much return your investment will generate.

No investor invests money blindly without doing preliminary research: he must be confident that it will pay off or, at a minimum, be returned. To do this, the potential profitability of the investment project is assessed. Of course, this cannot be done except comprehensively, using a number of specially developed indicators, the most important of which is the internal rate of return.

Let's consider what are the features of the investment research method based on calculating the internal rate of return - IRR.

What is internal rate of return?

No one can calculate with 100% probability what income will be received from the invested funds. There are too many variable factors that can affect the implementation of a business project financed by an investor. However, you can minimize the risk of inaccuracy if you use a relative rather than an absolute assessment.

The interest rate at which the investor is guaranteed to recoup his investment, but will not receive a profit, is called internal rate of return(VND). The norm is that all cash flows of a given investment project will be offset in total. In other words, the costs of an investment project at some point in time are balanced by the income received (they say that the project “broke to zero”).

IMPORTANT! The word “internal” in the definition of this investment rate means its dependence on the properties of the project itself, and not on external factors.

Experts may call the internal rate of return differently. The following are found names:

VND is a Russian abbreviation;
IRR is an English abbreviation for “Internal Rate of Return” - “internal rate of return”;
internal rate of return;
internal rate of return;
internal rate of return on investment;
maximum efficiency of capital investments;
percentage rate of return;
discounted flow of real money;
financial rate of return;
own rate of return.

ATTENTION! This norm can be considered the limit, since going beyond its limits already means a loss for the investor.

Indicators for calculating IRR

Mathematically, calculating the internal rate of return is not that complicated, but the formula includes many additional indicators that must be taken into account. Among them:

NPV– from the first letters of the expression “Net Present Value” (“net present value”) - the sum of all cash flows of a given project, reduced to a general indicator when netting income and costs;
CF– cash flows (from “Cash Flows”) – the magnitude of various inflows and outflows of finance, including invested funds, during a selected period of time t(usually a year is taken). For an investment project, the first cash flow - the investment itself - will naturally have a negative value (this is a cost).
R– discount rate, that is, the percentage at which an investor can receive funds for investments (take a bank loan, sell their shares or use internal funds).
WACC– weighted average cost of capital (from Weighted Average Cost of Capital) – if several sources of raising money are used at once, then the interest rate will represent the average value, calculated proportionally.

IMPORTANT INFORMATION! It would be very simple to calculate the internal rate of return if we knew all the necessary indicators. However, in practice it is impossible to determine the exact values of cash flows and unambiguously calculate the discount rate. Therefore, for each individual project, the degree of dependence of NPV on the interest rate is assessed.

Formula for calculating internal rate of return

IRR– this is the value of R at which NPV is equal to zero. Therefore, this indicator can be derived from the formula for calculating the present amount of cash flows.

In this formula:

0 means NPV value;
n – number of time periods studied;
CF t – cash flows at an accounting point in time.

Methods for calculating GNI

It is not possible to calculate the internal rate of return manually, since if you derive the IRR value from it, it will turn out to be a 4th power multiplier. This indicator can be calculated in several ways:

use a special financial calculator;
use the Excel program, in which this function is built into the “Financial Formulas” section called IRR (“internal rate of return”);
use one of the online calculators;
apply a graphical method (used before the widespread use of personal computers).

Advantages and disadvantages of the IRR method

To assess investment opportunities, two methods are mainly used - calculation NPV And IRR. The method for determining the internal rate of return is relative, so it cannot claim high accuracy, but it has a number of benefits:

helps to easily assess the profitability of an investment project;
shows the maximum allowable cost for investment in the project;
allows you to compare different projects in terms of profitability, even if they differ in scale and duration over time.

Disadvantages of the IRR method:

it is impossible to calculate the absolute return on an investment (that is, exactly how much money – rubles, foreign currency – it can bring to the investor);
it is possible that the investment effect may be overstated if the IRR is very different from the company's level of reinvestment (that is, positive cash flows are “returned” to the investor at an interest rate that does not coincide with the IRR);
when calculating, the sequence of cash flows is neglected (for the formula it does not matter in what order the costs occur and the profit comes, whereas in practice this can be of decisive importance);
distortions are possible when assessing mutually exclusive investment projects.

Interpretation of internal rate of return

The main rule by which the possibility of investing is assessed by the IRR indicator: a project can be accepted if the IRR is higher than the weighted average cost of capital for the company (WACC). This means that it is worthwhile for an investor to borrow money for an investment, and it is more likely to generate incremental returns.

FOR EXAMPLE. The bank can provide the investor with money at 12% per annum. An investor is going to take out a loan and invest in a project whose IRR is 16%. This means that 16% per annum is the upper limit at which you can borrow money for this project. If the project really brings 16% profit, 4% will remain in favor of the investor; in any case, he will be able to return the borrowed funds without loss to himself.

Example of application of VND

Let's give a real-life example of assessing internal profitability that an ordinary citizen, and not just an entrepreneur, can understand.

Renting out an apartment seems to be a profitable undertaking. But it all depends on whether this apartment is owned. If this is so, then the initial investment is zero, so the project is certainly profitable. But what if you plan to invest in buying an apartment, rent it out, and then sell it in order to repay the loan taken for it?

Let's consider whether this project will be profitable. Let the apartment cost 5 million rubles, and the rental amount for the year will be 25,000 rubles. per month. Let's neglect tax expenses for registering the purchase of an apartment and rent. Over 3 years, rental income will be 25,000 x 3 = 75,000 rubles. Let’s imagine that the real estate market conditions have not changed in 3 years and the apartment can be sold for the purchase price. This means that after three years the investment will bring an income of 75,000 + 5 million rubles. The IRR of such a project will be approximately 6%.

As you know, the bank lending rate is approximately 9%, which means that if you have free funds at your disposal (for example, having received them as an inheritance), it is more profitable to put them on deposit than to buy an apartment for rent.

But if the cost of the apartment increases significantly over the years, the GNI of the project will also change for the better.

Before choosing any investment project, the Internal Rate of Return (IRR) is calculated. In this case, the amount of net present value income is calculated at different discount rates, which can be done either manually or using automated methods. Thanks to this indicator, you can determine the profitability of a possible investment and the optimal loan rate. However, this method also has its drawbacks. What IRR is in practice and how to calculate the indicator using the calculation formula will be shown below.

Internal Rate of Return or IRR in Russian is defined as the internal rate of return (IRR), or in other words, the internal rate of return, which is often called the internal rate of return.

This internal rate of return is the interest rate at which the discounted value of all cash flows of the project (NPV) will be equal to zero. Under such conditions, the absence of losses is ensured, that is, income from investments is identical to the costs of the project.

The economic meaning of the calculation is to:

Characterize the profitability of a potential investment. The higher the IRR rate of return, the higher the project’s profitability indicator, and, accordingly, when choosing from two possible investment options, ceteris paribus, choose the one where the IRR calculation showed a higher rate.
Determine the optimal loan rate. Since the IRR calculation shows the maximum price at which the investment will break even, it can be related to the rate of borrowing that the company can take out for the investment. If the interest on the planned loan is greater than the received GNI value, then the project will be unprofitable. And vice versa - if the loan rate is lower than the investment rate (IRR), then borrowed funds will bring added value.

For example, if you take a loan on which you need to pay 15% per annum and invest it in a project that will bring 20% per annum, then the investor makes money on the project. If an error is made in assessing the profitability of the project and the IRR turns out to be less than 15%, then the bank will need to give back more than the project activity will bring. The bank itself does the same, attracting money from the population and giving it to creditors at a higher interest rate. Thus, by calculating IRR, you can easily and simply find out the acceptable upper level - the limit on the cost of borrowed capital.

In fact, these opportunities are at the same time the advantages that calculating IRR gives the investor. An investor can compare promising projects with each other in terms of the efficiency of capital use. In addition, the advantage of using IRR is that it allows you to compare projects with different periods of investment - investment horizons. IRR identifies a project that can generate large profits in the long term.

However, the peculiarities of GNI are that the resulting indicator does not allow for an exhaustive assessment.

To assess investment attractiveness (including in comparison with other projects), IRR is compared, for example, with the required return on capital (effective discount rate). Practices often use the weighted average cost of capital (WACC) for this comparative measure. But, instead of WACC, another rate of return can be taken - for example, the bank deposit rate. If, after making calculations, it turns out that the interest rate on a bank deposit is, for example, 15%, and the IRR of a potential project is 20%, then it would be more appropriate to invest money in the project rather than place it on deposit.

Internal rate of return formula

To determine the IRR indicator, they rely on the equation for net present profitability:

Based on this, the formula for the internal rate of return will look like this:

Here r is the interest rate.

The same IRR formula in general form will look like this.

Here CF t is cash flows at a point in time, and n is the number of time periods. It is important to note that the IRR indicator (as opposed to NPV) is applicable only to processes with the characteristics of an investment project - that is, for cases where one cash flow (most often the first - the initial investment) is negative.

Examples of IRR calculations

Not only professional investors, but also almost any person who wants to profitably invest their accumulated funds are faced with the need to calculate the IRR indicator.

An example of calculating IRR for business investing

Let us give an example of using the method of calculating the internal rate of return under the condition of a constant barrier rate.

Project characteristics:

The size of the planned investment is $114,500.
Investment income:

in the first year: $30,000;
in the second year: $42,000;
in the third year: $43,000;
in the fourth year: $39,500.
The size of the compared effective barrier rate is 9.2%.

This calculation example uses the successive approximation method. “Types” of barrier rates are selected so as to obtain the minimum NPV values modulo. Then approximation is carried out.

Let's recalculate cash flows in the form of current values:

PV1 = 30000 / (1 + 0.1) = $27272.73
PV2 = 42000 / (1 + 0.1) 2 = $34710.74
PV3 = 43000 / (1 + 0.1) 3 = $32306.54
PV4 = 39500 / (1 + 0.1) 4 = $26979.03

NPV(10.0%) = (27272.73 + 34710.74 + 32306.54 + 26979.03) - 114500 = $6769.04

PV1 = 30000 / (1 + 0.15) 1 = $22684.31
PV2 = 42000 / (1 + 0.15) 2 = $31758.03
PV3 = 43000 / (1 + 0.15) 3 = $28273.20
PV4 = 39500 / (1 + 0.15) 4 = $22584.25

NPV(15.0%) = (22684.31 + 31758.03 + 28273.20 + 22584.25) - 114500 = -$9200.21

Assuming that on the segment a-b the NPV(r) function is rectilinear, we use the equation for approximation on this section of the line:

IRR calculation:

IRR = ra + (rb - ra) * NPVa /(NPVa - NPVb) = 10 + (15 - 10)* 6769.04/ (6769.04 – (-9200.21)) = 12.12%

Since a certain dependency must be preserved, we check the result against it. The calculation formula is considered fair if the following conditions are met: NPV(a) > 0 > NPV(b) and r(a)< IRR < r(b).

The calculated IRR shows that the internal payback ratio is 12.12%, which exceeds 9.2% (the effective barrier rate), which means that the project can be accepted.

To eliminate the problem of multiple determination of IRR and avoid (with alternating cash flows) incorrect calculation, an NPV(r) graph is most often constructed.

An example of such a graph is presented above for two conditional projects A and B with different interest rates. The IRR value for each of them is determined by the intersection with the X-axis, since this level corresponds to NPV=0. So in the example you can see that for project A the intersection with the scale will be at the point marked 14.5 (IRR=14.5%), and for project B the intersection will be at the point marked 11.8 (IRR=11.8%) ).

Comparative Case Study of Private Investment

Another example of the need to determine IRR can be an illustration from the life of an ordinary person who does not plan to launch any business project, but simply wants to make the most profitable use of accumulated funds.

Let’s say that having 6 million rubles requires either taking them to the bank at interest, or purchasing an apartment to rent it out for 3 years, and then sell it, returning the main capital. Here the IRR will be calculated separately for each solution.

In the case of a bank deposit, it is possible to place funds for 3 years at 9% per annum. Under the conditions offered by the bank, you can withdraw 540 thousand rubles at the end of the year, and after 3 years you can withdraw all 6 million plus interest for the last year. Since a deposit is also an investment project, the internal rate of return is calculated for it. Here it will coincide with the percentage offered by the bank - 9%. If the starting 6 million rubles are already available (that is, you do not need to borrow them and pay interest on using the money), then such investments will be profitable at any deposit rate.
In the case of buying an apartment, renting it out and selling it, the situation is similar - funds are also invested at the beginning, then income is taken and, by selling the apartment, the capital is returned. If the cost of the apartment and rent do not change, then the rent at the rate of 40 thousand per month for the year will be equal to 480 thousand rubles. Calculation of the IRR indicator for the “Apartment” project will show 8% per annum (subject to uninterrupted delivery of the apartment throughout the entire investment period and a return of capital in the amount of 6 million rubles).

It follows from this that, if all conditions remain unchanged, even if there is equity (rather than borrowed) capital, the IRR rate will be higher in the first project “Bank” and this project will be considered more preferable for the investor.

At the same time, the IRR rate in the second case will remain at the level of 8% per annum, regardless of how many years the apartment will be rented out.

However, if inflation affects the cost of the apartment, and it consistently increases annually by 10%, 9% and 8%, respectively, then by the end of the billing period the apartment can be sold for 7 million 769 thousand 520 rubles. In the third year of the project, this increase in cash flow will demonstrate an IRR of 14.53%. In this case, the “Apartment” project will be more profitable than the “Bank” project, but only if there is equity capital. If, in order to obtain the starting amount, you need to apply to another conventional bank for a loan, then taking into account the minimum refinancing rate of 17%, the “Apartment” project will turn out to be unprofitable.

In our reflections, we have already repeatedly drawn attention to the fact that it is only possible to evaluate the effectiveness of projects comprehensively, using a number of special tools, among which the group of indicators NPV, PI, PP, IRR, MIRR occupy a leading place. In this article, I suggest you analyze such an indicator as the internal rate of return, which can offset some of the shortcomings of net present value and reveal a completely different facet of investment return, which is a priori set by cash flows as a result of the project.

What dictates the generation of a project's CF?

The NPV indicator has a special property. The method for calculating it largely depends on the investor's position regarding the rate of return, and this is often subjective. The fact is that it is almost impossible to adequately establish the rate of return on investments solely by calculation. The longer the event, the greater the distortion due to a logical error creeping in during the calculation of the discount rate. The impact of inaccuracy and subjective assessment can be minimized if a different approach is used, which is implemented in the IRR method of an investment project. In the literature, this indicator has a number of interpretations, so let’s denote its main names:

IRR or Internal Rate of Return;
internal rate of return or IRR;
internal rate of return or IRR.

Let's take a closer look at this method. The fact is that the calculation of step-by-step cash turnover generated by the implementation of an investment task already carries a certain profit potential, displayed in Net Cash Flow. On the other hand, the more the investor sets requirements for the efficiency of his investments, striving for the expected profit, the lower the NPV value he condemns the project to. At some point the net present value reaches zero (look at the graph below).

Graph of NPV versus project rate of return

The internal rate of return shows us the level of return on investment generated by the project, for which all project costs are covered from the income received. This indicator is quite informative; it determines the loss of value of upcoming DS receipts. The rate of return (profitability) is called internal because it is determined by the internal properties of the project, the existing proportions of costs and results.

Thus, the generation of project cash flows determines its own profitability logic. The internal rate of return characterizes the state of the level of capitalization of income (profit), which is already included in the forecasts of disposals and receipts by project steps throughout the entire implementation of the event. If we accept that these plans for the movement of the VA will be implemented, then a certain internal value of investment capital will appear, by which the IRR is meant.

IRR calculation

The IRR method assumes equality of discounted cash flows at the desired value of the discount rate and the size of the investments made. Mathematically calculating the IRR value without available tools presents certain difficulties. However, modern application software products, such as MS Excel, have an integrated function for calculating GNI. Following the logic justified above, the indicator formula is derived from a number of mathematical expressions, in the first of which it is generally accepted that investments are made simultaneously at the start of the project.

The original form of the expression preceding the derivation of the IRR formula

The formula is, to put it mildly, non-trivial. As you and I can see, at first glance, solving the equation for IRR is possible using the method of sequentially implemented iterations, simply by the selection method. The internal rate of return corresponds to the rate r for which NPV is zero. Based on the proposed definition, IRR can be considered as the result of calculating the positive root of the equation demonstrated above.

However, using tabulated values for discounting arguments, IRR can be calculated by building a special mathematical calculation model. To do this, it is necessary to select a conditional interval within the values of r1 and r2, between which the NPV function changes sign. Thanks to this assumption, the internal rate of return as another indicator of project efficiency is calculated not empirically, but mathematically, and the IRR formula takes on the following form.

Type of IRR formula

In addition to its advantages, the IRR method also has a number of disadvantages, among which the following can be highlighted.

Indifference of the IRR rate to the sequence of inflows and outflows of funds. As an example, borrowing or lending funds can produce the same IRR result.
The internal rate of return may have several solutions if the sign of CF changes repeatedly.
The probability of error in IRR results when considering mutually exclusive projects. The method assumes that the opportunity costs for cash flows are identical throughout the entire implementation of the task, which can lead to significant distortions.

Examples of calculating GNI

Let's consider an example of calculating the IRR indicator using MS Excel 2010. Let's assume that the investor invests a round sum of 100 million rubles in the construction of the facility. As a result of investments, he is interested in the amount of profit corresponding to a rate of return of 10%. The implementation of the event is planned for 7 years; for each step, the values of net cash flows are calculated, which are further presented in tabular form.

An example of calculating the IRR of an investment project based on MS Excel 2010 formulas

Using the built-in functions in the “Financial” category, we can calculate the values of NPV (the “NPV” function) and IRR (the “NET” or “IRR” functions). Since the periods of steps are equal in duration, we can apply the “VSD” function. The Excel Function Wizard dialog box is shown below. Thus, we get the opportunity to automatically calculate the “Internal rate of return” indicator, which for our example was 29%.

Excel Function Wizard dialog box for selecting VSD

What should we do if we don’t have such a tool for calculating IRR at hand? It is possible to approximate the value of the indicator using the visualization method. Let's take the same example and build the corresponding graphical model. Let us make the assumption that the investor is considering three options for the rate of return (return), which correspond to discount rates of 10, 20 and 30%. Let's calculate NPV for each option and plot the dependence of the NPV value on r at three points. The intersection point of the X-axis graph corresponds to the IRR value, which is approximately equal to 0.29.

Method for determining IRR by constructing a visual model

In this article, we analyzed an important indicator from the main parameters of the effectiveness of a unique investment task. The internal rate of return has a simple rule for valuing a project that the IRR must be at least higher than the current lending rate. In conclusion, I would like to remind you once again that high-quality selection of projects is possible only with an integrated approach to the consideration of performance indicators and other evaluation tools.