In: Finance
(For this part, you MUST present sufficient solution steps, and MUST apply specific Excel functions =NPV(…), =IRR(…), =AVERAGE(…), =YIELD(…) whenever applicable). Please show excel formulas.
Given the following information for Bajor Co.:
Debt: Bajor’s long-term debt capital consists of bonds with 6.250 percent coupon rate (semiannual coupon payments), 9 years time to maturity, and current price of 106.61 percent of its par value (i.e., price = 106.61 relative to full amount redemption par of 100).
Preferred stock: Bajor has not issued any preferred stocks.
Common stock (equity):
Bajor’s equity capital consists of common stocks with the most recent annual dividend of $0.92 per share, and a current stock price of $14 per share.
According to online data sources, Bajor’s long-term dividend growth (for next 5-Year average, per annum) g = 4.5% per year.
The “risk-free” Treasury bill return is 3.8%; the market expected return for the stock market on average is 12.3%; and Bajor’s systematic risk (Beta) is 0.71.
Taxes: The applicable federal-plus-state corporate tax rate for Bajor is 25.7 percent.
Capital weight: Bajor’s “Market Cap” amounts to $18.23 billion, and “Total Debt” amounts to $14.44 billion. You can use such data to estimate the capital weights for equity and debt, respectively (We and Wd).
Time constraint: For any investment projects, Bajor are required by her investors to recover its initial cost within no more than 6 years.
Q1: What is Bajor’s pretax cost of debt Rd, cost of equity Re, and WACC, respectively? (Hint: For the best estimate of cost of equity Re, you must apply both CAPM and Dividend Growth Model and then average the two estimates.)
Q2: There are three investment projects available to Bajor:
Project A costs $12 million to invest today, and then provides cash inflow from assets of $2.50 million per year for the next 7 years.
Project B costs $18 million to invest today, and then provides cash inflow from assets of $3.30 million per year for the next 8 years.
Project C costs $30 million to invest today, and then provides cash inflow from assets of $4.25 million per year for the next 10 years.
If Projects A, B & C are mutually exclusive, which project(s) should Bajor accept? (You must apply the three major investment evaluation rules NPV, IRR and Payback)
Q3: If Projects A, B & C are independent, which project(s) should Bajor accept? (You must apply the three major investment evaluation rules NPV, IRR and Payback)
We will first use the bond data to arrive at the cost of debt: coupon = 6.250%; Term = 9 years; current price = 106.61 and maturity value = 100. The cost of debt - lets denote by r - will be the discount rate which will equate the future bond cash flows to current price. Note since there is semi annual coupon payments, we wil use discount 18 semi annual periods and the r will be semi annual which we will annualise later.
106.61 = 3.125/(1+r) + 3.125/(1+r)2 + .... + (100+3.125)/(1+r)18
We can solve this using Yeild function in excel - we will use current date as settlement and maturity 9 years from today. Yield (date(2018,7,24), date(2027, 7, 23), 6.250%, 106.61, 100, 2) = 5.32%
This the pretax cost of debt and the post tax cost of debt = 5.32% (1-25.7%) = 3.95%
Cost of Equity using CAPM will be = risk free rate + Beta * (market return - risk free return) = 3.8% + 0.71 * (12.3% - 3.8%) = 9.84%
Dividend discount model: Stock price = dividend / (r - g) where r is the cost of equity and g is the dividend growth rate. Plugging in the values and solving : 14 = 0.92 / (r-4.5%); solving for r, we get r = 11.07%
Average cost of equity : Average (11.07%, 9.84%) = 10.45%
Weight of debt in capital structure (Wd) : 14.44/18.23 = 79.21%
Weight of equity in capital structure (We) : (1-79.21%) = 20.79%
WACC = 79.21% * 3.95% + 20.79% * 10.45% = 5.30%
Part B. Project A: Initial investment = 12 million and periodic cash flow = 2.50million for 7 years.
NPV (using excel formula) : NPV (5.30%, -12, 2.5, 2.5, 2.5, 2.5, 2.5, 2.5, 2.5) = 2.19 million
IRR : IRR(-12, 2.5, 2.5, 2.5, 2.5, 2.5, 2.5, 2.5) = 10.43%
Payback period = 12/2.5 = 4.8 years
Project B: Initial investment = 18 million and periodic cash flow = 3.3 million for 8 years.
NPV (using excel formula) : NPV (5.30%, -18, 3.3, 3.3, 3.3, 3.3, 3.3, 3.3, 3.3, 3.3) = 2.92 million
IRR : IRR(-18, 3.3, 3.3, 3.3, 3.3, 3.3, 3.3, 3.3, 3.3) = 9.39%
Payback period = 18/3.3 = 5.45 years
Project C: Initial investment = 30 million and periodic cash flow = 4.25 million for 10 years.
NPV (using excel formula) : NPV (5.30%, -30, 4.25, 4.25, 4.25, 4.25, 4.25, 4.25, 4.25, 4.25. 4.25, 4.25) = 2.23 million
IRR : IRR(-30, 4.25, 4.25, 4.25, 4.25, 4.25, 4.25, 4.25, 4.25. 4.25, 4.25) = 6.89%
Payback period = 30/4.25 = 7.06 years
Though all three projects have positive NPV, the project C payback is more than 6years hence it will not meet company's guidelines. The other 2 projects can be accepted since the NPV is positive and IRR is more than WACC and payback is within in 6 years