PLEASE SHOW HOW TO COMPLETE IN EXCEL.

After Dan's EFN analysis for East Coast Yachts, Larissa has decided to expand the company's operation. She asked Dan to enlist an underwriter to help sell $50 million in new 20 year bonds to finance new construction. Dan has entered into discussion with Kendahl Shoemaker, an underwriter from the firm Crow & Mallard about which bond features East Coast Yachts should consider and also what coupon rate the issue will likely have. Although Dan is aware of bond features, he is uncertain as to the costs and benefits of some of them, so he isn't clear on how much each feature would affect the coupon rate of the bond.

Dan is also considering whether to issue coupon-bearing bonds or zero coupon bonds. The YTM on either bond issue will be 7.5 percent. The coupon bond would have a 6.5 percent coupon rate. The company's tax rate is 21 percent.

2. How many of the coupon bonds must east coast yachts issue to raise the $50 million? How man of the zeroes must it issue?

3. in 20 years, what will be the principal repayment due if East Coast Yachts issues the coupon bonds? What if it issues the zeroes?

4. What are the company's considerations in issuing a coupon bond compared to a zero coupon bond

I've attached a picture of the excel template, please help with filling out based on the information above. Thank you!

Chapter 8 Financing East Coast Yachts Expansion Plans with a Bond Issue hutang Years to maturity Required return Amount needed Face value 20 7.50% $ 50,000,000 $ 1,000 Coupon rate Taurate 7.50% 21% Year bond is called Spread above Treasury Treasury rate at call Treasury rate at call 7 0.40% 4.80% 8.20% U279 2] Price of coupon bond # of coupon bonds needed Price of zero coupon bond # of zeroes needed 3] Repayment of coupon bonds Repayment of zeroes 4] Yaar Test pameras Pretax coupon payment Aftertax coupon payment Value of zero in one year Zero coupon growth Zero coupon bond During the life of a bond, the zero generates cash inflows to the firm in the form of the interest tax shield of debt. Chapter 8 Financing East Coast Yachts Expansion Plans with a Bond Issue hutang Years to maturity Required return Amount needed Face value 20 7.50% $ 50,000,000 $ 1,000 Coupon rate Taurate 7.50% 21% Year bond is called Spread above Treasury Treasury rate at call Treasury rate at call 7 0.40% 4.80% 8.20% U279 2] Price of coupon bond # of coupon bonds needed Price of zero coupon bond # of zeroes needed 3] Repayment of coupon bonds Repayment of zeroes 4] Yaar Test pameras Pretax coupon payment Aftertax coupon payment Value of zero in one year Zero coupon growth Zero coupon bond During the life of a bond, the zero generates cash inflows to the firm in the form of the interest tax shield of debt