6.2 By Yield To Maturity

К оглавлению1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 
17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 
34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 
51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 
68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 
85 86 87 88 89 90 91 92 93 94 95 96 97 

What is the relationship between bond price and yield to maturity? We can construct a graph to find out.

FIGURE 6.3 Spreadsheet Model of Bond Valuation - By Yield To Maturity.

How To Build This Spreadsheet Model.

1. Start with the Basics Spreadsheet and Delete Rows. Open the spreadsheet that you created for

Bond Pricing – Basics and immediately save the spreadsheet under a new name using the File |

Save As command. Delete rows 15 through 29 by selecting the range A15:A29, clicking on Edit

| Delete, selecting the Entire Row radio button on the Delete dialog box, and clicking on OK.

2. Enter Yield To Maturity (Annualized). Enter Yield To Maturity values 1.0%, 2.0%, 3.0%,

4.0%, …, 20% in the range B16:U16.

3. Calculate Discount Rate / Period. Copy the Discount Rate / Period formula from cell B12 to the

cell B17. In cell B17, change the variable kd to B16, so that the formula reads

=IF($B$4=1,((1+B16)^(1/NOP))-1,B16/NOP) and then copy it across.

4. Calculate Bond Price. Calculate the bond price using PV function and the inputs N, INT, M,

and the Discount Rate / Period in cell B17. Enter =-PV(B17,N,INT,M) in cell B18 and copy it

across.

5. Graph the Bond Price By Yield To Maturity. Highlight the range B16:U16 and then while

holding down the Ctrl button highlight the ranges B18:U18. Next choose Insert | Chart from

the main menu. Select an XY(Scatter) chart type and make other selections to complete the

Chart Wizard. Place the graph in the range C2:J15.

This graph shows the inverse relationship between bond price and yield to maturity. In other word, a

higher discount rate (yield to maturity) lowers the present value of the bond’s cash flows (price). The

graph also that the relationship is curved (nonlinear) rather than being a straight line (linear).

What is the relationship between bond price and yield to maturity? We can construct a graph to find out.

FIGURE 6.3 Spreadsheet Model of Bond Valuation - By Yield To Maturity.

How To Build This Spreadsheet Model.

1. Start with the Basics Spreadsheet and Delete Rows. Open the spreadsheet that you created for

Bond Pricing – Basics and immediately save the spreadsheet under a new name using the File |

Save As command. Delete rows 15 through 29 by selecting the range A15:A29, clicking on Edit

| Delete, selecting the Entire Row radio button on the Delete dialog box, and clicking on OK.

2. Enter Yield To Maturity (Annualized). Enter Yield To Maturity values 1.0%, 2.0%, 3.0%,

4.0%, …, 20% in the range B16:U16.

3. Calculate Discount Rate / Period. Copy the Discount Rate / Period formula from cell B12 to the

cell B17. In cell B17, change the variable kd to B16, so that the formula reads

=IF($B$4=1,((1+B16)^(1/NOP))-1,B16/NOP) and then copy it across.

4. Calculate Bond Price. Calculate the bond price using PV function and the inputs N, INT, M,

and the Discount Rate / Period in cell B17. Enter =-PV(B17,N,INT,M) in cell B18 and copy it

across.

5. Graph the Bond Price By Yield To Maturity. Highlight the range B16:U16 and then while

holding down the Ctrl button highlight the ranges B18:U18. Next choose Insert | Chart from

the main menu. Select an XY(Scatter) chart type and make other selections to complete the

Chart Wizard. Place the graph in the range C2:J15.

This graph shows the inverse relationship between bond price and yield to maturity. In other word, a

higher discount rate (yield to maturity) lowers the present value of the bond’s cash flows (price). The

graph also that the relationship is curved (nonlinear) rather than being a straight line (linear).