Rozrakhunok irr at excel butt. Rozrakhunok IRR. Internal rate of return: value, formula and application. For more help with a calculator

The project's internal rate of return (IRR) is an indicator that the discounted net penny flow (NPV) to the project is zero. This is the same discount rate for which NPV is equal to zero (on which the IRR breakdown formula will be based).

This show is about how profitable the project is shown in terms of the flow term of the investment model (if the model is generated for 7 years, then for seven years, if for 10, then for ten years, meaning that the same project is shown it's different the level of profitability for various terms of the model).

This figure can be compared with the bank interest rate for the investment project. So, since the IRR for a project for 5 rubles is equal to 30%, then you can confirm that if you invest an amount in the project, then for 5 rocks you deduct an amount equivalent to the income that you would have received by depositing that amount into the bank at 30% interest.

Nuances of the rate breakdown

When developing and analyzing the bet, it is necessary to take into account certain features of the development, for example:

1. As was already said earlier, for one and the same project, and if the rate increases over the course of several hours, the rate changes. The profitability rate for a project for 5 years is in no way comparable to the profitability rate for a project for 7 years.
2. the internal rate of return cannot be financed for a period shorter than the project's payback period (so, if the simple payback period for a project is 7 times, then you can't cover the IRR for 6 times);
3. If you cover the internal rate of profitability for any long period (for example, 7 years), then you must understand that this rate shows hundreds of profitability itself for 7 years, but the business continues to operate and generate income (or generate profits) and, obviously, , as was said earlier, there will be changes. For which the approximate value of the project is insured at the time of completion of the term of the per-model model, the amount of its implementation (if we are selling our business) and IRR is already insured with the settlement of its sale.

Why is the internal rate of return needed?

This indicator requires a significant profitability of investment projects and shows what profitability the project will provide against other investment options using a similar term. For an investor, this indicator allows compensation for profitability as a number of investment projects, and alternative investment methods.

However, I would like to point out that it is not possible to analyze the internal profitability rate alongside other indicators of project efficiency, such as NPV.

Formula for rozrunku rates

To develop the indicator of internal norms of vicoris growth

Rozrakhunok in Excel

To find out how the indicator is processed in Excel, enter the output data. For example, the cost and expenditure of funds for the project looks like an ongoing order (thousand rubles):

Statta nadhojen/vitrati	1 rik	2 rik	3 rik	4 rik	5 rik
Investments	1 000
Revenue from operating activities	2 000	2 200	2 400	2 600	2 800
Spend from operational activities	1 800	1 950	2 100	2 250	2 400
Net penny flow for the period	- 800	250	300	350	400

To expand the internal rate of return in Excel, use the IRR (range) function. As a range, the dynamics of the net penny flow are selected. Rozrahunok IRR representations are lower on the little one:

As a matter of fact, the internal profitability rate for this application is still 21%.

Others, apply the rozhrakunka

You can find out how to determine the IRR insurance policy for a specific business plan by following the instructions or for an additional search form.

In financial analysis of the profitability of investments, the internal rate of return is especially important. It is recommended that all organizations carry out screening tests before selecting an investment project.

The process of calculation and analysis is available in this article.

Viznachennya

The internal profitability rate (IRR) is a fixed rate that will ensure the existence of surpluses on deposits, the sameness of income from investments and expenses of this project. Otherwise, it seems that the difference between the tide and the tide of pennies, so that the net worth (NPV), is equal to zero, is of marginal importance.

It is best to develop this display using additional special programs, such as Excel. You can also use a financial calculator.

Without the implementation of automated calculation methods, hundreds of hundreds of values ​​will end up in a path of trivial losses. In this case, the amount of net induced profit is determined at different discount rates. This method is called by iteration method.

For example, NPV at a rate of 15% is greater than zero and negative at 5. It is possible to make a replacement so that the IRR is in the range of rates from 5 to 15%. Further down, the number steadily increases and the calculation is repeated until the NPV reaches zero.

Anything you need to get rid of?

The economical position of the indicator is that it characterizes the following points:

