N
nomail1983
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)
i>1: 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.
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)
i>1: 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.