Create spreadsheet models to price coupon bonds, compute duration, and examine reinvestment risk. The purpose of this
Question:
Create spreadsheet models to price coupon bonds, compute duration, and examine reinvestment risk. The purpose of this assignment is to get comfortable with basic spreadsheet modeling and to reinforce the concepts of bond pricing, present value, and duration. Please display formulas using the FORMULATEXT command. Some of these have been prepopulated in the homework template.
This assignment has three parts. You should upload a single Excel spreadsheet. Please do not insert or delete rows/columns and be sure answers are in the indicated shaded regions. Please be sure to include your name and student ID on the first sheet.
The first worksheet of the homework template (‘Part 1’) provides a basic bond pricing spreadsheet. This is similar to the bond pricing sheet we used in class. The assignment is to perform a sensitivity analysis of bond prices to changes in the YTM. Please include the following outputs:
 The bond price for the displayed inputs – in cell C19.
 A table of bond prices for YTMs ranging from 1% to 10% in increments of 0.5% calculated using locked formulas and named cells (using the names indicated in parentheses). Please consider two maturities: 8 periods and 20 periods.
 A corresponding table of bond prices for the same YTMs, but calculated using an Excel Data Table. For this table, please also consider possible maturities of 8 periods and 20 periods.
 A chart of bond prices (on the yaxis) versus YTMs (on the xaxis). Again, the YTMs should run from 1% to 10% in increments of 0.5%. Please plot bond prices for both maturities from (2) & (3) above. That is, there should be one line for the 4year bond and one line for the 10 year bond. Please format the plot so that the yaxis runs from $50 to $175 in increments of $25. Label the lines and make the 10year bond line dashed. (You can accomplish this using the chart tools formatting menu.)
Aside from the different maturities and YTMs, all other inputs should be kept the same as the template provides.
Bond Pricing  Plot of Bond Prices:  
Inputs  
Annual Coupon Rate (CR)  6%  
Yield to Maturity (Annualized) (YTM)  3.69%  
Number of Payments / Year (NOP)  2  
Number of Periods to Maturity (T)  8  
Face Value (M)  100  
Calculation  
Discount Rate / Period (DR)  #N/A  
Coupon Payment (I)  #N/A  
Outputs  
Bond Price  #N/A 
Named Cells/Locked Cells Method  Data Table Method  
Bond Price For Number of Periods to Maturity of:  Bond Price For Number of Periods to Maturity of:  
YTM  8  20  8  20  
1.0%  YTM:  1.0%  
Displayed Formula:  #N/A  Displayed Formula:  #N/A 
 Calculate the duration of a bond with a face of $1,000, a 6% coupon rate, and a 5% YTM with 1 payment per year and a maturity of 10 years, using two different methods:
 Weighting by the present value of cash flows
 The Excel DURATION formula
 Calculate the following:
 What are the approximate percentage and dollar changes in price for a 1% change in interest rates using the duration approximation formula?
 What are the actual percentage and dollar changes in price if interest rates increase by 1%?

Duration Calculation Inputs Annual Coupon Rate (CR_P2) 6% Yield to Maturity (Annualized) (YTM_P2) 5.00% Number of Payments / Year (NOP_P2) 1 Number of Periods to Maturity (T_P2) 10 Face Value (M_P2) 1000 Calculation Discount Rate / Period (DR_P2) Coupon Payment (I_P2) Outputs Price Duration (weighting by PV of CFs) Duration (using DURATION formula)

Calculation Period Cash Flows PV Weight 1 2 3 4 5 6 7 8 9 10 
Part b) Inputs Change in interest rate 1% Outputs Approx % change in price using duration Approx $ change in price using duration Price if YTM increases by 1% Actual % change in price Actual $ change in price
Consider a guaranteed annuity contract issued by a life insurance company with a rate of 3.08% and a term of 20 years. For a $100,000 nominal amount, the insurance company is promising to pay $183,437.53 (=100,000*(1.0308)^20) to the holder of the guaranteed annuity contract in 20 years. (In practice, this lump sum would be spread out over a subsequent period as an annuity; for simplicity, we will assume a lump sum payment.) We want to know the interest rate risk exposure to the company if it funds this liability using a 30year bond with a coupon rate of 3.08% issued at par. To do so, assume that in the first year, rates change to one of the five possible values in row 18 and then stay constant for the next 20 years. For each rate change, calculate two things: (1) the future value (as of 20 years down the road) of each coupon payment made by the 30year bond for the next 20 years, and (2) the value at year 20 of the bond used to fund the obligation. For (1), assume the coupons are reinvested at the new prevailing interest rate. The sum of the reinvested coupons and the value of the bond are the total future value of the assets used to finance the annuity contract.
What is the duration of the 30year bond as of today? What is the duration of the guaranteed annuity contract? Briefly describe how the value of the reinvested coupons and the value of the bond change as a function of interest rate changes. Will the insurance company be able to meet its obligation to the purchaser of the guaranteed annuity contract?
Reinvestment Risk  
Inputs  
Annual Coupon Rate (CR_P3)  3.08%  
Yield to Maturity (Annualized) (YTM_P3)  3.08%  
Number of Payments / Year (NOP_P3)  1  
Number of Periods to Maturity (T_P3)  30  
Face Value (M_P3)  100,000  
Maturity of Liability  20  
Calculations  
Discount Rate / Period (DR_P3)  
Coupon Payment (I_P3)  
Duration of bond  
Future value of annuity contract  
Duration of annuity contract 
Rates changes to:  
Future value at time 20 of payment #:  3.08%  2.00%  4.00%  3.00%  3.20% 
1  
2 
Data Analysis and Decision Making
ISBN: 9780538476126
4th edition
Authors: Christian Albright, Wayne Winston, Christopher Zappe