In Excel, with step by step

Duration

Part 1: Duration and Coupon Interest Consider a five-year $1,000 face value, Treasury bond with a 10 percent yield to maturity selling at par.

A) Calculate the duration if the semiannual coupon is 7% (5 Points)

B) Calculate the duration if the semiannual coupon is 12% (5 Points)

C) Calculate the duration if the semiannual coupon is 17% (5 Points)

D) Plot the relationship between coupon interest rate (x-axis) and the duration (y-axis). Be sure to label the y-axis, x-axis, and chart title (5 Points)

In your report, identify and comment on the relationship between duration and coupon interest rate.

Part 2: Duration and Rate of Return Consider a five-year $1,000 face value, Treasury bond with a 5 percent semiannual coupon selling at par.

E) Calculate the duration if the yield to maturity is 7% (5 Points)

F) Calculate the duration if the yield to maturity is 12% (5 Points)

G) Calculate the duration if the yield to maturity is 17% (5 Points)

H) Plot the relationship between yield to maturity (x-axis) and the duration (y-axis). Be sure to label the y-axis, x-axis, and chart title (5 Points)

In your report, identify and comment on the relationship between duration and yield to maturity.

Part 3: Duration and Maturity Consider a $1,000 face value, Treasury bond with a 10 percent semiannual coupon selling at par with a 12 percent yield to maturity.

I) Calculate the duration if the maturity is in 6 years (5 Points)

J) Calculate the duration if the maturity is in 4 years (5 Points)

K) Calculate the duration if the maturity is in 2 years (5 Points)

L) Plot the relationship between maturity (x-axis) and the duration (y-axis). Be sure to label the y-axis, x-axis, and chart title (5 Points)

In your report, identify and comment on the relationship between duration and maturity.