term structure in Excel

Learning Goal: I’m working on a excel discussion question and need an explanation and answer to help me learn.

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:
    1. Estimate the par yield curve using the yield function in Excel. Place your answers in the column Calculated par yield.
    2. 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)
    3. 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.
    4. 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.
    1. 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: I only want you to 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.
    2. If the answers in #4 and #5 are the same, explain why. If they are different, explain why.

If you need answers to this assignment, WhatsApp/Text to +1 646 978 1313  

or send us an email to admin@shrewdwriters.com and we will reply instantly. We provide original answers that are not plagiarized. Please, try our service. Thanks

Leave a Reply

Your email address will not be published.