EME 460
Geo-Resources Evaluation and Investment Analysis

Rate of Return (ROR) Calculation


So far, we have learned how to determine the unknown variables including present value, future value, uniform series of equal investments, and so on. In these question types, the interest rate was a given parameter. But, there are situations where the interest rate,i, is the unknown variable. In such cases, we know (or expect) the amount of money to be invested and the revenue that will occur in each time period, and we are interested in determining the period interest rate that matches these numbers. This category of problems is called rate of return (ROR) calculation type. In these problems, we are interested to find the interest rate that yields a Net Present Value of zero (the break-even interest rate). This break-even rate is sometimes called the Internal Rate of Return.

For example, assume for an investment of 8000 dollars at present time, you will receive 2000 dollars annually in each of year one to year five. What would be the interest rate (compounded annually) for which this project would break even?

The problem can be written as:
8000 = 2000( P/ A i,5 ) or
( P/ A i,5 )= 4

With a trial and error procedure, we can find the interest rate that fits into this equation (i= 7.93%). Therefore, the rate of return on this investment (or Internal Rate of Return) is i= 7.93% per year.

Again, assume all the parameters are known and specified except the rate of return i. In order to determine i, usually, a trial and error method is used that will be explained in Example 2-10 and the following video.

Example 2-10:

In Example 2-9, assume 20,000 dollars is paid for the asset in present time (C = 20,000 dollars), a yield of 2,000 dollars per year in income after all expenses is expected for each of the next ten years and also the resale value in the tenth year will be 25,000 dollars. What annual compound interest rate, or return on investment dollars, will be received for this cash flow?

C=20,000 I=2000 I=2000 I=2000 ... I=2000 L=$25,000

0 1 2 3 ... 10

Figure 2-4: Cash flow: 20,000 dollars investment at present time, 2,000 dollars per year in income after all expenses for 10 years and resale value of $25,000 in the tenth year.

An equation can be written setting costs equal to income at any point in time and the project rate of return can be calculated, i.e., the beginning or end of any period. Here, we will use the present value method to determine internal rate of return, i.
In order to solve this problem, an equation that equates costs to income at any point in time (for example beginning or end of any period) should be written with the project rate of return as an unknown variable.

present value equation at present time to calculate i:
present value of cost = present value of income + present value of salvage

present value of all costs ( C ) = 20,000 present value of all incomes =2,000*( P/ A i,10 )= 2000 * [ ( 1+i ) 10 1 ]/[ i ( 1+i ) 10 ] present value of salvage =25,000*( P/ F i,10 ) =25,000 * [ 1/ ( 1+i ) 10 ]

20,000=2000 *[ ( 1+i ) 10 1 ]/[ i ( 1+i ) 10 ] +25,000 * [ 1/ ( 1+i ) 10 ]

It is very difficult to solve this explicitly for i. By trial and error, we can easily find the that makes the right side of the equation equal to the left side.

For the initial guess of i=10% , the left side is:
2,000*6.1446 + 25,000*0.3855 = $21,930

And for i=12% , the left side is:
2,000*5.6502 + 25,000*0.3220 = $19,350

Then, we can try i=11% (the middle point) and i=11.5% to find 11.5% is the rate of return to make the left side to equal to the right side.

In Excel specifically, another way to calculate the break-even rate of return is to use the IRR function. As long as the project has an investment cost in the present year and subsequent cash flows, you can use the IRR function to calculate the Internal Rate of Return. (If the project has a different cost and cash flow structure, then it's harder to use the Excel function here.) This video has a short example (without any narration) of the Excel IRR function. The Excel help file for IRR is also very useful.

For an illustration of the trial and error method, see the following video, Trial and error problem in Excel (6:52).

(Please use 1080p HD resolution to view it).

Rate of Return for a Financial Project
Click for the transcript of "Trial and error problem in Excel" video.

PRESENTER: OK, this video shows you how to calculate the rate of return for mining all your project. So specifically, I want to show you how to use based on this equation to get the rate of return for a project. On the left side, it's 20,000. It's 20,000. And on the right side is this part, which is showing only one parameter in the equation, the interest rate i.

So what I wanted to do is just find the right interest rate, i, to make this part equal to the left part, which is 20,000. So this part equals to the left side, 20,000. OK.

So I want to do this in Excel. And firstly, we get a try if we plug in i equals to 10%. We get the right side to be $21,930. Which is here. And if I plug in 12% I get the right side value of $19,350.

So we want to make the right side to be $20,000. So we know the right rate of return and interest rate i should be between 10% and 12%. So let's do this in Excel. Here this column is i. And this column, we call it right side result.

OK, it's basically a trial and error solution, trial and error method. So we plug in 10%. And we want to make the stack to be thousands.

So the next number should be 0.101. And then the next number should be 0.102. And then we select them 3, drag them down to 12%, which is here. Here.

We want to calculate the right side result based on this column, the interest rate. OK, we plug in the number. So it's 2,000 multiplied by a number. 1 plus this number, to the power of 10.

And minus 1 divided by this number, multiplied by 1 plus this number. OK, to the power of 10. Plus $25,000 divided by 1 plus this number to the power of 10.

OK, we got $21,927. And we drag this down to 12%. And then we get this column for the right side result. We can see for $20,000 value it should be between 11.4% and 11.5%. So $20,000 is between these two numbers.

And then we want to narrow it down further. So let's make another column. This is i. This is right side result. And we want to make a smaller stack.

OK, stack, and we drag it down to this number. And then we still calculate the right side result. We copy this equation and we put it here. Put it here. And this should be D2. D2. D2. D2. OK.

Then we drag this down. So we can see that this number should be the nearest to $20,000. Which means this should be the right interest rate, the right rate of return for this project. And so that should be 11.46% as the rate of return for this project.

And we can use this trial and error method to solve the rate of return for our project. So that's it for this video.

Credit: Tim's Energy and Resource Economics Channel