Excel formulas and functions should be used for this project. Make the spreadsheet as concise and clear as possible. Formulas should have cells as arguments, not numbers. Points will be deducted for sloppy work Consider the following 2 bonds that pay coupons on a semi-annual basis. Both bonds have a face/par value of $1,000 Bond A is callable and matures 5/15/2024 with a coupon rate of 5.25%. /15/2020 with a call price of 103.00 (percent of par) Call Date #1 is 11 Call Date #2 is 5/15/2022 with a call price of 102.00 (percent of par) Settlement date is 11/15/2017 and the quoted price is 101-15 (percent of par with fraction in 32nds). Accrued interest 0 since settlement is on a coupon date. . . * Bond B is not callable and matures 8/15/2025 with a coupon rate of 3.75% Settlement date is 10/15/2017 and the CLEAN quoted price is 99-26 (percent of par with fraction in 32nds) * Questions for Bond A: 1) Calculate the Bond Equivalent Yield to maturity. Use the YIELD function in excel. Verify in excel that you calculated the right yield by adding up the discounted individual cash flows with the result from the yield function. 2) Calculate the Yield to Call for call date #1 and #2. Use the YIELD function in excel, what is the Yield to Worst for this bond? Questions for Bond B: 1) Calculate the Bond Equivalent Yield to maturity. Use the YIELD function in excel. Note that the function uses the clean price as an input. 2) Verify in excel that you calculated the right yield by discounting the cash flows with the yield from (1). To do this you will need to calculate accrued interest and the dirty price, since the settlement date is not on the coupon date. Assume a 30/360 day count convention as in the example spreadsheet posted on canvas. Excel formulas and functions should be used for this project. Make the spreadsheet as concise and clear as possible. Formulas should have cells as arguments, not numbers. Points will be deducted for sloppy work Consider the following 2 bonds that pay coupons on a semi-annual basis. Both bonds have a face/par value of $1,000 Bond A is callable and matures 5/15/2024 with a coupon rate of 5.25%. /15/2020 with a call price of 103.00 (percent of par) Call Date #1 is 11 Call Date #2 is 5/15/2022 with a call price of 102.00 (percent of par) Settlement date is 11/15/2017 and the quoted price is 101-15 (percent of par with fraction in 32nds). Accrued interest 0 since settlement is on a coupon date. . . * Bond B is not callable and matures 8/15/2025 with a coupon rate of 3.75% Settlement date is 10/15/2017 and the CLEAN quoted price is 99-26 (percent of par with fraction in 32nds) * Questions for Bond A: 1) Calculate the Bond Equivalent Yield to maturity. Use the YIELD function in excel. Verify in excel that you calculated the right yield by adding up the discounted individual cash flows with the result from the yield function. 2) Calculate the Yield to Call for call date #1 and #2. Use the YIELD function in excel, what is the Yield to Worst for this bond? Questions for Bond B: 1) Calculate the Bond Equivalent Yield to maturity. Use the YIELD function in excel. Note that the function uses the clean price as an input. 2) Verify in excel that you calculated the right yield by discounting the cash flows with the yield from (1). To do this you will need to calculate accrued interest and the dirty price, since the settlement date is not on the coupon date. Assume a 30/360 day count convention as in the example spreadsheet posted on canvas