6.4 System of Five Bond Variables
К оглавлению1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 1617 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.