Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to compute coupon amount for bond?
Are you trying to calculate accrued interest for a particular security or
determine a coupon payment? Coupon payment is simply coupon rate/frequency * face value of bond. Accrued interest calculation is a bit more complex, as it involves the coupon date, settle date, and coupon rate. Simplisticly, its the coupon rate/frequency * # of days accrued (days since last coupon payment). Hope this helps, -Chad " wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to compute coupon amount for bond? | Excel Discussion (Misc queries) | |||
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) |