IN AN EXCEL SPREADSCHEET, SCREENSHOT EACH STEP AND FORMULA INPUT FOR EACH ANSWER. FOR THE GRAPHS SCREENSHOT AND INCLUDE EACH STEP (WHAT YOU CLICKED TO CREATE AND PLOT THE GRAPH) THANK YOU

The following table gives the details of five fixed-coupon bonds with varying maturities.

BondA N=1 COUPON RATE=2% FV= $1,000 Bond Price= $988.12

BondB N=2 COUPON RATE=2.25% FV= $1,000 Bond Price=$972.13

BondC N=3 COUPON RATE=2.50% FV= $1,000 Bond Price=$915.47

BondD N=4 COUPON RATE=2.50% FV= $1,000 Bond Price=$901.11

BondE N=5 COUPON RATE=3% FV= $1,000 Bond Price=$982.11

Using the above information:

Calculate the spot rates for each period. Plot the spot rates as a function of time

(treasury rate curve) and comment on the shape of the graph.

Suppose you have two investment options:

Option A: invest in a zero-coupon bond that matures in five years.

Option B: invest in a zero-coupon bond that matures in four years. You will collect your money at the end of year four and wont receive any interest in

the fifth year.

Which of the two investment options would you prefer and why? Justify your

answer by calculating the relevant metric

Using the spot rates calculated in (a), calculate the price of a $1,000 par fixed- coupon bond that matures in three years, and pays a 2% coupon in the first year, 3% coupon in the second year, and 5% coupon in the third year.