Question

Please provide specific Excel functions =NPV(…), =IRR(…), =AVERAGE(…), =YIELD(…) whenever applicable.

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.)  

Answer 1

cost of debt: coupon = 6.250%; Term = 9 years; current price = 106.61 and maturity value = 100.

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 annualize later.

106.61 = 3.125/(1+r) + 3.125/(1+r)² + .... + (100+3.125)/(1+r)¹⁸

We can solve this using Yield 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 (W_d) : 14.44/18.23 = 79.21%

Weight of equity in capital structure (W_e) : (1-79.21%) = 20.79%

WACC = 79.21% * 3.95% + 20.79% * 10.45% = 5.30%