• The taste of the possible investment. When a business chooses which project to make investments, it is guided by the size of its indicator. The larger the GNI, the greater the profitability of the investment.
• Optimal loan rate. VND is the maximum price for which the investment is forfeited. As the company plans to withdraw credit for this investment price, following the increase in respect of the market rate. If the amount of money for the loan is higher, the lower the value, then the project will bring in profits.

When selecting indicators for assessing future investments, it is necessary to take advantage of the advantages and disadvantages of this method.

Until the positive aspects of the stagnation of GNI remain:

• Equalization of possible investments among themselves to ensure the effectiveness of the investment in capital. As a business, choose the investment that has the highest interest rate at the same rate.
• Alignment of projects with different investment horizons, depending on the time period during which the investment is made. In this case, when the GNI of various possible investments is set, the one that generates the greatest income in the long term is determined.

Bring to the main deficiencies and negative factors:

• Ease of payment forecasting. For additional expansion, this amount cannot be transferred, depending on the amount of the deposit due. The size of the profit is influenced by various factors, risks, the situation on the micro and macroeconomic level, which are not included in the calculation.
• It is impossible to calculate the absolute value of the money spent on pennies. IRR is a final indicator, it specifies only those hundreds in which the deposit is still not beatable.
• Reinvestment is not insured. These deposits are transferred to the warehouse for hundreds of thousands of items. Then the process of increasing the amount of investment and income from it begins. The VNI structure does not convey such potential, so the indicator does not reflect the real profitability of these expenditures.

Formula and butt

To determine the method of calculating GNI, the following formula is used:

• NPV – NPV size;
• IC – investment amount;
• CFt – influx of pennies over a time-hour period;
• IRR – internal profitability rate.

As a doctor, you can see that the patient is covered by the following formula:

De r – monthly rate.

To better understand how to examine the internal income, take a look at the butt. Let the project receive an investment of 1 million rubles. According to the previous forecast, the first fate of income becomes 100 thousand. crb, for another - 150 thousand. crb., on the third - 200 thousand. crb., on the fourth - 270 thousand. crb.

The development of the display without the use of special programs must be carried out in an iterative manner. Why do you need to sign a low order:

Let us note that the highest market rate of the loan taken for the deposit is 23%.

If the organization is obligated to pay off positions at a rate greater than the established amount, then the project will be crowded. Businesses need to know the best minds.

Rozrakhunok in Excel

The display structure is easiest to create using automated and automated tools, for example, Excel. This program has built-in financial formulas that allow you to make payments quickly.

For the size distribution є VSD function. However, this formula works correctly only due to the presence in the table of at least one positive and one negative value.

The formula for the pouch center looks like this: =VSD(E3:E12).

You can watch the procedure for breaking down this program in the following video:

Analysis of the result

The indicator is being developed to analyze the effectiveness of potential investments. In order to evaluate the value of investing pennies, GNI will be equal to the previous level of profitability. Often, in this case, the value of equity capital (SBK) is of central importance.

The SBK indicator characterizes the minimum amount of enterprise income that can ensure that the founders repay the funds spent on capital deposits. Most investment decisions are taken from these numbers.

Also, the high-hundred rate for loans is often taken as an indicator of profitability. This method allows you to determine whether your project can effectively earn positional costs.

The value of investments in the enterprise is determined based on the ratio of IRR and profitability indicator (P):

• VSD = P. This means that the investment is within the maximum acceptable level. To ensure the effectiveness of your deposits, please review your deposits first, speed up flows, and terms. Also, during the analysis of investments, their regular analysis is carried out.
• VSD>P. This is consistent with the fact that the investment will cover your security. This investment can be made as soon as possible, as a result of further financial analysis.
• VSD<П . This means that the analysis of the project has less profitability, less expenditure on capital, loans, etc. Organizations are recommended to rely on deposits, otherwise they will not generate income.
• VSD1<ВСД2 . This relationship shows that one of the transferred deposits is more profitable than the other.

The IRR function rotates the internal rate of return of low cash flows represented by their numerical values.

Description of the VSD function

Turns the internal rate of return on low cash flows, represented by their numerical values. In exchange for anonymity, penny sums between these flows may fluctuate. Protein obligatory mental health is the regularity of work (for example, as soon as possible). The internal profitability rate is the total rate that is accepted for investment, which consists of payments (negative values) and income (positive values), which take place in the advance one by one and, however, for the trivalist period.

