Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
what is a coupon rate and ytm of a bond? | Excel Worksheet Functions | |||
Bond duration for monthly coupon | Excel Worksheet Functions | |||
to compute interest rate from principal and interest amount | Excel Discussion (Misc queries) | |||
Bond YTM if Coupon Payment is at Maturity? | Excel Discussion (Misc queries) | |||
A coupon organizer with product name, value, exp. date, etc. | Excel Discussion (Misc queries) |