Question 1 On November 15, 2015, TRB Corp. issued a 15-year bond maturing on November 15, 2030. The bond has a coupon rate of 4.75%, payable semi-annually on May 15th and November 15th. Assume that today's date is January 23, 2021. Use '1' (Actual/actual) for any Excel functions requiring a day count basis. a) Compute the accrued interest using a manual calculation and using the ACCRINT function. b) Suppose that a $1,000 face value bond from this issue has a quoted (clean) price of $915. Compute the effective yield to maturity using the XIRR function and the semiannual yield doubled using the YIELD function, c) Suppose that the effective) market yield on similar bonds is 3.75%. Compute the dirty price of the bond using the XNPV function and the clean price of the bond using the PRICE function. d. e) Suppose the bond is callable at a quoted (clean) price of $1,050 plus accrued interest starting November 15, 2025. A callable bond means that the bond issuer has the right to pay off the bonds early. Assume that the bond is actually called on March 1, 2026. First, compute the accrued interest on the call date using a manual calculation and using the ACCRINT function. Then compute the effective yield to call using the XIRR function and the semiannual yield doubled using the YIELD function. Assume the quoted bond price today is the same as in part (b): $915. D E F Price calculations Market yield on similar bonds Note: You may not need to fill in every row to solve the problem. Date Cash flow B 1 Analyzing a Corporate Bond 2 3 Face value of bond 1,000.00 4 Coupon rate 4.75% 5 Coupon payment 47.50 =B4-B3 6 Settlement date 1/23/2021 7 Previous coupon date 8 Next coupon date 9 Maturity date 11/15/2030 10 11 12 a) Accrued interest (manual calc) 13 a) Accrued interest (ACCRINT) 14 15 Yield calculations 16 clean price 17 18 Note: You may not need to fill in every row to solve the problem. 19 Date Cash flow 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 c) Dirty price (XNPV) c) Clean price (PRICE) Cash flow 44 45 b) Effective yield to maturity (XIRR) 46 b) Semiannual yield doubled (YIELD) 47 48 49 Yield to call calculations 50 Call price 51 First call date 52 Next coupon date 53 Actual call date 54 55 d) Accrued interest as of actual call date (manual calc) 56 d) Accrued interest as of actual call date (ACCRINT) 57 58 Note: You may not need to fill in every row to solve the problem. 59 Date 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 e) Effective yield to call (XIRR) 78 e) Semiannual yield doubled (YIELD) 70 Question 1 On November 15, 2015, TRB Corp. issued a 15-year bond maturing on November 15, 2030. The bond has a coupon rate of 4.75%, payable semi-annually on May 15th and November 15th. Assume that today's date is January 23, 2021. Use '1' (Actual/actual) for any Excel functions requiring a day count basis. a) Compute the accrued interest using a manual calculation and using the ACCRINT function. b) Suppose that a $1,000 face value bond from this issue has a quoted (clean) price of $915. Compute the effective yield to maturity using the XIRR function and the semiannual yield doubled using the YIELD function, c) Suppose that the effective) market yield on similar bonds is 3.75%. Compute the dirty price of the bond using the XNPV function and the clean price of the bond using the PRICE function. d. e) Suppose the bond is callable at a quoted (clean) price of $1,050 plus accrued interest starting November 15, 2025. A callable bond means that the bond issuer has the right to pay off the bonds early. Assume that the bond is actually called on March 1, 2026. First, compute the accrued interest on the call date using a manual calculation and using the ACCRINT function. Then compute the effective yield to call using the XIRR function and the semiannual yield doubled using the YIELD function. Assume the quoted bond price today is the same as in part (b): $915. D E F Price calculations Market yield on similar bonds Note: You may not need to fill in every row to solve the problem. Date Cash flow B 1 Analyzing a Corporate Bond 2 3 Face value of bond 1,000.00 4 Coupon rate 4.75% 5 Coupon payment 47.50 =B4-B3 6 Settlement date 1/23/2021 7 Previous coupon date 8 Next coupon date 9 Maturity date 11/15/2030 10 11 12 a) Accrued interest (manual calc) 13 a) Accrued interest (ACCRINT) 14 15 Yield calculations 16 clean price 17 18 Note: You may not need to fill in every row to solve the problem. 19 Date Cash flow 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 c) Dirty price (XNPV) c) Clean price (PRICE) Cash flow 44 45 b) Effective yield to maturity (XIRR) 46 b) Semiannual yield doubled (YIELD) 47 48 49 Yield to call calculations 50 Call price 51 First call date 52 Next coupon date 53 Actual call date 54 55 d) Accrued interest as of actual call date (manual calc) 56 d) Accrued interest as of actual call date (ACCRINT) 57 58 Note: You may not need to fill in every row to solve the problem. 59 Date 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 e) Effective yield to call (XIRR) 78 e) Semiannual yield doubled (YIELD) 70
