The goal of this assignment is to become familiar with using Excel to (1) construct a zero- coupon spot curve from an empirical on-the-run (par) curve and (2) price off the zero-coupon curve. The assignment will be graded out of ten (10) points.
Important:
SHOW YOUR WORK
DO NOT ROUND any numbers (you may format your answers such that the underlying data appear rounded, but do not actually round the answers)
All answers must go in the second tab. Use this Excel file to complete the following:
Estimate the par yield curve using the yield function in Excel. Place your answers in the column Calculated par yield.
Interpolate a portion of the par yield curve using piecewise linear interpolation. Specifically, interpolate from the 6-month to the 5-year tenor. (You do not have to interpolate anything beyond 5 years.) The par term structure should include one interpolated yield at each six-month interval. Place your answers in the column Interpolated par yield. (You can use the formula from slide 25 of Lecture 2 for the interpolation): ? = ?1 + (?−?1)∗(?2−?1) (?2−?1)
Using the interpolated par term structure from #2, derive the first six zero-coupon rates using the bootstrapping method discussed in Lecture 2. That is, derive the zero-coupon rate for the first six tenors (from 6 months to 3 years). Place your answers in the column Zero-coupon yield (bootstrapped). The ZC yields should be expressed in annual terms.
Use the zero-coupon rates you derived in #3 to price a 3-year bond that pays a 3% coupon. (Assume zero default or liquidity risk.) Place your answer in the column Pricing a 3-year 3% bond: ZC curve. Express your answer out to 6 decimal places.
Use the 3-year YTM that you calculated in the process of creating the par curve in #1 to price the same 3-year, 3% bond. (Note: Only use the 3-year discount rate here, not a series of discount rates.) Place your answer in the columns Pricing a 3-year 3% bond: par 3-year YTM. Express your answer out to 6 decimal places.
If the answers in #4 and #5 are the same, explain why. If they are different, explain why.
