Seth Bullock, the owner of Bullock Gold Mining, is evaluating a new gold mine in South Dakota. Dan Dority, the company's geologist, has just finished his analysis of the mine site. He has estimated that the mine would be productive for eight years, after which the gold would be completely depleted. Dan has taken an estimate of the gold deposits to you, the company's financial officer. You have been asked by Seth to perform an analysis of the proposed mine and present your recommendation on whether the company should proceed with this project.

You used the estimates provided by Dan to determine the projected after-tax, incremental cash inflows that would be generated during the life of this project (see below). The cost to open this mine is estimated to be $500,000,000 and it is expected that closing the mine at the end of its useful life (and reclaiming the surrounding area) will result in an additional cost that is already reflected in the net cash flow for Year 8 presented below.

Bullock Mining has a 12% required rate of return on its mining projects, and a maximum acceptable payback period of 4 years.

Year

Cash Flow

1$60,000,000

2. $90,000,000

3. $170,000,000

4 $230,000,000

5 $205,000,000

6 $140,000,000

7. $110,000,000

8. $30,000,000

You are to evaluate the proposed mine using the net present value, profitability index, internal rate of return, modified internal rate of return, payback period and discounted payback period. Based on your analysis, provide a recommendation to Seth of whether to proceed with this project and explain your rationale based on the applicable acceptance criteria.

How would I be able to accomplish this through excel?