Syntax

=VSD(meaning; [suppression])

Argumenti

significance

A linguistic argument. Massive or sent to the companies to create numbers for which it is necessary to maintain the internal rate of return.

• The meaning of guilty revenge has one positive and one negative meaning.
• The VSD function needs to use a value order to interpret the order of penny payments. Please make sure that the payment and payment requirements have been entered in the required order.
• If the argument is massive or sent, with text, logical meanings or empty lines, such meanings are ignored.

A non-linguistic argument. The value is, apparently, close to the result of VSD.

• Microsoft Excel uses the iteration method to calculate VSD. The VSD function calculates cyclical calculations, starting from the value of the “suspended” argument, until the result is obtained with an accuracy of 0.00001%. Since the VSD function cannot cancel the result after 20 trials, the value of the #NUMBER! rotation is rotated.
• In most cases, for calculating the additional VSD function, there is no need to specify the “omission” argument. If this is omitted, the value of 0.1 (10%) is transferred.
• The VSD function rotates the value of the #NUMBER! But the result is far from clear, try repeating the calculation of other values ​​for the “omission” argument.

Respect

The function of the VSD is closely related to the function. The rate of return, which is calculated by the VSD function, is associated with a net zero flow rate. The relationship between the NPV and IRR functions is shown in the following formula:

NPV(VSD(A2:A7),A2:A7)

up to 1.79E-09 (In view of the accuracy of the design of the VSD function, the value is set to zero).

Apply it

Butt1 Butt task

Zavdannya "Buy orenduvati"
You consider the purchase or rental, say, vantage, as it brings you income (for example, a transport company). You can buy a vantazhivka for 2.5 million rubles (figures taken from the table), rent will cost you 600 thousand rubles/rik. You know that the term for the corysty of vantage is five rocks, after which there is a surplus of, say, 400 thousand. After the rental, the property is lost to the landlord. It is acceptable that payment be made in advance for a period of time. You don’t have a lot of money to buy, but you still have the opportunity to get financing at 18% interest rate. What's better?

IRR is the Internal Rate of Return, which is translated by the Russian language as “internal profitability rate”. This is the name of one of the two main methods for assessing investment projects. There were a lot of articles on the Internet with a short summary of those who are assistants in financial analysis. Their biggest minus is that they contain a lot of mathematics and little explanation.

This article provides not only the formula and value of IRR, but also the application of the breakdown of this indicator and the interpretation of the results.

IRR – what is it? IRR formula.

IRR and internal rate of return is the rate of return, if it is established that all penny flows of the investment project (NPV) are equal to zero. This means that for such rates, the investor can recover his initial investment, but no more. Those who use the IRR indicator to praise investment projects are discussed a little further in this article. First of all, you need to learn to understand the value of the internal profitability rate IRR, what is also called the internal rate of profitability.

The mathematics behind the IRR calculation is simple. The best way to look at them is on the elementary butts. For the development of the display in one of the larger earlier articles on this site, there were vikoristan projects A and B with the same amount of primary investment (10,000), and with varying amounts of cash inflows at the onset of the 4th. It will be handy to quickly get hold of these butts for the implantation formula and the rozhunka of the IRR show.

It has been established (until today) that the totality of all penny flows for several projects is calculated using the formula:

de NPV – net cash flow, CF – cash flows, R – % rate, capital yield, 0,1,2,3,4 – number of periods in an hour at the current moment.

If we equate NPV to zero, and replace CF with penny flows similar to the skin project, then the equals will lose one change of R. The bet is hundreds, as will be the decisions of this equalization, then. when the sum of all additional contributions equals zero, it will be called IRR or internal rate of return.

For Project A, I can see the jealousy:

For project B, you can write a similar formula for the IRR breakdown, but the penny flows will be different:

To make it even clearer, you can add penny flows from the project to the hour scale and detect discounting automatically. Let's say, for project A, the internal rate of return structure can be presented in this way:

Basically, for any investment project, the formula for the IRR breakdown looks like this:

