1. Construct a spreadsheet to calculate the payback period, internal rate of ret

Question

1. Construct a spreadsheet to calculate the payback period, internal rate of return, modified internal rate of return, and net present value of the proposed mine. You must submit the spreadsheet. Repeat your answers below.

Payback Period:

IRR:

MIRR:

NPV:

2. Based on your analysis, should the company open the mine?

3. Bonus Question (not graded): Most spreadsheets do not have a built-in formula to calculate the payback period. Write a VBA script that calculates the payback period for a project.

Hint: http://www.vbaexpress.com/kb/getarticle.php?kb_id=252.

    

           

Explanation / Answer

1.

                                   

Cumulative

Cash flows

PVF

@12%

Amount

in thousand $

Cumulative

Amount

PVF

@20%

Payback Period = 4 years + [650000-584000]/[794000-584000] = 4.31 years

Modified Payback Period = 6 years + [650000-620925]/[669741-620925]

= 6.5956 years

IRR = 12+ [28493/28493-(-126854)]*8

= 13.47%

2. The mine should be opened as the NPV at cost of capital of the company is positive.

Year Cash flows

Cumulative

Cash flows

PVF

@12%

Amount

in thousand $

Cumulative

Amount

PVF

@20%

Amount 0 -650000 1 -650000 1 -650000 1 80000 80000 0.893 71440 71440 0.833 66640 2 121000 201000 0.797 96437 167877 0.694 83974 3 162000 363000 0.712 115344 283221 0.579 93798 4 221000 584000 0.636 140556 423777 0.482 106522 5 210000 794000 0.567 119070 542847 0.402 84420 6 154000 948000 0.507 78078 620925 0.335 51590 7 108000 1056000 0.452 48816 669741 0.279 30132 8 86000 1142000 0.404 34744 704485 0.233 20038 9 -72000 0.361 -25992 0.194 -13968 NPV 28493 -126854

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.