A town estimates that it will cost $160 million to design and construct a new water treatment plant and $12.5 million per year to maintain the plant which is expected to last for 10 years. The plant would be constructed over the course of year 0 and maintenance costs commence in year 1. During year 5, plant equipment will need to be replaced at a capital cost of 20 million. (The average wastewater plant life expectancy is between 40 – 50 years, but for simplicity, we assume only 10 years). Analysts estimate that the benefits of the new plant will total $50 million in the first year of operation (year 1), $45 million in year 2, $35 million in the next 3 years and $33 million in each of the final 5 years of its life.

1. Given these cost and benefit estimates, is the project feasible using a discount rate of 8%? Use the Excel template provided to find the project’s net present value. I have completed the calculations for 2 cells for those unfamiliar with using formulas in Excel. YOU MUST UPLOAD YOUR COMPLETED EXCEL FILE.

2. If it is not feasible at 8%, how much would the initial capital costs (in year 0) need to be reduced to make it feasible??

3. Would the project be feasible if a discount rate of 7% was used? Use a new template to determine the NPV. YOU MUST UPLOAD YOUR COMPLETED EXCEL FILE.

 

Transcribed Image Text:

Compute the PV of all costs and benefits in the blanks provided in the spreadsheet using the Excel formula. Two of the PV's have been computed for you. If you are unfamiliar with using formulas in Excel, you can click on the PV's I have entered to see the formula. You will use this formula to compute all PV's, but will need to change the time periods in each formula. YOU WILL NEED TO REDO THE CALCULATIONS USING A 7% DISCOUNT RATE FOR PART C OF QUESTION #2 TREATMENT PLANT ESTIMATED COSTS Year (t) Operating costs PV of Operating Costs Capital costs PV of Capital Costs PV of Costs 0 0 160 160 0 TREATMENT PLANT ESTIMATED BENEFITS Year (t) Benefits PV of Benefits 0 1 12.5 0 0 1 50 2 12.5 0 2 45 3 12.5 0 3 35 4 12.5 0 4 35 5 12.5 20 13.6116639 5 35 6 12.5 0 6 33 7 12.5 0 7 33 8 12.5 0 8 33 17.8288732 9 12.5 0 9 33 10 12.5 0 10 33 PV of Costs PV of Benefits Compute the PV of all costs and benefits in the blanks provided in the spreadsheet using the Excel formula. Two of the PV's have been computed for you. If you are unfamiliar with using formulas in Excel, you can click on the PV's I have entered to see the formula. You will use this formula to compute all PV's, but will need to change the time periods in each formula. YOU WILL NEED TO REDO THE CALCULATIONS USING A 7% DISCOUNT RATE FOR PART C OF QUESTION #2 TREATMENT PLANT ESTIMATED COSTS Year (t) Operating costs PV of Operating Costs Capital costs PV of Capital Costs PV of Costs 0 0 160 160 0 TREATMENT PLANT ESTIMATED BENEFITS Year (t) Benefits PV of Benefits 0 1 12.5 0 0 1 50 2 12.5 0 2 45 3 12.5 0 3 35 4 12.5 0 4 35 5 12.5 20 13.6116639 5 35 6 12.5 0 6 33 7 12.5 0 7 33 8 12.5 0 8 33 17.8288732 9 12.5 0 9 33 10 12.5 0 10 33 PV of Costs PV of Benefits