Implementing the Internal Rate of Return Financial ...



Implementing the Internal Rate of Return Financial Function in Evaluating Investment Projects

BRANKA CRNKOVIĆ – STUMPF, Ph.D., BORKA UHAČ, B.Sc.

Department of Business Economy

Faculty of Economics Rijeka

University of Rijeka

Ivana Filipovića 4, Rijeka

CROATIA



Abstract: Each and every entrepreneur is, at least once, obligated to make the decision whether to invest into expanding his/her business or not. The simplest way of resolving whether it is worthwhile investing into such ideas or not, is to carry out an investment study. The study calculates and establishes the acceptability of such investments by using various indicators and methods. Computers, to a large extent, facilitate this calculating process by using mentioned indicators. The implementation of the most commonly used methods of the dynamic approach to investment project evaluation, the net present value and the internal rate of return methods, is largely facilitated by the use of the @NPV and the @IRR financial functions from the Excel program package. However, the internal rate of return method has three basic flaws limiting its implementation ability. But, the implementation of the @IRR financial function is possible in all situations where the internal rate of return method shows its limitations, since we can reach the same results by simpler and a quicker calculations of desired indicators.

Key-Words: Internal Rate of Return, Financial Function, Evaluation, Investment Project

1 Introduction

Each and every entrepreneur is, at least once, obligated to make the decision whether to invest into expanding his/her business or not. Nevertheless, how magnificent the idea may sound, it may turn out to be totally unacceptable in financial terms, bringing actually no profit. The simplest way of resolving whether it is worthwhile investing into such ideas or not, is to carry out an investment study. The study calculates and establishes the acceptability of such investments by using various indicators and methods. Computers, to a large extent, facilitate this calculating process by using mentioned indicators.

Therefore, the research problem becomes the implementation of the Excel program package financial functions in evaluating investment projects and investment studies. The research matter emerges from the research problem: to analyse the implementation of the internal rate of return financial function in evaluating investment projects, with emphasis on specific examples.

According to the consistent notions on investment projects and investment study evaluation, especially through investment study evaluation methods, it is possible to prove that financial functions facilitate and speed up investment project evaluations.

2 Internal Rate of Return Financial Function (@IRR)

The implementation of the most commonly used methods of the dynamic approach to investment project evaluation, the net present value and the internal rate of return methods, is largely facilitated by the use of the @NPV and the @IRR financial functions from the Excel program package.

