Forming decile portfolios of mutual funds on lagged one-year returns.

[Thijs Kroon]

Dear Statalist,.

for a performance analysis as in Carhart (1997) I need to form decile portfolios of mutual funds on lagged one-year excess returns. On January the first of each year I would like to form 10 equally weighted portfolios ranging from the lowest excess return to the portfolio with the highest excess return (based on the prior year monthly excess returns over a one year period). These portfolios will be held for one year after which they are re-formed (again on January the first over the lagged monthly one year return). Doing this for my dataset will yield to a time series of monthly excess returns on each decile portfolio. Since not all my funds survive through my entire dataset I will include the funds until they disappear. So even if a fund disappears from my dataset on the first of April I would like to include the fund until this moment in my equally weighted portfolio.
I tried to use the astile command but this did not work out for me. I did not divide the portfolios correctly according to their excess return.

 bys time : astile portfolio = excessReturn , nq(10)

A second line of reasoning was to generate the lags separately for each month to calculate the cumulative excess return for each year after which it can be assigned to portfolios.

 gen L1_excessReturn = L1.excessReturn
gen L2_excessReturn = L2.excessReturn….
….gen L12_excessReturn = L12.excessReturn

I however am not sure how to proceed this way either since:

 gen cum_excessReturn = sum(L*_excessReturn)

gives me an error: L ambiguous abbreviation

I’m working with panel data. _j identifies the fund (254 funds in total) and time identifies the time in months (144 months in total)

* Example generated by -dataex-. To install: ssc install dataex
clear
input long time int(_j date) double(return RF) float excessReturn double portfolio
 92 111 19571  -63.75975345878958  -.003400460951373363  -63.75635 1