where CF t - penny flows into the project at time t, n - Number of periods per hour, IRR - internal rate of return. Please note that the understanding of IRR at the expense of NPV may have less impact on the investment project, then. if one of the penny flows (namely the first) is negative. This negative penny flow will be an investment. In the other case, we never reject the NPV, which is equal to zero.

Development of internal norms of profitability using additional Excel programs - applications

It is impossible to manually find out the IRR values ​​for projects A and B using a basic calculator, because in this case the level of the 4th stage comes out (in this case the multiplier of IRR 4 is the rate of the fourth stage). The problem of completing such a level of the nth level can be solved either using an additional financial calculator, or, more simply, you can quickly use the function in Excel. This function is located in the Formulas -> Financial section, and is called the IRR (internal rate of return).

For project A, the IRR value, as can be seen, is slightly lower, becoming 14.48%.

To speed up the VSD function, in the “value” row you need to put a message on the tables of the table from the sums of penny flows. The middle part of the “poached meal” may not be forgotten, but this argument is not compelling. The value that is displayed is 0.144888443 - this will be the IRR value, then. internal rate of return for the project. To translate this value into hundreds, the won is 14.48% accurate to two digits after the coma.

For project B, the IRR value based on Excel is 11.79%.

I will provide an important explanation of this function in the “updating” section with my additions:

1. The values ​​are responsible for taking one positive and one negative value. Otherwise, the VSD function rotates the values ​​of the #NUM! It is true that since there is no negative penny flow, the NPV cannot be zero, and in this case the IRR does not exist.
2. To develop the function of the VSD, it is important to keep the items in order. Therefore, if the flows of funds vary in magnitude in different periods, which arise and occur, then they must be entered into the table exactly before the hour of their birthday.
3. Microsoft Excel uses the iteration method to calculate VSD. The VSD function is used to calculate cyclical calculations starting from the value of the “suspended” argument until the result is obtained with an accuracy of 0.00001%. In most cases, for calculating the additional VSD function, there is no need to specify the “omission” argument. If this is omitted, the value of 0.1 (10%) is transferred.

Then the IRR function of the Excel program will search for the IRR values ​​by selection, sequentially presenting the formula for the different values ​​of the interest rate, starting with the value in the “allowance” or 10%. Since the VSD function cannot cancel the result after 20 trials, the value of the correction #NUMBER appears! Therefore, in some cases, for example, if you value the IRR for thousands of flows for a few thousand rubles, it is better to put in the “allowance” box the amount of the thousandth interest rate you have calculated. Otherwise, Excel may not work properly after 20 trials.

Graphical method for breaking down IRR

For personal computers, use the graphical method for calculating IRR. Below is a chart of changes in NPV for projects A and B depending on the interest rate. For daily graphs, it is necessary to calculate the NPV values ​​by substituting the difference in the discount rate into the NPV formula. can be read in one of my previous articles.

The smaller blue graph is project A, the red graph is project B. The retina of the graphs from all X (at which point the NPV of the project is equal to zero) will give the IRR values ​​for these projects. It does not matter that the graphical method gives an IRR value similar to the values ​​of the internal profitability rate found in Excel for projects A - 14.5% and B - 11.8%.

How to use the IRR indicator to evaluate investment projects?

Any investment project conveys the presence of seed investments (the flow of capital), which will lead to the availability of capital in the future (in an ideal situation). What does the internal norm of profitability of an investment project indicate? The won shows the interest rate on the loan when we withdraw the surplus from our investment, then. As a result of all penny inflows and outflows, the amount will have zero - no gain, no surplus. In this case, our investment in the project will pay off with future capital gains from the project, otherwise we will not earn anything.

Rule for evaluating investment projects:

If the IRR value of the project is greater than the cost of capital for the company (then WACC), then the project should be accepted.

In other words, since the loan rate is less than the investment rate (the internal rate of profitability of the project), then the deposit money will bring additional profit. Because such an investment project will earn a greater amount of income, but less capital, which is necessary for an initial investment.

