calculate year t annual returns for the 12-month period starting from 3-months after year t-1 fiscal year end

[Jessica Xie]

Hi All,

I have monthly return data for companies. Based on these monthly returns, I need calculate annual returns of year t for 12-month period starting from 3-months after year t-1 fiscal year. That is, if company's fiscal year end on June, year t annual returns will be from October, this year to next year September.

My data is:
ID date ret

02 29feb1988 -.036144577
02 31mar1988 .025
02 29apr1988 -.048780486
02 31may1988 -.012820513
02 30jun1988 .025974026
02 29jul1988 -.012658228
02 31aug1988 -.025641026
02 30sep1988 -.013157895
02 31oct1988 0
02 30nov1988 -.06666667
02 30dec1988 -.042857144
02 31jan1989 .059701491
02 28feb1989 -.014084507
02 31mar1989 -.028571429

I know when the fiscal year end on December, the code is as follows.

gen int fy = year(date)
replace fy = fy-1 if month(date) < 4
gen log_one_plus_return = log(1+ret)
egen annual_return = total(log_one_plus_return), by(permno fy)
replace annual_return = exp(annual_return) - 1

But when some companies' fiscal year is not on December, then how can we create "fiscal year" to calculate the year t's annual returns starting from 3-months after year t-1 fiscal year?

Thanks!!!

[Clyde Schechter]

So the question boils down to how to calculate the variable fy in your code when the fiscal year end is not December. The sample data you show don't actually say anything about when the fiscal year ends. So I'll just assume you have another variable somewhere that you didn't mention, let's call it fy_end that ranges from 1 to 12 and encodes the final calendar month of the firm's fiscal year. You want to calculate a period variable, let's call it per, so that it is constant between that month +3 and that month - 8, inclusive. I assume that fy_end is constant within firm. I also assume that there is only one observation in each month.

by id (date), sort: gen period = sum(month(date) == mod(fy_end+4, 12))

This will mark each firm's data into 12 month periods with the desired beginning an end. The first such period will be either 0 or 1, and then it goes up from there by 1. So in your -egen annual_return- command, just replace fy in the -by()- option with period.

[Robert Picard]

Clyde, I'm afraid that you need a more complicated condition to identify period changes, this will not work if the fiscal year ends in August.

I'm also concerned with what Jessica wants to do with the results. Since the 12-month window is not aligned with the fiscal year or calendar year, what does she want to do with these annual returns?

Anyway, here's a worked-out example using made-up monthly data for 100 firms over 5 years. Since I get really confused trying to make sense of this 12 month window year, I double-check the results using -rangestat- (from SSC).

* setup up fake data, start with 100 firms
clear
set seed 432142
set obs 100
gen permno = _n

* pick a random month for the fiscal year end
gen fy_month = runiformint(1,12)

* add 5 years of monthly data up to April 2015
expand 60
bysort permno: gen mdate = tm(2015-5) - _n
format %tm mdate

* add some random returns, roughly half are negative
gen ret = runiform() * .04 * cond(runiform() > .5,-1,1)

isid permno mdate, sort


* the 12 month window ends 3 months after the fiscal year, if that
* puts it over 12, loop back to new year months.
gen w12end = cond(fy_month + 3 > 12, mod(fy_month + 3,12), fy_month + 3)

* the reference year for the 12 month window is next year if the
* month of the current obs if after the month were the window ends
gen w12year = year(dofm(mdate))
replace w12year = w12year + 1 if month(dofm(mdate)) > w12end

* calculate the 12 month return
gen ret1 = log(1+ret)
bysort permno w12year: egen double w12ret_tot = total(ret1)
gen double w12ret = exp(w12ret_tot) - 1


* confirm using -ragestat- (from SSC)
isid permno mdate, sort

* tag the end of the fiscal year month
gen tag = month(dofm(mdate)) == fy_month

* the start of the interval to use to calculate 12 month return
gen istart = mdate - 8 if tag
gen iend = mdate + 3 if tag
format %tm istart iend

* no need to calculate for observations that are not fiscal end month
replace istart = 0 if !tag
replace iend = 0 if !tag

rangestat (sum) ret1 (count) ret1, interval(mdate istart iend) by(permno)

assert ret1_sum == w12ret_tot if tag

[Clyde Schechter]

Robert is correct: my code fails if the fiscal year ends in August. But it is simple to fix that:

gen byte  target = mod(fy_end + 4, 12)
replace target = 12 if target == 0
by id (date), sort: gen period = sum(month(date) == target)

and then proceeding to do annual returns within period.

That said, -rangestat- is very elegant.

[Jessica Xie]

Hi Prof. Schechter and Prof. Picard,

Thank you for your great help! The code works great. I learned a lot from this discussion.

Thanks again!

[Thuy Dung Nguyen]

Robert is correct: my code fails if the fiscal year ends in August. But it is simple to fix that:

gen byte target = mod(fy_end + 4, 12)
replace target = 12 if target == 0
by id (date), sort: gen period = sum(month(date) == target)

and then proceeding to do annual returns within period.

That said, -rangestat- is very elegant.

Dear Clyde,

If now we have firms with the different fiscal-year end, how can we compute the rolling of 12month for each firm?
Because the interval in -rangestat- will be different for each firm.
For example:
- Firms have December fiscal-year end will have an interval for computing returns from month 1(year t) to month 12 (year t)
- Firms have March fiscal-year end will have an interval for computing returns from month 4 (year t-1) to month 3(year t)

Thank you so much for your consideration,