Spreadsheet Assignment 2 Instructions: This spreadsheet assignment accounts for 10% of your final grade (10 points) for this course. Each group is required to submit the required excel spreadsheet together with a report (WORD or PDF) to Blackboard and turn in a hard copy before class by the above- stated due date. The spreadsheet must be interactive; i.e, the spreadsheet must automatically generate the answers when the user changes the input. The report should be no more than page (double-spaced, 12 point font, and excluding the front page) and include at least (1) the summary of your answers; and (2) a brief illustration of how you come up with your answers (eg, the Excel fomula used to get your answers, etc). Answer this question in a sheet named term structure. Consider the Treasury securities listed below. Yields to maturity are provided. Create an interactive spreadsheet that generates theoretical spot rates and six-month implied forward rates. Calculate the theoretical spot rates in cells F4 through F13, and implied forward rates in cells G5 through G13. Graph the spot rate curve on a graph, with time to maturity correctly shown on the x-axis. Limit the graph's y-axis range to 1-4% Term Structure Data as of November 15, 2006 Sis-month Term to Dollar Implied Forward Maturity Date un Rate Theoretical Price (Per s100) Maturity (Years) Spot Rate Rate 15-May-07 15-Nov-07 15-May-08 0.50 0.000% 167 % 100 0.000% 1.78% 102.57 1.50 3.625% 1.88% 2.125% 7.500% 15-Nov-08 2.00 2.50 100.19 2.03% 15-May-09 2.16 % 112.93 6.500% 5.625% 15-Nov-09 3.00 198 2.34 % 110.41 2.50% 15-May-10 15-Nov-10 3.50 116.4 114.45 4.00 7.000% 2.65% 2.81% 15-May-11 15-Nov-11 6.250% 4,50 5.00 3.250% 101.48 2.93% Spreadsheet Assignment 2 Instructions: This spreadsheet assignment accounts for 10% of your final grade (10 points) for this course. Each group is required to submit the required excel spreadsheet together with a report (WORD or PDF) to Blackboard and turn in a hard copy before class by the above- stated due date. The spreadsheet must be interactive; i.e, the spreadsheet must automatically generate the answers when the user changes the input. The report should be no more than page (double-spaced, 12 point font, and excluding the front page) and include at least (1) the summary of your answers; and (2) a brief illustration of how you come up with your answers (eg, the Excel fomula used to get your answers, etc). Answer this question in a sheet named term structure. Consider the Treasury securities listed below. Yields to maturity are provided. Create an interactive spreadsheet that generates theoretical spot rates and six-month implied forward rates. Calculate the theoretical spot rates in cells F4 through F13, and implied forward rates in cells G5 through G13. Graph the spot rate curve on a graph, with time to maturity correctly shown on the x-axis. Limit the graph's y-axis range to 1-4% Term Structure Data as of November 15, 2006 Sis-month Term to Dollar Implied Forward Maturity Date un Rate Theoretical Price (Per s100) Maturity (Years) Spot Rate Rate 15-May-07 15-Nov-07 15-May-08 0.50 0.000% 167 % 100 0.000% 1.78% 102.57 1.50 3.625% 1.88% 2.125% 7.500% 15-Nov-08 2.00 2.50 100.19 2.03% 15-May-09 2.16 % 112.93 6.500% 5.625% 15-Nov-09 3.00 198 2.34 % 110.41 2.50% 15-May-10 15-Nov-10 3.50 116.4 114.45 4.00 7.000% 2.65% 2.81% 15-May-11 15-Nov-11 6.250% 4,50 5.00 3.250% 101.48 2.93%