can you show me how to do the Yield to call in Excel and the Yield to worst...

Calibri 12 AA p Wrap Teal Delete BIU BA Merge & Center $ % 968-% Formato Conditional Formatas Cell Formatting Table Styles Styles Sort & Find Filter Sale porting Number 5 Alimen For C D 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 4 maturity but never higher. Of course, with a traditional callable bond that has a call premium, the call premium can decline over time. 5 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 lett. 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 6 today, the call premium will be zero. Given the following information, what is the yield to worst for this bond? 7 B 10 19 Current date: Maturity date Price percent of par Coupon rate Par value percent of par Coupons per year Call date call premium 5/1/2016 5/1/2016 104.5 10.00% 100 2 12 11 14 15 Home Percentage w 2 Wrap Text Calibri - 12 A BIU A ilil $ -% 948 - 5 Merge & Center IH Conditional Format as Formatting Table sa Styles Number Font Alignment d H E B D Master it! Solution The yield to maturity and yield to call for each call date are: interest/ coupon Face Value of Bon 5 100 Current date: Maturity date: Price (percent of par): Coupon rate: Par value (percent of par): Coupons per year: 5/1/2016 5/1/2036 104.5 10.00% 100 2 10 11 12 13 Yield to maturity: 9.49% 14 15 Call date Yield to call 16 17 18 19 20 Call premium 5/1/2019 $ 100.00 5/1/2020 $ 90.00 5/1/2021 $ 80.00 5/1/2022 $ 70.00 5/1/2023 $ 60.00 5/1/2024 $ 50.00 5/1/2025 $ 40.00 5/1/2026 $ 30.00 5/1/2027 $ 20.00 5/1/2028 $ 10.00 21 23 24 25 26 27 28 Yield to worst