Question

Please provide excel functions only

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

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)

Answer 1

Q1]

WACC = (weight of debt * cost of debt) + (weight of equity * cost of equity)

market value of debt =  $14.44 billion

market value of equity = $18.23 billion

total market value = $14.44 billion + $18.23 billion = $32.67 billion

weight of debt = $14.44 billion / $32.67 billion = 0.442

weight of equity = $18.23 billion / $32.67 billion = 0.558

cost of debt = YTM of bond * (1 - tax rate)

YTM is calculated using RATE function in Excel with these inputs :

nper = 9*2 (9 years to maturity with 2 semiannual coupon payments each year)

pmt = 6.250 / 2 (semiannual coupon payment = face value * annual coupon rate / 2. this is a positive figure as it is an inflow to the bondholder)

pv = -106.61 (current bond price = face value * 106.61 / 100. this is a negative figure as it is an outflow to the buyer of the bond)

fv = 100 (face value of the bond receivable on maturity. this is a positive figure as it is an inflow to the bondholder)

the RATE is calculated to be 2.66%. This is the semiannual YTM. To calculate the annual YTM, we multiply by 2. Annual YTM is 5.32%

cost of debt = YTM * (1 - tax rate)

cost of debt = 5.32% * (1 - 25.7%) ==> 3.95%

cost of equity (CAPM) = risk free rate + (beta * market risk premium)

cost of equity (CAPM) = 3.8% + (0.71 * (12.3% - 3.8%) ==> 9.84%

cost of equity (Gordon model) = (next year dividend / current share price) + constant growth rate

cost of equity (Gordon model) = (($0.92 + 4.5%) / $14) + 4.5% = 11.37%

cost of equity = average of two methods = (9.84% + 11.37%) / 2 = 10.60%

WACC = (0.422 * 3.95%) + (0.558 * 10.60%) = 7.66%

Q2]

Project A :

NPV is calculated using NPV function in Excel using WACC of 7.66% as the discount rate. NPV is $1,168,660

IRR is calculated using IRR function in Excel. IRR is 10.43%

Payback period is the time taken for the cumulative cash flows to equal zero.

Payback period = 4 + ($2,000,000 / $2,500,000) = 4.8 years

Project B :

NPV is calculated using NPV function in Excel using WACC of 7.66% as the discount rate. NPV is $1,211,064

IRR is calculated using IRR function in Excel. IRR is 9.39%

Payback period is the time taken for the cumulative cash flows to equal zero.

Payback period = 5 + ($1,500,000 / $3,300,000) = 5.45 years

Project C :

NPV is calculated using NPV function in Excel using WACC of 7.66% as the discount rate. NPV is -$1,039,590

IRR is calculated using IRR function in Excel. IRR is 6.89%

Payback period is the time taken for the cumulative cash flows to equal zero.

Payback period = 7 + ($250,000 / $4,250,000) = 7.06 years

If the projects are mutually exclusive:

As per NPV, Project B should be accepted as it has the highest NPV.

As per IRR, Project A should be accepted as it has the highest IRR

As per payback period, Project A should be accepted as it has the lowest payback period

Although Project A has a higher IRR and lower payback period than project B, Project B should be accepted as per NPV rule because it creates the most value (net present value)

Q3]

If the projects are independent, Project A and Project B should be accepted as they have positive NPV and IRR > WACC