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 (Le. $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. p up- AZ A C D E F H B Period 3 4 A. 8% coupon bond 5 6 7 8 9 1 2 3 (Years) 0.5 1.0 1.5 2.0 Cash flow 40 40 40 1040 4% per period) 38.462 36.982 35.560 888.996 1000.000 Weight 0.0385 0.0370 0.0356 0.8890 1.0000 G Column (F) 0.0192 0.0370 0.0533 1.7780 1.8875 4 Sum: Submiss 10 "Chapte 11 OO Noua 12 en the 13 culate th 14 readsheel 15 ows you estion, y 16 Semiannual int rate: 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 duratior 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% per period) -04/(1+58516)AB4 -D5/(1+$8$16)485 --06/(1+$B$16)AB6 -07/(1+$B$16)487 - SUM(E4:E7) Weight E4/ES8 -ES/ESB -E6/E58 -E7/E88 -SUM(F4:F7) Column (F) -F4C4 -F5C5 -F6 C6 -F7C7 =SUM(G4:47) A B 1 2 Period 4 A. 8% coupon bond 1 5 2 6 3 7 4 Submiss 8 Sure "Chapte 9 10 Open the 11 calculate th 12 spreadsheet 13 shows you 14 question, y 15 to the word 16 Semiannual int rate: 0.04 A. (0.6) M 17 duration 18 8. (0.6) Ve 19 settleme 20 C (0.6) M 21 duration 22 D. (0.6) Vel 23 E. (0.7) As: 24 next 2 y 25 rate is 26 duratior 27 ON 28 29 30 31 32 33 34 35 36 37 38 39 40 MM ow Spreadsheet 3.1 Spreadsheet 3.2 Spreadsheet 3.3 arde Devel 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,B6) shows you A 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, y 16 to the word 17 A. (0.6) M 18 duration 19 B. (0.6) Ve 20 settleme 21 C. (0.6) M 22 duration 23 D. (0.6) Ver 24 E. (0.7) As: 25 next 2 y 26 rate is E 27 duration 28 29 30 31 32 33 34 35 36 37 38 39 40 WNOCO in WN om 41 Spreadsheet 3.1 Spreadsheet 3.2 Spreadsheet 3.3 + 36 words Raad 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 (Le. $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. p up- AZ A C D E F H B Period 3 4 A. 8% coupon bond 5 6 7 8 9 1 2 3 (Years) 0.5 1.0 1.5 2.0 Cash flow 40 40 40 1040 4% per period) 38.462 36.982 35.560 888.996 1000.000 Weight 0.0385 0.0370 0.0356 0.8890 1.0000 G Column (F) 0.0192 0.0370 0.0533 1.7780 1.8875 4 Sum: Submiss 10 "Chapte 11 OO Noua 12 en the 13 culate th 14 readsheel 15 ows you estion, y 16 Semiannual int rate: 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 duratior 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% per period) -04/(1+58516)AB4 -D5/(1+$8$16)485 --06/(1+$B$16)AB6 -07/(1+$B$16)487 - SUM(E4:E7) Weight E4/ES8 -ES/ESB -E6/E58 -E7/E88 -SUM(F4:F7) Column (F) -F4C4 -F5C5 -F6 C6 -F7C7 =SUM(G4:47) A B 1 2 Period 4 A. 8% coupon bond 1 5 2 6 3 7 4 Submiss 8 Sure "Chapte 9 10 Open the 11 calculate th 12 spreadsheet 13 shows you 14 question, y 15 to the word 16 Semiannual int rate: 0.04 A. (0.6) M 17 duration 18 8. (0.6) Ve 19 settleme 20 C (0.6) M 21 duration 22 D. (0.6) Vel 23 E. (0.7) As: 24 next 2 y 25 rate is 26 duratior 27 ON 28 29 30 31 32 33 34 35 36 37 38 39 40 MM ow Spreadsheet 3.1 Spreadsheet 3.2 Spreadsheet 3.3 arde Devel 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,B6) shows you A 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, y 16 to the word 17 A. (0.6) M 18 duration 19 B. (0.6) Ve 20 settleme 21 C. (0.6) M 22 duration 23 D. (0.6) Ver 24 E. (0.7) As: 25 next 2 y 26 rate is E 27 duration 28 29 30 31 32 33 34 35 36 37 38 39 40 WNOCO in WN om 41 Spreadsheet 3.1 Spreadsheet 3.2 Spreadsheet 3.3 + 36 words Raad