spreadsheet is attached. need help with a and b.

h

disregard the the TIPS spreadsheet use other.

4. On January 16, 2010, Utility Corp sold a 20-year (maturity date 1/15/2030) 6% non-callable bond to the public at a premium of 5%. The flotation cost related to the bond issue at that time was 6% (of par). The bond pays interest semi-annually on January 15 and July 15 of each year. a) Compute the effective annual yield to the bond investors and effective annual cost to the company at the time of bond issue (on 1/16/2010). Use the Rate, IRR and Yield functions. b) The Utility Corp's bond is quoted in the market today, 11/4/2021, at 103 % of par). Please note the quoted price does not include the accrued interest. If you plan to invest in the bond today and hold it until maturity, what would be your effective yield to maturity (YTM) on the Utility Corp bond? Use the XIRR and Yield functions to compute the YTM. 3 4 1/15/2024 1/15/2025 5 1/15/2026 1020 25.75 25.75 1010 25.5 25 50 950 25.25 975.25 B 1 TIPS vs Zero Coupon Bond 2 TIPS coupon rate (annual) 3 Time (year) 0 1 2 4 Dates (any starting date would work) 1/15/2021 1/15/2022 1/15/2023 5 Expected inflation 6 Notional principal (adjusted for inflation) 980 1030 7 Coupons 25 24.5 8 (a) Total periodic cashflows from TIPS -1000.00 25.00 24.50 9 10 a) EAIR from the TIPS: 11 IRR 1.55% 12 13 XIRR 1.55% 14 15 (6) Required purchase price for a 5 year zero coupon bond: 16 Time (year) 0 1 2 17 Dates 1/15/2021 1/15/2022 1/15/2023 18 Cash flows -1000 2 2 19 Rate 1.55% 20 Par value 1000 21 Coupon payment $15.50 22 # of periods 23 Required zero coupon bond price 24 3 1/15/2024 2 4 1/15/2025 ? 5 1/15/2026 100012 26 5 0 7 8 in the disse Par value/bond 1000 Issue date 1/16/10 Issue premium/discount 5% Purchase price/bond (investor) 1050 Flotation cost 6.0% Netissue price bond (Ulty Corp) Chunon frecuen 2 Corinnat 6% Maturity period (years) 2 Maturity date 1015700 thin the landshantada Quoted clean price (%) Quoted clean price (S) Purchase date Prior coupon date Next coupon date Days since last coupon Davs between coupons Semi-annual coupon $ Accrued interest sisalda Timeline for in CE to the investor CE to the issue Timeline for Data Conventar & Sale $8898819898&&&&$3$*:48 NENONE Date 13166 7/15110 1/15/11 7/15/11 1/15/12 7/15/12 1/15/13 7/15/ 1/15/14 7/15/14 1/15/15 7/15/15 1/15/16 7/15/16 1/15/17 7/15/17 1/15/18 7/15/18 1/15/19 7/15/19 1/15/20 7/15/20 1/15/21 7/15/21 1/15/22 7/15/22 1/15/23 7/15/23 1/15/24 7/15/24 1/15/25 7/15/25 1/15/25 7/15/26 1/15/27 7/15/27 1/15/28 7/15/28 1/15/29 7/15/29 aldninger Contin Yield Rate EAIR (annualized XIRR EAIR (annualized) IRR EAIR (annualized Yield EAIR (annualized) 65 Yield FAIR and FR 4. On January 16, 2010, Utility Corp sold a 20-year (maturity date 1/15/2030) 6% non-callable bond to the public at a premium of 5%. The flotation cost related to the bond issue at that time was 6% (of par). The bond pays interest semi-annually on January 15 and July 15 of each year. a) Compute the effective annual yield to the bond investors and effective annual cost to the company at the time of bond issue (on 1/16/2010). Use the Rate, IRR and Yield functions. b) The Utility Corp's bond is quoted in the market today, 11/4/2021, at 103 % of par). Please note the quoted price does not include the accrued interest. If you plan to invest in the bond today and hold it until maturity, what would be your effective yield to maturity (YTM) on the Utility Corp bond? Use the XIRR and Yield functions to compute the YTM. 3 4 1/15/2024 1/15/2025 5 1/15/2026 1020 25.75 25.75 1010 25.5 25 50 950 25.25 975.25 B 1 TIPS vs Zero Coupon Bond 2 TIPS coupon rate (annual) 3 Time (year) 0 1 2 4 Dates (any starting date would work) 1/15/2021 1/15/2022 1/15/2023 5 Expected inflation 6 Notional principal (adjusted for inflation) 980 1030 7 Coupons 25 24.5 8 (a) Total periodic cashflows from TIPS -1000.00 25.00 24.50 9 10 a) EAIR from the TIPS: 11 IRR 1.55% 12 13 XIRR 1.55% 14 15 (6) Required purchase price for a 5 year zero coupon bond: 16 Time (year) 0 1 2 17 Dates 1/15/2021 1/15/2022 1/15/2023 18 Cash flows -1000 2 2 19 Rate 1.55% 20 Par value 1000 21 Coupon payment $15.50 22 # of periods 23 Required zero coupon bond price 24 3 1/15/2024 2 4 1/15/2025 ? 5 1/15/2026 100012 26 5 0 7 8 in the disse Par value/bond 1000 Issue date 1/16/10 Issue premium/discount 5% Purchase price/bond (investor) 1050 Flotation cost 6.0% Netissue price bond (Ulty Corp) Chunon frecuen 2 Corinnat 6% Maturity period (years) 2 Maturity date 1015700 thin the landshantada Quoted clean price (%) Quoted clean price (S) Purchase date Prior coupon date Next coupon date Days since last coupon Davs between coupons Semi-annual coupon $ Accrued interest sisalda Timeline for in CE to the investor CE to the issue Timeline for Data Conventar & Sale $8898819898&&&&$3$*:48 NENONE Date 13166 7/15110 1/15/11 7/15/11 1/15/12 7/15/12 1/15/13 7/15/ 1/15/14 7/15/14 1/15/15 7/15/15 1/15/16 7/15/16 1/15/17 7/15/17 1/15/18 7/15/18 1/15/19 7/15/19 1/15/20 7/15/20 1/15/21 7/15/21 1/15/22 7/15/22 1/15/23 7/15/23 1/15/24 7/15/24 1/15/25 7/15/25 1/15/25 7/15/26 1/15/27 7/15/27 1/15/28 7/15/28 1/15/29 7/15/29 aldninger Contin Yield Rate EAIR (annualized XIRR EAIR (annualized) IRR EAIR (annualized Yield EAIR (annualized) 65 Yield FAIR and FR