8.2 Using It To Price A Coupon Bond

Problem. Given the yield curve as published by the financial press, consider a coupon bond has a face

value of $1,000, an annual coupon rate of 5.0%, makes 2 (semiannual) coupon payments per year, and

8 periods to maturity (or 4 years to maturity). What is price and yield to maturity of this coupon bond

based on the Annual Percentage Rate (APR) convention? What is price and yield to maturity of this

coupon bond based on the Effective Annual Rate (EAR) convention?

Solution Strategy. We will use the yield curve you entered in The Yield Curve - Obtaining It From

Bond Listings. We will calculate the bond price as the present value of the bond’s cash flows, where each

cash flow is discounted based on the correspond yield on the yield curve (e.g., a cash flow in year three

will be discounted based on the yield curve's yield at year three). We will use Excel’s RATE function to

determine the yield to maturity of this coupon bond.

FIGURE 8.2 Spreadsheet Model of The Yield Curve – Using It To Price A Coupon Bond.

How To Build This Spreadsheet Model.

1. Start with the Bond Listings Spreadsheet. Open the spreadsheet that you created for The Yield

Curve – Obtaining It From Bond Listings and immediately save the spreadsheet under a new

name using the File | Save As command.

2. Enter The Bond Inputs and Name Them. Enter 0 in cell B20. This will serve as a switch

between the APR and the EAR rate conventions. To highlight which rate convention in use, enter

=IF($B$20=1,"Effective Annual Rate","Annual Percentage Rate") in cell C20. Enter the

other bond inputs into the range B21:B25 and then name each one. Put the cursor on cell B21,

click on Insert | Name | Define, enter CR in the Names in Workbook box, and click on OK. Put

the cursor on cell B22 and repeat the process to name it NOP. Repeat the process to give the cells

B23, and B24 the names N and M, respectively.

3. Calculate the Coupon Payment. The formula is Coupon Payment = Coupon Rate * Face Value /

(Number of Payments / Year). Enter =CR*M/NOP in cell B27 and use the process above to give

the cell B27 the name INT.

4. Calculate the Price and Yield To Maturity of a Coupon Bond using the Cash Flows.

Calculate the price as the present value of the coupon bond’s cash flows. This bond has two cash

flows per year for four years or eight periods. Enter the period numbers 0, 1, 2, …, 8 in the

range B30:J30. Complete the bond price calculation as follows:

Time (years) = (Period) / (Number of Payments / Year) = Period / NOP. Enter =B30/NOP in

cell B31 and copy it across.

Cash Flows in Periods 1-7 = Coupon Payment. Enter =INT in cell C32 and copy it across.

Cash Flow in Period 8 = Coupon Payment + Face Value. Add +M to the formula in cell J32, so

that it reads =INT+M.

Yield To Maturity = correspond yield on the yield curve. Where there a yield curve Time To

Maturity that closely matches the cash flow Time, use the corresponding yield. Enter =D7 in cell

C33, =D8 in cell D33, =D9 in cell F33, =D10 in cell H33, and =D11 in cell J33. Otherwise, we

will interpolate from the two closest points on the yield curve. For example the yield for the cash

flow at year 1.5, take the average of the one year yield and the two year yield. Enter

=(D33+F33)/2 in cell E33, =(F33+H33)/2 in cell G33, and =(H33+J33)/2 in cell I33.

Discount Rate / Period depends on the rate convention being used as follows:





1+Yield To Maturity ^ 1/ Number of Payments / Year 1 under EAR

Discount Rate / Period =

Yield To Maturity / Number of Payments / Year under APR

−





Enter =IF($B$20=1,((1+C33)^(1/NOP))-1,C33/NOP) in cell C34 and copy it across.

Present Value of Cash Flow =(Cash Flow)/((1+Discount Rate/Period)^ Period). Enter

=C32/((1+C34)^C30) in cell C35 and copy it across.

Present Value of the Bond = Sum of all the Present Value of Cash Flows (row 19). Enter

=SUM(C35:J35) in cell B36.

Coupon Bond Discount Rate / Period. RATE is the Excel function to calculate the discount rate

per period. The format is =RATE(Number of Periods to Maturity, Coupon Payment, -Bond Price,

Par Value). Enter =RATE(N,INT,-B36,M) in cell B37.

Coupon Bond Yield To Maturity. Depends on the rate convention being used as follows:





1+Discount Rate / Period ^ Number of Payments / Year 1 under EAR

Yield To Maturity =

Discount Rate / Period Number of Payments / Year under APR

−



⋅

Enter =IF($B$20=1,((1+B37)^(NOP))-1,B37*NOP) in cell B38.

The Coupon Bond's price is $963.61 and its Yield To Maturity is 6.04%. Note that this yield is not the

same as four year yield (6.05%) or any other point on the yield curve. The yield of the coupon bond is a

weighted average of the yields for each of the eight periods. Since the bond's biggest cash flow is on the

maturity date, the biggest weight in the weighted average is on the maturity date. Thus the coupon bond's

yield is closest to the yield of the maturity date, but it is not the same.

Problem. Given the yield curve as published by the financial press, consider a coupon bond has a face

value of $1,000, an annual coupon rate of 5.0%, makes 2 (semiannual) coupon payments per year, and

8 periods to maturity (or 4 years to maturity). What is price and yield to maturity of this coupon bond

based on the Annual Percentage Rate (APR) convention? What is price and yield to maturity of this

coupon bond based on the Effective Annual Rate (EAR) convention?

Solution Strategy. We will use the yield curve you entered in The Yield Curve - Obtaining It From

Bond Listings. We will calculate the bond price as the present value of the bond’s cash flows, where each

cash flow is discounted based on the correspond yield on the yield curve (e.g., a cash flow in year three

will be discounted based on the yield curve's yield at year three). We will use Excel’s RATE function to

determine the yield to maturity of this coupon bond.

FIGURE 8.2 Spreadsheet Model of The Yield Curve – Using It To Price A Coupon Bond.

How To Build This Spreadsheet Model.

1. Start with the Bond Listings Spreadsheet. Open the spreadsheet that you created for The Yield

Curve – Obtaining It From Bond Listings and immediately save the spreadsheet under a new

name using the File | Save As command.

2. Enter The Bond Inputs and Name Them. Enter 0 in cell B20. This will serve as a switch

between the APR and the EAR rate conventions. To highlight which rate convention in use, enter

=IF($B$20=1,"Effective Annual Rate","Annual Percentage Rate") in cell C20. Enter the

other bond inputs into the range B21:B25 and then name each one. Put the cursor on cell B21,

click on Insert | Name | Define, enter CR in the Names in Workbook box, and click on OK. Put

the cursor on cell B22 and repeat the process to name it NOP. Repeat the process to give the cells

B23, and B24 the names N and M, respectively.

3. Calculate the Coupon Payment. The formula is Coupon Payment = Coupon Rate * Face Value /

(Number of Payments / Year). Enter =CR*M/NOP in cell B27 and use the process above to give

the cell B27 the name INT.

4. Calculate the Price and Yield To Maturity of a Coupon Bond using the Cash Flows.

Calculate the price as the present value of the coupon bond’s cash flows. This bond has two cash

flows per year for four years or eight periods. Enter the period numbers 0, 1, 2, …, 8 in the

range B30:J30. Complete the bond price calculation as follows:

Time (years) = (Period) / (Number of Payments / Year) = Period / NOP. Enter =B30/NOP in

cell B31 and copy it across.

Cash Flows in Periods 1-7 = Coupon Payment. Enter =INT in cell C32 and copy it across.

Cash Flow in Period 8 = Coupon Payment + Face Value. Add +M to the formula in cell J32, so

that it reads =INT+M.

Yield To Maturity = correspond yield on the yield curve. Where there a yield curve Time To

Maturity that closely matches the cash flow Time, use the corresponding yield. Enter =D7 in cell

C33, =D8 in cell D33, =D9 in cell F33, =D10 in cell H33, and =D11 in cell J33. Otherwise, we

will interpolate from the two closest points on the yield curve. For example the yield for the cash

flow at year 1.5, take the average of the one year yield and the two year yield. Enter

=(D33+F33)/2 in cell E33, =(F33+H33)/2 in cell G33, and =(H33+J33)/2 in cell I33.

Discount Rate / Period depends on the rate convention being used as follows:





1+Yield To Maturity ^ 1/ Number of Payments / Year 1 under EAR

Discount Rate / Period =

Yield To Maturity / Number of Payments / Year under APR

−





Enter =IF($B$20=1,((1+C33)^(1/NOP))-1,C33/NOP) in cell C34 and copy it across.

Present Value of Cash Flow =(Cash Flow)/((1+Discount Rate/Period)^ Period). Enter

=C32/((1+C34)^C30) in cell C35 and copy it across.

Present Value of the Bond = Sum of all the Present Value of Cash Flows (row 19). Enter

=SUM(C35:J35) in cell B36.

Coupon Bond Discount Rate / Period. RATE is the Excel function to calculate the discount rate

per period. The format is =RATE(Number of Periods to Maturity, Coupon Payment, -Bond Price,

Par Value). Enter =RATE(N,INT,-B36,M) in cell B37.

Coupon Bond Yield To Maturity. Depends on the rate convention being used as follows:





1+Discount Rate / Period ^ Number of Payments / Year 1 under EAR

Yield To Maturity =

Discount Rate / Period Number of Payments / Year under APR

−



⋅

Enter =IF($B$20=1,((1+B37)^(NOP))-1,B37*NOP) in cell B38.

The Coupon Bond's price is $963.61 and its Yield To Maturity is 6.04%. Note that this yield is not the

same as four year yield (6.05%) or any other point on the yield curve. The yield of the coupon bond is a

weighted average of the yields for each of the eight periods. Since the bond's biggest cash flow is on the

maturity date, the biggest weight in the weighted average is on the maturity date. Thus the coupon bond's

yield is closest to the yield of the maturity date, but it is not the same.