109  94 20089 -53.147482014388494     -.062559398371968  -53.08492 1
 90  95 19510  -50.54347826086957 .00046049349552945706  -50.54394 1
 34 136 17806 -28.421052631578945  -.015874875017854673  -28.40518 1
 34   3 17806 -27.467069747354778  -.015874875017854673 -27.451195 1
 34 114 17806 -26.867771178493282  -.015874875017854673 -26.851896 1
 34 111 17806 -26.777530443891585  -.015874875017854673 -26.761656 1
 34 109 17806 -26.770751505629736  -.015874875017854673 -26.754877 1
 34 112 17806  -26.71702335991047  -.015874875017854673  -26.70115 1
 34 113 17806 -26.716559752910428  -.015874875017854673 -26.700686 1
 34  59 17806  -25.93149540517962  -.015874875017854673  -25.91562 1
 34  54 17806 -25.833016596984663  -.015874875017854673  -25.81714 1
 34  57 17806    -25.794922356421  -.015874875017854673 -25.779047 1
 34 173 17806 -23.835491933651433  -.015874875017854673 -23.819616 2
 34 195 17806 -23.636363636363633  -.015874875017854673  -23.62049 2
 34 193 17806 -23.563218390804604  -.015874875017854673 -23.547344 2
 34 172 17806  -23.24980872226473  -.015874875017854673 -23.233934 2
 34  14 17806  -23.23193551482584  -.015874875017854673  -23.21606 2
 34 228 17806  -22.08755168971909  -.015874875017854673 -22.071676 2
 34 237 17806 -22.023313905079096  -.015874875017854673  -22.00744 2
 34  16 17806  -21.13525742099129  -.015874875017854673  -21.11938 2
 34 140 17806 -21.006289308176108  -.015874875017854673 -20.990414 2
 34 139 17806 -21.000684061370073  -.015874875017854673  -20.98481 2
 34 108 17806   -20.9965102753005  -.015874875017854673 -20.980635 3
 34 137 17806  -20.96705177578091  -.015874875017854673  -20.95118 3
 34   2 17806  -20.92716686806547  -.015874875017854673  -20.91129 3
 34 103 17806 -20.906903908129454  -.015874875017854673  -20.89103 3
 34 105 17806 -20.904836193447746  -.015874875017854673  -20.88896 3
 34 106 17806 -20.904836193447746  -.015874875017854673  -20.88896 3
 34 107 17806 -20.859628176701353  -.015874875017854673 -20.843754 3
 33  59 17776 -19.890242270111102    .10369101065834996  -19.99393 1
 33  57 17776  -19.80628582723859    .10369101065834996 -19.909977 1
 33  54 17776 -19.802885592359274    .10369101065834996 -19.906576 1
 34 241 17806  -19.89651548225402  -.015874875017854673  -19.88064 3
 96 106 19693 -19.543343653250762   .016449673588315505 -19.559793 1
 34 125 17806  -19.51219512195122  -.015874875017854673  -19.49632 3
 33 193 17776 -19.319938176197834    .10369101065834996  -19.42363 1
 33 195 17776 -19.315403422982886    .10369101065834996 -19.419094 1
 33 173 17776 -19.195813825392463    .10369101065834996 -19.299505 1
 34 152 17806 -19.305571666086678  -.015874875017854673   -19.2897 3
 34 163 17806   -19.3013231756215  -.015874875017854673  -19.28545 4
 34 154 17806  -19.30030065206357  -.015874875017854673 -19.284426 4
 34 162 17806 -19.299037304452458  -.015874875017854673 -19.283163 4
 34 153 17806  -19.21165233176297  -.015874875017854673  -19.19578 4
 34 155 17806 -19.155817571440267  -.015874875017854673  -19.13994 4
 34 169 17806   -18.6901609289061  -.015874875017854673 -18.674286 4
 34 168 17806 -18.688239765446856  -.015874875017854673 -18.672365 4
 34 242 17806 -18.500453720508155  -.015874875017854673  -18.48458 4
 33 188 17776  -17.92682926829269    .10369101065834996  -18.03052 1
 33 187 17776  -17.90799561883899    .10369101065834996 -18.011686 1
 33 189 17776  -17.90074832611264    .10369101065834996 -18.004438 1
 33 114 17776 -17.784631200205606    .10369101065834996 -17.888323 1
 33 109 17776   -17.7626443433555    .10369101065834996 -17.866335 2
 33 111 17776 -17.757915141072576    .10369101065834996 -17.861607 2
 33 112 17776 -17.751955821445016    .10369101065834996 -17.855646 2
 33 113 17776 -17.744772327535664    .10369101065834996 -17.848463 2
 33 174 17776 -17.527636054421762    .10369101065834996 -17.631327 2
 35 169 17837 -17.549793310785407    .01680108158946636 -17.566595 1
 35 168 17837 -17.548076923076916    .01680108158946636 -17.564878 1
 34 132 17806  -17.43027888446215  -.015874875017854673 -17.414404 4
 35 114 17837  -17.03355417824321    .01680108158946636 -17.050356 1
 35 111 17837 -17.024320457796858    .01680108158946636 -17.041122 1
 35 109 17837    -17.020515800295    .01680108158946636 -17.037317 1
 53 237 18383 -17.060681991502353  -.058036053130929965 -17.002645 1
 35 112 17837  -16.91162435579309    .01680108158946636 -16.928425 1
 35 113 17837 -16.907116226292764    .01680108158946636 -16.923918 1
 33 190 17776 -16.600790513833992    .10369101065834996 -16.704481 2
 68 114 18840 -16.635696821515896    .01352052672347015 -16.649218 1
 34 196 17806 -16.633706877609317  -.015874875017854673 -16.617832 4
 68 109 18840  -16.57193534083121    .01352052672347015 -16.585455 1
 68 111 18840   -16.5712442544956    .01352052672347015 -16.584764 1
 68 113 18840 -16.536328735959042    .01352052672347015 -16.549849 1
 68 112 18840 -16.531346781618815    .01352052672347015 -16.544867 1
 34 190 17806 -16.429699842022124  -.015874875017854673 -16.413824 5
 34 174 17806 -16.355200412424278  -.015874875017854673 -16.339325 5
 33 228 17776   -16.1625821876868    .10369101065834996 -16.266273 2
 35 103 17837  -16.24103299856527    .01680108158946636 -16.257833 1
 35 106 17837  -16.23931623931623    .01680108158946636 -16.256117 1
 35 105 17837  -16.23931623931623    .01680108158946636 -16.256117 1
 69  16 18871 -16.324608034452595   -.06852391144170378 -16.256084 1
 33 241 17776 -16.150899908483893    .10369101065834996 -16.254591 2
 35 108 17837  -16.22085889570552    .01680108158946636  -16.23766 2
 35 107 17837 -16.185344827586203    .01680108158946636 -16.202147 2
 35   2 17837 -16.171158655181994    .01680108158946636  -16.18796 2
 77  16 19114 -16.075714971538304   -.05583137996219267 -16.019884 1
 34 186 17806 -16.017587939698508  -.015874875017854673 -16.001713 5
 33 186 17776  -15.81173982020094    .10369101065834996  -15.91543 2
 53 241 18383 -15.896632732967891  -.058036053130929965 -15.838596 1
 34 252 17806 -15.678985348786355  -.015874875017854673  -15.66311 5
 33 125 17776 -15.463917525773196    .10369101065834996  -15.56761 2
 77  56 19114  -15.55555555555556   -.05583137996219267 -15.499724 1
 77  23 19114 -15.518778557895907   -.05583137996219267 -15.462947 1
 53  56 18383 -15.493951612903231  -.058036053130929965 -15.435916 1
 34 177 17806 -15.412452445021806  -.015874875017854673 -15.396578 5
 35 195 17837  -15.35947712418302    .01680108158946636 -15.376278 2
 69 193 18871 -15.436810856658187   -.06852391144170378 -15.368287 1
 34 175 17806 -15.367627955313065  -.015874875017854673 -15.351753 5
 69 195 18871 -15.416270971826531   -.06852391144170378 -15.347747 1
 34 176 17806  -15.32856942693009  -.015874875017854673 -15.312695 5
 34 179 17806 -15.327322618580954  -.015874875017854673 -15.311448 5
