Chapter 6 Master It! Excel Homework

Please show excel functions in the solution, thank you very much!

Chapter 6 - Master it! In an earlier worksheet we discussed the difference between yield to maturity and yield to call. There is another yield that is commonly quoted, the yield to worst. The yield to worst is the lowest potential yield that can be received on a bond without the issuer actually defaulting. Yield to worst is calculated on all possible call dates. It is assumed that prepayment occurs if the bond has a call provision. The yield to worst will be the lowest of yield to maturity or yield to call. The yield to worst may be the same as yield to maturity but never higher. Of course, with a traditional callable bond that has a call premium, the call premium can decline over time. A company has the following bond outstanding. The bond is callable every year on May 1st, the anniversary date of the bond. The bond has a deferred call with three years left. The call premium on the first call date is one year's interest. The call premium will decline by 10 percent of the original call premium for 10 years. Eleven years from today, the call premium will be zero. Given the following information, what is the yield to worst for this bond? 5/1/2016 5/1/2036 104.5 10.00% 100 Current date: Maturity date: Price (percent of par): Coupon rate: Par value (percent of par): Coupons per year: Call date Call premium 5/1/2019 5/1/2020 5/1/2021 5/1/2022 5/1/2023 5/1/2024 5/1/2025 5/1/2026 5/1/2027 5/1/2028 Master it! Solution The yield to maturity and yield to call for each call date are: Current date: Maturity date: Price (percent of par): Coupon rate: Par value (percent of par): Coupons per year: Yield to maturity: Yield to call Call date Call premium 5/1/2019 5/1/2020 5/1/2021 5/1/2022 5/1/2023 5/1/2024 5/1/2025 5/1/2026 5/1/2027 5/1/2028 Yield to worst