Given the following two bonds, compute the following: Settlement Coupon Maturity Yield Face Value Frequency Excel Basis Bond A 11/8/2021 3.00% 6/3/2032 8.53% 100 2 1 Bond B 11/8/2021) 8.000% 8/15/2038 6.00% 100 2 1 Instructions: Fill out the information below. 1. Use the Cash flow Table and the Coup Functions to compute: Invoice Price. Quoted Price, Duration, Modified Duration, Convexity do NOT use PRICE. DURATION, or MDURATION functions. 2. in the cash flow table, use IF statements, so that the table re-sizes properly if the dates are changed. 3. Fill in the table in cells $H$27:$M$36 and answer which bonds you would prefer under the two conditions Computing a new estimated invoice price. Using Duration APP-PE-DAY Coup Pmt Begin Frac Percent price change From Previous Pmt to Settlement Date $ price change New price Inv Price AI OP Dur Mdur Cvx P P. AP-P-P--PD4y P= P.-P.Duod 4y Using duration and Convexity APP-P PP AP=P-P--P.D4y+ -DAY + Percent price change + Cox (41) P. CvX( 41 ) Pa = P. P. Dags Av++ PC12(41) 2 $ price change + New price Which bond would you prefer if interest rates were to New Price Bond A Price Change Pct Price Change Bond BBond A Bond BBond A Bond B a. Increase 1.50% Actual Using the Duration estimate Using the Dur&Cvx estimate Prefer: Neither b. Decrease -0.900% New Price Bond A Price Change Pet Price Change Bond BBond A Bond BBond A Bond B Prefer: Neither Actual Using the Duration estimate Using the Dur&Cvx estimate Sums: 0 0 0 PV(PMT)*PV(PMT/(t+t)*PV(PMT) Sums: t 0 0 0 PVPMT) *PV(PMT/(t+t)*PV(PMT) Pmt t Date Pmt Date 11/8/2021 12/3/2021 6/3/2022 12/3/2022 6/3/2023 12/3/2023 6/3/2024 12/3/2024 6/3/2025 12/3/2025 6/3/2026 12/3/2026 6/3/2027 12/3/2027 6/3/2028 12/3/2028 6/3/2029 12/3/2029 6/3/2030 12/3/2030 6/3/2031 12/3/2031 6/3/2032 12/3/2032 6/3/2033 12/3/2033 6/3/2034 12/3/2034 6/3/2035 12/3/2035 6/3/2036 12/3/2036 6/3/2037 12/3/2037 11/8/2021 2/15/2022 8/15/2022 2/15/2023 8/15/2023 2/15/2024 8/15/2024 2/15/2025 8/15/2025 2/15/2026 8/15/2026 2/15/2027 8/15/2027 2/15/2028 8/15/2028 2/15/2029 8/15/2029 2/15/2030 8/15/2030 2/15/2031 8/15/2031 2/15/2032 8/15/2032 2/15/2033 8/15/2033 2/15/2034 8/15/2034 2/15/2035 8/15/2035 2/15/2036 8/15/2036 2/15/2037 8/15/2037 2/15/2038 Given the following two bonds, compute the following: Settlement Coupon Maturity Yield Face Value Frequency Excel Basis Bond A 11/8/2021 3.00% 6/3/2032 8.53% 100 2 1 Bond B 11/8/2021) 8.000% 8/15/2038 6.00% 100 2 1 Instructions: Fill out the information below. 1. Use the Cash flow Table and the Coup Functions to compute: Invoice Price. Quoted Price, Duration, Modified Duration, Convexity do NOT use PRICE. DURATION, or MDURATION functions. 2. in the cash flow table, use IF statements, so that the table re-sizes properly if the dates are changed. 3. Fill in the table in cells $H$27:$M$36 and answer which bonds you would prefer under the two conditions Computing a new estimated invoice price. Using Duration APP-PE-DAY Coup Pmt Begin Frac Percent price change From Previous Pmt to Settlement Date $ price change New price Inv Price AI OP Dur Mdur Cvx P P. AP-P-P--PD4y P= P.-P.Duod 4y Using duration and Convexity APP-P PP AP=P-P--P.D4y+ -DAY + Percent price change + Cox (41) P. CvX( 41 ) Pa = P. P. Dags Av++ PC12(41) 2 $ price change + New price Which bond would you prefer if interest rates were to New Price Bond A Price Change Pct Price Change Bond BBond A Bond BBond A Bond B a. Increase 1.50% Actual Using the Duration estimate Using the Dur&Cvx estimate Prefer: Neither b. Decrease -0.900% New Price Bond A Price Change Pet Price Change Bond BBond A Bond BBond A Bond B Prefer: Neither Actual Using the Duration estimate Using the Dur&Cvx estimate Sums: 0 0 0 PV(PMT)*PV(PMT/(t+t)*PV(PMT) Sums: t 0 0 0 PVPMT) *PV(PMT/(t+t)*PV(PMT) Pmt t Date Pmt Date 11/8/2021 12/3/2021 6/3/2022 12/3/2022 6/3/2023 12/3/2023 6/3/2024 12/3/2024 6/3/2025 12/3/2025 6/3/2026 12/3/2026 6/3/2027 12/3/2027 6/3/2028 12/3/2028 6/3/2029 12/3/2029 6/3/2030 12/3/2030 6/3/2031 12/3/2031 6/3/2032 12/3/2032 6/3/2033 12/3/2033 6/3/2034 12/3/2034 6/3/2035 12/3/2035 6/3/2036 12/3/2036 6/3/2037 12/3/2037 11/8/2021 2/15/2022 8/15/2022 2/15/2023 8/15/2023 2/15/2024 8/15/2024 2/15/2025 8/15/2025 2/15/2026 8/15/2026 2/15/2027 8/15/2027 2/15/2028 8/15/2028 2/15/2029 8/15/2029 2/15/2030 8/15/2030 2/15/2031 8/15/2031 2/15/2032 8/15/2032 2/15/2033 8/15/2033 2/15/2034 8/15/2034 2/15/2035 8/15/2035 2/15/2036 8/15/2036 2/15/2037 8/15/2037 2/15/2038