Question

The director of finance has discovered an error in his WACC calculation. He did not factor in the tax rate when determining the cost of debt. UPC has a line of credit at 4% interest, and the company is taxed at 30%. Further, assume that UPC’s required rate of return on equity is 14%, and its capital structure is 40% debt and 60% equity. Additionally, the budget committee question and answer session revealed that UPC has discovered a technology that will increase its product life span by 1 year. The new technology will add $120,000 and $130,000 to projects A and B’s initial capital outlay, respectively. Further, the finance department has determined that cash flows for years 1, 2, and 3 will be unchanged. However, net cash flows for year 4 will be $300,000 and $150,000 for projects A and B, respectively. • In an Excel spreadsheet, using the UPC scenario, and the new information above, calculate the NPV, IRR, MIRR, and payback periods from projects A and B. You must input all of your data into an Excel spreadsheet and show all formulas. • Using MS Word, explain any risk factors inherent in the budgeting for the 2 projects.

Answer 1

after tax cost of debt	before tax cost of debt*(1-tax rate)	4*(1-.3)	2.8
cost of equity			14
WACC	(weight of debt*cost of debt)+(weight of equity*cost of equity)	(0.4*2.8)+(0.6*14)	9.52
Project A
Year	0	1	2	3	4
incremental cash outflow	-120000
Incremental revenue		0		0	300000
incremental net operating cash flow	-120000	0	0	0	300000
present value of net operating cash flow = net operating cash flow/(1+r)^n r= 9.52%	I758/1.0952^0	J758/1.0952^1	K758/1.0952^2	L758/1.0952^3	M758/1.0952^4
present value of net operating cash flow = net operating cash flow/(1+r)^n r= 9.52%	-120000	0	0	0	208519.903
net present value = sum of present value of cash flow	SUM(I760:M760)	88519.90299
IRR = Using IRR function in MS excel	IRR(I758:M758)	25.74%
MIRR = Using MIRR function in MS excel	MIRR(I758:M758,9.52%,9.52%)	25.74%
Payback period = Initial Investment/cash flow
incremental net operating cash flow	-120000	0	0	0	300000
cumulative cash flow		0	0	0	120000
					Amount to be recovered
Payback period = year before the final year of recovery+(amount to be recovered/cash flow of final year of recovery)	3+(120000/300000)	3.4
Project B
Year	0	1	2	3	4
incremental cash outflow	-130000
Incremental revenue		0		0	150000
incremental net operating cash flow	-130000	0	0	0	150000
present value of net operating cash flow = net operating cash flow/(1+r)^n r= 9.52%	I775/1.0952^0	J775/1.0952^1	K775/1.0952^2	L775/1.0952^3	M775/1.0952^4
present value of net operating cash flow = net operating cash flow/(1+r)^n r= 9.52%	-130000	0	0	0	104259.9515
net present value = sum of present value of cash flow	SUM(I777:M777)	-25740.0485
IRR = Using IRR function in MS excel	IRR(I775:M775)	3.64%
MIRR = Using MIRR function in MS excel	MIRR(I775:M775,9.52%,9.52%)	3.64%
Payback period = Initial Investment/cash flow
incremental net operating cash flow	-130000	0	0	0	150000
cumulative cash flow		0	0	0	130000
					Amount to be recovered
Payback period = year before the final year of recovery+(amount to be recovered/cash flow of final year of recovery)	3+(130000/150000)	3.87
2-Risk factors which are inherited in capital budgeting is related to certanity of future cash flow and risk factor related to time period and uncertainity related to purchasing power or inflation rate