Use the 3.803% coupon bond that matures on 8/15/2042 to answer the following question. Assume that the bond makes coupon payments on February 15 and August 15 each year. Calculate the price o the bond on 2/14/2020 and the Macaulay Duration on 2/14/2020 (using excel by hand - not a predefined function in excel), based on the Ask YTM reported in Bloomberg of 2.816% on 2/14/2020. (Assume the next coupon payment is exactly 6 months away from your date and that you had received the 2/15/2020 coupon so it is not part of your calculation). (10 points) Bloomberg screen shots (for reference to see changes over time - not needed to do calculations) CAT 3.8% 2042 Hist Ask Price CAT 3.8% 2042 Hist Ask YTM Cat 3.8% 2042 Hist Ask Price 104 VIETOT CAT 3.803 08/15/42 $f 101.447 +4.053 230.0 bp_vs T 2.375 11/15/2049 As of 24 Mar Source BMRK EJ434856 Corp 9 5 Compare 96 Actions - 97) Edit Line Chart 03/25/2019 - 03/25/20205 BMRK Ask Px Local CCY Mov Avgs Key Events 1D 3D 1M 6MYTD 1y5Y Max Daily Table . Duration Analysis for CAT 6.95 05/42 Settlement 3/26/20 Price 146.397 Yield 3.821325 to 5/ 1/42 a 100 YLD S/A Pricing at 3/27/20 HORIZON Mod Duration SHET ReinvTraded to R SPRD# Yield Price Bond 30YR PROB - 150 2.32 MTY 5/ 1/42 100 +265.7 2.321 179.65 13.90 24.45 -100 2.82 5/ 1/42 100 +265.7 2.821 167.53 13.58 23.99 -50 3.32 5/ 1/42 100 +265.7 3.321 156.48 13.25 23.51 3.82 5/ 1/42 100 +265.7 3.821 146.39 12.92 23.01100.0 50 4.32 5/ 1/42 100 +265.7 4.321 137.17 12.60 22.48 100 5/ 1/42 100 +265.7 4.821 128.73 12.27 21.94 150 5.32 5/ 1/42 100 +265.7 5.321 121.01 11.94 21.38 ExVal 3.82 265.7 3.821 146.39 12.92 23.01 Mode: T Traditional Fixed Yld Convention? Y BOND DURATION VS TSY YLD SHIFT BMK TSY YLD Probabilities V 23:08 C-Custom 30YR 1.391 V-Yld Std Dev at 10YR 0.808 11 bp/year Log?Y 10.0 3 Yld Volat. View D Duration - SPRDS done to interpolated BMRK Curve CAT 6.95% 2042 YAS Will Centner 104 Yield and Spread Analysis 95) Buy 96) Sell CAT 6.95 05/01/42 Corp Settings - 143.787/146.397 3.958/3.821 BMRK @ 18:45 No Notes 1 Yield & Spread ) Graphs 30 Pricing Description 5) Custom CAT 6.95 05/01/42 ( 149123BK6 ) Risk Spread 245.03 bp vs T 28 11/15/49 Price 145.092 3 122-16+ 22:29:00 M.Duro Dur 3.889412 wst 1.439142 S/A - Risk Wkout 05/01/2042 a 100.00 Consensus Yld 66 Convey Settle 03/26/20 03/25/200 DV : 01 on 1MM Benchmark Risk Risk Hedge Proceeds Hedge Spreads Yield Calculations Invoice 11 G-Sprd 270.0 Street Convention 3.889412 Face 12 I-Sprd 306.3 Equiv 1 /Yr 3.927231 Principal 13) Basis - 220.4 Mmkt (Act/360) Accrued (145 Days) 14 Z-Sprd 307.3 True Yield 3.889141 Total (USD) 15) ASW 381.1 Current Yield 4.790 16) OAS 280.4 Workout 13.131 19.050 2.314 1,905 27.195 700M 1,199 M OAS N.A. 19.244 2.358 1,924 28.144 684M 1,000 M 1,450,920.00 27,993.06 1,478,913.06 After Tax (Inc 40.8OC % CG23.80C%) 2.302604 Issue Price = 99.742. Bond Purchased with Premi... CAT 3.8% 2042 DURA If this item does not open automatically you can open CAT 3.8% 2042 DURA here DURA ENTER ALL VALUES AND HIT . Duration Analysis for CAT3.803 08/42 Settlement 3/26/20 Price 101.652 Yield 3.693693 to 8/15/42 @ 100 YLD S/A Pricing at 3/27/20 HORIZON Mod Duration SHET ReinvTraded to R SPRD* Yield Price Bond 30YR PROB 2.19 MTY 8/15/42 100 +252.1 2.194 128.34 15.97 24.45 - 100 2.69 8/15/42 100 +252.1 2.694 118.55 15.65 23.99 -50 3.19 8/15/42 100 +252.1 3.194 109.69 15.32 23.51 3.69 8/15/42 100 +252.1 3.694 101.65 14.99 23.01100.0 50 4.19 8/15/42 100 +252.1 4.194 94.36 14.65 22.48 100 4.69 8/15/42 100 +252.1 4.694 87.737 14.31 21.94 5.19 8/15/42 100 +252.1 5.194 81.217 13.97 21.38 ExVal 3.69 252.1 3.694 101.65 14.99 23.01 Mode: T Traditional Fixed Yld Convention? Y BOND DURATION vs TSY YLD SHIFT BMK TSY YLD Probabilities V 23:44 C-Custom 30YR 1.391 V-yld Std Dev at 10YR 0.808 11_bp/year Log?Y 10.0 % Yld Volat. View D Duration - 150 - 100 -50 0 50 100 150 SPRDS done to interpolated BMRK Curve Use the 3.803% coupon bond that matures on 8/15/2042 to answer the following question. Assume that the bond makes coupon payments on February 15 and August 15 each year. Calculate the price o the bond on 2/14/2020 and the Macaulay Duration on 2/14/2020 (using excel by hand - not a predefined function in excel), based on the Ask YTM reported in Bloomberg of 2.816% on 2/14/2020. (Assume the next coupon payment is exactly 6 months away from your date and that you had received the 2/15/2020 coupon so it is not part of your calculation). (10 points) Bloomberg screen shots (for reference to see changes over time - not needed to do calculations) CAT 3.8% 2042 Hist Ask Price CAT 3.8% 2042 Hist Ask YTM Cat 3.8% 2042 Hist Ask Price 104 VIETOT CAT 3.803 08/15/42 $f 101.447 +4.053 230.0 bp_vs T 2.375 11/15/2049 As of 24 Mar Source BMRK EJ434856 Corp 9 5 Compare 96 Actions - 97) Edit Line Chart 03/25/2019 - 03/25/20205 BMRK Ask Px Local CCY Mov Avgs Key Events 1D 3D 1M 6MYTD 1y5Y Max Daily Table . Duration Analysis for CAT 6.95 05/42 Settlement 3/26/20 Price 146.397 Yield 3.821325 to 5/ 1/42 a 100 YLD S/A Pricing at 3/27/20 HORIZON Mod Duration SHET ReinvTraded to R SPRD# Yield Price Bond 30YR PROB - 150 2.32 MTY 5/ 1/42 100 +265.7 2.321 179.65 13.90 24.45 -100 2.82 5/ 1/42 100 +265.7 2.821 167.53 13.58 23.99 -50 3.32 5/ 1/42 100 +265.7 3.321 156.48 13.25 23.51 3.82 5/ 1/42 100 +265.7 3.821 146.39 12.92 23.01100.0 50 4.32 5/ 1/42 100 +265.7 4.321 137.17 12.60 22.48 100 5/ 1/42 100 +265.7 4.821 128.73 12.27 21.94 150 5.32 5/ 1/42 100 +265.7 5.321 121.01 11.94 21.38 ExVal 3.82 265.7 3.821 146.39 12.92 23.01 Mode: T Traditional Fixed Yld Convention? Y BOND DURATION VS TSY YLD SHIFT BMK TSY YLD Probabilities V 23:08 C-Custom 30YR 1.391 V-Yld Std Dev at 10YR 0.808 11 bp/year Log?Y 10.0 3 Yld Volat. View D Duration - SPRDS done to interpolated BMRK Curve CAT 6.95% 2042 YAS Will Centner 104 Yield and Spread Analysis 95) Buy 96) Sell CAT 6.95 05/01/42 Corp Settings - 143.787/146.397 3.958/3.821 BMRK @ 18:45 No Notes 1 Yield & Spread ) Graphs 30 Pricing Description 5) Custom CAT 6.95 05/01/42 ( 149123BK6 ) Risk Spread 245.03 bp vs T 28 11/15/49 Price 145.092 3 122-16+ 22:29:00 M.Duro Dur 3.889412 wst 1.439142 S/A - Risk Wkout 05/01/2042 a 100.00 Consensus Yld 66 Convey Settle 03/26/20 03/25/200 DV : 01 on 1MM Benchmark Risk Risk Hedge Proceeds Hedge Spreads Yield Calculations Invoice 11 G-Sprd 270.0 Street Convention 3.889412 Face 12 I-Sprd 306.3 Equiv 1 /Yr 3.927231 Principal 13) Basis - 220.4 Mmkt (Act/360) Accrued (145 Days) 14 Z-Sprd 307.3 True Yield 3.889141 Total (USD) 15) ASW 381.1 Current Yield 4.790 16) OAS 280.4 Workout 13.131 19.050 2.314 1,905 27.195 700M 1,199 M OAS N.A. 19.244 2.358 1,924 28.144 684M 1,000 M 1,450,920.00 27,993.06 1,478,913.06 After Tax (Inc 40.8OC % CG23.80C%) 2.302604 Issue Price = 99.742. Bond Purchased with Premi... CAT 3.8% 2042 DURA If this item does not open automatically you can open CAT 3.8% 2042 DURA here DURA ENTER ALL VALUES AND HIT . Duration Analysis for CAT3.803 08/42 Settlement 3/26/20 Price 101.652 Yield 3.693693 to 8/15/42 @ 100 YLD S/A Pricing at 3/27/20 HORIZON Mod Duration SHET ReinvTraded to R SPRD* Yield Price Bond 30YR PROB 2.19 MTY 8/15/42 100 +252.1 2.194 128.34 15.97 24.45 - 100 2.69 8/15/42 100 +252.1 2.694 118.55 15.65 23.99 -50 3.19 8/15/42 100 +252.1 3.194 109.69 15.32 23.51 3.69 8/15/42 100 +252.1 3.694 101.65 14.99 23.01100.0 50 4.19 8/15/42 100 +252.1 4.194 94.36 14.65 22.48 100 4.69 8/15/42 100 +252.1 4.694 87.737 14.31 21.94 5.19 8/15/42 100 +252.1 5.194 81.217 13.97 21.38 ExVal 3.69 252.1 3.694 101.65 14.99 23.01 Mode: T Traditional Fixed Yld Convention? Y BOND DURATION vs TSY YLD SHIFT BMK TSY YLD Probabilities V 23:44 C-Custom 30YR 1.391 V-yld Std Dev at 10YR 0.808 11_bp/year Log?Y 10.0 % Yld Volat. View D Duration - 150 - 100 -50 0 50 100 150 SPRDS done to interpolated BMRK Curve