please show work and ill leave a like!

Open the "Duration Spreadsheet". This spreadsheet currently shows you how to calculate the duration of a 2-year 8% semiannual coupon bond, YTM=8%. Note: spreadsheet 3.2 shows you the formulas used in spreadsheet 3.1. Spreadsheet 3.3 shows you the excel build-in function to calculate duration. For each part of the question, you must mark the final solution and provide the excel table (copy and paste to the word file). A. (0.6) Modify the spreadsheet 3.1 of "Duration Spreadsheet" to calculate the duration of the 4-year 8% semiannual coupon bond, YTM=10%. B. (0.6) Verify your solution of part A) using spreadsheet 3.3. Hint: You can put any settlement date and maturity date as long as the maturity matches the question. C. (0.6) Modify the spreadsheet 3.1 of "Duration Spreadsheet" to calculate the duration of the 4-year 8% annual coupon bond, YTM=10%. D. (0.6) Verify your solution of part C) using spreadsheet 3.3. E. (0.7) Assume you will be paying $10,000 a year in tuition expenses at the end of the next 2 years (ie. $10,000 at end of the year 1, $10,000 at end of the year 2). Market rate is 8%. Modify the spreadsheet 3.1 of "Duration Spreadsheet" to calculate the duration of your obligation? Hint: you can treat this obligation as a bond. ep up- AZ fx A B C E F G H BO Period 0 3 4 A. 8% coupon bond 5 6 7 8 9 1 2 3 (Years) 0.5 1.0 1.5 2.0 D Cash flow 40 40 40 1040 4% per porlod) 38.462 36.982 35.560 888.996 1000.000 Weight 0.0385 0.0370 0.0356 0.8890 1.0000 Column (F) 0.0192 0.0370 0.0533 1.7780 1.8875 4 Sum: Submiss 10 "Chapte 11 Douw 12 en the culate th 13 14 readsheel 15 ows you 16 Semiannual int rate: estion, y 0.04 17 the word 18 Weight - Present value of each payment (Column E) divided by the bond price (0.6) M 19 duration 20 (0.6) Ve 21 settleme 22 (0.6) M 23 duration 24 (0.6) Ver 25 (0.7) As: 26 next 2 y 27 rate is E 28 duration 29 30 31 32 33 34 35 36 37 38 39 40 41 42 Spreadsheet 3.1 Spreadsheet 3.2 Spreadsheet 3,3 ards Ready + + Keep up B17 D F G Column (C) C Time unti Payment (Years) 0.5 1 1.5 2 Cash flow 40 40 40 1040 E PV of CF (Discount rate 5% por period) --04/(1+$8516)AB4 -D5/(1+$8516)485 -06/(1+$B$16)ABG -07/(1+$B$16)487 -SUM(E4:17) Weight E4/ES8 -ES/ESB -E6/ESB -E7/ESB - SUM(F4:F7) Column (F) -F4C4 -F5C5 -F6C6 -F7.07 =SUM(G4:47) A B 1 2 Period 4 A.8% coupon bond 1 5 2 6 3 7 4 Submiss 8 Sumns "Chapte 9 10 Open the 11 calculate th 12 spreadsheet 13 shows you 14 question, 15 to the word 16 Semiannual int rate: 0.04 A. (0.6) M 17 duration 18 6. (0.6) Ve 19 settleme 20 C (0.6) M 21 duration 22 D. (0.6) Ver 23 E. (0.7) As: 24 next 2 y 25 rate is 26 duration 27 28 29 30 31 32 33 34 35 36 37 38 39 40 Spreadsheet 3.1 arde Devel M o un Spreadsheet 3.2 Spreadsheet 3.3 To keep up C20 fx D m F B 1/1/00 1/1/02 0.08 0.08 2 -DATE(2000,1,1) -DATE(2002,1,1) 0.08 0.08 2 1.8875 - DURATION(B2,B3,B4,B5,B6) 1.8149 -MDURATION(B2,B3,B4,B5,86) shows you 2 Settlement date 3 Maturity date 4 Coupon rate 5 Yield to maturity 6 Coupons per year 7 8 Outputs Submiss 9 Macaulay duration *Chapte 10 Modified duration 11 Open the 12 calculate th 13 spreadsheet 14 15 question, yo 16 to the word 17 A. (0.6) M 18 duration 19 B. (0.6) Ve 20 settlemt 21 C. (0.6) M 22 duration 23 D. (0.6) Ves 24 E. (0.7) As: 25 next 2 y 26 rate is & 27 duration 28 29 30 31 32 33 34 35 36 37 38 39 40 ou WNO 00 Noin WN 41 Spreadsheet 3.1 Spreadsheet 3.2 Spreadsheet 3.3 + 36 words Read Open the "Duration Spreadsheet". This spreadsheet currently shows you how to calculate the duration of a 2-year 8% semiannual coupon bond, YTM=8%. Note: spreadsheet 3.2 shows you the formulas used in spreadsheet 3.1. Spreadsheet 3.3 shows you the excel build-in function to calculate duration. For each part of the question, you must mark the final solution and provide the excel table (copy and paste to the word file). A. (0.6) Modify the spreadsheet 3.1 of "Duration Spreadsheet" to calculate the duration of the 4-year 8% semiannual coupon bond, YTM=10%. B. (0.6) Verify your solution of part A) using spreadsheet 3.3. Hint: You can put any settlement date and maturity date as long as the maturity matches the question. C. (0.6) Modify the spreadsheet 3.1 of "Duration Spreadsheet" to calculate the duration of the 4-year 8% annual coupon bond, YTM=10%. D. (0.6) Verify your solution of part C) using spreadsheet 3.3. E. (0.7) Assume you will be paying $10,000 a year in tuition expenses at the end of the next 2 years (ie. $10,000 at end of the year 1, $10,000 at end of the year 2). Market rate is 8%. Modify the spreadsheet 3.1 of "Duration Spreadsheet" to calculate the duration of your obligation? Hint: you can treat this obligation as a bond. ep up- AZ fx A B C E F G H BO Period 0 3 4 A. 8% coupon bond 5 6 7 8 9 1 2 3 (Years) 0.5 1.0 1.5 2.0 D Cash flow 40 40 40 1040 4% per porlod) 38.462 36.982 35.560 888.996 1000.000 Weight 0.0385 0.0370 0.0356 0.8890 1.0000 Column (F) 0.0192 0.0370 0.0533 1.7780 1.8875 4 Sum: Submiss 10 "Chapte 11 Douw 12 en the culate th 13 14 readsheel 15 ows you 16 Semiannual int rate: estion, y 0.04 17 the word 18 Weight - Present value of each payment (Column E) divided by the bond price (0.6) M 19 duration 20 (0.6) Ve 21 settleme 22 (0.6) M 23 duration 24 (0.6) Ver 25 (0.7) As: 26 next 2 y 27 rate is E 28 duration 29 30 31 32 33 34 35 36 37 38 39 40 41 42 Spreadsheet 3.1 Spreadsheet 3.2 Spreadsheet 3,3 ards Ready + + Keep up B17 D F G Column (C) C Time unti Payment (Years) 0.5 1 1.5 2 Cash flow 40 40 40 1040 E PV of CF (Discount rate 5% por period) --04/(1+$8516)AB4 -D5/(1+$8516)485 -06/(1+$B$16)ABG -07/(1+$B$16)487 -SUM(E4:17) Weight E4/ES8 -ES/ESB -E6/ESB -E7/ESB - SUM(F4:F7) Column (F) -F4C4 -F5C5 -F6C6 -F7.07 =SUM(G4:47) A B 1 2 Period 4 A.8% coupon bond 1 5 2 6 3 7 4 Submiss 8 Sumns "Chapte 9 10 Open the 11 calculate th 12 spreadsheet 13 shows you 14 question, 15 to the word 16 Semiannual int rate: 0.04 A. (0.6) M 17 duration 18 6. (0.6) Ve 19 settleme 20 C (0.6) M 21 duration 22 D. (0.6) Ver 23 E. (0.7) As: 24 next 2 y 25 rate is 26 duration 27 28 29 30 31 32 33 34 35 36 37 38 39 40 Spreadsheet 3.1 arde Devel M o un Spreadsheet 3.2 Spreadsheet 3.3 To keep up C20 fx D m F B 1/1/00 1/1/02 0.08 0.08 2 -DATE(2000,1,1) -DATE(2002,1,1) 0.08 0.08 2 1.8875 - DURATION(B2,B3,B4,B5,B6) 1.8149 -MDURATION(B2,B3,B4,B5,86) shows you 2 Settlement date 3 Maturity date 4 Coupon rate 5 Yield to maturity 6 Coupons per year 7 8 Outputs Submiss 9 Macaulay duration *Chapte 10 Modified duration 11 Open the 12 calculate th 13 spreadsheet 14 15 question, yo 16 to the word 17 A. (0.6) M 18 duration 19 B. (0.6) Ve 20 settlemt 21 C. (0.6) M 22 duration 23 D. (0.6) Ves 24 E. (0.7) As: 25 next 2 y 26 rate is & 27 duration 28 29 30 31 32 33 34 35 36 37 38 39 40 ou WNO 00 Noin WN 41 Spreadsheet 3.1 Spreadsheet 3.2 Spreadsheet 3.3 + 36 words Read