The net present value, as a sum of present values of all future net receipts NR, at discount rate is calculated according to the formula:(1(

[pic]

The internal rate of return as the discount rate according to which the net present value of the investment project equals to 0 i.e. (2(

[pic]

It is calculated by test-insertion of different values of p in the formula: (3(

[pic]

or by the financial function @IRR that uses specific mathematical methods (usually iterative) for solving the same problem: (4(

@ IRR (values, guess)

Values represent net receipts, where the negative value of the investment comes first and where at least one negative and one positive value are present. Guess represents our assumption of what the internal rate of return value will be. In case the function calculating with 0.1, or 10% value is omitted, we should take into account that the investment and the net cash receipts time continuum are not disrupted. If #NUM appears during calculations, the function will not be able to establish the accurate value not even after 20 iterations, therefore, it must be helped by introducing a new value guess.

3 Implementing the @IRR Financial Function in specific Cases

The internal rate of return method has three basic flaws limiting its implementation possibilities. Those are: the occurrence of multiple internal rates of return, the case of mutually exclusive projects and in situations where the internal rate of return is a relative criterion. The following examples will give an explanation on how to use the @IRR financial function in such specific situations.

3.1 Occurrence of multiple internal rates of return

The basic problem of implementing the internal rate of return method is the possibility of multiple internal rates of return. A necessary, but not a sufficient precondition for such an occurrence is brought about by a change of cash-flow direction more then once.

Example 1 Investment value equals 1,000 kn. The expected net cash receipts amount to: 6,000 kn in the first year, - 11,000 kn the second year and 6,000 kn the third year. (5( The annual discount rate is 10%.

Prior to calculating the internal rate of return, it is necessary to take into account the cash-flow direction. Since there is quite a big negative cash flow, the emergence of multiple internal rates of return is a possibility.

Table 1 Calculating present values for different discount factors (example 1)

|Discount |Present |Discount |Present |Discount |Present |

|factor |value |factor |value |factor |value |

|- 5% |125.53 |100% |0 |200% |0 |

|0% |0 |110% |10.69 |250% |-43.73 |

|5% |-80.01 |120% |18.03 |300% |-93.75 |

|10% |-128.47 |150% |24.00 |350% |-144.03 |

Source: Author`s calculations

Since the internal rate of return is the rate at which the net present value equals 0, the internal rate of return in this example would be 0%, 100% and 200%. By inserting these values into the @IRR function we get the following results:

@IRR (-1,000;6,000;-11,000;6,000)= 0%

@IRR (-1,000;6,000;-11,000;6,000;0,5)= 100%

@IRR (-1,000;6,000;-11,000;6,000;1)= 100%

@IRR (-1,000;6,000;-11,000;6,000;1,5)= 200%

@IRR (-1,000;6,000;-11,000;6,000;2)= 200%

These differences occur according to different guess values.

Due to the emergence of multiple internal rates of return, this method cannot be used in evaluating cash-flow direction since it might give contradictory results (opposite values) concerning cash-flow acceptability.

Example 2 Investment value equals 1,000 kn. The expected net cash receipts amount to: 4,000 kn in the first year, - 4,000 kn the second year. (6( The annual discount rate is 10%.

A negative cash-flow direction is also present in this example. The results of implementing different discount rates are given in table 2.

Table 2 Calculating present values according to different discount factor (example 2)

|Discount |Present value|Discount |Present |Discount |Present |

|factor | |factor |value |factor |value |

|- 5% |-1,221.61 |100% | 0 |200% |-111.11 |

|0% |-1,000.00 |110% | -2.27 |250% |-183.67 |

|10% | -669.42 |120% | -8.26 |300% |-250.00 |

|20% | -444.44 |150% |-40.00 |350% |-308.64 |

Source: Author`s calculations

Therefore, the internal rate of return is 100%. The same conclusion is reached if the @IRR function is used:

@IRR (-1,000,4,000;-4,000)= 100%

@IRR (-1,000;4,000;-4,000;0,5)= 100%

@IRR (-1,000;4,000;-4,000;1)= 100%

@IRR (-1,000;4,000;-4,000;1,5)= 100%

@IRR (-1,000;4,000;-4,000;2)= 100%

This is also the only rate at which the net present value is not negative. Calculating present values by using other discount rates result in negative values.

Example 3 Investment value equals 1,000 kn. The expected net cash receipts amount to: 3,000 kn in the first year, - 2,500 kn the second year. (7( The annual discount rate is 10%.

The results reached by inserting different discount rates are shown in table 3.

Table 3 Calculating present values according to different discount factors (example 3)

|Discount |Present |Discount |Present |Discount |Present |

|factor |value |factor |value |factor |value |

|- 5% |-612.19 |100% |-125.00 |200% |-277.78 |

|0% |-500.00 |110% |-138.32 |250% |-346.94 |

|10% |-338.84 |120% |-152.89 |300% |-406.25 |

|20% |-236.11 |150% |-200.00 |350% |-456.79 |

Source: Author`s calculations

In this example none of the discount rates result in positive present values. Therefore, there is no internal rate of return. The same conclusion is reached if the @IRR function is used:

@IRR (-1,000;3,000;-2,500)= #NUM

@IRR (-1,000;3,000;-2,500;0,5)= #NUM

@IRR (-1,000;3,000;-2,500;1)= #NUM

@IRR (-1,000;3,000;-2,500;1,5)= #NUM

@IRR (-1,000;3,000;-2,500;2)= #NUM

@IRR (-1,000;3,000;-2,500;2,5)= #NUM

No matter which guess value is used in the function, the result is always #NUM. As a result, we may conclude that there is no internal rate of return in this example.

Graphic illustration of the present values given in the three previous examples (Example 1, Example 2, Example 3) with pertaining internal rates of return (intersections of curves and axis X) is shown in fig. 1.

Fig. 1 Illustration of present value curves

[pic]

Source: Calculated values from Examples 1, 2 and 3

According to the given examples, we may conclude that the use of @IRR financial function is possible even in cases where there is an occurrence of multiple rates of return. The @IRR function also enables calculating more internal rates of return just by changing the guess values. The implementation of the mentioned function enables a more rapid conclusion that multiple internal rates of return do exist. As a result, the only method to be used in such cases is the net present value method.

2. Mutually exclusive projects

In cases when two projects are mutually exclusive, so only one can be chosen, the net present value method and the internal rate of return method can give contradictory results.

Example 4 Companies can invest money into just one of the mutually exclusive projects. The expected results of the cash-flow direction of both projects are given in table 4. It is necessary to compare both projects. The discount rates are a) 8 % and b) 10%.

Table 4 Project A and project B net cash-flow directions (example 4)

|Year |0 |1 |2 |3 |4 |

|Project A |-2,000 |1,000 |800 |600 |200 |

|Project B |-3,000 |1,000 |1,200 |1,050 |650 |

Source: Bendeković, J.: Planiranje investicijskih projekata, knjiga III, Ekonomski institut Zagreb, 1993, p. 34

The net present values and the internal rates of return for both projects are calculated according to data on cash-flow directions (table 5) and are illustrated by fig. 2.

Table 5 Calculating net present values and internal rates of return (example 4)

| |NPV |NPV |NPV |IRR |

| |(8%) |(9,1078%) |(10%) | |

|Project A |23,510 |19,160 |15,764 |14.49% |

|Project B |26,603 |19,160 |13,367 |12.17% |

Source: Author`s calculations

Fig. 3 Illustration of present value curves

[pic]

Source: Calculated values from Example 4

If we use the internal rate of return rate method, we can see that project A is better than project B since 14.49%( 12.17 %. If we use the net present value method as a criterion of choice, then we will get a different result depending on the discount rate choice by which the net present value of the project is calculated. If we take the discount rate at which both projects have the same net present value, the results will be ambiguous. In this example, the rate is 9.1078%. If we consider a rate higher than 9.1078%, project A will be better than project B, where the project A curve is above the project B curve. We will reach the same results if we were to use the internal rate of return method. However, if the discount rate is lower than 9.1078%, then project B will be preferable to project A and the project B curve is above the project A curve. In this case, the internal rate of return is not related to the choice of the discount rate. Consequently, the results are contradictory depending on the chosen method; the internal rate of return method calculations say that project A is better, while the net present value method demonstrates project B to be better.

With mutually exclusive projects, there is a possibility that the net present value method and the internal rate of return method give contrary results about the acceptability of the project without the analyst being aware of this fact. As a result, it is better to use just the net present value method whenever evaluating the acceptability of mutually exclusive projects.

The use of the @IRR financial function, in this case, speeds up the the calculations considering the internal rates of return and facilitates the search for the most precise discount rate at which the present values of both projects will be the same. At the same time, it enables quicker calculations considering the data necessary for a graphic illustration of the present value curves of the projects in question.

3.3 Internal rate of return as a relative criterion

The internal rate of return is a relative measurement of a project’s acceptability. It provides information solely on the average annual rate of accumulation, but offers no information on the absolute value of this accumulation.

Example 5 The company may invest money into just one of the two projects. The expected net cash flow directions for both projects are shown in table 6. It is necessary to compare both projects. The discount rate is 10%.

Table 6 Project A and project B net cash-flow directions (example 5)

|Year |0 |1 |

|Project A |-100 |150 |

|Project B |-500 |625 |

Source: Van Horne J.: Financijsko upravljanje i politika : (Financijski menedžment), Mate d.o.o., Zagreb, 1993, p. 150

The net present values and the internal rate of return for both projects are calculated according to cash-flow direction data. (Table 7)

Table 7 Calculating net present values and internal rates of return (example 5)

| |NPV |IRR |

| |(10%) | |

|Project A |36.36 |50% |

|Project B |68.18 |25% |

Source: Author`s calculations

By comparing these two projects and the given indicators, we may conclude that, according to the internal rate of return method, project A is more acceptable since it is always better to have a 50% return rather than a 25% return on an investment. Furthermore, we can say that a 50% return on an investment of a 100 kn is better than a 25% return on 625 kn investment. The net present value gives results on absolute values where, under the condition that the discount rate is 10%, the given net present values equal 36.36 kn in the case of project A and 68.18 kn in the case project B. Here we may conclude that it is better to have 68.18 kn than 36.36, regardless what the return percentage is. The reason for this lies in the different investment amounts, which enable a higher net present value.

The implementation of the @IRR financial function in this case leads us to the same conclusions as in the previous examples, speeding up the calculating processes, at the same time.

4. Conclusion

The implementation of the internal rate of return financial function (@IRR) in “normal” situations is unquestionable, considering that it gives the same results as if the values where calculated using the formula, but through a quicker and simpler process.

However, the internal rate of return method has three basic flaws limiting its implementation ability. They are: the occurrence of multiple internal rates of return, situations of mutually exclusive projects and situations where the internal rate of return is a relative criterion. The question that arises is whether it is actually possible to use the @IRR financial function in such cases and will it lead us to the conclusion that we can solely use the net present value method.

The three examples of multiple internal rates of return demonstrate that the use of the @IRR financial method is possible in cases where we have an occurrence of several internal rates of return. The @IRR function enables one to calculate more internal rates of return just by introducing changes in the guess values. The implementation of this method results in a more rapid process in reaching the conclusion that multiple internal rates of return exist, bringing about the sole use of the net present value method.

In case of two mutually exclusive projects, the internal rate of return method and the net present value method may give contrary results. The @IRR financial function implementation in such cases speeds up the calculation of internal rates of return. It also gives more precise discount rates at which the present values of the projects in question are the same. Simultaneously, it enables a quicker calculation of data needed for graphic illustrations of projects’ present value curves; these graphs being the best way of illustrating the reasons for diversified ranking of mutually exclusive projects.

The internal rate of return is a relative measurement of the project’s efficiency. It provides information solely on the average annual rate of accumulation. It gives us no information about the absolute amount of this accumulation. According to the given data we can reach different ranking results depending on the method used, the rate of return method or the net present value method. By using the @IRR financial function in such cases, we not only get the same results but the process is more rapid.

Finally, we may conclude that the implementation of the @IRR financial function is possible in all situations where the internal rate of return method shows its limitations, since we reach the same results by simpler and a quicker calculations of desired indicators.

References:

(1( Bendeković, J.: Planiranje investicijskih projekata, knjiga III, Ekonomski institut Zagreb, Zagreb, 1993, p. 23

(2( Bendeković, J.: Planiranje investicijskih projekata, knjiga III, Ekonomski institut Zagreb, Zagreb, 1993,, p. 28.

(3( Bendeković, J.: Planiranje investicijskih projekata, knjiga III, Ekonomski institut Zagreb, Zagreb, 1993,, p. 30.

(4( Božić, D.: Excel za Windows, Mozaik knjiga, Zagreb, 1995, p.149

(5( Hazen, G. B.: "A New Perspective on Multiple Internal Rates of Return", 2002, p. 14, (13.12.2003)

(6( Hazen, G. B.: "A New Perspective on Multiple Internal Rates of Return", 2002, p. 14, (13.12.2003)

(7( Hazen, G. B.: "A New Perspective on Multiple Internal Rates of Return", 2002, p. 14, (13.12.2003)

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download