An estimated 6 billion gallons of clean drinking water disappear each day across the United States due to aging, leaky pipes, and water mains before it gets to the consumer or industrial user. The American Society of Civil Engineers (ASCE) has teamed with municipalities, counties, and several excavation companies to develop robots that can travel through mains, detect leaks, and repair many of them immediately. Four propos­als have been received for funding. There is a $100 million ($100 M) limit on capital funding and the MARR is established at 12% per year.

Question

(a) Use the IROR to rank and determine which of the four independent projects should be funded. (Solve by hand or spreadsheet as instructed.)

(b) Determine the rate of return for the projects selected in part (a).

(c) Determine the overall rate of return for the projects selected in part (a), assuming that excess funds are invested at the MARR. Is the overall return economically acceptable?

Answer 1

(a) By hand: Find IROR for each project; select highest ones within budget

constraint of $100 million.



For W: 0 = -12,000 + 5000(P/A,i*,3)

i* = 12.0%



For X: 0 = -25,000 + 7,300(P/A,i*,4)

i* = 6.5%



For Y: 0 = -45,000 + 12,100(P/A,i*,6)

i* = 15.7%



For Z: 0 = -60,000 + 9000(P/A,i*,8)

i* = 4.2%



Only two projects (W and Y) have i* ? MARR = 12%.



Select Y and W with total investment of $57 million.

By spreadsheet: Select Y and W after ranking (row 12); invest $57 million



(b) Find i* of Y and W



NCF, year 0: $-57.0 million

NCF, years 1-3: $17.1 million

NCF, years 4-6: $12.1 million



0 = -57 + 17.1(P/A,i*,3) + 12.1(P/A,i*,3)(P/F,i*,3)

i* = 15.1% (IRR function, column L)



(c) The $43 million not committed makes MARR = 12% elsewhere.



Overall ROR = [57,000(15.1) + 43,000(12.0)]/100,000

= 13.8%



Since 13.8% > MARR = 12%, the selection of Y and W is acceptable.