For example, if you take out a loan from a bank at 14% of income in order to invest in a business project that will bring you 20% of income, then you will earn money on that project. If your allocations turn out to be incorrect, and the internal profitability rate for your project is less than 14%, then you will have to pay more money to the bank than you deduct from the project. Then you recognize the beats.

It’s easy to break the bank itself. He receives money from the population, say, at 10% of the market rate (deposit rate), and receives loans at 20% of the market rate (the figure taken from the table). The lower rate for deposits accepted by the bank will be lower, the lower rate for loans accepted by the bank, the bank lives on this market.

Having expanded the IRR indicator, we find out about the upper acceptable level of equity capital that is transferred to the investor. If the capital contribution (for which the company can obtain financial resources) is paid for the project’s internal return (IRR), then the project is recognized as a surplus. Since the cost of capital for the company is lower for the IRR of the project, the company in a sense acts like a bank - it lives on the difference between high bank lending rates and investment profitability.

To make the logic of the IRR development even clearer, I will point out a number of examples from life with which the average person can (and does) stumble.

Exhibit 1 - term contribution to Oschadbank

Let’s say you obviously have 6,000,000 rubles. Right now you can make a term deposit with Oschadbank, say, for three years. The sum is large, and the most reliable bank in Russia is needed. Oschadbank is currently offering a rate for deposits over 2 million rubles for three periods at the rate of 9.0% of the market without capitalization and 10.29% of the market with capitalization. What else can you read on the email?

As a result, we will be able to withdraw hundreds of amounts in the event of skin cancer, without making deposits without capitalization of hundreds, and the rate will become 9% of the river ones. For example, according to the skin risk, you can withdraw an amount equal to 6,000,000 * 0.09 = 540,000 rubles. At the end of the third round, the deposit can be closed by withdrawing hundreds for the third round and the main amount of 6 million rubles.

A deposit with a bank is the same as an investment project, from which the initial investment is initially made (negative penny flow), and then penny inflows from our project are collected. A bank deposit is a financial instrument and the simplest investment method available to the average person. If it is an investment project, then its internal rate of return can be calculated. Chantly, who has already guessed why she is so dear.

The internal rate of return (IRR of investment) for a bank deposit is the same as the hundredth rate for this deposit, then. 9%. As 6,000,000 rubles were given to you at the recession after paying taxes, this means that your capital ownership is as low as zero. Therefore, such an investment project will be profitable at any deposit rate. If you take out a loan for 6 million in one bank and deposit those pennies on a deposit in another bank, you will not get any profit: the loan rate will now be obviously higher than the investment rate. This is the principle of the banking system.

Example 2 - buying an apartment with the idea of ​​earning money by renting it out

You can buy real money in another way, and buy an apartment in Moscow, rent it out for three years, and finally, on the third day, sell this apartment in order to turn around your fixed capital. The penny flows from such a project will be even similar to the flows of money from a term deposit in a bank: it is accepted that the rent, for the sake of simplicity of distribution, is paid by the tenant of the apartment immediately for the event of a skin cancer, and the property of the apartment in rubles will be lost in three years the same way as at once . I will completely forgive the situation, you can work out the complex developments on your own.

I found the first apartment I was looking for on the Internet for 6 million rubles in the northwestern part of Moscow. Renting such a one-room apartment costs 30,000 rubles per month. For the sake of simplicity, taxes on the inheritance of these lands are not covered by insurance.

Well, the rent for the warehouse is 30,000*12 = 360,000 rubles. In the beginning, penny flows from both projects - contributions from Oschadbank and the building of a 1-room apartment for rent at the outskirts of Moscow - are shown in the table below:

Without calculating IRR, it is clear that a bank deposit is a more profitable option. It is easy to achieve this if you develop the internal profitability rate for another project - it will be lower than the IRR for the deposit. When building a one-room Moscow apartment for three years per month, the sale at the end of the third period of investment stock IRR is 6.0% of market prices.

Since you have a shortfall of 6 million rubles, then borrowing pennies to rent out an apartment is unreasonable, since the lending rate is clearly lower than 6.0% of the internal profitability of this project. Moreover, the IRR does not depend on the number of risks of renting out an apartment - the internal rate of profitability will lose the same value as instead of three risks of renting out 10 or 15 times.

