Question

Bullock Gold Mining 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 mined. Dan has taken an estimate of the gold deposits to Alma Garrett, the company’s financial officer. Alma has been asked by Seth to perform an analysis of the new mine and present her recommendation on whether the company should open the new mine. Year Cash Flow 0 −$625,000,000 1 70,000,000 2 129,000,000 3 183,000,000 4 235,000,000 5 210,000,000 6 164,000,000 7 108,000,000 8 86,000,000 9 − 90,000,000 Alma has used the estimates provided by Dan to determine the revenues that could be expected from the mine. She also has projected the expense of opening the mine and the annual operating expenses. If the company opens the mine, it will cost $625 million today, and it will have a cash outflow of $90 million nine years from today in costs associated with closing the mine and reclaiming the area surrounding it. The expected cash flows each year from the mine are shown in the nearby table. Bullock Gold Mining has a 12 percent required return on all of its gold mines. QUESTIONS 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. Based on your analysis, should the company open the mine? Bonus question: 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

Answer 1

For the purpose of our analysis we'll need the cumulative value of all cash inflows (i.e., ignoring the cash outflows) and also the discounted values (or present values) of all cash flows (including cash outflows). Let us first do this using Excel spreadsheet.

Required rate of return ( r )	12%
Year (n)	Cash flow (C)	Cumulative cash inflows	Discounted cash flow (present value of each cash flow = C/(1+r)^n
0	-625000000		-625000000
1	70000000	70000000	62500000
2	129000000	199000000	102838010
3	183000000	382000000	130255785
4	235000000	617000000	149346748
5	210000000	827000000	119159640
6	164000000	991000000	83087504
7	108000000	1099000000	48853715
8	86000000	1185000000	34733958
9	-90000000		-32454902
Sum of all discounted cash flows	73320458

Based on the above table we can calculate the different measures as follows:

A. Payback period

We know,

Payback period = Initial investment/Annual cash inflow

In this case,

Initial investment = 625000000 (Note, we need not to consider the salvage cost)

However, as the annual cash flows are not same, we need to find the point of time (in years) when the cash flows are just crossing the initial investment value. Clearly, the cumulative cash inflows will reach $617 million mark in the 4th year, and so the $625 million will be crossed between 4th and 5th year.

We can find the exact point of time (y) through interpolation as follows:

where, y1=4, y2=5, x=625 million, x1=617 million, and x2=827 million

Upon inputting these values in the above formula we get,

Payback period =

= 4.04 years

Decision based on payback period:

As payback period is less than the total project duration, the company shall open the mine.

B. Internal rate of return

In order to calculate the internal rate of return, we can make use of the inbilt Excel formula IRR as

=IRR(values, [guess])

where, "values" will be all the actual cash flows values (not the discounted ones) given below:

-625000000
70000000
129000000
183000000
235000000
210000000
164000000
108000000
86000000
-90000000

Upon inputting these values in the formula, and taking 12% as the initial guess, we get IRR = 15%

Decision based on IRR:

As IRR is greater than the required rate of return, it would be profitable for the company to open the mine.

C. Modified Internal rate of return

Again, we can calculate this using the inbuilt Excel function MIRR:

= MIRR(values, finance_rate, reinvest_rate)

Here again, we need to use the "values" that we used in above step B. We are given the finance_rate as 12%, and we can assume the same "reinvest_rate".

Upon inputting these values in the function we get the MIRR = 13%

Decision based on MIRR:

As MIRR is greater than the required rate of return, it would be profitable for the company to open the mine.

D. Net present value (NPV)

Net present value is the sum of present values of all cash flows associated with the project (including the present values of cash outflows as well cash inflows), or in other words, it is the Sum of present values of all cash inflows minus Sum of present values of all cash outflows. This we already calculated as: $73,320,458

Decision based on NPV:

As NPV is positive, it would be profitable for the company to open the mine.