1. What is your cost to cash flow match this liability stream?

Excel File Edit View Insert Format Tools Data Window Help Tue 6:08 PM a AutoSave OFF HEG55 HW #5 - Managing Bond Portfolios Q Home Insert Draw Page Layout Formulas Data Review View Tell me Share Q Comments X Calibri (Body) 11 - = ab Wrap Text General TI TT DV IIX V PA AY O v Paste 00 60 B I U a. Av ini Insert E = Delete Format $ % Ideas V Merge & Center Conditional Format Cell Formatting as Table Styles Sort & Filter Find & Select Q53 fx A c E G G H V N s T U V w X Y 2 AA AB AD AE AF AG Al A AK AL AM AN AD Homework #5 - Bond Portfolios Due 10/21 You are managing a bond portfolio for a pension fund that is required to pay pension benefit payments for 5 years starting in December, 2022. You cash flow match the liabilities using the bonds presented in your finance class. Using the bonds in class from Table 2, the cost to cash flow match was $12,304 with an average duration of 3.80. You decide that its worth using bonds with a lower credit rating to get the cost to cash flow match down. Using the bonds in Table 3, replace each bond in Table 2 with one that has a higher YTM but same Maturity. The coupon rate for the replacement bond will vary. Therefore you will need to adjust the quantity purchased by change the par value. You begin with 2026 and move backward like in class. Your goal is 1) cash flow match at a lower cost, 2) maintain a positive new cash balance in the last column of Table 1 for each year (this ensures all benefits are able to be paid each year), and minimize the ending cash balance in cell 024 (this will improve efficiency). *Note it is near impossible to match exactly, therefore a positive cash balance is always maintained in the account. Tecay 2020-10 Previous Reinvent 0.5% Principal Payment $ Dade Coupon 1. Payments $ . Reinvestment of Payments $ Due New Cash Balance $ 49 539 Use this soape how many bonds to purchase in 335 340 Value should be between 0 and 100 for 2022 on $ 49 3 2020-12 2021-12 2022-12 2023-32 2024-32 2025-12 2026-12 1 . 49 539 31 79 79 187 76 1.900 2,300 2.300 2,100 2,200 488 422 307 238 9 2.900 2,675 2.500 2.450 2.300 79 187 76 67 49 539 31 79 182 28/ 76 67 1 To $12.425 Table 2. Replace these bonds with a comparable one from Table 3, update the quantities in the Par Value column like in dies. Only adjust cells in yellow Coupon Value (100) Sofa Coupon Marity Price YTM Duration Payment Cost (10) 1900 Balduinc 3.50% 11/15/22 105 1.05% 1.99 665 $ 1995 2300 Goldman Sachs 5.00% 12/15/23 112 2300 1.12% 2.92 115 $ 2,576 Natural Rural Uslities 3.00% 12/15/24 107 1.26% 3.91 69 $ 2.463 2100 Burlington Northam RR 7.00% 12/15/25 129 147 $ 2,709 2200 JP Morgan 4.13% 12/15/26 116.5 1.32% 5.49 90.75 $ 2.563 Ang Duration 3.78 Costo Cai Row Match s 12,304 Questions 1. What is your costs as few mash this liability an? (Cdl 343) Price YTV 2. Given that it cost lessless to cash few masch, did the excess rem on the cash flow mancing come with any risk? Explain Table 3. Bond Unives, 2 bonds to choose from for each maturity ser Coupon American General Finance Corp 9.00% Fortiames Corp 7.75% Wynn Las Vegas uc 4.25% American Airlines 5.25% Eldorado Gold Corp 9.50% united Airlines Holdings S.COM Occidental Petroleum 6.50% Wendy's national 7.00% Ford Motor Corp, Inc 4.00% Golden Nine 8.75% May 11/15/22 11/15/22 12/15/23 12/15/23 12/15/24 12/15/24 12/15/25 12/15/25 12/15/26 12/15/26 103.50 110.00 99.00 75.00 106.50 89.00 99.00 110.00 98.00 87.50 1.28% 2.74% 4.59% 15.58% % 7.64% 8.17% 6.73% 4.79% 4.97% 11.65% Example 5 + Ready + 63% OCT ABC 20 A X W Excel File Edit View Insert Format Tools Data Window Help Tue 6:08 PM a AutoSave OFF HEG55 HW #5 - Managing Bond Portfolios Q Home Insert Draw Page Layout Formulas Data Review View Tell me Share Q Comments X Calibri (Body) 11 - = ab Wrap Text General TI TT DV IIX V PA AY O v Paste 00 60 B I U a. Av ini Insert E = Delete Format $ % Ideas V Merge & Center Conditional Format Cell Formatting as Table Styles Sort & Filter Find & Select Q53 fx A c E G G H V N s T U V w X Y 2 AA AB AD AE AF AG Al A AK AL AM AN AD Homework #5 - Bond Portfolios Due 10/21 You are managing a bond portfolio for a pension fund that is required to pay pension benefit payments for 5 years starting in December, 2022. You cash flow match the liabilities using the bonds presented in your finance class. Using the bonds in class from Table 2, the cost to cash flow match was $12,304 with an average duration of 3.80. You decide that its worth using bonds with a lower credit rating to get the cost to cash flow match down. Using the bonds in Table 3, replace each bond in Table 2 with one that has a higher YTM but same Maturity. The coupon rate for the replacement bond will vary. Therefore you will need to adjust the quantity purchased by change the par value. You begin with 2026 and move backward like in class. Your goal is 1) cash flow match at a lower cost, 2) maintain a positive new cash balance in the last column of Table 1 for each year (this ensures all benefits are able to be paid each year), and minimize the ending cash balance in cell 024 (this will improve efficiency). *Note it is near impossible to match exactly, therefore a positive cash balance is always maintained in the account. Tecay 2020-10 Previous Reinvent 0.5% Principal Payment $ Dade Coupon 1. Payments $ . Reinvestment of Payments $ Due New Cash Balance $ 49 539 Use this soape how many bonds to purchase in 335 340 Value should be between 0 and 100 for 2022 on $ 49 3 2020-12 2021-12 2022-12 2023-32 2024-32 2025-12 2026-12 1 . 49 539 31 79 79 187 76 1.900 2,300 2.300 2,100 2,200 488 422 307 238 9 2.900 2,675 2.500 2.450 2.300 79 187 76 67 49 539 31 79 182 28/ 76 67 1 To $12.425 Table 2. Replace these bonds with a comparable one from Table 3, update the quantities in the Par Value column like in dies. Only adjust cells in yellow Coupon Value (100) Sofa Coupon Marity Price YTM Duration Payment Cost (10) 1900 Balduinc 3.50% 11/15/22 105 1.05% 1.99 665 $ 1995 2300 Goldman Sachs 5.00% 12/15/23 112 2300 1.12% 2.92 115 $ 2,576 Natural Rural Uslities 3.00% 12/15/24 107 1.26% 3.91 69 $ 2.463 2100 Burlington Northam RR 7.00% 12/15/25 129 147 $ 2,709 2200 JP Morgan 4.13% 12/15/26 116.5 1.32% 5.49 90.75 $ 2.563 Ang Duration 3.78 Costo Cai Row Match s 12,304 Questions 1. What is your costs as few mash this liability an? (Cdl 343) Price YTV 2. Given that it cost lessless to cash few masch, did the excess rem on the cash flow mancing come with any risk? Explain Table 3. Bond Unives, 2 bonds to choose from for each maturity ser Coupon American General Finance Corp 9.00% Fortiames Corp 7.75% Wynn Las Vegas uc 4.25% American Airlines 5.25% Eldorado Gold Corp 9.50% united Airlines Holdings S.COM Occidental Petroleum 6.50% Wendy's national 7.00% Ford Motor Corp, Inc 4.00% Golden Nine 8.75% May 11/15/22 11/15/22 12/15/23 12/15/23 12/15/24 12/15/24 12/15/25 12/15/25 12/15/26 12/15/26 103.50 110.00 99.00 75.00 106.50 89.00 99.00 110.00 98.00 87.50 1.28% 2.74% 4.59% 15.58% % 7.64% 8.17% 6.73% 4.79% 4.97% 11.65% Example 5 + Ready + 63% OCT ABC 20 A X W