Bullock gold Mining: If the company opens the mine it will cost $650 million tod

Question

Bullock gold Mining: If the company opens the mine it will cost $650 million today, and it will have a cash outflow of $72 million in nine years from today. Bulluock mining has a 12% required return on all of its gold mines. All cash flows are shown in millions

-$ 650 million

Answer 3 questions:

1) construct a spreadsheet to calculate the payback period, IRR,MIRR,and NPV of the proposed mine.

2) based on your analysis. should the company open the mine?

3) most spreadsheet do not have a build in formula to calculate the payback period. Write a VBA script to calculate the payback period for a project.

year cash flow 0

-$ 650 million

1 80 2 121 3 162 4 221 5 210 6 154 7 108 8 86 9 -72

Explanation / Answer

Enter the cash flows in an excel spreadsheet from cells A1 to B10 as below

1) Payback peroid = COUNTIF(C2:C11,"<0")-1+(-C6/B7) = 4.31 Years

IRR = IRR(B2:B11) = 13.25 %

MIRR = MIRR(B2:B11,12%,12%) = 13.51%

NPV =NPV(12%,B2:B11) = 25..40

2) Since IRR > Required rate of return and also NPV is positive, the company should open the mine

3) Create a excel function using VBA to calculate payback period as follows

Put this code In a module of an excel sheet


Function PAYBACK(invest, finflow)
Dim x As Double, v As Double
Dim c As Integer, i As Integer
x = Abs(invest)
i = 1
c = finflow.Count
Do
x = x - v
v = finflow.Cells(i).Value
If x = v Then
PAYBACK = i
Exit Function
ElseIf x < v Then
P = i - 1
Z = x / v
PAYBACK = P + Z
Exit Function
End If
i = i + 1
Loop Until i > c
PAYBACK = "no payback"
End Function

Now Function PAYBACK is created in this excel workbook

Use using the formula =payback(your investment amount,range of your future cash inflows)

Year Cashflow Cummulative Cash flow Year 0 -650 -650 Year 1 80 -570 Year 2 121 -449 Year 3 162 -287 Year 4 221 -66 Year 5 210 144 Year 6 154 298 Year 7 108 406 Year 8 86 492 Year 9 -72 420

Related Questions

Navigate

• Browse (All)
• Browse B
• Subjects

---

---

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