Basic Bond Setup Settlement Coupon Maturity Yield Face Value Frequency Excel Basis 11/8/2021 4.75% 3/23/2028 2.36% 100 2 0 Instructions: Fill out the information below. 1. Compute the following, in column F ONLY, using Excel functions: Invoice Price, Quoted Price, Duration, Modified Duration, Convexity 2. for the answers in column E, do NOT use PRICE, DURATION, or MDURATION functions. 13. in the cash flow table, use IF statements, so that the table re-sizes properly if the dates are changed. 4. Compute the following by writing VBA functions that only use the information in cells $B$3:$B$9. 1. Invoice Price, 2. Quoted Price, 3. Duration. 4. Modified Duration, 5. Convexity 4a. VBA NOTE To use =Coupxxx(...) functions in VBA, you must use =WorksheetFunction.Coupxxx(...) From Table below Excel Function My VBA Function Coup Pmt Num Pmts Previous Pmt Date Days from last payment Days in coupon Period Fraction of a period since pmt Accrued Interest Invoice Price Quoted Price Duration Modified Dur Convexity CF 15 (1+2) D = P. PV Duration Calc Cvx Calc IXCF Beginning Fraction for Cash flow Table ((1+) D 69763 Pmt 1 Droo $(1+) (1+7) CF (1 +1) 1 1 -1 (1+%A Cw= (1+42 . r t PV(PMT) t*PV(PMT) (t+1)*PV(PMT) Arbitrary date far in future (for if statements in Date Col) Date Pmt Number 11/8/2021 3/23/2022 1 9/23/2022 2 3/23/2023 3 9/23/2023 4 3/23/2024 5 9/23/2024 6 3/23/2025 7 9/23/2025 8 3/23/2026 9 9/23/2026 10 3/23/2027 11 9/23/2027 12 3/23/2028 13 Basic Bond Setup Settlement Coupon Maturity Yield Face Value Frequency Excel Basis 11/8/2021 4.75% 3/23/2028 2.36% 100 2 0 Instructions: Fill out the information below. 1. Compute the following, in column F ONLY, using Excel functions: Invoice Price, Quoted Price, Duration, Modified Duration, Convexity 2. for the answers in column E, do NOT use PRICE, DURATION, or MDURATION functions. 13. in the cash flow table, use IF statements, so that the table re-sizes properly if the dates are changed. 4. Compute the following by writing VBA functions that only use the information in cells $B$3:$B$9. 1. Invoice Price, 2. Quoted Price, 3. Duration. 4. Modified Duration, 5. Convexity 4a. VBA NOTE To use =Coupxxx(...) functions in VBA, you must use =WorksheetFunction.Coupxxx(...) From Table below Excel Function My VBA Function Coup Pmt Num Pmts Previous Pmt Date Days from last payment Days in coupon Period Fraction of a period since pmt Accrued Interest Invoice Price Quoted Price Duration Modified Dur Convexity CF 15 (1+2) D = P. PV Duration Calc Cvx Calc IXCF Beginning Fraction for Cash flow Table ((1+) D 69763 Pmt 1 Droo $(1+) (1+7) CF (1 +1) 1 1 -1 (1+%A Cw= (1+42 . r t PV(PMT) t*PV(PMT) (t+1)*PV(PMT) Arbitrary date far in future (for if statements in Date Col) Date Pmt Number 11/8/2021 3/23/2022 1 9/23/2022 2 3/23/2023 3 9/23/2023 4 3/23/2024 5 9/23/2024 6 3/23/2025 7 9/23/2025 8 3/23/2026 9 9/23/2026 10 3/23/2027 11 9/23/2027 12 3/23/2028 13