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 EXCEL FORMULAS/FUNCTIONS

(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

A) Before Tax cost of Debt=r_d = 5.75% , Cost of common stock = r_e = 16.5%

Weight of debt = w_d =40% and Weight of Equity = w_e = 60% , Tax rate = 35%

Weighted average cost of Capital = WACC = r_d x w_d x (1-tax rate) + r_e x w_e

WACC = 5.75% x 40% x (1-35%) + 16.5% x 60% = 5.75% x 40% x 65% + 16.5% x 60% = 0.01495 + 0.09900 = 0.11395

= 11.395% = 11.40% (rounding to 2 place of decimal)

B) For calculating IRR, we will first calculate Net Cash flows

Calculating Cash Inflows

Cash inflow in year 1 = 300000

Cash inflow in year 2 = 110% of Cash flow in year 1 = 110% x 300000 = 330000

Cash inflow in year 3 = Cash inflow in year 2 x (1+6%) = Cash inflow in year 2 x 1.06 = 330000 x 1.06 = 349800

Cash inflow in year 4 = Cash inflow in year 3 x (1+5%) = Cash inflow in year 3 x 1.05 = 349800 x 1.05 = 367290

Cash inflow in year 5 = Cash inflow in year 4 x (1+4%) = Cash inflow in year 3 x 1.04 = 367290 x 1.04 = 381981.60

Cash inflow in year 6 = Cash inflow in year 5 x (1+3%) = Cash inflow in year 3 x 1.03 = 381981.60 x 1.03 = 393441.05

Calculating Cash Outflows

Cash outflow in year 0 = Initial upfront cost = -1050000

Cash outflow for each of the year 1 to 6 = annual cost to upkeep the equipment = -30000

Net cash flow for a year = Cash inflow - Cash outflow

Calculating Net Cash Flow
Year	0	1	2	3	4	5	6
Cash Inflows		300000.00	330000.00	349800.00	367290.00	381981.60	393441.05
Cash Outflows	-1050000.00	-30000.00	-30000.00	-30000.00	-30000.00	-30000.00	-30000.00
Net Cash Flow	-1050000.00	270000.00	300000.00	319800.00	337290.00	351981.60	363441.05

To calculate IRR, we will use IRR function in excel

Formula to be used in excel: =IRR(cash flows)

where cash flow are net cash flows from year 0 to 6

Below is excel screenshot for function of IRR calculation

Using IRR function in excel we get IRR of new investment opportunity = 19.79%

C) Net present value of Investment opportunity = Cash flow in year 0 + Sum of present values of cash flows from year 1 to 6 discounted at WACC

= -1050000 + 270000/(1+11.40%) + 300000/(1+11.40)² + 319800/(1+11.40)³ + 337290/(1+11.40)⁴ + 351981.60/(1+11.40)⁵ + 363441.05/(1+11.40)⁶

= -1050000 + 242369.84 + 241741.31 + 231325.17 + 219009.37 + 205160.62 + 190161.60 = 279767.92

Hence NPV of this investment opportunity is 279767.92

D) This investment opportunity should be pursued because

i) NPV of this investment opportunity is greater than 0

ii ) IRR of this investment opportunity is greater than Weighted average cost of capital.