TERM STRUCTURE OF INTEREST RATES

 TERM STRUCTURE OF INTEREST RATES

1. Attached is a “Bootstrapping example Excel doc.” Please replicate this “Bootstrapping
   example Excel doc” using the following data on these 6 treasury bonds:
   PLEASE OBTAIN THIS INFORMATION FROM A BLOOMBERG TERMINAL
   Maturity Coupon Bid Ask Change Ask Yield
   7/31/22
   1/31/23
   7/31/23
   1/31/24
   7/31/24
   1/31/25
   These 6 treasury bonds are from Bloomberg and pay semi-annual coupons. Annualize the rates
   (this is shown in the last line in the attached “Bootstrapping example Excel file attached”.
2. Need a Word Doc Report organized with sections. Number the tables and figures in the Word
   Doc. Discuss the data: a) Present a table in the main body of the Word Doc Report on the 6
   treasury bonds above showing: maturity, coupon, bid, ask, change and asked yield.
3. In the Word doc Report, construct a timeline. It will be a table, where each row contains cash
   flows of one of the six treasury bonds and each column represents a time-period (the cash flow
   calculations are shown in the “Bootstrapping example Excel doc” that you will replicate using the
   data on the above 6 treasury bonds.
4. Show details of all your calculations in the main body of the Word Doc Report. The data on
   the 6 bonds is from Bloomberg and are in ticks: a) Show how you converted prices from
   ticks into dollars and cents (the attached 3-page file shows how to convert ticks to
   dollars and cents). Show how you calculated semi-annual coupon payments.
5. Show how you calculated each of the six rates of the term structure: a) Show actual formulas,
   not Excel formulas. b) Show equations, which you used to find each of the six semi-annual rates
   first as formulas with symbols and then with the data plugged into them (use Insert – Equation
   in Word to type your equations like in the “Bootstrapping example Excel file attached) c)
   Second, show how you plugged in your data into these formulas.
6. Using the Excel Chart menu, “draw” the graph of the term structure: time on the horizontal
   axis, corresponding annualized term structure rates on the vertical axis. Paste and copy this
   graph into the Word Doc Report. Comment on the graph of the term structure, the implications
   observed (is it increasing or decreasing) and comment on the economic conditions as perceived
   by market participants.
7. Calculate yields to maturity (YTM) on each bond (you can use the IRR function in Excel).
   Compare YTMs to the term structure rates: a) use the graph: time periods on the horizontal axis
   a
   n
   d
   b
   o
   t
   h, c
   o
   r
   r
   e
   s
   p
   o
   n
   din
   g
   t
   e
   r
   m
   s
   t
   r
   u
   c
   t
   u
   r
   e
   r
   a
   t
   e
   s
   a
   n
   d
   Y
   T
   M
   s, o
   n
   t
   h
   e
   v
   e
   r
   tic
   al a
   xis. b
   )
   C
   o
   m
   m
   e
   n
   t
   o
   n
   t
   his
   g
   r
   a
   p
   h.

Sample Solution