Generate monthly date variables to project dividend schedule

[Francois Durant]

Hi,

I would like to generate monthly date variables to project dividend schedule for each client depending on the beginning date of the contract and a given maturity of that contract in years. Here is the data I have:

* Example generated by -dataex-. For more info, type help dataex
clear
input double date str26 BegBates double dol_int_m byte term
20150 "USAO_SDNY_R_CFTC_00120711"              42.72 3
20150 "USAO_SDNY_0082559"                        750 3
20173 "USAO_SDNY_R_0000036092"                 591.7 3
20241 "USAO_SDNY_0083439"                     702.08 3
20289 "USAO_SDNY_0082671"                    3333.31 5
20321 "USAO_SDNY_0082613"          181.8194425018166 5
20340 "USAO_SDNY_R_CFTC_00166287"              31.71 5
20347 "USAO_SDNY_0082648"                    1666.67 5
20349 "USAO_SDNY_R_CFTC_00093121"              49.56 5
20501 "USAO_SDNY_R_CFTC_00164134"              32.29 5
20507 "USAO_SDNY_R_0000050534"               1506.23 5
20513 "USAO_SDNY_R_CFTC_00166272"             129.06 5
20537 "USAO_SDNY_R_CFTC_00164887"               4.98 5
20543 "USAO_SDNY_R_CFTC_00166226"              30.66 5
20543 "USAO_SDNY_R_CFTC_00166241 "             48.88 5
20543 "USAO_SDNY_R_CFTC_00166211"              48.88 5
20543 "USAO_SDNY_R_CFTC_00166197"              30.66 5
20552 "USAO_SDNY_R_CFTC_00164315"              99.45 5
20590 "USAO_SDNY_R_0000051822"                186.95 5
20591 "USAO_SDNY_R_0000052074"                 28.03 5
20593 "USAO_SDNY_R-0000052060"                266.35 2
20594 "USAO_SDNY_R_0000052009"                 27.13 5
20607 "USAO_SDNY_R_0000051959"                 91.46 5
20607 "USAO_SDNY_R_0000051252"                 91.46 5
20615 "USAO_SDNY_0000051447"                  268.54 5
20654 "USAO_SDNY_R_0000080237"                108.66 5
20683 "USAO_SDNY_R_0000049619"                363.99 5
20683 "USAO_SDNY_R_0000049521"                 84.62 5
20683 "USAO_SDNY_R_CFTC_00166059"             363.99 5
20698 "USAO_SDNY_R_0000051557"                 60.93 5
20699 "USAO_SDNY_R_0000049106"                 162.5 5
20705 "USAO_SDNY_R_CFTC_00164601"             362.31 5
20710 "USAO_SDNY_R_CFTC_00166153"             719.69 5
20711 "USAO_SDNY_R_0000050949"                602.48 5
20711 "USAO_SDNY_R_0000050236"                405.83 3
20752 "USAO_SDNY_R_0000053310"                 58.04 5
20752 "USAO_SDNY_R_0000052702"                 58.04 5
20755 "USAO_SDNY_R_CFTC_00165602"              28.84 5
20761 "USAO_SDNY_0083285"                     115.62 5
20824 "USAO_SDNY_R_0000008347"               1721.36 5
20832 "USAO_SDNY_R_CFTC_00170063"             431.25 5
20836 "USAO_SDNY_R_0000052842"                186.49 5
20836 "USAO_SDNY_R_CFTC_00165562"             186.49 5
20864 "USAO_SDNY_R_0000052950"                   375 5
20864 "USAO_SDNY_R_0000052827"                   375 5
20864 "USAO_SDNY_R_0000052965"                  58.5 5
20880 "USAO_SDNY_0082905"                     111.76 5
20880 "USAO_SDNY_R_0000053261"                 115.9 5
20880 "USAO_SDNY-R-CFTC-00165951"              115.7 5
20882 "USAO_SDNY_R_CFTC_00166428"             159.84 5
20903 "USAO_SDNY_R_CFTC_00166457"                375 5
20943 "USAO_SDNY_R_0000036337"                159.84 5
20948 "USAO_SDNY_R_CFTC_00164432"             406.25 5
20961 "USAO_SDNY_R_CFTC_00165930"              70.58 5
20963 "USAO_SDNY_R_CFTC_00165900"                425 5
20963 "USAO_SDNY_0083931"                        425 5
20964 "USAO_SDNY_R_CFTC_00165915"              27.62 5
20964 "USAO_SDNY_R_0000055141"                 27.62 5
20964 "USAO_SDNY_00073924"                       375 5
20984 "USAO_SDNY_R_CFTC_00166088"             427.38 5
20984 "USAO_SDNY_R_0000055875"                 93.31 5
20984 "USAO_SDNY_R_CFTC_00165883"              259.5 5
20984 "USAO_SDNY_R_0000055827"                568.42 5
20984 "USAO_SDNY_R_CFTC_00165869"             568.42 5
20984 "USAO_SDNY_R_CFTC_00166410"             360.41 5
20984 "USAO_SDNY_R_CFTC_00165376"             360.41 5
20997 "USAO_SDNY_R_CFTC_00165821"             497.33 5
20997 "USAO_SDNY_R_CFTC_00164367"             130.37 5
21026 "USAO_SDNY_R_CFTC_00165790"               31.2 5
21045 "USAO_SDNY_R_CFTC_00165992"             691.81 5
21053 "USAO_SDNY_R_CFTC_00165773"             140.36 5
21077 "USAO_SDNY_R_CFTC_00165756"             303.04 5
21102 "USAO_SDBY_R_CFTC_00164858"             871.47 5
21151 "USAO_SDNY_R_CFTC_00166256"             140.88 5
21154 "USAO_SDNY_R_CFTC_00165968"             536.97 5
21155 "USAO_SDNY_R0000060059"                 133.07 5
21159 "USAO_SDNY_0083381"                     708.01 5
21168 "USAO_SDNY_R_CFTC_00165433"              33.28 5
21168 "USAO_SDNY_R_CFTC_00166044"             258.27 5
21181 "USAO_SDNY_R_CFTC_00166348"            1610.99 5
21181 "USAO_SDNY_R_CFTC_00166362"               29.1 5
21185 "USAO_SDNY_R_CFTC_00166303"              30.66 5
21197 "USAO_SDNY_R_CFTC_00165695"             348.95 5
21217 "USAO_SDNY_R_CFTC_00165711"              27.62 5
21228 "USAO_SDNY_R_CFTC_00166123"             348.95 5
21230 "USAO_SDNY_R_CFTC_00165680"                211 5
21259 "USAO_SDNY_R_CFTC_00165177"            3033.63 5
21272 "USAO_SDNY_R_CFTC_00166471"             134.06 5
21327 "USAO_SDNY_R_CFTC_00165805"             134.06 5
21328 "USAO_SDNY_R_CFTC_00166484"             134.06 5
21371 "USAO_SDNY_R_CFTC_00166443"             228.24 5
21484 "USAO_SDNY_R_CFTC_00165618"             374.68 5
end
format %td date

For each client "BegDates", I would like to create a new date starting from the next month of the beginning of the contract called "date". This variable would be populated with the monthly $ dividend "dol_int_m" which is fixed over the maturity of the contract "term". So, for a 3 year contract, I would like to create 12*3 = 36 month variables that would not include the day but only the month and year. So for a contract starting in "03mar2015", the first variable created would have a name like "_2015_04" and would be populated with the "dol_int_m". The next variable for the next month "_2015_05" and would be populated with the same "dol_int_m". The process would be repeated for 36 month variables, all the way to "_2018_03" with the same dividend payment "dol_int_m".

Would you be able to help? I do not know how to iterate in a way that I create new date variables based on the beginning "date" and maturity "term" of the contract. Thanks.

[Francois Durant]

Anyone has an idea? Thanks

[Hemanshu Kumar]

I think before finding a solution, it might be worth thinking more clearly about a sensible data structure you need. Since the terms can vary from 3 to 5 years (as per your sample), and more importantly, you have many different contract start dates, you will end up with lots and lots of variables, a large number of which will be empty for any given row. You might prefer a long format instead, or a different way of doing this.