Question

DeHass, Inc. is looking at a new investment opportunity that will have an up-front cost of $1,050,000. The company projects that the life of this opportunity will be 6 years. This opportunity will have an annual cost of $30,000 (cash outflow) for upkeep of equipment. This $30,000 cost occurs at the end of each year. DeHass, Inc. expects to generate $300,000 cash inflow at the end of the first year from taking on this potential opportunity. Cash inflows at the end of the second year are expected to be 110% of year one cash inflows. Year 3 cash inflows are expected to be 6% greater than Year 2 cash inflows. Each year after Year 3, the annual increase in cash inflows will be 1% less than it was the year before. So, that means that the Year 4 cash inflows will be 5% greater than the year three cash inflows. You will need to calculate net cash flow amounts for each period in order to answer parts B-D below. The net cash flow for a period is equal to the cash inflows for that period less the cash outflows for that period. The company plans on financing this opportunity 40% with debt and 60% with common stock. The before-tax cost of the debt is 5.75% and the cost of the common stock is 16.5%. The company's marginal tax rate is 35%.

PLEASE SHOW ALL STEPS IN EXCEL

(A) What is the weighted average cost of capital (WACC)?

(B) What is the Internal Rate of Return (IRR) of this new opportunity?

(C) What is the Net Present Value (NPV) of this new opportunity?

(D) Should this opportunity be pursued? Why or why not?

Answer 1

After tax cost of debt	pre tax cost of debt*(1-tax rate)	5.75*(1-.35)	3.7375
WACC	(weight of debt* after tax cost)+(weight of equity*cost of equity)	(.4*3.7375)+(.6*16.5)	11.395
If there is no consideration of depreciation in the question
Year	0	1	2	3	4	5	6
cash outflow	-1050000
cash flow		300000	330000	349800	367290	381981.6	393441.048
less cash outflow		30000	30000	30000	30000	30000	30000
operating cash flow		270000	300000	319800	337290	351981.6	363441.048
less taxes-35%		94500	105000	111930	118051.5	123193.56	127204.3668
after tax cash flow or net operating cash flow	-1050000	175500	195000	207870	219238.5	228788.04	236236.6812
present value of cash flow = net operating cash flow/(1+r)^n r = 11.395%	-1050000	157547.4662	157145.9583	150381.6074	142381.653	133384.3352	123638.3344
Net present value =sum of present value of cash flow	-185520.6455
IRR =Using IRR function in MS excel =irr(cell reference year 0 net operating cash flow : cell reference year 6 net operating cash flow	5.27%
Project should not be considered as NPV is negative and IRR is less than required rate of return
If question is solved assuming straight line depreciation method is Used
Year	0	1	2	3	4	5	6
cash outflow	-1050000
cash flow		300000	330000	349800	367290	381981.6	393441.048
less cash outflow		30000	30000	30000	30000	30000	30000
add depreciation-1050000/6		175000	175000	175000	175000	175000	175000
operating cash flow		95000	125000	144800	162290	176981.6	188441.048
less taxes-35%		33250	43750	50680	56801.5	61943.56	65954.3668
after tax cash flow		61750	81250	94120	105488.5	115038.04	122486.6812
add depreciation-1050000/6		175000	175000	175000	175000	175000	175000
net operating cash flow	-1050000	236750	256250	269120	280488.5	290038.04	297486.6812
present value of cash flow = net operating cash flow/(1+r)^n r = 11.395%	-1050000	212531.9808	206505.9067	194692.347	182159.6858	169093.3283	155694.5246
Net present value =sum of present value of cash flow	70677.77327
IRR =Using IRR function in MS excel =irr(cell reference year 0 net operating cash flow : cell reference year 6 net operating cash flow	13.62%
Project should be considered as NPV is positive and IRR is more than required rate of return