1.What is your cost to cash flow match this liability stream? (Cell B43)
File Edit View Insert Format Tools Data Window Help O Tue 6:08 PM AutoSave O OFF 3 HW #5 - Managing Bond Portfolios a D Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments LOY Calibri (Body) 11 A" A SE =1 ab Wrap Text v General 27- O . Paste BIU~ Merge & Center v $ ~ % " Conditional Format Cel Insert Delete Format Sort & Find & Ideas Formatting as Table Styles Filter Select 053 X V fx M AA AB AC AD AE AF AG AH AK AL AM AN AO 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. Table 1 Today 2020-10 Previous Cak Bal * Balance Principal Coupon Reinvestment Liability New Cash 2020-12 5 of Payments Due Balance Excess the this so grape how many bonds to purchase in 835-840 2021-12 Value should be between 0 and 100 for 2022 en 2022-12 2023-12 539 1,900 2024 42 2.675 2025-12 187 2,300 2026-12 2,100 2,500 2 200 3 300 $12,825 fable 2 - Replace these bonds with a comparable offer from Table 3, update the quantities in the "Par Value"call live in clinic. Only adjust cells in yellow Value (400) Maturity Feldman Sachs 13/35/22 Cost (x10] sud Bury Uslit 12/35/23 2200 Burlington Northem & 2.00% 12/35/24 IP Morgan 2/35/25 12/35/26 147 5 90 35 est so Cash Flow Match Wg Durader 12,304 Table 3 - Bond Universe, 2 b ands to che Questions 1. What is your cost so cash flow match this liability stream American General Finance Corp Coupon 14/15/22 Price Fort James Corp 3.00%% 7.75%% 19/15/22 1-28% 4.259% 2.749% 2. Given that it cost less less to cash flow math, did the excels resum on the cash flow matching come with any risk? Beplain american Airlines 12/15/23 Elderado Gold Corp. 5.259% 9.50% 12/15/23 United Airlines Holdings 42/15/24 5.C0% Occidental Peroleum 12/15/24 6.50% Wendy's International 7.00% 15/25 19.00 8.179 Ford Motor Corp, Ine 12/15/ Golden Nugget, In 8.7596 12/15/ 12/15/26 13.65 1 Example 5 + Ready - + 63% OCT 20 49 X W FELI
