How to create a new variable from multiplying data from another?

[Carolina Vasquez]

Hello, I'm sorry if this has been answered before but some solutions I found don't really suit my problem or I'm not able to understand them.

I have a database that looks like this, the table below is a simplified example:

year	month	id	month return
1999	1	1	1.01
1999	2	1	0.9
1999	3	1	1.03
1999	4	1	0.8
1999	5	1	1.03
1999	6	1	0.89
1999	7	1	1.01
1999	8	1	0.9
1999	9	1	1.03
1999	10	1	0.9
1999	11	1	0.8
1999	12	1	1.05

The above is a simple example, but what I have are 3 ids, and for each I have data from 20 years. This means that I have 240 monthly returns for each id.
I want to create a new variable called annual return, created from the values from month return. I need to calculate it as a simple return: (e.g. (1+return_annual ) = (1+r_month1)*(1+r_month3)*(1+r_month4)* ... * (1+r_month12) )

To be more clear, what I need should look like this:

year	month	id	month return	annual return
1999	1	1	1.01	.
1999	2	1	0.9	.
1999	3	1	1.03	.
1999	4	1	0.8	.
1999	5	1	1.03	.
1999	6	1	0.89	.
1999	7	1	1.01	.
1999	8	1	0.9	.
1999	9	1	1.03	.
1999	10	1	0.9	.
1999	11	1	0.8	.
1999	12	1	1.05	(1+returnannual )

If anyone could have some insight on how to do this, it would be wonderful. I'm not very skilled in Stata so I can't figure out how to do this. The missing values in the table above could just be the obtained annual return. The key is to create a new variable that has data for each year, from the monthly data.

Thank you in advance,
Carolina

[Erik Ruzek]

I'm don't understand the (1+return_month1) idea. If you want the sum of all the monthly returns, then an egen call should do the trick:

bysort(id year): egen annual_sum = total(month_return)

If the 1+ idea is that each of the month returns need to have 1 added to them, then I would create a new month_return variable that did that and then run the egen on that:

gen mont_return1 = month_return + 1
bysort(id year): egen annual_sum1 = total(month_return1)

[Andrew Musau]

If you are multiplying across observations, you can take advantage of the nice property of logarithms which states that the log of a product is the sum of the logs.

log_a xy = log_a x + log_a y.

Like #2, it is not clear what those returns are (are they percentage returns?) In any case, consider

di (2)*(4)*(6)
di "`=exp(`=log(2)'+ `=log(4)'+ `=log(6)')'"

Res.:

. di (2)*(4)*(6)
48

.
. di "`=exp(`=log(2)'+ `=log(4)'+ `=log(6)')'"
48.00000000000001

[Carolina Vasquez]

Hello Erik! Thank you for your answer.

I'm sorry if it wasn't clear. In the month return column at the table, those returns are already (1+return_month1). For example, the first return is exactly that: 1.01 = (1+return_month1) , and so on.

Looking at what the first code you suggested, that looks like a solution, but I don't need them to be a sum of the returns, but a multiplication, hence the (1+return_annual ) = (1+r_month1)*(1+r_month3)*(1+r_month4)* ... * (1+r_month12) . In this case, using the data in the table, it should be something like:
(1+return_annual ) = 1.01 * 0.9 * 1.03 * .... etc.

Is there a way to do exactly what you suggested, but instead of a sum, a multiplication?

Thanks again!

[Hemanshu Kumar]

#2 creates the sum of monthly returns; I think you need the product. You could try this: (I am assuming your return already has the 1+ built into it).

gen ln_return = ln(return)

bysort id year: egen ln_annual_return = total(ln_return)
gen annual_return = exp(ln_annual_return)
drop ln_return ln_annual_return

[Carolina Vasquez]

Thank you Andrew and Hemanshu! I didn't think of this before! This totally solves my problem. It was more of a math skills problem that Stata apparently!

I will try your solutions but I'm pretty sure they will work nicely.

Thanks again!

[Erik Ruzek]

Sorry about my confusion, but building on Andrew Musau's insight, the following should work:

gen log_month = exp(log(month_return))
bysort(id year): egen annual_return= total(log_month)

[Carolina Vasquez]

Update: It worked! I did this:

gen log_month_return = log(month_return)
bysort id year: egen log_annual_return = total(log_month_return)
gen annual_return = exp(log_annual_return)

This generated 3 new variables:
- First line created logarithmic monthly returns.
- Second created the log annual return. This created the same result for each monthly observation from the same year).
- Third converted the previous into non logarithmic annual returns.

Thank you everyone! Greetings and take care