Please show how you get your numbers and formulas in Excel. Thank you.

Calculate the payback period, profitability index, net present value, and internal rate of return. Also, should Bethesda Mining take the contract?

Relevant Data for question:

1. 5,000 acres of land purchased 10 years ago for $4 million.

2. Company could receive $6.5 million on an after-tax basis if land was sold today.

3. Additional equipment will need to be purchased for $95 million and depreciated on a 7 year MACRS schedule.

4. The contract runs for 4 years. The equipment can be sold for 60% of its initial value after 4 years.

5. The contract calls for the delivery of 500,000 tons of coal per year at a price of $86 per ton. Coal production will be 620,000 tons, 680,000 tons, 730,000 tons, and 590,000 tons over the next 4 years. The excess productions will be sold for $77 per ton. Variable Costs are $31 per ton and Fixed Costs are $4,100,000 per year. The mine will require a net working captial investment of 5% of sales. the NWC will be built up in the year prior to the sales.

6. cost of reclamation of land is $2.7 million in year 5.

7. Charitable expense deduction of $6 million in year 6

8. 38% tax rate

9. 12% required return on new strip mine projects

10. A loss in any year will result in a tax credit