Question

A Modify the spreadsheet 3.1 of "Duration Spreadsheet" to calculate the duration of the 4-year 8% semiannual coupon bond, YTM=10%. B. 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. Modify the spreadsheet 3.1 of "Duration Spreadsheet" to calculate the duration of the 4-year 8% annual coupon bond, YTM=10%. D. Verify your solution of part C) using spreadsheet 3.3. E. Assume you will be paying $10,000 a year in tuition expenses at the end of the next 2 years (i.e. $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.

Answer 1

A). Duration for a 4-year, 8% coupon rate, 10% YTM semi-annual bond = 3.48 years

CF/(1+YTM)^n PV/BP Formula Time until payment (n) Cash flow (CF) 0.5 1.5 PV of CF Weight (w) 38.14 0.04 36.36 0.04 34.67 0.04 33.06 0.04 31.52 0.03 30.05 0.03 28.65 0.031 710.33 0.75 942.79 Duration (in years) w*n Weighted Time 0.02 0.04 0.06 0.07 0.08 0.10 0.11 3.01 1040 Bond price (BP) 3.481

B). Macaulay duration (using formula) = 3.48 years

01-01-2000 01-01-2004 Inputs: Settlement date Maturity date Coupon rate Yield to maturity Coupons per year Outputs: Macaulay duration Modified duration 10% 3.48 3.32

C). Duration for a 4-year, 8% coupon rate, 10% YTM annual bond = 3.56 years

CF/(1+YTM)^n PV/BP wen Formula Time until payment (n) 1 Cash flow (CF) 80 80 80 1080 Bond price (BP) Weighte PV of CF Weight (w) d Time 72.73 0.08 0.08 66.12 0.07 0.14 60.11 0.06 0.19 737.65 0.79 3.15 936.60 Duration (in years) 3.56

D). Macaulay duration (using formula) = 3.56 years

01-01-2000 01-01-2004 | Inputs: Settlement date Maturity date Coupon rate Yield to maturity Coupons per year Outputs: Macaulay duration Modified duration 3.56 3.24

E). Duration of your obligation = 1.48 years

Formula CF/(1+8%)^n PV/BP win Time until payment (n) Cash flow (CF) 10000 10000 Bond price (BP) PV of CF Weight (w) Weighted Time 9259.26 0.52 0.52 8573.39 0.48 0.96 17832.65 Duration (in years) 1.48