Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
what is a coupon rate and ytm of a bond? Art Excel Worksheet Functions 2 September 18th 06 01:44 PM
Bond duration for monthly coupon N. Excel Worksheet Functions 0 September 8th 06 10:36 PM
to compute interest rate from principal and interest amount PVJ Excel Discussion (Misc queries) 3 December 28th 05 06:01 PM
Bond YTM if Coupon Payment is at Maturity? Babar Ali Excel Discussion (Misc queries) 0 October 25th 05 02:05 PM
A coupon organizer with product name, value, exp. date, etc. mtchappy Excel Discussion (Misc queries) 2 May 3rd 05 05:00 PM


All times are GMT +1. The time now is 01:57 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"