end
format %tdnn/dd/CCYY date

Thanks in advance!


*Carhart, M. M. (1997). On persistence in mutual fund performance. The Journal of finance, 52(1), 57-82

[Clyde Schechter]

I'm not familiar with the Carhart reference and I don't have access to it. From your code and explanations, I am not really sure I understand what you are trying to do. I think maybe you want to do this:

1. For each fund in each calendar year, calculate the total of excess returns of that fund in the preceding year. Call that cumulative_excess_returns_prior_year.

2. Partition the funds in each calendar year into deciles of cumulative_excess_returns_prior_year.

Now, this is impossible to do in your example data because nearly all of the observations for a given fund lack any observations for the same fund in the preceding year. But let me assume that your real data is not so badly gapped. Then, you could apply the following code:

gen year = yofd(date)
rangestat (sum) cum_excess_return_prior_year = excessReturn, by(_j) interval(year -1 -1)
by year, sort: egen decile = xtile(cum_excess_return_prior_year)

Now, to run this you will have to install the -rangestat- program, by Robert Picard, Nick Cox, and Roberto Ferrer, available from SSC. Also, official Stata's -egen- does not have an -xtile()- function, but you can get it by installing the -egenmore- package from SSC.

[Thijs Kroon]

Thank you for your answer Clyde!
he assumptions you made were indeed correct. My real data does not have these gaps and you did understand what I need. However the code you so kindly provided is not entirely the right one. The code you provide retuned: cum_excess_return_prior_year = 0 if there were no observations in that year for this fund. But if there is no fund observation I do not want it to be taken into account in that point of time. I only want the fund to occur when it does have data. But this problem can easily be solved by the code:

 replace cum_excess_return_prior_year=. if cum_excess_return_prior_year==0

For the formation of portfolios however I do not understand the code you provided.

 by year, sort: egen decile = xtile( cum_excess_return_prior_year)

Returns only two portfolios and I do not understand how they are formed. However if I just simply use:

 xtile decile = cum_excess_return_prior_year, nq(10)

I do get results that appear to be the correct ones for me. Do I make a mistake thinking this easy or can I go for this code?

[Nick Cox]

The help for egenmore (SSC) underlines that xtile() there included defaults to producing two bins, split at the median. In this case, binning is separately by year. Just as with the xtile command you need to spell out that you want decile bins with an option nq(10).

xtile(varname) [ , percentiles(numlist) nquantiles(#) weights(varname) altdef
by(byvarlist) ] categorizes varname by specific percentiles. The function works
like xtile. By default varname is dichotomized at the median. percentiles()
requests percentiles corresponding to numlist: for example, p(25(25)75) is used to
create a variable according to quartiles. Alternatively you also may have specified
n(4): to create a variable according to quartiles. weights() requests weighted
calculation of percentiles. altdef uses an alternative formula for calculating
percentiles. See xtile. by() requests calculation by groups. You may also use the
by: construct. (Stata 8.2 required.)

Conversely your xtile command lumps all the years together. If that's what you want, go for it.

The main rationale for writing xtile() as an extra egen function is indeed the lack of a by() option or support for by: in xtile.

[Thijs Kroon]

Clear, thank you.

[Clyde Schechter]

Thijs, I'm sorry. I meant to have an -nq(10)- option on my -egen decile = xtile(...- command, and omitted it by mistake.

As Nick points out, your substitute command does not create separate portfolios for each year. So, to be completely explicit, the command you need is:

by year, sort: egen decile = xtile( cum_excess_return_prior_year), nq(10)