EXCEL ANSWERS ONLY. Please show work in excel

Table 1 Partial Long-Term Debt Listing for TECO Energy Face Amount Maturity Year 1997 Years to Maturity 5 $ 48,000,000 $ 32,000,000 Coupon Rate 4 %% 84% 12%% 2007 15 $ 100,000,000 2017 25 Note: The terms stated here are modified slightly from the actual terms to simplify the case. 1. To begin, assume that it is now January 1, 1993, and that each bond in Table 1 matures on December 31 of the year listed. Further, assume that each bond has a $1,000 par value, each had a 30-year maturity when it was issued, and the bonds currently have a 10 percent required nominal rate of return. a. Why do the bonds' coupon rates vary so widely? b. What would be the value of each bond if they had annual coupon payments? c. TECO's bonds, like virtually all bonds, actually pay interest semiannually. What is each bond's value under these conditions? Are the bonds currently selling at a discount or at a premium? d. What is the effective annual rate of return implied by the values obtained in Partc? e. Would you expect a semiannual payment bond to sell at a higher or lower price than an otherwise equivalent annual payment bond? Now look at the 5-year bond in Parts b and c. Are the prices shown consistent with your expectations? Explain. Table 1 Partial Long-Term Debt Listing for TECO Energy Face Amount Maturity Year 1997 Years to Maturity 5 $ 48,000,000 $ 32,000,000 Coupon Rate 4 %% 84% 12%% 2007 15 $ 100,000,000 2017 25 Note: The terms stated here are modified slightly from the actual terms to simplify the case. 1. To begin, assume that it is now January 1, 1993, and that each bond in Table 1 matures on December 31 of the year listed. Further, assume that each bond has a $1,000 par value, each had a 30-year maturity when it was issued, and the bonds currently have a 10 percent required nominal rate of return. a. Why do the bonds' coupon rates vary so widely? b. What would be the value of each bond if they had annual coupon payments? c. TECO's bonds, like virtually all bonds, actually pay interest semiannually. What is each bond's value under these conditions? Are the bonds currently selling at a discount or at a premium? d. What is the effective annual rate of return implied by the values obtained in Partc? e. Would you expect a semiannual payment bond to sell at a higher or lower price than an otherwise equivalent annual payment bond? Now look at the 5-year bond in Parts b and c. Are the prices shown consistent with your expectations? Explain