Annualization based on monthly returns, last request on Statalist

[LydiaSmit]

Last request on Statalist based on the following table:

Month	Employment (thousands)	Monthly percent change (not annualized)	Monthly percent change (annualized)
December	9,452.5	n/a	n/a
January	9,465.2	.13	1.62
February	9,472.9	.08	0.98
March	9,498.3	.27	3.27
April	9,516.3	.19	2.30
May	9,539.5	.24	2.96
June	9,553.8	.15	1.81
July	9,574.8	.22	2.67
May/Dec	n/a	.92	2.22

The last couple of days I've been trying to replicate the last column with only the third column (not annualized monthly change) because in my own dataset I don't have a column similar to the second column (Employment (thousands)). For my own dataset, I need to be able to get the result '2.22' with only monthly data (not annualized yet). (If I know a method for the above table, I can use that method for my own data.)

There are 2 methods if you have the column with 'Employment (thousands)' which I don't have.

Method 1:




Using the above formula for the months December, Jan (1.62), Feb (0.98), March (3.27), April (2.30), May (2.96) and then calculate the average of those results, which is 2.223.

Method 2:

Hopefully you know a method which I can use in Stata to get 2.22 as a result with only 'Monthly percent change' (not annualized). (I don't have a column yet which is similar to the 'Emloyment' column)

Source: http://dallasfed.org/research/basics/annualizing.cfm

[Roberto Liebscher]

You could add 1 to your monthly percentage changes, subsequently take the log of this sum and add those logarithmic values. Raising e to the power of this sum gives the desired result.

Edit: Finally you have to subtract 1 to arrive at 2.2 percent.

[Roberto Liebscher]

There might be a better way but here I tried to compute the cumulative annualized percentage change in a step by step manner:

clear all
input chg
0.13
0.08
0.27
0.19
0.24
0.15
0.22
end

//Divide through 100 to get decimal numbers
gen chgdec = chg/100+1
//Take log
gen logchgdec = log(chgdec)
//Sum them up
gen sumchg = sum(logchgdec)
//Exp
gen expsumchg = exp(sumchg)
//Annualize
gen annual = expsumchg^(12/_n)-1

Usually you would simple multiply the percentage changes (after you added 1) and annualize. But you could instead take logs and sum them up (instead of multiplying). I also would state some formulas but I cannot figure out how to use TeX here.

[bsc.j.j.w]

Well, this case is just a simple kind of mathematics. Let us consider the following:

Your method one is essentially:

$$\left(\frac{m_t}{m_{t-1}}\right)^{12}-1$$

where the third column is simply:

$$\frac{m_t}{m_{t-1}}$$

So from the third column to the fourth column, we just raise it to the 12 power and then subtract one. In Stata 13.1 terms:

gen fourth_column = (third_column)^12-1

The method provided by Roberto is better, since it uses the numerical stability of computers (http://en.wikipedia.org/wiki/Numerical_stability). If you insist on using method 1, it is going to fail in our case due to:

$$e^{\ln\left(\frac{m_t}{m_{t-1}}\right)\cdot 12}-1$$

which due to the multiplication is going to be the same as above, so numerical stability does not really exists here.

PS: for nice latex stuff do

$$...$$

Btw, your second method is actually if you have like quarterly data and stuff, you shouldn't use that one I think.

[LydiaSmit]

Well, this case is just a simple kind of mathematics. Let us consider the following:

Your method one is essentially:

$$\left(\frac{m_t}{m_{t-1}}\right)^{12}-1$$

where the third column is simply:

$$\frac{m_t}{m_{t-1}}$$

So from the third column to the fourth column, we just raise it to the 12 power and then subtract one. In Stata 13.1 terms:

gen fourth_column = (third_column)^12-1

The method provided by Roberto is better, since it uses the numerical stability of computers (http://en.wikipedia.org/wiki/Numerical_stability). If you insist on using method 1, it is going to fail in our case due to:

$$e^{\ln\left(\frac{m_t}{m_{t-1}}\right)\cdot 12}-1$$

which due to the multiplication is going to be the same as above, so numerical stability does not really exists here.

PS: for nice latex stuff do

$$...$$

Btw, your second method is actually if you have like quarterly data and stuff, you shouldn't use that one I think.

Thank you very much for the help. Just before I saw your post, I figured out how to get the last column, except how to get the value of 2.22 which by far was the hardest problem.

method 1: "Using the above formula for the months December, Jan (1.62), Feb (0.98), March (3.27), April (2.30), May (2.96) and then calculate the average of those results, which is 2.223." I misread that. It turned out to be 0.0223 which of course is 2.23 if you multiply with 100....

using method 2 gives 2.223

So my first method was wrong. By the way, you forgot something. The +1 in your code below.

gen fourth_column = (third_column+1)^12-1

[LydiaSmit]

You could add 1 to your monthly percentage changes, subsequently take the log of this sum and add those logarithmic values. Raising e to the power of this sum gives the desired result.

Edit: Finally you have to subtract 1 to arrive at 2.2 percent.

Somehow that didn't work (I probably misunderstood that calculation). However, your second method works perfectly!
I just need a little adjustment now. Could you or someone else adjust your code so that it works for my whole dataset instead of only for the first year of the first ID-code.

  //Divide through 100 to get decimal numbers

gen chgdec = chg/100+1
//Take log
gen logchgdec = log(chgdec)
//Sum them up
gen sumchg = sum(logchgdec)
//Exp
gen expsumchg = exp(sumchg)
//Annualize
gen annual = expsumchg^(12/_n)-1

Thank you very much Roberto.
In my dataset, I have about 600 ID-codes. For many of these codes I have monthly data. Some ID-codes have monthly data for several years.
Could you or someone else adjust your code so that it works for my whole dataset instead of only for the first year of the first ID-code.

I only need to keep the final annualized result for each year. For example, if an ID-code has monthly data from jan2013 until may2014. Then I only want to keep the annualized result of dec2013 and the annualized result of may2014. Like the 2.22 in the table of my first post. (The number of the last column and the last row).

That is my final request on Statalist.