Using Excel To Determine Internal Rate Of Return

The internal rate of return can reflect the real reward of investment projects, and is widely used in the evaluation of investment projects.

Unfortunately, the internal rate of return is a solution of a higher order equation, and it is not easy to get the exact value, so it has to be replaced by an estimate in practical application.

Starting from the concept of "accuracy", if an indicator can not get its exact value and replace it with the estimated value, the error between the estimated value and the accurate value should be limited to an allowable range, that is, the estimated value should reach the required accuracy.

However, the error margin of internal rate of return determined by the usual look-up table method is large, which affects the accuracy of evaluation to a certain extent.

Excel can solve this problem and use it to determine the internal rate of return simply and accurately.

As the net present value (NPV) decreases with the increase of the R, the R1 and R2 can be determined first, so that NPV (R1) > 0, NPV (R2) < 0, then the R1 will be enlarged, and the R2 will be adjusted to the R0, so that the ratio of R2 (0) will be 0.

Under the premise of R1 < R0 < R2, NPV (R1) > 0 and NPV (R2) < 0, the R1 is enlarged and the R2 is near to R0, so that [[R2] / 2] will be less than.

Among them, the allowable error between the estimated intrinsic value and the exact value of the intrinsic rate is given.

The estimated value r = (R1 + R2) / 2, then, the error between the estimated value of the intrinsic return rate R and the exact value R0 is not more than the given permissible error epsilon.

In other words, by adjusting the R1 and R2 by the above method, the error of the estimated internal rate of return R and the exact value R0 can be limited to the given range.

However, under normal circumstances, adjusting R1 and R2 is a very troublesome matter. Excel can be easily carried out.

The following example shows that the initial investment amount of the investment scheme is 50000 yuan, and the duration of the project is 5 years. The net cash flows of first, second, third, 4 and 5 years are 8000, 12030, 13900, 15000 and 25000 yuan respectively.

The internal rate of return of the scheme is determined (the error is not more than 0.0001).

Firstly, we choose different rate of return R, if we take several numbers from 0.01 to 0.07, calculate the corresponding net present value NPV (R), and determine R1 and R2 that satisfy NPV (R1) > 0 and NPV (R2) < 0, and the closest to R.

In this case, select a worksheet for Excel and input 0.0700 and 0.0800 in A1 to A10 respectively.

0.1600, input the formula in B1 cell: "= NPV (A1800012030139001500025000) - 50, 000".

Calculate the net present value corresponding to the A1 cell return rate of 0.0700 (formula NPV is Excel's built-in net present value function) 8598.73, then use the automatic fill function of Excel to fill the following formula in B2 to B10 area: "Excel" = "NPV (A2800012030139001500025000) - 50000".

"NPV (A10800012030139001500025000) - 50000", "B1", that is, using the left button to fill the handle of the B1 cell, drag it down to B10, calculate the corresponding A2, A3...

The net present value of A10 cell return rate data.

Through the automatic calculation of Excel, it satisfies NPV (R1) > 0 and NPV (R2) < 0, and the closest R1 and R2 are R1 = 0.1200, R2 = 0.1300.

It is obvious that the error value of this time is 0.005 [(0.1300 - 0.1200) / 2], which does not conform to the requirement of not exceeding 0.0001. It needs to re tune the R1 and adjust the small R2 to R0 to narrow the error value.

The second step, from 0.1200, takes 0.001 numbers from 0.001 to 0.1300, and then determines the R values satisfying NPV (R1) > 0 and NPV (R2) < 0, and the closest R1 and R2.

The first step is to calculate the net present value in the C and D columns of the table.

Note that the formula entered in the D1 to D10 area should respond to C1 and C10 cell rate data.

The results calculated by Excel show that the R1 and R2 satisfying the conditions are: R1 = 0.1220, R2 = 0.1230.

It is easy to see that the error is still below the required accuracy.

The third step is to continue to expand the R1 and adjust the small R2 to R0, and from 0.1220 to 0.0001, take 0.0001 to 0.1230, and further determine the R values satisfying NPV (R1) > 0 and NPV (R2) < 0, and the closest R1 and R2.

According to the above method, E calculates the net present value of the corresponding return rate in the F and the column.

The results calculated by Excel show that the R1 and R2 satisfying the conditions are: R1 = 0.1223, R2 = 0.1224.

The error value at this time is 0.00005 [(0. 12 24 to 0. 23 23) / 2] less than 0.0001, reaching the required accuracy, and 0.12235 is the R.

In the second, third step of adjustment, the R1 will be adjusted to the R0 and the R2 will be divided into ten equal values according to the scope of the previous step [R1, R2].

In this way, step by step reduction will eventually make [[R 2] R / 2) less than.

If the accuracy of the request is higher, you can adjust the R1 and R2 to R0 further until the required accuracy is achieved.

In practical applications, we can make full use of Excel's replication and automatic filling function to make the operation simpler and faster.