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.

Part 1: Bond pricing sensitivity

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:

1. The bond price for the displayed inputs – in cell C19.
2. 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.
3. 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.
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% 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 4-year bond and one line for the 10 year bond. Please format the plot so that the y-axis runs from $50 to $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.

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

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 different methods:
   1. Weighting by the present value of cash flows
   2. The Excel DURATION formula
2. Calculate the following:
   1. What are the approximate percentage and dollar changes in price for a 1% change in interest rates using the duration approximation formula?
   2. What are the actual percentage and dollar changes in price if interest rates increase by 1%?

   3. 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)

3. Calculation
   Period	Cash Flows	PV	Weight
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10

4. 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

Part 3: Duration, reinvestment risk, & price risk

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 30-year 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 30-year 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 30-year 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

Answer rating: 100% (QA)

Part 1 Bond Pricing Inputs Annual Coupon Rate CR 6 Yield to Maturity Annualized YTM 369 Number of Payments Year NOP 2 Number of Periods to Maturity T ... View the full answer