Can you please put it in excel format so I can see which formulas are used
YIELD CURVE
ZERO COUPON
The Board of Governors of the Federal Reserve System publishes constant maturity and zero- coupon yields that are interpolated by the U.S. Treasury from the daily yield curve for non- inflation-indexed Treasury securities. These yields are based on the closing market bid yields on actively traded Treasury securities in the over-the-counter market. These market yields are calculated from composites of quotations obtained by the Federal Reserve Bank of New York. The attached spreadsheet contains year-end, constant-maturity and zero-coupon yields from 2004 2020. For the Yield Curve tab, the assignment is to: 1. Plot a yield curve for each year between 2004 2020 and display on one graph. 2. Indicate the shape of the yield curve for each year, i.e., Normal, Inverted, or Flat. For the Zero-Coupon tab, the assignment is to: 1. Calculate the series of one-year forward rates for each year between 2004 2020, i.e., 2f1, 3f1, 4f1, 5f1, 6f1, 7f1, 8f1, 9f1, 10f1. 2. Plot the series of one-year forward rates and display on one graph. 3. Indicate for each forward-rate curve, the expected direction of future short-term rates, i.e., Increasing, Decreasing, Constant. DATE 2004 1-mo 1.17 1-yr 2.09 2-yr 2.70 20-yr 5.33 3-yr 3.16 3.67 5.13 7-yr 4.24 3.80 2005 30-yr 5.41 4.19 3.45 3-mo 1.33 3.13 5.01 4.82 1.90 2.99 4.54 4.28 5-yr 3.81 3.72 5.10 4.92 10-yr 4.62 3.94 5.15 5.03 5.19 3.66 5.16 4.87 2.63 5.21 4.91 2006 2007 2008 2009 5.11 4.96 4.28 5.31 5.21 4.59 4.30 3.74 5.12 4.53 1.60 2.36 3.61 3.19 0.17 4.89 2.91 1.64 1.00 0.81 1.11 0.56 0.32 3.34 2.54 1.79 1.76 3.99 3.53 2.97 4.32 3.91 2010 2.42 2011 0.19 0.61 0.45 0.33 3.18 4.38 0.19 0.18 0.03 0.09 0.04 0.04 0.01 2.50 1.11 2012 0.21 0.41 2.76 0.72 1.41 2013 0.36 0.66 1.96 1.67 2.52 2.53 0.15 0.11 3.52 4.09 2.38 3.22 3.08 2.83 0.47 1.62 0.17 0.01 0.04 0.02 0.02 0.02 0.20 0.84 1.77 2.18 3.34 2014 2015 0.28 0.64 1.63 2.35 3.11 1.01 1.49 1.86 0.88 1.01 0.71 1.55 2.63 2016 2017 2018 2.30 0.58 1.38 0.26 1.03 1.93 2.12 0.16 0.45 1.24 2.33 1.92 0.16 1.89 2.73 2.13 2.07 1.29 2.14 2.81 1.87 0.49 2.61 2.91 2.52 1.75 2.31 2.85 2.00 0.66 2.84 2.98 2.52 2019 1.71 1.76 2.31 2020 0.13 0.16 0.18 0.29 1.18 1.41 6-yr 4.1424 3.7621 5.0397 4.8871 3.6088 8-yr 4.5444 3.8627 5.0741 DATE 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 1-yr 2.0775 3.4546 5.1635 4.8784 2.2662 0.5512 0.2797 0.1503 0.1943 0.1497 0.1771 0.3344 0.4819 1.2685 2.3322 1.9569 0.1918 2-yr 2.7449 3.6267 5.1835 4.9002 2.6521 1.1007 0.6227 0.4936 0.3222 0.4218 0.5144 0.7300 0.6571 1.4671 2.5592 1.8037 0.1988 3-yr 3.2287 3.6850 5.1377 4.8834 2.9543 1.6471 1.0480 0.9458 0.4808 0.7591 0.8780 1.0590 0.7792 1.6021 2.6315 1.7202 0.2007 4-yr 3.5984 3.7085 5.0864 4.8682 3.2036 2.1428 1.4710 1.4026 0.6514 1.0990 1.2258 1.3408 0.8855 1.7147 2.6529 1.6923 0.2209 5-yr 3.8949 3.7302 5.0520 4.8686 3.4182 2.5768 1.8606 1.8248 0.8250 1.4177 1.5442 1.5886 0.9912 1.8223 2.6678 2.9529 2.2092 2.2020 0.9971 1.7085 1.8313 1.8110 1.1013 1.9314 2.6929 1.7510 0.3303 7-yr 4.3558 3.8066 5.0483 4.9217 3.7820 3.2790 2.5185 2.5353 1.1655 1.9720 2.0899 2.0141 1.2165 2.0437 2.7326 1.8176 0.4134 4.9692 3.9417 3.5639 2.7933 2.8298 1.3293 2.2108 2.3238 2.2017 1.3358 2.1587 9-yr 4.7142 3.9282 5.1132 5.0263 4.0905 3.8152 3.0390 3.0915 1.4880 2.4285 2.5369 2.3768 1.4578 2.2755 2.8513 1.9914 0.6155 10-yr 4.8693 4.0005 5.1622 5.0901 4.2304 4.0390 3.2602 3.3258 1.6416 2.6282 2.7324 2.5413 1.5812 2.3929 2.9252 2.0902 0.7275 1.7063 0.2648 2.7862 1.8994 0.5098 The Board of Governors of the Federal Reserve System publishes constant maturity and zero- coupon yields that are interpolated by the U.S. Treasury from the daily yield curve for non- inflation-indexed Treasury securities. These yields are based on the closing market bid yields on actively traded Treasury securities in the over-the-counter market. These market yields are calculated from composites of quotations obtained by the Federal Reserve Bank of New York. The attached spreadsheet contains year-end, constant-maturity and zero-coupon yields from 2004 2020. For the Yield Curve tab, the assignment is to: 1. Plot a yield curve for each year between 2004 2020 and display on one graph. 2. Indicate the shape of the yield curve for each year, i.e., Normal, Inverted, or Flat. For the Zero-Coupon tab, the assignment is to: 1. Calculate the series of one-year forward rates for each year between 2004 2020, i.e., 2f1, 3f1, 4f1, 5f1, 6f1, 7f1, 8f1, 9f1, 10f1. 2. Plot the series of one-year forward rates and display on one graph. 3. Indicate for each forward-rate curve, the expected direction of future short-term rates, i.e., Increasing, Decreasing, Constant. DATE 2004 1-mo 1.17 1-yr 2.09 2-yr 2.70 20-yr 5.33 3-yr 3.16 3.67 5.13 7-yr 4.24 3.80 2005 30-yr 5.41 4.19 3.45 3-mo 1.33 3.13 5.01 4.82 1.90 2.99 4.54 4.28 5-yr 3.81 3.72 5.10 4.92 10-yr 4.62 3.94 5.15 5.03 5.19 3.66 5.16 4.87 2.63 5.21 4.91 2006 2007 2008 2009 5.11 4.96 4.28 5.31 5.21 4.59 4.30 3.74 5.12 4.53 1.60 2.36 3.61 3.19 0.17 4.89 2.91 1.64 1.00 0.81 1.11 0.56 0.32 3.34 2.54 1.79 1.76 3.99 3.53 2.97 4.32 3.91 2010 2.42 2011 0.19 0.61 0.45 0.33 3.18 4.38 0.19 0.18 0.03 0.09 0.04 0.04 0.01 2.50 1.11 2012 0.21 0.41 2.76 0.72 1.41 2013 0.36 0.66 1.96 1.67 2.52 2.53 0.15 0.11 3.52 4.09 2.38 3.22 3.08 2.83 0.47 1.62 0.17 0.01 0.04 0.02 0.02 0.02 0.20 0.84 1.77 2.18 3.34 2014 2015 0.28 0.64 1.63 2.35 3.11 1.01 1.49 1.86 0.88 1.01 0.71 1.55 2.63 2016 2017 2018 2.30 0.58 1.38 0.26 1.03 1.93 2.12 0.16 0.45 1.24 2.33 1.92 0.16 1.89 2.73 2.13 2.07 1.29 2.14 2.81 1.87 0.49 2.61 2.91 2.52 1.75 2.31 2.85 2.00 0.66 2.84 2.98 2.52 2019 1.71 1.76 2.31 2020 0.13 0.16 0.18 0.29 1.18 1.41 6-yr 4.1424 3.7621 5.0397 4.8871 3.6088 8-yr 4.5444 3.8627 5.0741 DATE 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 1-yr 2.0775 3.4546 5.1635 4.8784 2.2662 0.5512 0.2797 0.1503 0.1943 0.1497 0.1771 0.3344 0.4819 1.2685 2.3322 1.9569 0.1918 2-yr 2.7449 3.6267 5.1835 4.9002 2.6521 1.1007 0.6227 0.4936 0.3222 0.4218 0.5144 0.7300 0.6571 1.4671 2.5592 1.8037 0.1988 3-yr 3.2287 3.6850 5.1377 4.8834 2.9543 1.6471 1.0480 0.9458 0.4808 0.7591 0.8780 1.0590 0.7792 1.6021 2.6315 1.7202 0.2007 4-yr 3.5984 3.7085 5.0864 4.8682 3.2036 2.1428 1.4710 1.4026 0.6514 1.0990 1.2258 1.3408 0.8855 1.7147 2.6529 1.6923 0.2209 5-yr 3.8949 3.7302 5.0520 4.8686 3.4182 2.5768 1.8606 1.8248 0.8250 1.4177 1.5442 1.5886 0.9912 1.8223 2.6678 2.9529 2.2092 2.2020 0.9971 1.7085 1.8313 1.8110 1.1013 1.9314 2.6929 1.7510 0.3303 7-yr 4.3558 3.8066 5.0483 4.9217 3.7820 3.2790 2.5185 2.5353 1.1655 1.9720 2.0899 2.0141 1.2165 2.0437 2.7326 1.8176 0.4134 4.9692 3.9417 3.5639 2.7933 2.8298 1.3293 2.2108 2.3238 2.2017 1.3358 2.1587 9-yr 4.7142 3.9282 5.1132 5.0263 4.0905 3.8152 3.0390 3.0915 1.4880 2.4285 2.5369 2.3768 1.4578 2.2755 2.8513 1.9914 0.6155 10-yr 4.8693 4.0005 5.1622 5.0901 4.2304 4.0390 3.2602 3.3258 1.6416 2.6282 2.7324 2.5413 1.5812 2.3929 2.9252 2.0902 0.7275 1.7063 0.2648 2.7862 1.8994 0.5098
