Question

In: Finance

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

Solutions

Expert Solution

1:

Using financial calculator

Input: FV= 100

PMT = 6.25%*100/2 = 3.125

N = 9*2 = 18

PV = -106.61

Solve for I/Y as 2.66

Pre tax cost of debt = 2.66%*2 = 5.32%

After tax cost of debt = 5.32%*(1-25.7%)

= 3.95%

2: cost of equity as per CAPM= risk-free rate + Beta*(market return-risk-free rate)

= 3.8%+0.71*(12.3%-3.8%)

=0.09835

Cost of equity as per dividend discount model= expected dividend/current price+ growth rate

= 0.92*104.5%/ 14 + 4.5%

=0.113671

Average cost of equity= (0.09835+ 0.113671)/2 = 10.6%

3: weighted average cost of capital= after-tax cost of debt* market value of debt/total value + average cost of equity* market cap/total value

=3.95%*14.44/(14.44+18.23) + 10.6%*18.23/(14.44+18.23)

=7.66%

WORKINGS


Related Solutions

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...
Show Excel functions =NPV and =IRR 9. Calculating Project OCF [LO1] Quad Enerpriese is considering a...
Show Excel functions =NPV and =IRR 9. Calculating Project OCF [LO1] Quad Enerpriese is considering a new three-year expansion project that rerrquires an initial fixed asset investment of $2.9 mllion. The fized asset will be depreciated striaght-line to zero over its three-year tax life, after which time time it will be worthless. The project is estrimate to generate $2,190,000 in annual sales with costs of $815,000. If the tax rate is 35 percent, what is the OCF for this project?...
discussing advanced functions in Excel (finance functions, If, nested If, Vlookup, Hlookup, etc.). Please research additional...
discussing advanced functions in Excel (finance functions, If, nested If, Vlookup, Hlookup, etc.). Please research additional informative sources in order to increase your understanding of these, or any other, advanced functions. Subsequently, choose an advanced function and write down to educate regarding the meaning and application of it.
1. Compute the NPV of a project with the following information. The IRR is 9%. The...
1. Compute the NPV of a project with the following information. The IRR is 9%. The project life is 3 years. The initial cost is $19,000. In years 1 and 2 the cash inflows are $4,500 and $5,500, respectively. Cash flow in year 3 is missing. WACC is 12%.
Utilizing the excel examples in Blackboard create and excel file that answers NPV, IRR, Payback for...
Utilizing the excel examples in Blackboard create and excel file that answers NPV, IRR, Payback for the following situation. Initial investment $1000, annual cash flow of $300 Program life for 8 years Hurdle rate of 5%
(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...
(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...
IN EXCEL< SHOW THE FORMULAS > Determine the Payback period, NPV and IRR for both project...
IN EXCEL< SHOW THE FORMULAS > Determine the Payback period, NPV and IRR for both project A and B (show work). Which Project would you select and why? Be specific. Project A will require an initial investment of $ 200,000 and Project B will require and initial investment of $ 325,000. The cost of capital for both projects is 12%. The cash inflows for the next 5 years are listed below: Project A Project B 0 ($200,000) ($325,000) 1 $50,000...
Can you use Excel for time value of money computations such as NPV or IRR? If...
Can you use Excel for time value of money computations such as NPV or IRR? If so, what are the functions? Please no handwritten answers
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT