What is and How to Calculate the Internal Rate of Return (IRR)

44
55672
internal rate of return (tir)
internal rate of return (tir)
Excel Spreadsheets

A Internal Rate of Return (IRR)comes from English Internal Return Rate (IRR) and is a mathematical-financial formula used to calculate the discount rate that would have a certain cash flow to equal its net present value to zero. In other words, it would be the rate of return on the investment in question.

Go back to:
How to make a business plan?
How to make a financial plan?

When To Use: IRR is one of the key indicators in project return or company valuation analyzes (valuation). The most famous terms for studies of the genre are economical viability with variations and economical-financial or technical-economic.

Excel Spreadsheets

Economic Feasibility Study Sheet

How to calculate: In this post, we will show you how to calculate the Internal Rate of Return through Excel, but the mathematical calculation that actually occurs is as follows:

formula TIR

In which, F means the cash flow for each period and the t is the period in question. As we are seeing it, each cash flow will be divided by the high IRR to its respective period, since interest, in this case, is compounded. And all this must be equal to zero.

Now that we have seen the mathematical side, let's look at the practical side via Excel. To calculate the IRR, it is enough to have the free cash flow of your financial projection, we will use the same example of the Net present value:

cash flow - vpl

In this case, the project had an initial investment of R $ 20.000,00 and then generated R $ 10.000,00 of profit in the following months. To calculate the IRR, in this case, simply choose the cell in which you want to see the result and choose the IRR formula (IRR) and select the cells of the desired cash flow.

In this case, we have the incredible TIR: 41,04%

Economic Feasibility Study Sheet

How to Interpret: This means that the investment in question has a return rate of 41,04%. So you should compare it to your minimum investment attractiveness rate, which would be the minimum expected return on the contract. This is usually compared to other financial returns as investments in funds, but can also be seen in relation to the risk of the operation, ability to dispose of the business and also other opportunities.

Lastly, because it is a percentage rate, it allows you to compare the return of the most different types of investments within the same criteria!

Traps: IRR is a rate that is best suited for simpler cash flows (for example, the first negative period and positive futures). In addition, in some cases, there may be conflicts between the NPV and the IRR. When this occurs, it is suggested to follow the NPV.

Was the importance of this analysis clear? At LUZ, we offer a worksheet ready in excel for economic feasibility calculation of projects!

Economic Feasibility Study Sheet

Excel Spreadsheets

44 COMMENTS

  1. If the NPV was positive, it is an indication that the project is positive. In relation to IRR, the analysis is that it is greater than the TMA (which also occurs)

  2. Good night! I made a discounted cash flow in which the calculated horizon was monthly. The TMA used was 0,5% which is the same return on savings on a monthly basis. The IRR gave 4,2% do not know if it is a good value when compared to the TMA. The NPV was 693 thousand.

  3. An IRR of 200% does not necessarily mean an error, but may be an exaggerated optimism. You did not specify inbox flows in the 8 year period, if it is a high value, this may justify the IRR in 200%. When in doubt, I recommend that you do the calculation with an HP12C to check the values ​​and make sure that everything is correct

  4. Opa mano, congratulations on the material, very good.
    I am with a very specific case here a situation of an agricultural project, in which the total investment is about R $ 1.770.000,00 and the returns (Revenue generated in time), is around R $ 3.000.000,00 in each year during 8 years. With this I am getting an IRR of almost 200% ... is this possible, or am I necessarily wrong?

  5. I did not understand your question Taina. The IRR can be calculated by an Excel formula as long as you have the initial investment and cash flow

  6. I'm so confused, I have a presentation on Investment Analysis and I do not know if I calculate the IRR alone or with the formula as a whole.

  7. To calculate the IRR, we have to use some form of simulation.
    For example, to find the IRR of the following cash flow:
    Year Flow of Caxa
    0 -R $ 300.000
    1 $ 200.000
    2 $ 216.000
    When we bring these values ​​into the present we have:
    Year Flow of Caxa Present value
    0 -R $ 300.000 R $ -300.000
    1 R $ 200.000 R $ 200.000 / (1 + TIR)
    2 R $ 216.000 R $ 216.000 / (1 + TIR) ^ 2

    What is the value of IRR?
    R $ 300.000 = R $ 200.000 / (1 + TIR) + R $ 216.000 / (1 + TIR) ^ 2
    To solve this equation, we need to simulate the TIR value, from 0,01% to 100%. A VBA code to solve this would be:

    Sub calcula_tir ()
    For i = 1 To 0 Step -0.0001
    unimportant = 200000 / (1 + i) + 216000 / ((1 + i) ^ 2)
    If_simple_value> 300000 Then
    tir = i
    Exit For
    End If
    Next
    MsgBox tir * 100 & "%"
    End Sub

    In Excel, press ALT + F11, click "Insert"> "Module" and then paste this code. Run, and the result will be 24,5%.
    To do in the calculator, manually, you would need to make trial and error. Make a table, write the equation, test values ​​for IRR until you reach the value of the initial outlay.

  8. Hi Mauro, the IRR will always be related to the future cash flows of a given project and the initial investment made in it. To be greater than 100% this project needs to be very advantageous (so it is difficult to find IRR above this value).

  9. It is difficult to have an IRR> = the 100% of a project.
    I wonder how I find this result!

  10. Geiza, in order to help the colleague in his / her demonstration, using the financial calculator HP12c (what super recommended), we have:

    300.000 CHS g CF0 - Initial investment (disbursement)
    35.000 g CFj - Monthly inflow of capital
    24 g Nj - Number of times this flow repeats (24 months = 2 years)
    6,5 i - Used the Selic Rate as parameter
    You will have, through the calculator, IRR = 10,64%

    Espero ter ajudado.

  11. Hi Jorge, the IRR analysis aspect is whether it is above or below the discount rate used in the feasibility study. You probably used a lower rate than 100%, indicating that this IRR is good, that is, it shows that the project is feasible.

    Now, one thing you need to be aware of is that an IRR greater than 100% indicates an extremely good project, and perhaps you may have been too optimistic about the projections, showing a positive scenario but impossible to achieve in practice.

  12. Hello, if the IRR shows a value above 100%, is it possible? If so, what does it mean?

  13. I found the error.
    I was discounting the 17.907,87 VPL (generated with the TMA of 10%) by the TIR of 41,04%.
    The correct thing to do is to discount the FLOW by the IRR, and the result (NPV) will give ZERO.

  14. Considering the cash flow example of the text we have the following generated values:
    VPL = 17.907,87
    TIR = 41,04%

    Considering also that the IRR should function as a discount rate to equal the NPV to zero, why when I discount this NPV for this IRR, the value generated is 3.208,55 and not zero?
    The calculation I'm doing is:
    = 17.907,87 / (1 + 41,04%) ^ 5

    I know I'm doing something wrong in the calculation, but I could not figure it out.

  15. Hi Anabela, you did not explain the exercise very well, so I'll try to explain the concepts. The positive NPV indicates that one should invest in the project. The IRR is the rate that causes the NPV to be zeroed, so any lower discount rate than the IRR causes the NPV to be negative.

  16. How to calculate the return on investment of a project of R $ 5000 (3 years) 1500, 3500, 1400. The NPV = 309. Investors think the return on investment is 6,18%. And you when calculating the IRR gave approximately 13,5%. How can I solve?

  17. Hi Cleber, if it arrives in the IRR doing interpolation of calculations, that is, it is not very smart to do in the hand, but if you want, it is by approximation, applying rates and seeing which comes closer to transform the NPV in 0

  18. Good night

    How to calculate the IRR in the hand ???

    Investment - 18.750,00
    4 years
    3.200,00 input stream

    What is the IRR ??

    HOW TO MAKE IT ON ?? NA HP I KNOW

    THANKS

  19. Do you know the basics at least? do you know the meaning of each "symbol" of the equation?

  20. Hi Patricia, I find it more relevant to make use of the whole period of the projected cash flows that your project has.

  21. If a project is going to last, for example, 20 years (forecast), should the IRR be calculated considering the 20 years and all the cash flows up to it, or should it consider a shorter period? Thanks

  22. Hi Andrei, how are you? We do not usually settle jobs like that, but if you have questions I can solve, it will be a pleasure to help. Just tell me what your difficulty has been.

  23. Fazenda Esperança is a rural enterprise duly formalized in the State of Minas Gerais and currently produces soy, corn and potatoes.

    The rural entrepreneur, owner of the farm, seeks to reduce the impacts of the time lag, which make the operational cycle extensive, through its strategy of crop diversification. It should be noted that this type of action adopted at the Farm is an interesting option to contribute to working capital and the availability of resources in the business.

    Satisfied with his strategy, the entrepreneur decides to invest in a new crop, but it counts on you, in the function of Agribusiness Technician, to make the decision that can give you the best return.

    Based on the information provided below, perform a financial analysis using the valuation / referencing mechanisms (TIR, VPL, B / C ratio, discounted payback and break even point) and indicate, explaining your answer and presenting the calculations, which would be the best option to the rural entrepreneur.

    The following is more information about Fazenda Esperança:

    • Fazenda Esperança is looking for a new production for investment, after the first analyzes the production options for the products "X" and "Y" appear.
    • The opportunity cost to capital is 12% per year.
    • For the calculation of the break even point, consider the marketing price of product Kg "X" R $ 1,05Kg, and product "Y", R $ 1,45Kg.
    • The work has already been initiated by the rural entrepreneur, who previously identified that the Internal Rate of Return (IRR) for the two investment possibilities would be equal, in 13%. Also, the Net Present Value (NPV) for production "X" would be R $ 19.779,94, while for "Y" production it would be exactly 55,6063% of that value.

    Project Cash Flow - "X"
    Revenue / Expenses 1 year 2 year 3 year 4 year 5 year 6 year
    Startes
    525.000 Capital Contribution
    Vendas 225.000 225.000 225.000 225.000 225.000 225.000
    TOTAL DE ENTRADAS 750.000 225.000 225.000 225.000 225.000 225.000
    Saídas
    695.000 Investments
    Custeio 55.000 55.000 55.000 55.000 55.000 55.000
    TOTAL DE SAÍDAS 750.000 55.000 55.000 55.000 55.000 55.000
    SALDO OPERACIONAL 0 170.000 170.000 170.000 170.000 170.000

    Project cash flow - "Y"
    Revenue / Expenses 1 year 2 year 3 year 4 year 5 year 6 year
    Startes
    550.000 Capital Contribution
    Vendas 205.000 205.000 205.000 205.000 205.000 205.000
    TOTAL DE ENTRADAS 755.000 205.000 205.000 205.000 205.000 205.000
    Saídas
    675.000 Investments
    Custeio 80.000 80.000 80.000 80.000 80.000 80.000
    TOTAL DE SAÍDAS 755.000 80.000 80.000 80.000 80.000 80.000
    SALDO OPERACIONAL 0 125.000 125.000 125.000 125.000 125.000

    Analyzes for the horizon of 6 years
    Production "X" Production "Y"
    TIR
    VPL
    B / C
    Payback
    Break even point

    Write your opinion below analyzing the results found and guiding the rural entrepreneur in the most appropriate decision.

    I am with this work but I am doubtful in some results that I have arrived.

  24. Hi Sergio,

    you need to create a spreadsheet with monthly periods. In the initial month (zero), you must place your investment and in the months 18, 30, 42 and 54 you must enter the values ​​quoted. If you are using Excel, simply use an NPV and IRR formula using the monthly default rate to understand whether the result is positive or not. The payback is easier to calculate, in theory, it will occur in the thirtieth month, since the first positive FC would not pay the investment.

  25. I'm investing 270.000,00 to return in the 18 ° month in the value of 128.000,00 and in the 30 ° month 226.000,00, 42 ° month 226.000,00, 54 ° month 226.000,00, and so on, this is agricultural production. Question: How do I calculate the IRR, VPL and payback, and what would be the best acceptable at an attractive rate of 1% am?

  26. Hi Edy, it will be my pleasure to try to help you with your questions, just tell me what you are not understanding very well that, as much as possible, we try to explain.

  27. It was not clear at all. Thousandth site that researches this and does not find a good explanation.

  28. In this case, the IRR of the operation that is 11,72% is positive for the store, since it has the option to receive the view or install 5 times "without Interest" for ex. assuming a purchase worth $ 1.000,00 with a discount of 27,4% the store would receive the view
    R $ 726,00 in case the customer decides to pay in 5 installments of R $ 200,00 giving up the discount, the store would receive with an increase of 11,72% what would be a highly positive IRR for store, considering that the store would hardly have an investment that gave a return greater than or equal to that.

  29. The 3 methods are valid Kah, but if I were to choose one of them I would have the NPV that analyzes the future cash flows brought to present value. In the case of IRR, its comparison is with a market rate, which you will not always know what it is and in the case of payback, the fact that it is not discounted can make the analysis simplified and therefore imprecise

  30. Hello .. Good night! In relation to Payback, Net present value and internal rate of return which of the 3 methods can be considered the most efficient in giving support for the acceptance of a given project? it's because?

  31. A SHOP ANNOUNCES EVERYTHING IN FIVE TIMES, NO ENTRY AND "NO ADDITIONAL INTEREST" OR VISITING A SPECIAL DISCOUNT WITH 27,4%. FOR THE CUSTOMER, WHAT IS THE OPERATION TIR?

  32. Hello Leandro, could you explain me how to calculate the IRR manually (without Excel or HP). I already have the cash flows each year.

  33. A new product will be launched at XYZ. The total investment made to develop and launch this new product was R $ 70.000,00. The company expects to have four months of product sales to exceed the return on investment with monthly net inflows of R $ 20.000,00, R $ 30.000,00, R $ 25.000,00 and R $ 25.000,00.

    Calculate the effective cost of the financing, that is, the rate of return on investment for the company and then tick the correct alternative.

  34. Hi, whats up? ! but not Accounting Science course, I do Administration and I am in the second period and already comes this financial mathematics, afffff no one deserves. . :(
    It's all very complicated, I'm having a hard time in this challenge

  35. Professional Challenge of Accounting of the Third Period of the Anhanguera. kkkkkkkkkkk

  36. Right. But how do I get to this 10,64 percent value. ? How is the 'account' I have to do? Give me a practical example, please. I am in the beginning of the studies in Administration and I have much difficulty in calculations. .:( thanks

  37. Hi Geiza, everything good?

    using our Economic Feasibility Study sheet I saw that the IRR result for the first calculation is 10,64%. Whether this is a positive number or not depends on your analysis, since you compare the IRR with the attractiveness rate used.

    Regarding the second item, I did not quite understand what you meant. Have you invested real 50.000? What revenue streams does this money give you?

    I hope I have helped

  38. Hello . I bought a real estate for 300.000,00 real and I leased for two years by 35.000,00 thirty five thousand how to know which value of tir?
    Parallel to this I have an investment of 50.000,00 fifty thousand monthly for two years with a rate of 1 percent per month
    I can not do

LEAVE AN ANSWER

Please, write your comment
Please enter your name