Calculating the Price of a bond (without using the PRICE function)
Oct
1
Written by:
Charles Flock
10/1/2008 5:08 PM
The mechanics of bond pricing.
In this posting I will discuss the mechanics of bond pricing. There is plenty of documentation available from many sources that generally do two things. First, they present a series of complicated equations using a bunch of notation that makes it pretty hard to figure out what is going on. Second, they only provide examples when the bond settles on coupon payment date, so they ignore how accrued interest affects the calculation. And there are a variety of calculators available which make the whole thing a black box, which many times confuses the issue even more.
But, really, it’s simple. The price of a bond is the present value of its cash flows. Now, I have to throw in a few disclaimers. First, I am talking about bonds that pay regular periodic interest. No odd first coupon dates, no odd last coupon dates, no adjustable rates, no A/365 or A/360 day count conventions. Second, the price is actually adjusted by the amount of the accrued interest as of the settlement date. So the price of a bond that pays regular periodic interest is the present value of its cash flows minus the accrued interest. And it can be calculated pretty straightforwardly using the PV function.
Let’s say there is a bond with the following characteristics:
Settlement

4/30/2008

Maturity

10/31/2013

Rate

0.05

Yield

0.04

Redemption

100

Frequency

2

Basis

0

If we enter the following TSQL statement
SELECT wct.PRICE('4/30/2008','10/31/2013',.05,.04, 100, 2, 0)
It returns this result

104.893424022668
(1 row(s) affected)
The following table illustrates how this result was calculated:
Coupon No.

Date

Cash Flow

Discount Factor

Discounted Cash Flow

1

10/31/2008

2.5

1.02^1

2.450980392

2

4/30/2009

2.5

1.02^2

2.402921953

3

10/31/2009

2.5

1.02^3

2.355805836

4

4/30/2010

2.5

1.02^4

2.309613565

5

10/31/2010

2.5

1.02^5

2.264327025

6

4/30/2011

2.5

1.02^6

2.219928455

7

10/31/2011

2.5

1.02^7

2.176400447

8

4/30/2012

2.5

1.02^8

2.133725928

9

10/31/2012

2.5

1.02^9

2.091888165

10

4/30/2013

2.5

1.02^10

2.05087075

11

10/31/2013

2.5

1.02^11

2.010657598

11

10/31/2013

100

1.02^11

80.42630391

Total




104.893424023

We can see that there are 11 coupons remaining for this bond, and, in the parlance of fixed income mathematics, since the settlement date is also a coupon payment date, there are 11 whole coupons remaining. The cash flows consist of the coupon payments, which is the par value (which is assumed to be 100) multiplied by the rate (.05) divided by the frequency (2). The discount factor is 1 plus the yield (.04) divided by the frequency (2) raised to the coupon number multiplied by minus 1 (1) and the discounted cash flow is the cash flow multiplied by the discount factor. The sum of the discounted cash flows is the price of the bond.
In the PV calculation, the coupon payments are @Pmt, the redemption value is @FV, and @Rate is the yield (.04) divided by the frequency (2). The number of periods is calculated using the COUPNUM function. Thus, if we run the following TSQL statement:
select wct.PV(.02,11,2.5,100,0)
It returns this result

104.893424022668
(1 row(s) affected)
Which is identical to the result returned from the PRICE function. We could have also entered the statement as:
select wct.PV(.04/2,wct.COUPNUM('4/30/2008','10/31/2013',2,0),100*(.05/2),100,0)
and it returns the same result.
Now, what happens if the settlement date of the bond is moved forward one day? We enter the following TSQL statement, which is identical to the original statement, other than the settlement date has been advanced one day.
SELECT wct.PRICE('5/01/2008','10/31/2013',.05,.04, 100, 2, 0)
It returns this result

104.891075576252
(1 row(s) affected)
The following table illustrates how this was calculated:
Coupon No.

Date

Cash Flow

Discount Factor

Discounted Cash Flow

0

10/31/2008

2.5

1.02^0

2.5

1

4/30/2009

2.5

1.02^1

2.450980392

2

10/31/2009

2.5

1.02^2

2.402921953

3

4/30/2010

2.5

1.02^3

2.355805836

4

10/31/2010

2.5

1.02^4

2.309613565

5

4/30/2011

2.5

1.02^5

2.264327025

6

10/31/2011

