Question 2 Today is May 21, 2020. You have been tasked with analyzing a $1,000 face value bond with a 4.00% coupon rate. The bond matures in 10 years and pays coupon payments annually (the first coupon payment occurs one year from today). The bond's yield to maturity is 25.00%. Note: Use '1' (Actual/actual) for any Excel functions requiring a day count basis. a) Calculate the price of the bond using the table template provided as well as the NPV function. b) Calculate the bond's duration using the table template provided as well as the DURATION function. c) Suppose the bond's yield to maturity decreases by 20 basis points. Use NPV to compute the new bond price. d) Use the bond's duration to estimate the new bond price resulting from the decrease in yield. e) Use the DURATION function to calculate the bond's duration for various coupon rates (use values from 0% to 12% increasing by 1%). On the Q2 worksheet, describe the relation between the coupon rate and duration (i.e., does duration increase, or decrease, or increase then decrease, or decrease then increase?). Explain the intuition behind your answer. f) Use the DURATION function to calculate the bond's duration for various maturities (use values from 2 to 30 years increasing by 2). On the Q2 worksheet, describe the relation between the bond's maturity and duration (i.e., does duration increase, or decrease, or increase then decrease, or decrease then increase?). Explain the intuition behind your answer A B D E F 5/21/2020 1.000 4.00% 10 25.00% 5/21/2030 Cash flow PV PV/Price (PV/Price)*Time A 1 Bond duration 2 Current date 3 Face value 4 Coupon 5 Maturity (years) 6 YTM 7 Maturity date 8 9 Note: You may not need to fill in every row to solve the problem. 10 Time (years) 11 12 13 14 15 16 17 18 19 20 21 22 23 24 a) Bond price (sum from table) 25 a) Bond price (NPV) 26 27 b) Duration (sum from table) 28 b) Duration (DURATION) 29 30 New yield 31 c) New price (NPV) 32 33 d) New price (use duration to approximate) 34 35 e) Relationship between coupon rate and duration: 36 Coupon rate 37 38 39 40 41 42 43 44 45 46 47 49 49 50 Duration 51 Short-answer response: Maturity (date) Duration 52 53 54 f) Relationship between maturity and duration: 55 Maturity (years) 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 Short-answer response: 73 74 75 76
