Question

A company is considering a project that requires an initial investment of $56M to build a new plant and purchase equipment. The investment will be depreciated as a MACRS 10-year class (see p. 21 in the text) asset. The new plant will be built on some of the company’s land which has a current, after-tax market value of $5.5M. The company will produce units at a cost of $255 each and will sell them for $300 each. There are annual fixed costs of $1.5M. Unit sales are expected to be 275,000 each year for the next 9 years, at which time the project will be abandoned. At that time, the plant and equipment is expected to be worth $22M (before tax) and the land is expected to be worth $10M (after tax). To supplement the production process, the company will need to purchase $3M worth of inventory. That inventory will be depleted during the final year of the project. The company has $500M of debt outstanding with a yield-to-maturity of 8%, and has $600M of equity outstanding with a beta of 1.2. The expected market return is 13% and the risk-free rate is 5%.The company’s marginal tax rate is 35%. See Excel for solution. Is there a way i can put it into excel too?

1. What is the NPV of the project?

2. What is the IRR of the project?

3. What is the Pay-back period (discount and non-discount)

4. What is the profitability index?

5. Assuming the firm requires a 5 year pay-back period should the project be accepted?

6. Is there a problem of multiple cash-flows?

Answer 1

	after tax cost of debt	YTM*(1-tax rate)	8*(1-.35)	5.2
	cost of equity	risk free rate+(market return-risk free rate)*beta	5+(13-5)*1.2	14.6
	WACC
	source	value	weight	cost	weight*cost
	debt	500	0.4545455	5.2	2.363636364
	equity	600	0.5454545	14.6	7.963636364
	total	1100	1	WACC =sum of weight*cost	10.33
	Year	0	1	2	3	4	5	6	7	8	9
	cost of machine	-56000000
	Investment in Inventory	-3000000
	sales = units sold*selling price		82500000	82500000	82500000	82500000	82500000	82500000	82500000	82500000	82500000
	less operating cost		70125000	70125000	70125000	70125000	70125000	70125000	70125000	70125000	70125000
	less fixed cost		1500000	1500000	1500000	1500000	1500000	1500000	1500000	1500000	1500000
	less depreciation		5600000	10080000	8064000	6451200	5163200	4127200	3668000	3668000	3673600
	operating profit		5275000	795000	2811000	4423800	5711800	6747800	7207000	7207000	7201400
	after tax profit = operating profit*(1-tax rate)		3428750	516750	1827150	2875470	3712670	4386070	4684550	4684550	4680910
	operating cash flow = after tax profit+depreciation		9028750	10596750	9891150	9326670	8875870	8513270	8352550	8352550	8354510
	after tax sale proceeds of machine										16226680
	recovery of working capital										3000000
	net operating cash flow	-59000000	9028750	10596750	9891150	9326670	8875870	8513270	8352550	8352550	27581190
	present value of cash flow = net operating cash flow/(1+r)^n r = 10.33%	-59000000	8183404.3	8705334.274	7364884.383	6294368.11	5429288	4719920.659	4197239.6	3804260	11385981
1-	net present value =sum of present value of cash flow	1084678.9
2-	IRR =Using IRR function in MS excel irr(cell reference Year 0 net operating cash flow:cell reference Year 9 net operating cash flow)	10.75%
4-	profitability index =1+(npv/initial investment)	1+(1084678.9/59000000)	1.02
3-	Year	0	1	2	3	4	5	6	7	8	9
	net operating cash flow	-59000000	9028750	10596750	9891150	9326670	8875870	8513270	8352550	8352550	27581190
	cumulative cash flow		9028750	19625500	29516650	38843320	47719190	56232460	2767540
									amount to be recovered
	payback period = year before final year of recovery+(amount to be recovered/cash flow of the final year of recovery)	6+(2767540/8352550)	6.3313407
	Year	0	1	2	3	4	5	6	7	8	9
	present value of cash flow = net operating cash flow/(1+r)^n r = 10.33%	-59000000	8183404.3	8705334.274	7364884.383	6294368.11	5429288	4719920.659	4197239.6	3804260	11385981
	cumulative cash flow		8183404.3	16888738.61	24253622.99	30547991.1	35977279	40697199.29	44894439	48698698	10301302
											amount to be recovered
	discounted payback period = year before final year of recovery+(amount to be recovered/cash flow of the final year of recovery)	8+(10301302/11385981)	8.90
	calculation of depreciation
	Year	1	2	3	4	5	6	7	8	9	10
	cost of equipment	56000000	56000000	56000000	56000000	56000000	56000000	56000000	56000000	56000000	56000000
	MACRS rate	10%	18%	14.40%	11.52%	9.22%	7.37%	6.55%	6.55%	6.56%
	annual depreciation	5600000	10080000	8064000	6451200	5163200	4127200	3668000	3668000	3673600
	total accumulated depreciation	sum of yearly depreciation	50495200
	Book value of equipment t the end of year 9 = cost less accumulated depreciation	5504800
	selling price of equipment	22000000
	gain on sale of equipment =selling price-book value	16495200
	tax on gain on sale of equipment = gain on sale*tax rate	16495200*35%	5773320
	after tax sale proceeds = total sale proceeds -tax on gain	22000000-5773320	16226680
5-	No as payback period is 6.33 Years which is more than 5 years period
6-	Yes as cash flows are occuring at different time period