Please help answer the question in excel format with the templates below.

6. The narrow gravel road to Jehnzen Lake is open only for the summer months. At present, the county spends $750 per mile each year to prepare the road for summer traffic and another $150 per mile for maintenance during the period in which it is open. A "permanent" road could be constructed at a cost of $10,000 per mile; the county would have to spend $800 per mile for maintenance (patch- ing, etc.) only every five years through the thirty-year life of the road. Prospects for the area suggest that the road would have to be relocated at the end of that period. If 5 percent is a reasonable discount rate, which option is less costly? What discount rate would cause the two alternatives to have the same cost in present value terms 1 2 3 4 5 6 10000 118 7 Alternative Upfront Year 5 Year 10 Year 15 Year 20 Year 25 Year 30 Total 72 57 44 35 10417 300 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 Dsicount Rate: 0.05 Curreat Road NPY 4,612 Year Current Road Proposed 0 3300 1 300 2 300 3 300 4 300 5 300 150 6 300 7 300 8 300 9 300 10 300 150 11 300 12 300 13 300 14 300 15 300 150 16 300 17 300 18 300 19 300 20 300 150 21 300 22 300 23 300 24 300 25 300 150 26 300 27 300 28 300 29 300 30 300 150 Use trial and error to find the rate that will get the same NPV for both options. Dsicount Rate: 0.061 Curreat Road NPY Alternative 4,076 Upfront 10000 Year Current Ro Proposed Year 5 111 0 3300 Year 10 83 1 300 Year 15 62 2 300 Year 20 46 3 300 Year 25 34 4 300 Year 30 25 5 300 150 Total 10361 6 300 7 8 300 300 10 300 150 11 300 12 300 13 300 14 300 15 300 150 16 300 17 300 18 300 19 300 20 300 150 21 300 22 300 23 300 24 300 25 300 150 26 300 27 300 28 300 29 300 30 300 150 24 25 26 27 28 29 30 31 32 33 34 35 B c D E F H L Year Permanent Road 5000 5 5 7 3 1 2 3 4 5 6 7 8 0 1 ffor Internal Rate of Return) -3300 300 300 300 300 150 300 300 300 300 150 300 150 9 150 3 4 5 6 7 3 3 C0 -1 10 11 12 13 14 300 Current Road 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 150 -4 -5 -6 27 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 150 300 300 150 300 300 300 300 150 300 300 300 300 150 300 300 300 300 150 0 =1 150 =4 =5 6 7 PY $4,611.74 $3,588.43 $5,380.40 $5,223.98 IRR 7.25% 7.2% 6. The narrow gravel road to Jehnzen Lake is open only for the summer months. At present, the county spends $750 per mile each year to prepare the road for summer traffic and another $150 per mile for maintenance during the period in which it is open. A "permanent" road could be constructed at a cost of $10,000 per mile; the county would have to spend $800 per mile for maintenance (patch- ing, etc.) only every five years through the thirty-year life of the road. Prospects for the area suggest that the road would have to be relocated at the end of that period. If 5 percent is a reasonable discount rate, which option is less costly? What discount rate would cause the two alternatives to have the same cost in present value terms 1 2 3 4 5 6 10000 118 7 Alternative Upfront Year 5 Year 10 Year 15 Year 20 Year 25 Year 30 Total 72 57 44 35 10417 300 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 Dsicount Rate: 0.05 Curreat Road NPY 4,612 Year Current Road Proposed 0 3300 1 300 2 300 3 300 4 300 5 300 150 6 300 7 300 8 300 9 300 10 300 150 11 300 12 300 13 300 14 300 15 300 150 16 300 17 300 18 300 19 300 20 300 150 21 300 22 300 23 300 24 300 25 300 150 26 300 27 300 28 300 29 300 30 300 150 Use trial and error to find the rate that will get the same NPV for both options. Dsicount Rate: 0.061 Curreat Road NPY Alternative 4,076 Upfront 10000 Year Current Ro Proposed Year 5 111 0 3300 Year 10 83 1 300 Year 15 62 2 300 Year 20 46 3 300 Year 25 34 4 300 Year 30 25 5 300 150 Total 10361 6 300 7 8 300 300 10 300 150 11 300 12 300 13 300 14 300 15 300 150 16 300 17 300 18 300 19 300 20 300 150 21 300 22 300 23 300 24 300 25 300 150 26 300 27 300 28 300 29 300 30 300 150 24 25 26 27 28 29 30 31 32 33 34 35 B c D E F H L Year Permanent Road 5000 5 5 7 3 1 2 3 4 5 6 7 8 0 1 ffor Internal Rate of Return) -3300 300 300 300 300 150 300 300 300 300 150 300 150 9 150 3 4 5 6 7 3 3 C0 -1 10 11 12 13 14 300 Current Road 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 300 150 -4 -5 -6 27 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 150 300 300 150 300 300 300 300 150 300 300 300 300 150 300 300 300 300 150 0 =1 150 =4 =5 6 7 PY $4,611.74 $3,588.43 $5,380.40 $5,223.98 IRR 7.25% 7.2%