If the increase in price of apartments increases as a result of inflation, the IRR of this project will be greater. For example, in the first river (2015) the Karbovantseva apartment price increased by 10%, in another (2016) by 9%, and in the third (2017) by 8% , then before the end of the third day it can be sold for 6,000,000 * 1.10 * 1.09 * 1.08 = 7,769,520 rubles. Such an increase in penny flow in the third stage of the project will give an IRR of 14.53%. Therefore, if we were able to transfer future Karbovantsev prices for apartments with great accuracy, then our project would become more realistic. However, it is all the same in the current situation, if the CPU refinancing rate is higher than 17%, and, obviously, all bank loans are expensive.

Rozrakhunok IRR with thousands of cash flows

As an additional function of the VSD, it is possible to obtain the IRR of an investment project at equal intervals between penny flows. The result will be the calculation of the interest rate for the period of the year, quarter, month. For example, if we believed that payments for renting an apartment come at the end of each month (and not fate), then we needed to create an Excel spreadsheet with 36 payments of 30,000 rubles each. In this case, the IRR function would show the value of the internal rate of return for the project per month. For our project, the IRR was equal to 0.5% per month. This is confirmed by the current interest rate of 6.17% (insurable as (1+0.005) 12 -1), which is much higher than 6.0%, which was insured before.

If you want to get this result on your own, easily fill in the middle of the “sauté” - put 0.03 there, otherwise you will get the output #NUMBER!, because Excel cannot print out 20 samples in order to expand the IRR.

IRR expansion at irregular intervals between penny flows

Excel makes it possible to develop the internal rate of profitability of a project if cash flows into the project occur at irregular intervals. To develop the IRR of such a project, it is necessary to use the NET INDOW function as an argument to indicate not only the average of penny flows, but the average of the dates of their income. For example, if we transfer the term of selling an apartment at once with the remaining rent to the end of the fourth period (from 12/31/17 to 12/31/18), and for example, at the end of the third period we do not expect to find the cost, then the IRR will fall from 6% to 4.53% of the current . It is worth remembering that it will be possible to develop the internal norm of profitability in this period only using the additional function of PURE INSPIRATION, since the function of IRR will give that same result, which is - 6%, then. VSD does not change from time to time.

“Where are we going with Pyatachkom, great, great secret...”

The lowest refinancing rate, which is higher than 17%, is driving down the business of banks. Therefore, it is important to know investment projects that would pay off at such lending rates. How to develop a business from such minds? Trade in drugs will, of course, be profitable and, in this case, most businesses will survive at some times, and at worst go bankrupt.

And how can banks earn money, since investment projects simply cannot be realized due to such high profitability? And in order to pay us more and more hundreds of dollars for deposits, banks are here to make money for someone.

Russia could have absorbed the lower ruble exchange rate in relation to major currencies, but would have ended up with a high interest rate in the economy - but it would have gone too far.

In 2014, we repeatedly suspected that the Central Bank of the Russian Federation was engaged in inflation targeting. And it worked with good intentions - with lower inflation, it is easier to achieve a return on investment. It turned out that they wanted “as best as possible,” but it turned out “as before.” For expensive currencies, as soon as possible, Russia could successfully develop power production, and import substitution would become a reality. Ale no, we don’t look for easy ways, and what’s worse, we don’t pay attention to our peaces. And it’s lively, as in that joke:

“Last year, 100 hectares of wheat were sown. Everyone eats a hamster... We are planning to sow 200 hectares of wheat. Hail hamster choke!

Internal rate of return- the central criterion on which the investor focuses, deciding whether to invest money in a project or not. This indicator appears in all financial models and business plans and is the heart of these documents. Why is it that the initiators of the project and representatives of the company are guilty of obligatory nobility, how to pay off the insurance and not have mercy on the payments.

Why is the indicator of internal norms of profitability a key indicator?

Apparently, any investment project is accompanied by a mass of mathematical calculations: analysis of data from past periods, statistics, similar projects, financial plans, models, forecasters, development scenarios, etc.

It is important to assess the prospects for the project as accurately as possible, identify the resources necessary for its implementation and predict the main possible difficulties, such detailed developments may lead to one successful goal - to identify indicators of the effectiveness of the project.

