6.1 Basics

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

Problem. A bond has a face value of $1,000, an annual coupon rate of 5.0%, a yield to maturity of

9.0%, makes 2 (semiannual) coupon payments per year, and 8 periods to maturity (or 4 years to

maturity). What is price of this bond based on the Annual Percentage Rate (APR) convention? What is

price of this bond based on the Effective Annual Rate (EAR) convention?

Solution Strategy. We will create a switch that can be used to select either the EAR or APR rate

convention. The choice of rate convention will determine the discount rate / period. For a given discount

rate / period, we will calculate the bond price in four equivalent ways. First, we will calculate the bond

price as the present value of the bond’s cash flows. Second, we use a formula for the bond price. Third,

we use Excel’s PV function for a bond price. Fourth, we use Excel’s Analysis ToolPak Add-In PRICE

function, which only works under the APR convention.

FIGURE 6.1 Spreadsheet Model of Bond Valuation - Basics.

How To Build This Spreadsheet Model.

1. Enter The Inputs and Name Them. Enter 0 in cell B4. This will serve as a switch between the

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

=IF($B$4=1,"Effective Annual Rate","Annual Percentage Rate") in cell D1. Enter the other

inputs into the range B5:B9 and then name each one. Put the cursor on cell B5, click on Insert |

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

cell B6 and repeat the process to name it kd. Repeat the process to give the cells B7, B8, and B9

the names NOP, N, and M, respectively.

2. Calculate the Discount Rate / Period. The 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$4=1,((1+kd)^(1/NOP))-1,kd/NOP) in cell B12 and use the process above to give

the cell B12 the name DR.

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

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

the cell B13 the name INT.

4. Calculate Bond Price using the Cash Flows. Calculate the bond price as the present value of the

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 B16:J16. Complete the bond price calculation as

follows:

o Time (years) = (Period) / (Number of Payments / Year) = Period / NOP. Enter

=B16/NOP in cell B17 and copy it across.

o Cash Flows in Periods 1-7 = Coupon Payment. Enter =INT in cell C18 and copy it

across.

o Cash Flow in Period 8 = Coupon Payment + Face Value. Add +M to the formula in cell

J18, so that it reads =INT+M.

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

=C18/((1+DR)^C16) in cell C19 and copy it across.

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

=SUM(C19:J19) in cell B20.

5. Calculate Bond Price using the Formula. The present value of the bond’s cash flows can be

simplified down to an equivalent formula. The bond price formula is





1 1

B N

INT DR M V

DR DR

−⋅−





where the first term is the present value of an annuity for the string of coupon payments and the

second term is the present value of face value payment at the end. Enter =INT*(1-((1+DR)^(-

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

6. Calculate Bond Price using the PV Function. Excel has a function to calculate the price of a

bond. The format is =-PV(Discount Rate / Period, Number of Periods to Maturity, Coupon

Payment, Face Value). Enter =-PV(DR,N,INT,M) in cell B26.

7. Calculate Bond Price using the PRICE Function (under APR). Excel’s Analysis ToolPak

Add-In contains several advanced bond functions, including a Bond Price function assuming the

APR convention is being used.

o Click on Tools | Add-Ins, check the Analysis ToolPak checkbox on the Add-Ins dialog

box (see Figure 2 below), and click on OK.

FIGURE 6.2 The Add-Ins dialog box.

8. The bond price function is =PRICE(Settlement Date, Maturity Date, Annual Coupon Rate, Yield

To Maturity, Redemption Value, Number of Payments). The Settlement Date is the date when

you exchange money to purchase the bond. Specifying the exact day of settlement and maturity

allows a very precise calculation. For our purpose, we simple want the difference between the

two dates to equal the (8 Periods To Maturity) / (2 Payments / Year) = 4 Years To Maturity. This

is easily accomplished by the use of the DATE function. The DATE Function has the format

=DATE(Year, Month, Day). We will enter an arbitrary starting date of 1/1/2000 for the

Settlement Date and then specify a formula for 1/1/2000 plus N / NOP for the Maturity Date. We

need to add an IF statement to test for the rate convention being used. The bond function is only

valid with APR. Enter =IF($B$4=1,"",PRICE(DATE(2000,1,1),DATE(2000+N/NOP,1,1),

CR,kd,100,NOP)*M/100) in cell B29. This uses a conventional Redemption Value of $100.00

and scales the resulting price by the ratio of (M Value) / $100.00.

The resulting bond price is $868.08. Notice you get the same answer all four ways: using the cash flows,

using the formula, using the PV function, or using the PRICE function!