On November 15, 2016, Tha Corp. issued a 15-year bond maturing on Novernber 15, 2031. The bond has a face value of 51,000 and a coupon rate of 4.25%, payable sem-annually on May 15th and November 15th. Assume that today's date is March 30, 2023. Use'1' (Actual/actual) for any Excel functions requiring a day count basis. a) Calculate the accrued interest using a mancal calculation and using the AcCeinT function (your answers should match). b) Suppose that the bond has a quoted (dean) price of $1,025. Calculate the effective vield to maturity using the XiRR function and the bond equivalent yield (semiannual yield doubled) using the neiD function. With each approach, py attention to the value you are using as the price today to calculate the yield; should it be the dean price or the dirty price? c| Suppose that the effective makket yield on comparable bonds is 5.25%. Calculate the dirty price of the bond using the XNpV function and the dean price of the bond using the PAICE function. With each apptoach, pay atteotion to the value you are using as the yield to calculate the price; should it be the etfective yicld or the bond equivalent yield (semiannual yleld doubled)? d, e) Suppose vou sel the bond on June 10,2026 at a quoted (dean) price of $1,045. First, calculate the accrued interest as of the sale date using a manual calculation and using the Accaint function (your answers should match). Then determine the realized yleid on your bond investment from purchase to sale; calculate the effective yield using the xirh function and the bond equivalent yield (semiannual yield doubled) using the vifuD function. 21 Assume the quoted bond price today (March 30,2023 ) is the same as in part (b): 51,025 . The main differ ence between this question and part (b) is that the bond cash flows end on the sale 22. \begin{tabular}{|l|l|} \hline 52 & Settlement date (today) \\ 53 & Clean price on settlement date (given) \\ 54 & Accrued interest as of settlement date (sameas part (a)) \\ 55 & Sale price \\ 56 & Sale date \\ 57 & Previous coupon date before sale date \\ 58 & Next coupon date after sale date \\ 59 & \\ 60 & d) Accrued interest as of sale date (manual calc) \\ 61 & d) Accrued interest as of sale date (ACCRINT) \end{tabular} Note: You may not need to fill in every row to solve the problem. Date Cash flow e) Effective yield (XIRR) e) Semiannual yield doubled (YIELD) On November 15, 2016, Tha Corp. issued a 15-year bond maturing on Novernber 15, 2031. The bond has a face value of 51,000 and a coupon rate of 4.25%, payable sem-annually on May 15th and November 15th. Assume that today's date is March 30, 2023. Use'1' (Actual/actual) for any Excel functions requiring a day count basis. a) Calculate the accrued interest using a mancal calculation and using the AcCeinT function (your answers should match). b) Suppose that the bond has a quoted (dean) price of $1,025. Calculate the effective vield to maturity using the XiRR function and the bond equivalent yield (semiannual yield doubled) using the neiD function. With each approach, py attention to the value you are using as the price today to calculate the yield; should it be the dean price or the dirty price? c| Suppose that the effective makket yield on comparable bonds is 5.25%. Calculate the dirty price of the bond using the XNpV function and the dean price of the bond using the PAICE function. With each apptoach, pay atteotion to the value you are using as the yield to calculate the price; should it be the etfective yicld or the bond equivalent yield (semiannual yleld doubled)? d, e) Suppose vou sel the bond on June 10,2026 at a quoted (dean) price of $1,045. First, calculate the accrued interest as of the sale date using a manual calculation and using the Accaint function (your answers should match). Then determine the realized yleid on your bond investment from purchase to sale; calculate the effective yield using the xirh function and the bond equivalent yield (semiannual yield doubled) using the vifuD function. 21 Assume the quoted bond price today (March 30,2023 ) is the same as in part (b): 51,025 . The main differ ence between this question and part (b) is that the bond cash flows end on the sale 22. \begin{tabular}{|l|l|} \hline 52 & Settlement date (today) \\ 53 & Clean price on settlement date (given) \\ 54 & Accrued interest as of settlement date (sameas part (a)) \\ 55 & Sale price \\ 56 & Sale date \\ 57 & Previous coupon date before sale date \\ 58 & Next coupon date after sale date \\ 59 & \\ 60 & d) Accrued interest as of sale date (manual calc) \\ 61 & d) Accrued interest as of sale date (ACCRINT) \end{tabular} Note: You may not need to fill in every row to solve the problem. Date Cash flow e) Effective yield (XIRR) e) Semiannual yield doubled (YIELD)
