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 am hoping to hear from someone with practical knowledge -- someone
who owns US Treasury bonds and can look at his/her statements.

Consider a hypothetical 20-yr US Treasury bond with a face value of
$12,345 at 4.625%. US Treasury bonds pay interest semiannually.

Which of the following more correctly computes the total interest in
the manner that the US Treasury does it?

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

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

In case #B, all coupon amounts would be the same.

In case #A, coupon amounts might differ. (But not by much.) Because
each coupon amount must be rounded (actual payment), I presume that
the coupon for any period i would be computed as follows so that the
sum of all the coupons equals the formula for case #A:

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

i1: round(i*12345*4.625%/2, 2) - sum($A$1:A[i-1])

replacing "i" and "A[i-1]" with appropriate cell references. This
assumes that A1:A40 contain the coupon amounts. The notation "A[i-1]"
refers to the cell to the left (i.e. offset($A$1,0,i-2)).

Of course, the difference is neglible. I am using this paradigm to
better understand how coupons are handled.


Notes:

1. For US Treasury bonds, which use the actual/actual basis, 4.625%/2
should be replaced with 4.625%*(date2-date1)/365 (or 366 for leap
years). I simplified the formula to focus on the point of my
question: rounding.