Create last coupon payment date

[Maryna Gulenko]

Dear all,

I am looking to solve the following:

I have price data of bonds (p_date; end-of-month prices) and the first coupon that the bond ever paid (fc_date), as well as the frequency with which the coupon is paid. If it is 2, it is paid semianually, if it is 4, quarterly and so on. Now I am looking for the last coupon payment before the p_date (c_date).

ID	p_date	fc_date	frequency	c_date
1	31-Jul-14	15-Jul-13	2	15-Jul-14
1	31-Aug-14	15-Jul-13	2	15-Jul-14
1	30-Sep-14	15-Jul-13	2	15-Jul-14
1	31-Oct-14	15-Jul-13	2	15-Jul-14
1	30-Nov-14	15-Jul-13	2	15-Jul-14
2	31-Jul-14	1-Mar-97	2	1-Mar-14
2	31-Aug-14	1-Mar-97	2	1-Mar-14
2	30-Sep-14	1-Mar-97	2	1-Sep-14
2	31-Dec-14	8-Nov-12	4	8-Nov-14
2	31-Jan-15	8-Nov-12	4	8-Nov-14
2	28-Feb-15	8-Nov-12	4	8-Feb-15
3	30-Jun-15	8-Nov-12	4	8-May-15

E.g. for a transaction price on June 30, 2015: if the first coupon payment is made on November 08, 2012 and the frequency is 4, the coupon date I am looking for is Feb 8, 2015.

Maybe there is an elegant way to solve this and since I am a beginner, I would be happy to learn from any advice. Thank you in advance.

Maryna

[Carole J. Wilson]

