View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.misc
[email protected] nomail1983@hotmail.com is offline
external usenet poster
 
Posts: 58
Default How to compute coupon amount for bond?

I hope someone with practical experience with US Treasury bonds will
answer this question, ideally by looking at your own statements.

Consider a hypothetical 20-year US Treasury bond with an interest rate
of 4.65% paid semiannually. Assume that the par value is $12,345.

Which more correctly computes the total of the 40 coupon amounts?

A. round(40*12345*4.65%/2, 2)

B. 40*round(12345*4.65%/2, 2)

In case #B, the coupon amounts are equal.

In case #A, the coupon amounts might not be equal. Since each coupon
payment must be rounded to the penny, in order to ensure that the
total of all coupons sum to #A, I presume that the coupon amount for
any period i is computed as follows:

i=1: round(12345*4.65%/2, 2)

i1: round(12345*i*4.65%/2, 2) - sum($A$1,offset($A$1,0,i-2))

assuming that the range A1:A40 contains each coupon amount.


Disclaimers:

1. I am using OFFSET() here merely to make the intent clear.
Normally, I would simply use a relative reference to the cell to the
left.

2. "i" might really be a cell reference to the coupon number.

3. I realize that the difference is only a few pennies -- not enough
to worry about. I am asking merely to get a better understanding of
how coupons are handled.

4. I guess that the above assumes a 30/360 basis. But according to
the HP 12C owner's handbook, US Treasury bonds use the actual/actual
basis. So I guess that 4.65%/2 should really be 4.65%*(date2-date1)/
365 (or 366 for leap years), where "date1" and "date2" are the
previous and current coupon dates respectively. But that's a
distraction. I use 4.65%/2 above to focus on my real issue, namely
rounding.