The output is 2: net discounted value (NPV – net present value) and internal rate of return (IRR – internal rate of return). In this case, the internal rate of return (profitability) is most often determined through its originality.

Aside from such financial documents, models often take up hundreds of pages of written text. And investors, apparently, people are even more interested. And in business stakes they figured out a lift test: a person (the initiator of the project) can transfer money into the project within an hour while he and the investor are at the lift (about 30 seconds).

How can you earn money? First of all, find out what the investor takes as an output in order to estimate the possible income from the project and the income for the investor. This method also shows the internal rate of return.

So, what is the internal rate of return?

What to talk about internal rate of return

The internal rate of profitability is the same rate for which the net project income, brought up to today's prices, is equal to 0. In other words, for such an interest rate discounted (pointed up to today's prices) income from The investment project is entirely covered by the expenses of investors, and nothing more. Togo. My income is not adjusted.

For an investor, this means that for such bets a hundred dollars can be fully compensated for their investment, so as not to spend on the project, and otherwise not earn anything. You can also say that there is a limit to the profit – a cordon, and after some change the project becomes a profit.

At first glance, the slightly wider internal norm of profitability means an indication that there is a major benefit for the investor in practice, since the Swede allows, above all, to completely eliminate statements about the profitability of the investment specific project.

Increase your respect! Indicator of internal rate of return - the value is valid. This means that by itself there is little to talk about. For example, it is clear that the internal profitability rate for the project is 20%, then these facts are not enough for the investor to praise the decision. It is imperative to know the other steps involved.

To understand how to profit from this show, it is necessary to properly insure him.

How to develop the internal rate of return

The main feature of calculating the internal rate of return is that in practice, using any formula, they are manually calculated. The following methods have been expanded:

• graphic method;
• I'll help you with EXCEL.

To better understand why this happens, we reduce the internal rate of return to the mathematical essence. Let's say we have an investment project that transfers the starting investment. As it was stated above, the internal rate of profitability is the rate for which income from the project (generated) becomes equal to the initial investment costs. However, we don’t know exactly when such jealousy will arise: 1, 2, 3 or 10 years of life for the project.

Mathematically, such jealousy can be represented in this way:

Z = D 1 / (1 + St) 1 + D 2 / (1 + St) 2 + D 3 / (1 + St) 3 + ... + D n / (1 + St) n,

de: IZ - initial investment in the project;

D 1, D 2 ... D n - discounted penny income from the project in the 1st, 2nd and future periods;

St - the rate is hundreds.

Apparently, it is difficult to extract the value of the bet from this formula. At the same time, if we transfer the right hand (with a negative sign) into this formula, then we remove the formula for the net discounted value of the project (NPV - the 2nd key indicator for assessing the effectiveness of an investment project):

NPV = -Z + D 1 / (1 + St) 1 + D 2 / (1 + St) 2 + D 3 / (1 + St) 3 + … + D n / (1 + St) n ,

de: NPV – net discounted value of the project.

A report about what you need to know in order to correctly manage NPV, div. at statti.

The most advanced method of the plan is the graphical selection method. For which there will be a graph, where on the X-axis the possible values ​​of the interest rate will be laid, and on the Y-axis the NPV values ​​will be displayed, and the graph will show the amount of NPV in relation to the interest rate. At this point, where the curved line of the graph is drawn and drags the entire X, there is a necessary value for the high-hundred rate, which is the internal rate of return for the project.

However, today’s indicator of the internal rate of profitability depends on the way of developing a financial model in EXCEL, so it is important for any startup initiator to understand how to estimate the indicator without the help of graphs.

To develop the internal profitability norm in EXCEL, there are 2 ways:

• from the selected functions;
• for the help of the “Search for Decision” tool.

1. Let's talk about the new functions. In order to ensure the internal rate of profitability of the project, you need to create a table of essential planned indicators for the project, which consists of several columns. It is obligatory to display such digital values ​​as initial investments and immediate financial results of the project.

Important! The major financial results of the project should be taken from the undiscounted view, so as not to reduce them to actual prices.

