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\left(P/A_{i,5}\right)$ or
$\left(P/A_{i,5}\right)=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?

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 i 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 i as an unknown variable.

present value equation at present time to calculate i:
$\text{present value of cost }=\text{ present value of income }+\text{ present value of salvage}$

$\begin{array}{l}\text{present value of all costs }\left(C\right)=20,000\\ \text{present value of all incomes }=2,000*\left(P/A_{i,10}\right)=2000*\left[{\left(1+i\right)}^{10}-1\right]/\left[i{\left(1+i\right)}^{10}\right]\\ \text{present value of salvage }=25,000*\left(P/F_{i,10}\right)=25,000*\left[1/{\left(1+i\right)}^{10}\right]\end{array}$

$20,000=2000*\left[{\left(1+i\right)}^{10}-1\right]/\left[i{\left(1+i\right)}^{10}\right]+25,000*\left[1/{\left(1+i\right)}^{10}\right]$

It is very difficult to solve this explicitly for i. By trial and error, we can easily find the i 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).