HOW WOULD I USE EXCEL TO CALCULATE THE PRICE OF ZERO COUPON BOND # OF ZEROS NEEDED AND REPAYMENT OF COUPON BONDS?

Financing East Coast Yachts Expansion Plans with a Bond Issue Input area: Years to maturity Required return Amount needed Face value Coupon rate Tax rate Year bond is called Spread above Treasury Treasury rate at call Treasury rate at call Nores: 2) Price of coupon bond \# of coupon bonds needed Price of zero coupon bond = PV of zero coupon bond \# of zeroes needed 3) Repayment of coupon bonds Repayment of zeroes =(# of zeros needed ) (Face Value ) 4) Year 1 interest payments: Pretax coupon payment =( Coupon rate ) *(Face value ) * (# of cou Aftertax coupon payment Value of zero in one year \begin{tabular}{|lll|} \hline$ & - & Cash inflow \\ \hline & & \\ $ & - & \\ \hline$ & - & Cash inflow \\ \hline \end{tabular} = Pretax coupon payment * (Tax Shield) = PV of zero coupon bond in one year Zero coupon growth = Increase in value of zeros 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. 5) Make whole price at 5.6% Treasury rate Calculate PV of remaining interest and p Make whole price at 9.10 Treasury rate Calculate PV of remaining interest and p Financing East Coast Yachts Expansion Plans with a Bond Issue Input area: Years to maturity Required return Amount needed Face value Coupon rate Tax rate Year bond is called Spread above Treasury Treasury rate at call Treasury rate at call Nores: 2) Price of coupon bond \# of coupon bonds needed Price of zero coupon bond = PV of zero coupon bond \# of zeroes needed 3) Repayment of coupon bonds Repayment of zeroes =(# of zeros needed ) (Face Value ) 4) Year 1 interest payments: Pretax coupon payment =( Coupon rate ) *(Face value ) * (# of cou Aftertax coupon payment Value of zero in one year \begin{tabular}{|lll|} \hline$ & - & Cash inflow \\ \hline & & \\ $ & - & \\ \hline$ & - & Cash inflow \\ \hline \end{tabular} = Pretax coupon payment * (Tax Shield) = PV of zero coupon bond in one year Zero coupon growth = Increase in value of zeros 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. 5) Make whole price at 5.6% Treasury rate Calculate PV of remaining interest and p Make whole price at 9.10 Treasury rate Calculate PV of remaining interest and p