6.3 Dynamic Chart

If you increased the coupon rate of a bond, what would happen to its price? If you increased the

yield to maturity of a bond, what would happen to its price? You can answer these questions and

more by creating a Dynamic Chart using “spinners.” Spinners are up-arrow / down-arrow

buttons that allow you to easily change the inputs to the model with the click of a mouse. Then

the spreadsheet recalculates the model and instantly redraws the model outputs on the graph.

FIGURE 6.4 Spreadsheet Model of Bond Valuation – Dynamic Chart.

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.

Repeat this procedure to delete row 8.

2. Increase Row Height for the Spinners. Select the range A4:A8. Then click on Format | Row

Height from the main menu. Enter a height of 30 and click on OK.

3. Display the Forms Toolbar. Click on View | Toolbars | Forms from the main menu.

4. Create the Spinners. Look for the up-arrow / down-arrow button on the Forms toolbar (which

will display the word “Spinner” if you hover the cursor over it) and click on it. Then draw the

box for a spinner from the upper left corner of cell C4 down to the lower right corner of the cell.

Then a spinner appears in the cell C4. Right click on the spinner (press the right mouse button

while the cursor is above the spinner) and a small menu pops up. Click on Copy. Then select the

cell C5 and click on Paste. This creates an identical spinner in the cell C5. Repeat the process

three times more. Select cell C6 and click on Paste. Then select cell C7 and click on Paste. Then

select cell C8 and click on Paste. You now have five spinners down column C.

5. Create The Cell Links. Right click on the first spinner in the cell C4 and a small menu pops up.

Click on Format Control and a dialog box pops up. Click on the Control tab, then enter the

cell link D4 in the Cell link edit box and click on OK. Repeat this procedure for the other four

spinners. Link the spinner in cell C5 to cell D5. Link the spinner in cell C6 to cell D6. Link the

spinner in cell C7 to cell D7. Link the spinner in cell C8 to cell D8 and also on the Control tab,

set the Minimum value equal to 1. Test your spinners by clicking on the up-arrows and downarrows

of the spinners to see how they change the values in the linked cells.

6. Create Scaled Inputs. The values in the linked cells are always integers, but they can be scaled

appropriately to the problem at hand. Restrict the value in cell B4 to be either 1 or 0 by entering

=IF(D4>1,1,D4). In cell B5, enter =D5/200. In cell B6, enter =D6/200. In cell B7, enter =D7. In

cell B8, enter =D8*50.

7. Enter Time To Maturity. Enter Time To Maturity values 1, 2, 3, 4, …, 30 in the range

B15:AE15.

8. Calculate Number of Periods to Maturity. The Number of Periods to Maturity = (Time to

Maturity) * (Number of Periods / Year). Enter =B15*NOP In cell B16 and copy it across.

9. Calculate Bond Price of a Coupon Bond. Calculate the duration of a coupon bond using the PV

bond duration function and the scaled inputs in cells DR, INT, M and the Time to Maturity in

cell B16. Specifically, enter =-PV(DR,B$16,INT,M) in cell B17. Be sure that B$16 has a $ in

the middle to lock in the row, but not the column.

10. Calculate Bond Price of a Par Bond. A par bond is a bond with a coupon rate equal to the yield

to maturity. As a benchmark for comparison, calculate the bond price of a par bond using the

same inputs for everything else. Copy the formula in cell B17 to cell B18. Then change the

coupon payment from INT to DR*M so that the formula reads =-PV(DR,B$16,DR*M,M). Copy

the range B17:B18 to the range C17:AE18.

11. Graph the Bond Price of a Coupon Bond and Par Bond. Highlight the range B15:AE15 and

then while holding down the Ctrl button highlight the range B17:AE18. 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 E3:J12.

Your Dynamic Chart allows you to change the Bond Price inputs and instantly see the impact on a graph

of the price of a coupon bond and par bond by time to maturity. This allows you to perform instant

experiments on Bond Price. Below is a list of experiments that you might want to perform:

What happens when the annual coupon rate is increased?

What happens when the yield to maturity is increased?

What happens when the number of payments / year is increased?

What happens when the face value is increased?

What is the relationship between the price of a par bond and time to maturity?

What happens when the annual coupon rate is increased to the point that it equals the yield to

maturity? What happens when it is increased further?

