Question

The Fox Corporation is looking to replace an existing printing press with one of two newer models that are more efficient. The current press is three years old, cost 32,000 and is being depreciated under MACRS using a 5-year recovery period. The first alternative under consideration, Printing Press A, cost $40,000 to purchase and $8,000 to install. It has a 5 year usable life and will be depreciated under MACRS using a 5-year recovery period. The second alternative, press B cost $54,000 to purchase and $6,000 to install. It also has a 5 year usable life and will be depreciated under MACRS using a 5 year recovery period. The purchase of press A would result in a $4,000 increase in net working capital, and the purchase of Press B would increase net working capital by $6,000. The projected Earnings before depreciation interest and taxes for each alternative is presented below.

Year	Press A	Press B	Existing press
1	25,000	22,000	14,000
2	25,000	24,000	14,000
3	25,000	26,000	14,000
4	25,000	28,000	14,000
5	25,000	28,000	14,000

The existing press can currently be sold for $18,000 before taxes. At the end of the 5 years the existing press can be sold for $1,000 before taxes. Press A can be sold to net $12,000 before taxes and press B can be sold to net $20,000 before taxes at the end of the 5 year period. The firm is subject to a 40% tax rate.

The company has $100M of debt outstanding with a yield-to-maturity of 8%, and has $150M of equity outstanding with a beta of 0.9. The expected market return is 13% and the risk-free rate is 5%.

What is the 1) discounted payback period 2) NPV  3) IRR 4) MIRR

Please answer on an excel document explaining how you found each answer. Thank you

Answer 1

Formulas Used:-

	book value	resale value	profit	tax	net resale value
value of old machine after 3 years	=(1-B3-B4-B5)*32000	18000	=F2-E2	=G2*40%	=F2-H2
value of old machine after 5 years	=(1-B3-B4-B5-B6-B7)*32000	1000	=F3-E3	=G3*40%	=F3-H3
cost of Equity	=5%+(0.9*(13%-5%))
cost of debt	=8%*(1-0.4)
WACC	=((100*E6)+(150*E5))/250
working capital	4000
initial cost of press A	48000
Press A	1	2	3	4	5
before tax cashflow	25000	25000	25000	25000	25000
Depriciation	=E9*B3	=E9*B4	=E9*B5	=E9*B6	=E9*B7
profit before tax	=E11-E12	=F11-F12	=G11-G12	=H11-H12	=I11-I12
tax	=E13*0.4	=F13*0.4	=G13*0.4	=H13*0.4	=I13*0.4
profit after tax	=E13-E14	=F13-F14	=G13-G14	=H13-H14	=I13-I14
operating cashflow	=E15+E12	=F15+F12	=G15+G12	=H15+H12	=I15+I12
value of press A After 5 years	=E9-SUM(E12:I12)
resale value	12000
net realised	=E19-(0.4*(E19-E18))
year	0	1	2	3	4	5
press A cashflow	=-(E9+E8)+I2	=E16	=F16	=G16	=H16	=I16+E8+E20
present value	=E23/(1+$E$7)^E22	=F23/(1+$E$7)^F22	=G23/(1+$E$7)^G22	=H23/(1+$E$7)^H22	=I23/(1+$E$7)^I22	=J23/(1+$E$7)^J22
discounted payback	=2-((E24+F24+G24)/H24)
NPV	=SUM(E24:J24)
IRR	=IRR(E23:J23)
MIRR	=MIRR(E23:J23,E7,E7)
working capital	6000
initial cost of press B	60000
Press B	1	2	3	4	5
before tax cashflow	22000	24000	26000	28000	28000
Depriciation	=E31*B3	=E31*B4	=E31*B5	=E31*B6	=E31*B7
profit before tax	=E33-E34	=F33-F34	=G33-G34	=H33-H34	=I33-I34
tax	=E35*0.4	=F35*0.4	=G35*0.4	=H35*0.4	=I35*0.4
profit after tax	=E35-E36	=F35-F36	=G35-G36	=H35-H36	=I35-I36
operating cashflow	=E37+E34	=F37+F34	=G37+G34	=H37+H34	=I37+I34
value of press B After 5 years	=E31-SUM(E34:I34)
resale value	20000
net realised	=E41-(0.4*(E41-E40))
year	0	1	2	3	4	5
press B cashflow	=-(E31+E30)+I24	=E38	=F38	=G38	=H38	=I38+E30+E42
present value	=E45/(1+$E$7)^E44	=F45/(1+$E$7)^F44	=G45/(1+$E$7)^G44	=H45/(1+$E$7)^H44	=I45/(1+$E$7)^I44	=J45/(1+$E$7)^J44
discounted payback	=3-((E46+F46+G46+H46)/I46)
NPV	=SUM(E46:J46)
IRR	=IRR(E45:J45)
MIRR	=MIRR(E45:J45,E7,E7)