2.5

1.02^6

2.219928455

7

4/30/2012

2.5

1.02^7

2.176400447

8

10/31/2012

2.5

1.02^8

2.133725928

9

4/30/2013

2.5

1.02^9

2.091888165

10

10/31/2013

2.5

1.02^10

2.05087075

10

10/31/2013

100

1.02^10

82.03482999

SubTotal




106.991292503




1.02^(179/180)

104.904964465

ACCRINT




000.013888889

PRICE




104.891075576

In this table, we can see that the coupon numbers have changed. When the bond settled on 4/30/08, there were 11 coupons, numbered 1 through 11. There are still 11 coupons, but they are numbered 0 through 10. The reason for this is that we need to account for the fractional coupon period that arises because the settlement date is greater than the previous coupon date. What this means is that the purchaser of the bond is going to earn 179/180 of the next coupon and that he needs to pay the seller of the bond 1/180 of the next coupon. I am using 180 in this example because the basis has been set to 0 and the frequency is 2.
The sum of the discounted cash flows is 106.991292503. But this actually represents the value of those cash flows as at the next coupon date. That value needs to adjusted for the fact the settlement date is 05/01/2008. This adjustment is made by dividing 106.991292503 by 1.02^(179/180), which is 1 plus the yield (.04) divided by the frequency (2) raised to the number of days from the settlement date until the next coupon date (179) divided by the number of days in the coupon period (180). This adjustment is made whenever the bond settles between coupon dates, other than in the final coupon period. The result, 104.904964465 is then adjusted by subtracting the accrued interest, which is the number of days from the previous coupon date until the settlement date (1) divided by the number of days in the coupon period (180) multiplied by the coupon interest (2.5).
So how can we represent this using the PV function? Enter the following TSQL statement to get the present value of the cash flows at the next coupon date:
select wct.PV(.04/2,
wct.COUPNUM('5/1/2008','10/31/2013',2,0),
100*(.05/2) * 1.02,
100 * 1.02,
0)
It returns this result

106.991292503121
(1 row(s) affected)
As you can see, @Pmt and @FV have been multiplied by 1 plus the yield (.04) divided by the frequency (2). It then needs to be adjusted for the number of days from settlement to next coupon date. This can be achieved by entering this TSQL statement:
select wct.PV(.04/2,
wct.COUPNUM('5/1/2008','10/31/2013',2,0),
100*(.05/2) * 1.02,
100 * 1.02,
0)/
power(cast(1.02 as float),
wct.coupdaysnc('05/01/2008','10/31/2013',2,0)/
wct.coupdays('05/01/2008','10/31/2013',2,0))
It returns this result

104.904964465141
(1 row(s) affected)
Finally, we need to subtract out the accrued interest
select (wct.PV(.04/2,
wct.COUPNUM('5/1/2008','10/31/2013',2,0),
100*(.05/2) * 1.02,
100 * 1.02,
0)/
power(cast(1.02 as float),
wct.coupdaysnc('05/01/2008','10/31/2013',2,0)/
wct.coupdays('05/01/2008','10/31/2013',2,0))) 
wct.accrint('4/30/2008','10/31/2008','5/1/2008',.05,100,2,0,0)
It returns this result

104.891075576252
(1 row(s) affected)
Which is identical to the result returned from the PRICE function.
As an aside the accrued interest could also have been entered as:
select wct.accrint(wct.COUPPCD('05/01/2008','10/31/2013',2,0),
wct.COUPNCD('05/01/2008','10/31/2013',2,0),
'5/1/2008',
.05,
100,
2,
0,
0)
Which returns the following result.

0.0138888888888889
(1 row(s) affected)
One final note. The calculation of the price of a bond on a coupon payment date is really unaffected by the day count convention. Since there are no fractional periods involved, there is no need to calculate the number of days from the previous coupon date to the settlement date, from the settlement date to the next coupon date, or the number of days in the period. It is only when the settlement occurs after the previous coupon date that day count conventions come into play. It is probably also important to note, since I have not previously stated it, that coupon interest is earned in the period where the settlement date is greater than the previous coupon date. By this I mean that if a bond settles on the coupon date, the buyer of the bond does not need to pay the seller of the bond the accrued interest as the seller should be receiving the interest from the issuer (which is a topic for another discussion). So, on the coupon date, the accrued interest is zero.