Question

Do the following problems in Excel. Submit your Excel spreadsheet so I can see how you solved the problems (functions you used).

1. Assume the same facts as in part 1, except on Feb. 1, 2016, due to financial problems, Barrick deposit nothing in the fund. After this date, they resume and make sufficient equal payments to meet their goal of $32 million on Aug. 1, 2018. In other words, they make the same amount of payments in the fund as part 1 from Aug. 1, 2013-Aug. 1, 2018. Determine the first set of required payments and the second set of required payments. (10 points).

2. Barrick is comparing two gold mines.

Mine A: The mine is expected to make $3.5 billion in year 1, $4.5 billion in year 2, and $5.5 billion in years 3-6 and $4.25 billion in years 7-10. At the end of year 10, Barrick will need to spend $202 million on environmental clean-up costs and expects the residual value to be $200 billion.

Mine B: The mine is currently in operation and produces $4.1 billion per year. At the end of 10 years the residual value would be $200 billion and no environmental clean-up costs would be necessary.

Which mine should Barrick buy for $142 billion. (To simplify things, assume Net Income is earned one time per year at the end of the year.) The appropriate discount rate is 6.22% compounded quarterly. (15 points)

Answer 1

Since it is not feasible to upload the excel file, I have expanded formula for few columns.

You can proceed with same in other columns

use PV formula for each year

rate would be 6.22% diveded by 4....since compounded quarterly

nper would be 4 in first year, 8 in second year, 12 in third year and so on . (Year number X 4, because compounded quarterly)

Mine A	Year0	Year1	Year2	Year3	Year4	Year5	Year6	Year7	Year8	Year9	Year10
	-142	3.5	4.5	5.5	5.5	5.5	5.5	4.25	4.25	4.25	4.25
											-0.202
											200
	-142	3.5	4.5	5.5	5.5	5.5	5.5	4.25	4.25	4.25	204.048
												Total
PV	-142	$3.29	$3.98	$4.57	$4.30	$4.04	$3.80	$2.76	$2.59	$2.44	$110.07	-0.16335
		=PV(0.0622/4,4,,-3.5,0)	=PV(0.0622/4,8,,-4.5,0)	=PV(0.0622/4,12,,-5.5,0)	=PV(0.0622/4,16,,-5.5,0)
Mine B	Year0	Year1	Year2	Year3	Year4	Year5	Year6	Year7	Year8	Year9	Year10
	-142	4.1	4.1	4.1	4.1	4.1	4.1	4.1	4.1	4.1	4.1
											200
	-142	4.1	4.1	4.1	4.1	4.1	4.1	4.1	4.1	4.1	204.1
												Total
PV	-142	$3.85	$3.62	$3.41	$3.20	$3.01	$2.83	$2.66	$2.50	$2.35	$110.10	-4.4518