6.4 System of Five Bond Variables

К оглавлению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 

There is a system of five bond variables: (1) Number of Periods to Maturity (N), (2) Face Value (M), (3)

Discount Rate / Period (DR), (4) Coupon Payments (INT), and (5) Bond Price (VB). Given any four of

these variables, the fifth variable can be found by using Excel functions (and in some cases by formulas).

FIGURE 6.5 Spreadsheet Model of Bond Valuation - System of Five Bond Variables.

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 27 through 29 by selecting the range A27:A29, clicking on Edit

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

Then repeat this procedure to delete rows 14 through 25 and repeat this procedure again to delete

rows 10 through 11. This places the five bond variables in rows 8 through 12, highlighted with

purple labels above.

2. Calculate Number of Periods to Maturity (N). NPER is the Excel function to calculate the

number of periods to maturity. The format is =NPER(Discount Rate / Period, Coupon Payment, -

Bond Price, Par Value). Enter =NPER(DR,INT,-VB,M) in cell B15.

3. Calculate Face Value (M). There are two ways to calculate the face value of the bond.

o Use the Excel Function FV. The format is =FV(Discount Rate / Period, Number of

Periods to Maturity, Coupon Payment, -Bond Price). Enter =FV(DR,N,INT,-VB) in cell

B18.

o Use the face value formula



1 1

1

N

N

B

INT DR

M V DR

DR

⋅−

⋅−,

where the first term is the future value of the bond price and the second term is the future

values of the string of coupon payments. Enter =VB*((1+DR)^N)-INT*(((1+DR)^N)-

1)/DR in cell B19.

4. Calculate Discount Rate / Period (DR). 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,-VB,M) in cell B22.

5. Calculate Coupon Payment (INT). There are two ways to calculate the coupon payment of the

bond.

o Use the Excel Function PMT. The format is =PMT(Discount Rate / Period, Number of

Periods to Maturity, -Bond Price, Par Value). Enter =PMT(DR,N,-VB,M) in cell B25.

o Use the coupon payment formula





1

1 1

N

N

M

B DR

DR

DR

V

INT −

−

,

where the numerator is the bond price minus the present value of the par value and the

denominator is the present value of a $1 coupon payment. Enter =(VBM/((

1+DR)^N))/((1-((1+DR)^(-N)))/DR) in cell B26.

6. Calculate Bond Price (VB). There are two ways to calculate the price of the bond.

o Use the Excel Function PV. The format is =PV(Discount Rate / Period, Number of

Periods to Maturity, Coupon Payment, Par Value). Enter =-PV(DR,N,INT,M) in cell

B29.

o Use the bond price formula





1 1

1

N

B N

INT DR M V

DR DR

−⋅−



,

where the first term is the present value of the string of coupon payments and the second

term is the present value of the par value. Enter =INT*(1-((1+DR)^(-

N)))/DR+M/((1+DR)^N) in cell B30.

We see that the system of five bond variables is internally consistent. The five outputs in rows 15 through

30 (N=8, M=1000, DR=4.5%, INT=$25, VB=$868.08) are identical to the five inputs in rows 8 through

12. Thus, any of the five bond variables can be calculated from the other four in a fully consistent manner.

There is a system of five bond variables: (1) Number of Periods to Maturity (N), (2) Face Value (M), (3)

Discount Rate / Period (DR), (4) Coupon Payments (INT), and (5) Bond Price (VB). Given any four of

these variables, the fifth variable can be found by using Excel functions (and in some cases by formulas).

FIGURE 6.5 Spreadsheet Model of Bond Valuation - System of Five Bond Variables.

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 27 through 29 by selecting the range A27:A29, clicking on Edit

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

Then repeat this procedure to delete rows 14 through 25 and repeat this procedure again to delete

rows 10 through 11. This places the five bond variables in rows 8 through 12, highlighted with

purple labels above.

2. Calculate Number of Periods to Maturity (N). NPER is the Excel function to calculate the

number of periods to maturity. The format is =NPER(Discount Rate / Period, Coupon Payment, -

Bond Price, Par Value). Enter =NPER(DR,INT,-VB,M) in cell B15.

3. Calculate Face Value (M). There are two ways to calculate the face value of the bond.

o Use the Excel Function FV. The format is =FV(Discount Rate / Period, Number of

Periods to Maturity, Coupon Payment, -Bond Price). Enter =FV(DR,N,INT,-VB) in cell

B18.

o Use the face value formula



1 1

1

N

N

B

INT DR

M V DR

DR

⋅−

⋅−,

where the first term is the future value of the bond price and the second term is the future

values of the string of coupon payments. Enter =VB*((1+DR)^N)-INT*(((1+DR)^N)-

1)/DR in cell B19.

4. Calculate Discount Rate / Period (DR). 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,-VB,M) in cell B22.

5. Calculate Coupon Payment (INT). There are two ways to calculate the coupon payment of the

bond.

o Use the Excel Function PMT. The format is =PMT(Discount Rate / Period, Number of

Periods to Maturity, -Bond Price, Par Value). Enter =PMT(DR,N,-VB,M) in cell B25.

o Use the coupon payment formula





1

1 1

N

N

M

B DR

DR

DR

V

INT −

−

,

where the numerator is the bond price minus the present value of the par value and the

denominator is the present value of a $1 coupon payment. Enter =(VBM/((

1+DR)^N))/((1-((1+DR)^(-N)))/DR) in cell B26.

6. Calculate Bond Price (VB). There are two ways to calculate the price of the bond.

o Use the Excel Function PV. The format is =PV(Discount Rate / Period, Number of

Periods to Maturity, Coupon Payment, Par Value). Enter =-PV(DR,N,INT,M) in cell

B29.

o Use the bond price formula





1 1

1

N

B N

INT DR M V

DR DR

−⋅−



,

where the first term is the present value of the string of coupon payments and the second

term is the present value of the par value. Enter =INT*(1-((1+DR)^(-

N)))/DR+M/((1+DR)^N) in cell B30.

We see that the system of five bond variables is internally consistent. The five outputs in rows 15 through

30 (N=8, M=1000, DR=4.5%, INT=$25, VB=$868.08) are identical to the five inputs in rows 8 through

12. Thus, any of the five bond variables can be calculated from the other four in a fully consistent manner.