For greater precision, it is possible to decipher the basic planned income and expenses, including the financial result of the project.

Butt 1

Risk of life for the project	Initial investment investments, rub.	Planned income for the project, rub.	Planned expenses for the project, rub.	Financial results of the project, rub.
				-100 000

After compiling such a table before expanding the internal rate of return, it will be impossible to formulate the IRR formula.

Increase your respect! For the company, the value of the IRR formula is to indicate the range of amounts from the column with the financial results of the project.

However, in practice, investment projects are not always accompanied by regular penniless expenses. In the future there is a risk of blaming the development: freezing of the project, its slowdown for other reasons, etc. In such minds, a different formula is used, which in the Russian version of EXCEL is designated as PURE INDOH. The difference from the previous formula is that, in addition to the financial results of the project, it is necessary to indicate the time periods (dates) on which specific financial results are approved.

2. To calculate the internal rate of profitability for the additional “Search for a solution” tool, it is necessary to add a column with the values ​​of the short-term discounted financial result to the table of planned project values. Next, you need to determine in your business what NPV will be calculated here, and write a formula in it to transfer the money to another empty business, in which the internal rate of return will be covered.

Important! In the “Install target business” row, you need to target the business with the NPV formula. Then indicate that the target box is responsible for adding 0. In the “Changing value of the box” field, you need to go to the empty box, which is the fault of the indicator we need. Then you need to quickly select “The decision is made by search” and calculate the value of the bet for which the NPV increases to 0.

Once the internal profitability of the project has been identified, the main question remains: how should the data be consolidated in order to correctly assess the profitability of the input?

Internal rate of return in the assessment of investment projects

The profitability of any investment project can be determined by equalizing the internal profitability rate of the project with a similar indicator for another project and the basis of the equalization.

Since the investor is faced with the choice of investing money into a project, he may choose to save money on the risk that the internal rate of return is higher.

Why should you work, since there is only one project? In this case, the investor needs to equalize the internal rate of profitability of the project against any universal base, which can serve as a guideline for analysis.

In practice, such a basis is the commitment to capital. Since the return to capital is lower than the internal norms of profitability of an investment project, such a project is considered promising. Since there is a need for capital, for example, money, then the investor has no sense in investing pennies in the project.

Instead of capital gain, you can substitute a hundred dollar rate for an alternative risk-free investment. For example, from a bank deposit.

Butt 2

A non-residential contribution to a bank can bring 10% of the deposits. In this case, an investment project with an internal profitability rate of over 10% will be an attractive investment option for the investor.

Reductions and shortcomings of the internal norms of profitability

Regardless of the fact that the development of internal profitability standards is designed to maximally help the investor assess the prospects for investment in any other project, there are still a number of points that separate the practical stagnation of the indicator:

• First of all, when choosing from alternative projects, equalization of the internal rate of return between them is not enough. This indicator allows you to evaluate the profitability of the initial investments, and which does not illustrate the income of your real estimate. As a result, projects with the same internal rate of return may have a different net discount rate. And here the choice is to work on the benefit of that project, the net discount is the value of any more, so that which will bring the investor more profit in pennies.
• Alternatively, an investment project may have a net discounted value greater than 0 for all interest rates. Such a project cannot be assessed based on internal profitability standards, since for this purpose it is simply impossible to pay for it.
• Thirdly, it is very difficult to accurately predict future financial flows. Especially the cost of future expenses (income).

Risks of an economic, political and other nature will soon emerge that may lead to counterparties not paying promptly. As a result, there is a financial model for the project and, obviously, the value of the internal profitability rate. Also, predict future needs as accurately as possible - task number 1 at the hour of developing a financial model.

Pouches

The internal rate of return is an indicator that is one of the most important in assessing the financial potential of an investment project. This is something investors should be looking at.

The initiators of the project should remember that the display can be expanded either using additional graphics or mathematically in EXCEL (in 2 ways: using additional functions and “Look for a solution”). Also, do not forget to equalize the value of the project based on the available capital.

In addition, it is important to understand that the indicator of the internal profitability rate of the project’s prospects has nothing to do with the net discounted profit, so the organization must provide the investor with rates and NPV.