***I need help to finish this spreadsheet, please.***

1. In January 1, 2016, Argo issued a 10-year, $700M bond paying 5.55% annually in two equal coupons each June and December. It is now June 2020 and Argo just paid the June coupon on its existing bond. Rates have come down, so it is thinking of buying back the bond and issuing a 5-year, $300M bond. This bond matures in June 2025 and will pay 3.45% per year in equal coupons each June and December. a. What is the price that Argo must pay the current bond holders to buy back the bond? (Hint - the present value of the coupon payments and the final face value) b. What are the cash flows associated with the new bond? c. What are the cash flow differentials to Argo? In other words, what are the net cash flows in or out for Argo each June and December when comparing both bonds? d. What is the present value of these cash flow differentials? A B D E F G H J K L M N O Q R S T U V W 1 in MS 2016 2017 2018 2019 2020 2021 2022 2023 2024 2025 2 Amt. Coupon Start June Dec. June Dec. June Dec. June Dec. June Dec. June Dec. June Dec. June Dec. June Dec. June Dec. 3 Existing Bonds 700.0 5.55% 700.000 (19.425) (19.425) (19.425) (19.425) (19.425) (19.425) (19.425) (19.425) (19.425) (19.425) (19.425) (19.425) (19.425) (19.425) (19.425) (19.425) (19.425) (19.425) (19.425) (719.425) 4 New Bonds 300.0 3.45% 300.000 (5.175) (5.175) (5.175) (5.175) (5.175) (5.175) (5.175) (5.175) (5.175) (305.175) 5 Purch. Price of Exist.Bonds (773.072) 6 Incremental Cash Flows (473.072) 7 NPV of Incremental CF in June 2020: 8 9 Answer a -d here. 10 12 13 1. In January 1, 2016, Argo issued a 10-year, $700M bond paying 5.55% annually in two equal coupons each June and December. It is now June 2020 and Argo just paid the June coupon on its existing bond. Rates have come down, so it is thinking of buying back the bond and issuing a 5-year, $300M bond. This bond matures in June 2025 and will pay 3.45% per year in equal coupons each June and December. a. What is the price that Argo must pay the current bond holders to buy back the bond? (Hint - the present value of the coupon payments and the final face value) b. What are the cash flows associated with the new bond? c. What are the cash flow differentials to Argo? In other words, what are the net cash flows in or out for Argo each June and December when comparing both bonds? d. What is the present value of these cash flow differentials? A B D E F G H J K L M N O Q R S T U V W 1 in MS 2016 2017 2018 2019 2020 2021 2022 2023 2024 2025 2 Amt. Coupon Start June Dec. June Dec. June Dec. June Dec. June Dec. June Dec. June Dec. June Dec. June Dec. June Dec. 3 Existing Bonds 700.0 5.55% 700.000 (19.425) (19.425) (19.425) (19.425) (19.425) (19.425) (19.425) (19.425) (19.425) (19.425) (19.425) (19.425) (19.425) (19.425) (19.425) (19.425) (19.425) (19.425) (19.425) (719.425) 4 New Bonds 300.0 3.45% 300.000 (5.175) (5.175) (5.175) (5.175) (5.175) (5.175) (5.175) (5.175) (5.175) (305.175) 5 Purch. Price of Exist.Bonds (773.072) 6 Incremental Cash Flows (473.072) 7 NPV of Incremental CF in June 2020: 8 9 Answer a -d here. 10 12 13