Applications of green, lean manufacturing techniques coupled with value stream mapping can make large financial differences over future years while placing greater emphasis on environmental factors. Engineers with Monarch Paints have recommended to management an investment of $200,000 now in novel methods that will reduce the amount of wastewater, packaging materials, and other solid waste in their consumer paint manufacturing facility. Estimated savings are $15,000 per year for each of the next 10 years and an additional savings of $300,000 at the end of 10 years in facility and equipment upgrade costs. Determine the rate of return using hand and spreadsheet solutions.

Solution by Hand

Use the trial-and-error procedure based on a PW equation.

1-      Figure (6–3) shows the cash flow diagram.

2-      Use Equation (6-1) format for the ROR equation.

Figure (6-3): Cash flow diagram, Example (6-2)

3-      Use the estimation procedure to determine i for the first trial. All income will be regarded as a single F in year 10 so that the P/F factor can be used. The P/F factor is selected because most of the cash flow ($300,000) already fits this factor and errors created by neglecting the time value of the remaining money will be minimized. Only for the first estimate of i, define P = $200,000, n = 10, and F = 10(15,000) + 300,000 = $450,000.

Now we can state that

The roughly estimated i is between 8% and 9%. Use 9% as the first trial because this approximate rate for the P/F factor will be lower than the true value when the time value of money is considered.

The result is positive, indicating that the return is more than 9%. Try i = 11%.

Since the interest rate of 11% is too high, linearly interpolate between 9% and 11%.

Solution by Spreadsheet

The fastest way to find i* is to use the RATE function ( Equation (6-4)). The entry = RATE(10,15000,-200000,300000) displays i* = 10.55% per year. It is equally correct to use the IRR function. Figure (6-4) , column B, shows the cash flows and = IRR(B2:B12) function to obtain i*.

For a complete spreadsheet analysis, use the procedure outlined above.

1-      Figure (6-3) shows cash flows.

2-      Equation (6-6) is the ROR relation.

3-      Figure (6–4) shows the net cash flows in column B.

4-      The IRR function in cell B14 displays i* = 10.55%.

5-      To graphically observe i* = 10.55%, column D displays the PW graph for different i values. The NPV function is used repeatedly to calculate PW for the xy scatter chart.

Figure (6-4): Spreadsheet to determine i* and develop a PW graph, Example (6-2).

Just as i* can be found using a PW equation, it may equivalently be determined using an AW relation. This method is preferred when uniform annual cash flows are involved. Solution by hand is the same as the procedure for a PW-based relation, except Equation (6-2) is used. In the case of Example (6-2) , i* = 10.55% is  determined using the AW-based relation.

The procedure for solution by spreadsheet is exactly the same as outlined above using the IRR function. Internally, IRR calculates the NPV function at different i values until NPV = 0. (There is no equivalent way to utilize the PMT function, since it requires a fixed value of i to calculate an A value.)