Week 1 Spreadsheet Assignment

In this assignment 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 pre- populated in the homework template.

This assignment has threeparts. 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.

Part 1: Bond pricing sensitivity

The firstworksheet of the homework template ('Part 1') provides a basic bond pricing spreadsheet. This issimilar to the bond pricing sheet we used inclass. The assignment isto perform a sensitivity analysis of bond prices to changes inthe YTM. Please include the followingoutputs:

1. The bond price for the displayed inputs - incellC19.
2. A table of bond prices for YTMs ranging from 1% to 10% inincrements of 0.5% calculated using locked formulas and named cells (using the names indicated in parentheses). Please consider two maturities: 8 periods and 20periods.
3. A corresponding table ofbond 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.
4. A chart of bond prices (on the y-axis) versus YTMs (on the x-axis). Again,the YTMs should run from 1% to 10% inincrements of 0.5%. Pleaseplot bond prices for both maturities from (2) & (3) above. That is,there should beone line for the 4-year bond and one linefor the 10 yearbond. Please format the plot sothat the y-axis runs from $50to

$175 in increments of $25. Label the lines and make the 10-year 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.

Part 2: Duration and price sensitivity

1. 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 differentmethods:
   1. Weighting by the present value of cashflows
   2. The Excel DURATIONformula
2. Calculate thefollowing:
   1. What are the approximatepercentage and dollarchanges inprice for a 1% change in interest rates using the duration approximationformula?

1. What are the actualpercentage and dollar changes inprice if interest rates increase by 1%?

Part 3: Duration, reinvestment risk, & price risk

Consider a guaranteed annuity contract issuedby a lifeinsurance company with a rate of 3.08% and a termof20 years. For a $100,000 nominal amount, the insurance company ispromising topay $183,437.53 (=100,000*(1.0308)^20) to the holderof the guaranteed annuity contract in20 years. (In practice, this lump sum would bespread out over a subsequent period as an annuity; for simplicity, we will assume a lumpsum payment.) Wewant to know the interest rate risk exposure to the company ifitfunds this liability using a 30-year bond with a coupon rate of 3.08% issuedat par. To doso, assumethat inthe first year,rates change to one of the five possible values inrow 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 30-year bond for the next20 years, and (2) the value at year20 of the bond used to fund the obligation. For (1), assumethe coupons are reinvested at the new prevailing interest rate. The sum ofthe reinvested coupons and the value ofthe bond are the total future value ofthe assets used to finance the annuitycontract.

What isthe duration of the 30-year bond as oftoday? What isthe 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 beable to meetits obligation tothe purchaser of the guaranteed annuitycontract?

Part 1:

Duration Calculation Inputs Calculation Annual Coupon Rate (CR_P2) 6% Period Cash Flows PV Weight 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 (1_P2) Outputs Price Duration (weighting by PV of CFS) Duration (using DURATION formula) 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 priceinvestment Risk In puts Annual Coupon Rate (CR_P3) 3.08% Yield to Maturity (Annualized) (YTM_P3) 3.08% Number of Payments / Year (NOP_P3) 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 ([_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% 15 16 17 18 19 20 Value of Bond at Time 20 Total Value of Bond and Reinvested Proceeds at Time 20 Dis played Formula for Year 1 Coupon FV: #N/A Displayed Formula for Bond Value at t=20:" #N/A Displayed Formula for Total Value: " #N/A Des cription of how value of reinvested coupons and value of the bond at t=20 changes with interest rates:D G H B C E F A Plot of Bond Prices: Bond Pricing Inputs Annual Coupon Rate (CR) 6% Yield to Maturity (Annualized) (YTM) 3.69% Number of Payments / Year (NOP) 2 10 11 Number of Periods to Maturity (T) 8 12 Face Value (M) 100 13 14 Calculation Discount Rate / Period (DR) #N/A 15 #N/A 16 Coupon Payment (1) 17 18 Outputs 19 Bond Price #N/A 20 Named Cells/Locked Cells Method Data Table Method 21 Bond Price For Number of Bond Price For Number of Periods to Maturity of: Periods to Maturity of: 22 8 20 8 20 23 YTM 1.0% YTM: 1.0% 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 Displayed Formula: #N/A 44 Displayed Formula: #N/A