We need to know the way the data is stored in Stata and need a working example. Please read the FAQ (especially #12) and install and run dataex to provide a sample of your data.

ssc install dataex
help dataex
*something like: dataex IDp_date fc_date frequenyc_date in 1/15

[Clyde Schechter]

Here's a start. First, I'm assuming the date variable are all Stata internal dates. If they are human-readable strings, they have to be converted. See -help daily()-.

// CALCULATE INTERVAL FROM FIRST COUPON TO PAYMENT IN YEARS
gen interval = (p_date-fc_date)/365
// CALCULATE NUMBER OF PAYMENTS IN INTERVAL
gen int n_pmts = floor(frequency*interval)
// CALCULATE DATE OF LAST PAYMENTS
gen last_pmt_date = fc_date + (npmts-1)*floor(365/frequency)
format last_pmt_date %td

Notes: This does not account for leap years. Also, since most years are an odd number of days, it isn't clear to me exactly how the payment date for 2 or 4 payments are picked. In this code, I truncate down. Also, if the calculated payment day falls on a weekend or holiday, do they still pay on that date? In this code, no adjustments for weekends or holidays are made.

[Maryna Gulenko]

Hello Carole,

thank you for the suggestion.

I have the following example data, where the last variable c_date is the one I am looking for. Unfortunately the dates are not given in an appropriate manner (I would be thankful for some advice).

p_date is an end-of-month bond price. fc_date is the first coupon that the bond ever paid. frequency is the frequency with which the coupon is paid. If it is 2, it is paid semianually, if it is 4, quarterly and so on. I am looking to create a new variable which gives me the date of the last coupon paid before p_date. In the data below I included the result already that I am looking for (c_date).

* Example generated by -dataex-. To install: ssc install dataex
clear
ID fc_date frequency p_date c_date
 1 18846  2 20057 19942
 2 18943  4 20239 20220
 3 19250  2 20057 19980
 3 19250  2 20088 19980
 3 19250  2 20119 20345
 3 19250  2 20147 20345
 3 19250  2 20178 20161
 3 19250  2 20208 20161
 4 19173  4 20057 19995
 4 19173  4 20147 20086
 5 19265  4 20239 20176
 6 19296  4 20239 20208
 7 19325  4 20239 20236
 8 19355  4 20239 20175
 9 19384  4 20239 20204
10 19342 12 20239 20223
end
format %tddd-Mon-YY fc_date
format %tddd-Mon-YY p_date
format %tddd-Mon-YY c_date

Thank you in advance.
Maryna Gulenko

[Maryna Gulenko]

Hello Clyde,

thank you for the suggestion.I tried this code with a small modification:

gen last_pmt_date = fc_date + (npmts)*floor(365/frequency) instead of
gen last_pmt_date = fc_date + (npmts-1)*floor(365/frequency)
This gives me almost the date I am looking for with only a few days of deviation. I will change this manually by also accounting for dates that then fall in a different month. Thank you!

[Carole J. Wilson]

Your dates are correctly coded as internal Stata dates. This is what you want and what Clyde's code needs..

[Lien Lekkerkerk]

Here's a start. First, I'm assuming the date variable are all Stata internal dates. If they are human-readable strings, they have to be converted. See -help daily()-.

// CALCULATE INTERVAL FROM FIRST COUPON TO PAYMENT IN YEARS
gen interval = (p_date-fc_date)/365
// CALCULATE NUMBER OF PAYMENTS IN INTERVAL
gen int n_pmts = floor(frequency*interval)
// CALCULATE DATE OF LAST PAYMENTS
gen last_pmt_date = fc_date + (npmts-1)*floor(365/frequency)
format last_pmt_date %td

Notes: This does not account for leap years. Also, since most years are an odd number of days, it isn't clear to me exactly how the payment date for 2 or 4 payments are picked. In this code, I truncate down. Also, if the calculated payment day falls on a weekend or holiday, do they still pay on that date? In this code, no adjustments for weekends or holidays are made.

I have the same problem with identifying the last coupon payment date. As Clyde pointed out, when I used those command, there are some lag day in my last coupon payment date. My data looks like this (I could not understand why when I use dataex all the date format become those format, but when you use format again you can see the date I mean bellow):

* Example generated by -dataex-. To install: ssc install dataex
clear
input str14 complete_cusip long(trd_exctn_dt first_interest_date) byte interest_frequency float interval int n_pmts float last_pmt_date
"000336AE7" 16559 14214 2 6.424657 12 16398
"000336AE7" 16560 14214 2 6.427397 12 16398
"000336AE7" 16561 14214 2 6.430137 12 16398
"000336AE7" 16562 14214 2 6.432877 12 16398
"000336AE7" 16565 14214 2 6.441096 12 16398
"000336AE7" 16566 14214 2 6.443836 12 16398
"000336AE7" 16588 14214 2 6.504109 13 16580
"000336AE7" 16589 14214 2 6.506849 13 16580
"000336AE7" 16590 14214 2 6.509589 13 16580
"000336AE7" 16593 14214 2 6.517808 13 16580
"000336AE7" 16594 14214 2 6.520548 13 16580
"000336AE7" 16595 14214 2 6.523288 13 16580
"000336AE7" 16596 14214 2 6.526027 13 16580
"000336AE7" 16597 14214 2 6.528767 13 16580
"000336AE7" 16600 14214 2 6.536986 13 16580
"000336AE7" 16601 14214 2 6.539726 13 16580
end
format %d trd_exctn_dt
format %d first_interest_date
format %td last_pmt_date

For example: As the first_interest_date is 01dec1998 and it's semi-annual coupon (frequency =2), the last_pmt_date of the trd_exctn_dt of 03may2005 should be 01dec2004 instead of 23nov2004. Similarly, the last_pmt_date of the trd_exctn_dt of 02jun2005 should be 01jun2005 instead of 24may2005. It's not a problem of holiday or weekend as accrued interest is based on calendar days. Can someone help me to document this problem? My data is very large so I can not fix it manually as Maryna did. I don't know where the problem comes from, but I guess it's due to leap year and rounding method.

Thank you so much in advance!

[Lien Lekkerkerk]

I forgot one more problem that I hope someone can help me figure out the solution.
This need some extended data as following:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str14 complete_cusip long(trd_exctn_dt first_interest_date) byte interest_frequency float interval int n_pmts float last_pmt_date
"000336AE7" 16559 14214 2 6.424657 12 16398
"000336AE7" 16560 14214 2 6.427397 12 16398
"000336AE7" 16561 14214 2 6.430137 12 16398
"000336AE7" 16562 14214 2 6.432877 12 16398
"000336AE7" 16565 14214 2 6.441096 12 16398
"000336AE7" 16566 14214 2 6.443836 12 16398
"000336AE7" 16567 14214 2 6.446575 12 16398
"000336AE7" 16568 14214 2 6.449315 12 16398
"000336AE7" 16569 14214 2 6.452055 12 16398
"000336AE7" 16572 14214 2 6.460274 12 16398
"000336AE7" 16573 14214 2 6.463014 12 16398
"000336AE7" 16574 14214 2 6.465754 12 16398
"000336AE7" 16575 14214 2 6.468493 12 16398
"000336AE7" 16576 14214 2 6.471233 12 16398
"000336AE7" 16579 14214 2 6.479452 12 16398
"000336AE7" 16580 14214 2 6.482192 12 16398
"000336AE7" 16581 14214 2 6.484931 12 16398
"000336AE7" 16582 14214 2 6.487671 12 16398
"000336AE7" 16583 14214 2 6.490411 12 16398
"000336AE7" 16587 14214 2  6.50137 13 16580
"000336AE7" 16588 14214 2 6.504109 13 16580
"000336AE7" 16589 14214 2 6.506849 13 16580
"000336AE7" 16590 14214 2 6.509589 13 16580
"000336AE7" 16593 14214 2 6.517808 13 16580
"000336AE7" 16594 14214 2 6.520548 13 16580
"000336AE7" 16595 14214 2 6.523288 13 16580
"000336AE7" 16596 14214 2 6.526027 13 16580
"000336AE7" 16597 14214 2 6.528767 13 16580
"000336AE7" 16600 14214 2 6.536986 13 16580
"000336AE7" 16601 14214 2 6.539726 13 16580
end
format %d trd_exctn_dt
format %d first_interest_date
format %td last_pmt_date

Even if the last_pmt_date was right, I don't know why for example on trd_exctn_dt from 25-27may2005, stata still gives the last_pmt_date as 23nov2004, instead of 24may2005? It seems not right to me.

Thank you so much

[Clyde Schechter]

This is all due to rounding and truncation, with the errors accumulating rather than canceling each other. In the example you show, from 1998 to 2005, two leap years intervene, so we are two days out of synch just from that. Then we add to it the fact that the payment intervals were calculated as halves or quarters of 365 days. Notice, for example that on 27 May 2005, the interval from 01dec1998 is shown as 6.490411, which is less than 6.5. It's not until that interval kicks up to 6.5 or more that the next payment gets reckoned in the code.

I think that what you need is code that does not make these approximations and does everything in terms of exact calendar dates. In principle, it is possible, but it's very finicky and I do not have the several hours of free time it would take me to work this out and debug it. So, I'm sorry, but I'm going to pass on this.

[Robert Picard]

This is indeed quite tricky to get right. From what I can gather, the possible frequencies for coupon payments are 1, 2, 3, 4 6, or 12 times a year and the coupon date is aligned with the first coupon date. The following solution looks backwards for the last coupon date, one month at a time, up to 12 months. This allows for the worst case scenario where coupons are annual and we must look 12 months back. The general rule is that coupons are issued on the same day of the month. The following code rolls back the date if the calculations overflow into the next month (e.g. the coupon is on day 31).

The first part creates a demonstration dataset that contains all 6 payment frequencies with daily observations over a period of 2 years and uses the same first coupon date throughout. This makes it easier to visualize how the code handles all the date possibilities. The last part of the code makes sure that last_date precedes the dtrade and that the difference in months is less than or equal to the length of the coupon.

* create a demonstration dataset with all possible coupon frequencies
clear
set obs 6
local periods 1 2 3 4 6 12
gen freq = real(word("`periods'", _n))
expand 730
bysort freq: gen dtrade = mdy(1,1,2005) + _n - 1
format %td dtrade

* pick a first coupon date
gen coupon1 = mdy(3,31,1998)
format %td coupon1

* coupon coverage in months
gen mcover = 12/freq
tab mcover

* for coding simplicity, extract the date components
gen dcoupon = day(coupon1)
gen mcoupon = month(coupon1)
gen dtr = day(dtrade)
gen mtr = month(dtrade)
gen ytr = year(dtrade)

// loop over a maximum of 12 months back
gen last_date = .
format %td last_date
forvalues i = 1/12 {

    // construct a date on coupon day i months before dtrade
    gen try = cond(dcoupon > dtr, ///
                    dofm(mofd(dtrade) - `i') + dcoupon - 1, ///
                    dofm(mofd(dtrade) - `i' + 1) + dcoupon - 1)
                    
    // adjust to last day of month to stay in same month
    replace try = mdy(month(try), 1, year(try)) - 1 if day(try) != dcoupon
    format %td try
    
    // how many months?
    gen offset = month(try) - mcoupon
    replace offset = mcoupon - month(try) if mcoupon >= month(try)
    
    // pick coupon date 
    replace last_date = try if mi(last_date) & ///
        mod(offset, mcover) == 0
    
    drop try offset
}

* checks
assert last_date <= dtrade
gen mdiff = mofd(dtrade) - mofd(last_date)
assert mdiff <= mcover

You can easily adapt the code for the data in #8 for example:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str14 complete_cusip long(trd_exctn_dt first_interest_date) byte interest_frequency float interval int n_pmts float last_pmt_date
"000336AE7" 16559 14214 2 6.424657 12 16398
"000336AE7" 16560 14214 2 6.427397 12 16398
"000336AE7" 16561 14214 2 6.430137 12 16398
"000336AE7" 16562 14214 2 6.432877 12 16398
"000336AE7" 16565 14214 2 6.441096 12 16398
"000336AE7" 16566 14214 2 6.443836 12 16398
"000336AE7" 16567 14214 2 6.446575 12 16398
"000336AE7" 16568 14214 2 6.449315 12 16398
"000336AE7" 16569 14214 2 6.452055 12 16398
"000336AE7" 16572 14214 2 6.460274 12 16398
"000336AE7" 16573 14214 2 6.463014 12 16398
"000336AE7" 16574 14214 2 6.465754 12 16398
"000336AE7" 16575 14214 2 6.468493 12 16398
"000336AE7" 16576 14214 2 6.471233 12 16398
"000336AE7" 16579 14214 2 6.479452 12 16398
"000336AE7" 16580 14214 2 6.482192 12 16398
"000336AE7" 16581 14214 2 6.484931 12 16398
"000336AE7" 16582 14214 2 6.487671 12 16398
"000336AE7" 16583 14214 2 6.490411 12 16398
"000336AE7" 16587 14214 2  6.50137 13 16580
"000336AE7" 16588 14214 2 6.504109 13 16580
"000336AE7" 16589 14214 2 6.506849 13 16580
"000336AE7" 16590 14214 2 6.509589 13 16580
"000336AE7" 16593 14214 2 6.517808 13 16580
"000336AE7" 16594 14214 2 6.520548 13 16580
"000336AE7" 16595 14214 2 6.523288 13 16580
"000336AE7" 16596 14214 2 6.526027 13 16580
"000336AE7" 16597 14214 2 6.528767 13 16580
"000336AE7" 16600 14214 2 6.536986 13 16580
"000336AE7" 16601 14214 2 6.539726 13 16580
end
format %d trd_exctn_dt
format %d first_interest_date
format %td last_pmt_date

* match variable names
clonevar coupon1 = first_interest_date
clonevar freq    = interest_frequency
clonevar dtrade  = trd_exctn_dt

* coupon coverage in months
gen mcover = 12/freq
tab mcover

* for coding simplicity, extract the date components
gen dcoupon = day(coupon1)
gen mcoupon = month(coupon1)
gen dtr = day(dtrade)
gen mtr = month(dtrade)
gen ytr = year(dtrade)

// loop over a maximum of 12 months back
gen last_date = .
format %td last_date
forvalues i = 1/12 {

    // construct a date on coupon day i months before dtrade
    gen try = cond(dcoupon > dtr, ///
                    dofm(mofd(dtrade) - `i') + dcoupon - 1, ///
                    dofm(mofd(dtrade) - `i' + 1) + dcoupon - 1)
                    
    // adjust to last day of month to stay in same month
    replace try = mdy(month(try), 1, year(try)) - 1 if day(try) != dcoupon
    format %td try
    
    // how many months?
    gen offset = month(try) - mcoupon
    replace offset = mcoupon - month(try) if mcoupon >= month(try)
    
    // pick coupon date 
    replace last_date = try if mi(last_date) & ///
        mod(offset, mcover) == 0
    
    drop try offset
}

* checks
assert last_date <= dtrade
gen mdiff = mofd(dtrade) - mofd(last_date)
assert mdiff <= mcover

list coupon1-last_date, sepby(last_date)

and the results

. list coupon1-last_date, sepby(last_date)

     +------------------------------------------------------------------------------------------+
     |   coupon1   freq      dtrade   mcover   dcoupon   mcoupon   dtr   mtr    ytr   last_date |
     |------------------------------------------------------------------------------------------|
  1. | 01dec1998      2   03may2005        6         1        12     3     5   2005   01dec2004 |
  2. | 01dec1998      2   04may2005        6         1        12     4     5   2005   01dec2004 |
  3. | 01dec1998      2   05may2005        6         1        12     5     5   2005   01dec2004 |
  4. | 01dec1998      2   06may2005        6         1        12     6     5   2005   01dec2004 |
  5. | 01dec1998      2   09may2005        6         1        12     9     5   2005   01dec2004 |
  6. | 01dec1998      2   10may2005        6         1        12    10     5   2005   01dec2004 |
  7. | 01dec1998      2   11may2005        6         1        12    11     5   2005   01dec2004 |
  8. | 01dec1998      2   12may2005        6         1        12    12     5   2005   01dec2004 |
  9. | 01dec1998      2   13may2005        6         1        12    13     5   2005   01dec2004 |
 10. | 01dec1998      2   16may2005        6         1        12    16     5   2005   01dec2004 |
 11. | 01dec1998      2   17may2005        6         1        12    17     5   2005   01dec2004 |
 12. | 01dec1998      2   18may2005        6         1        12    18     5   2005   01dec2004 |
 13. | 01dec1998      2   19may2005        6         1        12    19     5   2005   01dec2004 |
 14. | 01dec1998      2   20may2005        6         1        12    20     5   2005   01dec2004 |
 15. | 01dec1998      2   23may2005        6         1        12    23     5   2005   01dec2004 |
 16. | 01dec1998      2   24may2005        6         1        12    24     5   2005   01dec2004 |
 17. | 01dec1998      2   25may2005        6         1        12    25     5   2005   01dec2004 |
 18. | 01dec1998      2   26may2005        6         1        12    26     5   2005   01dec2004 |
 19. | 01dec1998      2   27may2005        6         1        12    27     5   2005   01dec2004 |
 20. | 01dec1998      2   31may2005        6         1        12    31     5   2005   01dec2004 |
     |------------------------------------------------------------------------------------------|
 21. | 01dec1998      2   01jun2005        6         1        12     1     6   2005   01jun2005 |
 22. | 01dec1998      2   02jun2005        6         1        12     2     6   2005   01jun2005 |
 23. | 01dec1998      2   03jun2005        6         1        12     3     6   2005   01jun2005 |
 24. | 01dec1998      2   06jun2005        6         1        12     6     6   2005   01jun2005 |
 25. | 01dec1998      2   07jun2005        6         1        12     7     6   2005   01jun2005 |
 26. | 01dec1998      2   08jun2005        6         1        12     8     6   2005   01jun2005 |
 27. | 01dec1998      2   09jun2005        6         1        12     9     6   2005   01jun2005 |
 28. | 01dec1998      2   10jun2005        6         1        12    10     6   2005   01jun2005 |
 29. | 01dec1998      2   13jun2005        6         1        12    13     6   2005   01jun2005 |
 30. | 01dec1998      2   14jun2005        6         1        12    14     6   2005   01jun2005 |
     +------------------------------------------------------------------------------------------+

[Lien Lekkerkerk]

Thanks a bunch Robert! You are brilliant! It works for me now.