need formula please, thank you
A firm wishing to evaluate interest rate behavior has gathered yield data on five U.S. Treasury securitics, cach having a different maturity and all measured at the same point in time. The summarized data follow. Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula acron a wow or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used the directions will specify the use of that function. Do not npe in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Giver Data section. 7 U.S. Treasury Security A B C D E Time To Maturity 1 year 10 years 6 months 20 years Yield 12.6% 11.2% 13.0% 11.0% 11.4% Sycurs To Do u. Draw the yield curve associated with these data, b. Describe the resulting yield curve in parte, and explain what it says about the direction of future interest rates under the expectations theory. 10 11 12 13 14 15 16 17 18 19 20 21 22 Solution u. Draw the yield curve associated with these dala, Time to Maturity (months) U.S. Treasury Security A B c D 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 11.1 + Ready 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 b. Describe the resulting yield curve in part a, and explain what it says about the direction of future interest rates under the expectations theory 43 44 The yield curve is clearly sloping. Expectations theory would interpret such a curve as forecasting a 45 46 47 Requirements 48 1 In cells D21:125, hy using cell references, convert the time to maturily to months. 2 In cells C27:540, insert a Scatter Chart to draw the yield curve. rates, perhaps because of inflation, in the future. Points 1 Inscrting a Chart On the Insert tab, in the Charts group, click the arrow next to Insert Scatter (X,Y) or Bubble Chart and choose Scatter Chart Find & Select Selecting Data Series Then in Select Data Source window, delete any series created automatically. Add new series for the security market line using cells D21:D25 for the X values and cells F6:F10 for the Y values. Do not name the series. 49 50 51 52 53 54 55 56 57 Edit Chart Elements Add a chart title and choose the Above Chart option. Replace Chart Title with Yield Curve. Add axis titles. Replace Axis Title for the horizontal axis with Time to Maturity (months) and Axis Title for the vertical axis with Yield to Maturity. 3 In cell D44, dctcrmine whcthcr the slope of the yield curve is upward or downward. 4 In cell 144, determine whether the rates are going to be higher or lower. 5 In cell K44, determine which level of inflation is the reason for specified direction of future interest rates. 6 Save the workbook. Close the workbook and then cxit Excel. Submit the workbook as directed. 1 1 1 0 11.1 + Ready Smart Zutter. Problem P11.1 (Excel). V1 Project Description: In this problem, you will draw the yield curve and describe the results using an expectations theory. Steps to Perform: Step Instructions Points Possible 1 1 Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. Start Excel. Download and open the workbook named: Smart_Zutter_Problem_11.1_Start.xlsx In cells D21:D25, by using cell references, convert the time to maturity to months. 2 In cells C27:F40, insert a Scatter Chart to draw the yield curve. 4. Inserting a Chart On the Insert tab, in the Charts group, click the arrow next to Insert Scatter (XY) or Bubble Chart and choose Scatter Chart. Selecting Data Series Then in Select Data Source window, delete any series created automatically. Add new series for the security market line using cells D21:D25 for the X values and cells F6:F10 for the Y values. Do not name the series. Edit Chart Elements Add a chart title and choose the Above Chart option. Replace Chart Title with Yield Curve. Add axis titles. Replace Axis Title for the horizontal axis with Time to Maturity (months) and Axis Title for the vertical axis with Yield to Maturity. 3 In cell 044, determine whether the slope of the yield curve is upward or downward. 1 4 In cell 144, determine whether rates are going to be higher or lower. 1 5 In cell K44, determine which level of inflation is the reason for specified direction of future interest rates. 1 6 0 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. O Total Points 8 O Focus A firm wishing to evaluate interest rate behavior has gathered yield data on five U.S. Treasury securitics, cach having a different maturity and all measured at the same point in time. The summarized data follow. Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula acron a wow or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used the directions will specify the use of that function. Do not npe in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Giver Data section. 7 U.S. Treasury Security A B C D E Time To Maturity 1 year 10 years 6 months 20 years Yield 12.6% 11.2% 13.0% 11.0% 11.4% Sycurs To Do u. Draw the yield curve associated with these data, b. Describe the resulting yield curve in parte, and explain what it says about the direction of future interest rates under the expectations theory. 10 11 12 13 14 15 16 17 18 19 20 21 22 Solution u. Draw the yield curve associated with these dala, Time to Maturity (months) U.S. Treasury Security A B c D 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 11.1 + Ready 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 b. Describe the resulting yield curve in part a, and explain what it says about the direction of future interest rates under the expectations theory 43 44 The yield curve is clearly sloping. Expectations theory would interpret such a curve as forecasting a 45 46 47 Requirements 48 1 In cells D21:125, hy using cell references, convert the time to maturily to months. 2 In cells C27:540, insert a Scatter Chart to draw the yield curve. rates, perhaps because of inflation, in the future. Points 1 Inscrting a Chart On the Insert tab, in the Charts group, click the arrow next to Insert Scatter (X,Y) or Bubble Chart and choose Scatter Chart Find & Select Selecting Data Series Then in Select Data Source window, delete any series created automatically. Add new series for the security market line using cells D21:D25 for the X values and cells F6:F10 for the Y values. Do not name the series. 49 50 51 52 53 54 55 56 57 Edit Chart Elements Add a chart title and choose the Above Chart option. Replace Chart Title with Yield Curve. Add axis titles. Replace Axis Title for the horizontal axis with Time to Maturity (months) and Axis Title for the vertical axis with Yield to Maturity. 3 In cell D44, dctcrmine whcthcr the slope of the yield curve is upward or downward. 4 In cell 144, determine whether the rates are going to be higher or lower. 5 In cell K44, determine which level of inflation is the reason for specified direction of future interest rates. 6 Save the workbook. Close the workbook and then cxit Excel. Submit the workbook as directed. 1 1 1 0 11.1 + Ready Smart Zutter. Problem P11.1 (Excel). V1 Project Description: In this problem, you will draw the yield curve and describe the results using an expectations theory. Steps to Perform: Step Instructions Points Possible 1 1 Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. Start Excel. Download and open the workbook named: Smart_Zutter_Problem_11.1_Start.xlsx In cells D21:D25, by using cell references, convert the time to maturity to months. 2 In cells C27:F40, insert a Scatter Chart to draw the yield curve. 4. Inserting a Chart On the Insert tab, in the Charts group, click the arrow next to Insert Scatter (XY) or Bubble Chart and choose Scatter Chart. Selecting Data Series Then in Select Data Source window, delete any series created automatically. Add new series for the security market line using cells D21:D25 for the X values and cells F6:F10 for the Y values. Do not name the series. Edit Chart Elements Add a chart title and choose the Above Chart option. Replace Chart Title with Yield Curve. Add axis titles. Replace Axis Title for the horizontal axis with Time to Maturity (months) and Axis Title for the vertical axis with Yield to Maturity. 3 In cell 044, determine whether the slope of the yield curve is upward or downward. 1 4 In cell 144, determine whether rates are going to be higher or lower. 1 5 In cell K44, determine which level of inflation is the reason for specified direction of future interest rates. 1 6 0 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. O Total Points 8 O Focus