If you increased the coupon rate of a bond, what would happen to its price? If you increased the

yield to maturity of a bond, what would happen to its price? You can answer these questions and

more by creating a Dynamic Chart using “spinners.” Spinners are up-arrow / down-arrow

buttons that allow you to easily change the inputs to the model with the click of a mouse. Then

the spreadsheet recalculates the model and instantly redraws the model outputs on the graph.

FIGURE 6.4 Spreadsheet Model of Bond Valuation – Dynamic Chart.

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.

Repeat this procedure to delete row 8.

2. Increase Row Height for the Spinners. Select the range A4:A8. Then click on Format | Row

Height from the main menu. Enter a height of 30 and click on OK.

3. Display the Forms Toolbar. Click on View | Toolbars | Forms from the main menu.

4. Create the Spinners. Look for the up-arrow / down-arrow button on the Forms toolbar (which

will display the word “Spinner” if you hover the cursor over it) and click on it. Then draw the

box for a spinner from the upper left corner of cell C4 down to the lower right corner of the cell.

Then a spinner appears in the cell C4. Right click on the spinner (press the right mouse button

while the cursor is above the spinner) and a small menu pops up. Click on Copy. Then select the

cell C5 and click on Paste. This creates an identical spinner in the cell C5. Repeat the process

three times more. Select cell C6 and click on Paste. Then select cell C7 and click on Paste. Then

select cell C8 and click on Paste. You now have five spinners down column C.

5. Create The Cell Links. Right click on the first spinner in the cell C4 and a small menu pops up.

Click on Format Control and a dialog box pops up. Click on the Control tab, then enter the

cell link D4 in the Cell link edit box and click on OK. Repeat this procedure for the other four

spinners. Link the spinner in cell C5 to cell D5. Link the spinner in cell C6 to cell D6. Link the

spinner in cell C7 to cell D7. Link the spinner in cell C8 to cell D8 and also on the Control tab,

set the Minimum value equal to 1. Test your spinners by clicking on the up-arrows and downarrows

of the spinners to see how they change the values in the linked cells.

6. Create Scaled Inputs. The values in the linked cells are always integers, but they can be scaled

appropriately to the problem at hand. Restrict the value in cell B4 to be either 1 or 0 by entering

=IF(D4>1,1,D4). In cell B5, enter =D5/200. In cell B6, enter =D6/200. In cell B7, enter =D7. In

cell B8, enter =D8*50.

7. Enter Time To Maturity. Enter Time To Maturity values 1, 2, 3, 4, …, 30 in the range

B15:AE15.

8. Calculate Number of Periods to Maturity. The Number of Periods to Maturity = (Time to

Maturity) * (Number of Periods / Year). Enter =B15*NOP In cell B16 and copy it across.

9. Calculate Bond Price of a Coupon Bond. Calculate the duration of a coupon bond using the PV

bond duration function and the scaled inputs in cells DR, INT, M and the Time to Maturity in

cell B16. Specifically, enter =-PV(DR,B$16,INT,M) in cell B17. Be sure that B$16 has a $ in

the middle to lock in the row, but not the column.

10. Calculate Bond Price of a Par Bond. A par bond is a bond with a coupon rate equal to the yield

to maturity. As a benchmark for comparison, calculate the bond price of a par bond using the

same inputs for everything else. Copy the formula in cell B17 to cell B18. Then change the

coupon payment from INT to DR*M so that the formula reads =-PV(DR,B$16,DR*M,M). Copy

the range B17:B18 to the range C17:AE18.

11. Graph the Bond Price of a Coupon Bond and Par Bond. Highlight the range B15:AE15 and

then while holding down the Ctrl button highlight the range B17:AE18. 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 E3:J12.

Your Dynamic Chart allows you to change the Bond Price inputs and instantly see the impact on a graph

of the price of a coupon bond and par bond by time to maturity. This allows you to perform instant

experiments on Bond Price. Below is a list of experiments that you might want to perform:

What happens when the annual coupon rate is increased?

What happens when the yield to maturity is increased?

What happens when the number of payments / year is increased?

What happens when the face value is increased?

What is the relationship between the price of a par bond and time to maturity?

What happens when the annual coupon rate is increased to the point that it equals the yield to

maturity? What happens when it is increased further?