Question

1 Approved Answer

Posted on Aug 04, 2024

In the following spreadsheet, I have outlined a liability that you have as a pension manager. Basically, you are going to pay $1 mm a

In the following spreadsheet, I have outlined a liability that you have as a pension manager. Basically, you are going to pay $1 mm a year in pension costs, starting in 15 years and going for 35 years. In the spreadsheet, I have also listed yields for STRIPS (zero coupon government bonds) for various maturities. What is the DV01 of your liability? How much would you need to invest in the STRIPS to defease your liability? SENY LOI Spreadsheet Home Insert DrawPage Layout Formulas Data Calibri Body ZLA HA A Review View Developer Tell me E. 13 Wrap Merge Carter X Out Copy of om General Pante $ - % ) Conditional 04 c D E F H 1 4 5 6 7 8 STRIPS Yield 0.20% 0.25% 030N 0.35% 0.40% 0.455 0.50% OSS NONO 9 10 11 12 13 14 15 16 17 13 19 20 22 23 24 25 26 27 28 29 30 31 12 33 34 35 36 32 Pension Payment 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 1.000.000 17 1.000.000 18 1.000.000 19 1.000.000 20 1,000,000 21 1,000,000 1,000,000 23 1,000,000 24 1,000,000 25 1.000.000 26 1.000.000 27 1,000,000 28 1.000.000 29 1,000.000 30 1,000,000 1,000,000 1,000,000 33 1,000,000 1,000,000 35 1.000.000 36 1,000,000 1.000.000 38 1,000,000 39 1.000.000 40 1,000,000 41 1.000.000 42 1.000.000 43 1,000,000 44 1,000,000 45 1,000,000 46 1.000.000 1,000,000 1.000.000 1,000,000 1.000.000 0.70% 0.75 O BON ORN 0.90% 1.00% 1. OSN 110K 115 1. 20% 1.25 1.30% 1.35% 1.40X 1.45% 1.50 1.55% 1. GON 1.65% 170N L70 170 1.70 1.70 1.705 1.70% 1.70 1.70W 1.70% 1.709 170 170N 1.709 170 1.70 1.70N 1. ION 1.70 1.70 L70% TE CE 35 40 43 41 44 45 > 49 05 15 53 6999 56 20 Sheet In the following spreadsheet, I have outlined a liability that you have as a pension manager. Basically, you are going to pay $1 mm a year in pension costs, starting in 15 years and going for 35 years. In the spreadsheet, I have also listed yields for STRIPS (zero coupon government bonds) for various maturities. What is the DV01 of your liability? How much would you need to invest in the STRIPS to defease your liability? SENY LOI Spreadsheet Home Insert DrawPage Layout Formulas Data Calibri Body ZLA HA A Review View Developer Tell me E. 13 Wrap Merge Carter X Out Copy of om General Pante $ - % ) Conditional 04 c D E F H 1 4 5 6 7 8 STRIPS Yield 0.20% 0.25% 030N 0.35% 0.40% 0.455 0.50% OSS NONO 9 10 11 12 13 14 15 16 17 13 19 20 22 23 24 25 26 27 28 29 30 31 12 33 34 35 36 32 Pension Payment 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 1.000.000 17 1.000.000 18 1.000.000 19 1.000.000 20 1,000,000 21 1,000,000 1,000,000 23 1,000,000 24 1,000,000 25 1.000.000 26 1.000.000 27 1,000,000 28 1.000.000 29 1,000.000 30 1,000,000 1,000,000 1,000,000 33 1,000,000 1,000,000 35 1.000.000 36 1,000,000 1.000.000 38 1,000,000 39 1.000.000 40 1,000,000 41 1.000.000 42 1.000.000 43 1,000,000 44 1,000,000 45 1,000,000 46 1.000.000 1,000,000 1.000.000 1,000,000 1.000.000 0.70% 0.75 O BON ORN 0.90% 1.00% 1. OSN 110K 115 1. 20% 1.25 1.30% 1.35% 1.40X 1.45% 1.50 1.55% 1. GON 1.65% 170N L70 170 1.70 1.70 1.705 1.70% 1.70 1.70W 1.70% 1.709 170 170N 1.709 170 1.70 1.70N 1. ION 1.70 1.70 L70% TE CE 35 40 43 41 44 45 > 49 05 15 53 6999 56 20 Sheet