Please provide specific Excel functions =NPV(…), =IRR(…), =AVERAGE(…), =YIELD(…) etc...... Given the following information for Bajor Co.: Debt:...

70.2K

Verified Solution

Question

Finance

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 bondswith 6.250 percent coupon rate (semiannual coupon payments), 9years time-to-maturity, and current price of 106.61 percent of itspar value (i.e., price = 106.61 relative to full amount redemptionpar of 100).
Preferred stock: Bajor has not issued any preferredstocks.
Common stock (equity):
Bajor’s equity capital consists of common stocks with the mostrecent annual dividend of $0.92 per share, and a current stockprice of $14 per share.
According to online data sources, Bajor’s long-term dividendgrowth (for next 5-Year average, per annum) g = 4.5% per year.
The “risk-free” Treasury bill return is 3.8%; the marketexpected return for the stock market on average is 12.3%; andBajor’s systematic risk (Beta) is 0.71.
Taxes: The applicable federal-plus-state corporate taxrate for Bajor is 25.7 percent.
Capital weight: Bajor’s “Market Cap” amounts to $18.23billion, and “Total Debt” amounts to $14.44 billion. You can usesuch data to estimate the capital weights for equity and debt,respectively (We and Wd).
Time constraint: For any investment projects, Bajor arerequired by her investors to recover its initial cost within nomore than 6 years.
Q1: What is Bajor’s pretax cost of debt Rd, cost ofequity Re, and WACC, respectively? (Hint: For the best estimate ofcost of equity Re, you must apply both CAPM and Dividend GrowthModel and then average the two estimates.)

Answer & Explanation Solved by verified expert

4.2 Ratings (736 Votes)

1Using financial calculatorInput FV 100PMT 6251002 3125N 92 18PV 10661Solve for IY as 266Pre tax cost of debt See Answer

Get Answers to Unlimited Questions

Join us to gain access to millions of questions and expert answers. Enjoy exclusive benefits tailored just for you!

Membership Benefits:

Unlimited Question Access with detailed Answers
Zin AI - 3 Million Words
10 Dall-E 3 Images
20 Plot Generations
Conversation with Dialogue Memory
No Ads, Ever!
Access to Our Best AI Platform: Flex AI - Your personal assistant for all your inquiries!

Become a Member

Transcribed Image Text

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 bondswith 6.250 percent coupon rate (semiannual coupon payments), 9years time-to-maturity, and current price of 106.61 percent of itspar value (i.e., price = 106.61 relative to full amount redemptionpar of 100).Preferred stock: Bajor has not issued any preferredstocks.Common stock (equity):Bajor’s equity capital consists of common stocks with the mostrecent annual dividend of $0.92 per share, and a current stockprice of $14 per share.According to online data sources, Bajor’s long-term dividendgrowth (for next 5-Year average, per annum) g = 4.5% per year.The “risk-free” Treasury bill return is 3.8%; the marketexpected return for the stock market on average is 12.3%; andBajor’s systematic risk (Beta) is 0.71.Taxes: The applicable federal-plus-state corporate taxrate for Bajor is 25.7 percent.Capital weight: Bajor’s “Market Cap” amounts to $18.23billion, and “Total Debt” amounts to $14.44 billion. You can usesuch data to estimate the capital weights for equity and debt,respectively (We and Wd).Time constraint: For any investment projects, Bajor arerequired by her investors to recover its initial cost within nomore than 6 years.Q1: What is Bajor’s pretax cost of debt Rd, cost ofequity Re, and WACC, respectively? (Hint: For the best estimate ofcost of equity Re, you must apply both CAPM and Dividend GrowthModel and then average the two estimates.)