Question

In: Finance

(For this part, you MUST present sufficient solution steps, and MUST apply specific Excel functions =NPV(…),...

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

Solutions

Expert Solution

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


Related Solutions

(For this part, you MUST present sufficient solution steps, and MUST apply specific Excel functions =NPV(…),...
(For this part, you MUST present sufficient solution steps, and MUST apply specific Excel functions =NPV(…), =IRR(…), =AVERAGE(…), =YIELD(…) Case Two (22 pts) 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....
(For this part, you MUST present sufficient solution steps, and MUST apply specific Excel functions =NPV(…),...
(For this part, you MUST present sufficient solution steps, and MUST apply specific Excel functions =NPV(…), =IRR(…), =AVERAGE(…), =YIELD(…) whenever applicable..) Ferengi, Inc. is subject to an applicable corporate tax rate of 21 percent, and the weighted average cost of capital (WACC) of 12.5 percent. There is no specific time constraint on investment project payback requirements. Q1: Ferengi is currently contemplating two capital investment plans. Plan A: the upgrade of an information system with an installed cost of $2,400,000. The...
(For this part, you MUST present sufficient solution steps, and MUST apply specific Excel functions =PV(…),...
(For this part, you MUST present sufficient solution steps, and MUST apply specific Excel functions =PV(…), =FV(…), =PMT(…), =NPER(…), =RATE(…), =PRICE(…) or =YIELD(…) whenever applicable. Please show me the EXCEL functions that was used to help me better understand was equals what. Using Excel finance formulas You apply for a 20-year, fixed-rate (APR 6.48%) monthly-payment-required mortgage loan for a house selling for $150,000 today. Your bank requires 22% initial down payment of house value (to be paid in cash immediately),...
(For this part, you MUST present sufficient solution steps, and MUST apply specific Excel functions =PV(…),...
(For this part, you MUST present sufficient solution steps, and MUST apply specific Excel functions =PV(…), =FV(…), =PMT(…), =NPER(…), =RATE(…), =PRICE(…) or =YIELD(…) whenever applicable. Please show me the EXCEL functions that was used to help me better understand was equals what. Using Excel finance formulas Case 3: We find the data for a municipal bond issued by the Illinois state government. The bond’s “last trade date” (i.e., settlement date) is June 05, 2019. The bond’s “maturity date” is March...
Please provide specific Excel functions =NPV(…), =IRR(…), =AVERAGE(…), =YIELD(…) whenever applicable. Given the following information for...
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...
Please provide specific Excel functions =NPV(…), =IRR(…), =AVERAGE(…), =YIELD(…) etc...... Given the following information for Bajor...
Please provide specific Excel functions =NPV(…), =IRR(…), =AVERAGE(…), =YIELD(…) etc...... 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...
use Excel Solver to answer the following question.You must provide sufficient evidence that you did the...
use Excel Solver to answer the following question.You must provide sufficient evidence that you did the work on your own. For example, what equations did you use in your model? How was the excel spread sheet laid out? A student at a local university has just completed a decision modeling course. On her assignments, she has earned a 86 on the mid-term, a 94 on the final, a 93 on problem sets, and 85 for participation. She has a unique...
In part 2 you will be creating multiple functions to calculate the present value. You may...
In part 2 you will be creating multiple functions to calculate the present value. You may be asking what a "present value" is. Suppose you want to deposit a certain amount of money into a savings account and then leave it alone to draw interest for some amount of time, say 12 years. At the end of the 12 years you want to have $15,000 in the account. The present value is the amount of money you would have to...
Create an Excel spreadsheet in which you use capital budgeting tools including net present value (NPV),...
Create an Excel spreadsheet in which you use capital budgeting tools including net present value (NPV), internal rate of return (IRR), payback period, and profitability index (PI) to determine the quality of 3 proposed investment projects, as well as an analysis of your computations and recommends the project that will bring the most value to the company. The analysis of the capital projects will need to be correctly computed and the resulting decisions rational. Scenario You work as a finance...
As part of the settlement for a class action lawsuit, Hoxworth Corporation must provide sufficient cash...
As part of the settlement for a class action lawsuit, Hoxworth Corporation must provide sufficient cash to make the following annual payments (in thousands of dollars): Year 1 2 3 4 5 6 Payment 195 220 270 320 350 470 The annual payments must be made at the beginning of each year. The judge will approve an amount that, along with earnings on its investment, will cover the annual payments. Investment of the funds will be limited to savings (at...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT