CAGR with six month lag

[bpkaufman]

I know how to calculate cagr though can someone please let me know if
this seems right for cagr with a six month lag?

((January 1, 1992 Value/July 1, 1991)^2)-1)*100

For some reason this has not been working for me...

Thanks,
bk

 

[joeu2004]

I know how to calculate cagr though can someone please let me know if
this seems right for cagr with a six month lag?
((January 1, 1992 Value/July 1, 1991)^2)-1)*100
For some reason this has not been working for me...

Using your notation, try:

( ( (July 1 1992 value) / (January 1 1992 value) )^2 - 1 ) * 100

Note that you do not need "*100" if you are writing an Excel formula.
Simply format as Percentage with 2 decimal places, if you want (Format

 

[bpkaufman]

Using your notation, try:

( ( (July 1 1992 value) / (January 1 1992 value) )^2 - 1 ) * 100

Note that you do not need "*100" if you are writing an Excel formula.
Simply format as Percentage with 2 decimal places, if you want (Format

Thanks for the response, Joe, but I didn't have any luck... here is the
series I am working on.. and below it is the correct figures I should
be getting with a 6 mth lagged cagr..I've tried everything and am
becoming desperate..


7/1/91 12.4545
8/1/91 12.5732
9/1/91 12.6147
10/1/91 12.6572
11/1/91 12.8421
12/1/91 13.0317
1/1/92 13.208
2/1/92 13.3711
3/1/92 13.4381
4/1/92 13.6484
5/1/92 13.8817
6/1/92 14.0936
7/1/92 14.3233
***********************

1/1/92 12.90030006
2/1/92 13.01828899
3/1/92 13.09353062
4/1/92 15.52380093
5/1/92 15.84093727
6/1/92 15.45566734
7/1/92 15.58660109

 

[David Biddulph]

I don't know what makes you think that the figures you've quoted are
correct, as they don't seem to be. [joeu2004's formula (effectively the
same as yours) looks OK.]

Are the values in your series intended to be the value at that date, or are
they perhaps something like a current percentage rate of growth (defined in
some way or other)?

 

[bpkaufman]

Thanks for then response. The data are index values and I assume them
to be correct as they are taken from an application called AREMOS and
the code used to download the series is correct. My purpose is to
transfer these codes to another application which does not have a lag
function so I need to coerce these values in excel.

I don't know what makes you think that the figures you've quoted are
correct, as they don't seem to be. [joeu2004's formula (effectively the
same as yours) looks OK.]

Are the values in your series intended to be the value at that date, or are
they perhaps something like a current percentage rate of growth (defined in
some way or other)?
--
David Biddulph

Thanks for the response, Joe, but I didn't have any luck... here is the
series I am working on.. and below it is the correct figures I should
be getting with a 6 mth lagged cagr..I've tried everything and am
becoming desperate..


7/1/91 12.4545
8/1/91 12.5732
9/1/91 12.6147
10/1/91 12.6572
11/1/91 12.8421
12/1/91 13.0317
1/1/92 13.208
2/1/92 13.3711
3/1/92 13.4381
4/1/92 13.6484
5/1/92 13.8817
6/1/92 14.0936
7/1/92 14.3233
***********************

1/1/92 12.90030006
2/1/92 13.01828899
3/1/92 13.09353062
4/1/92 15.52380093
5/1/92 15.84093727
6/1/92 15.45566734
7/1/92 15.58660109

 

[Fred Smith]

In order to duplicate the results in your table, you will need to define what a
'six month lagged cagr' is. As you can tell, most of us have never heard of the
term.

What you are calculating is the return over a six-month period, then annualizing
it. We would agree with your calculation method and results.

In order to duplicate the answer of 12.9003006, you'll need to go to the source
to find out their calculation method.

--
Regards,
Fred

Thanks for then response. The data are index values and I assume them
to be correct as they are taken from an application called AREMOS and
the code used to download the series is correct. My purpose is to
transfer these codes to another application which does not have a lag
function so I need to coerce these values in excel.

I don't know what makes you think that the figures you've quoted are
correct, as they don't seem to be. [joeu2004's formula (effectively the
same as yours) looks OK.]

Are the values in your series intended to be the value at that date, or are
they perhaps something like a current percentage rate of growth (defined in
some way or other)?
--
David Biddulph

(e-mail address removed) wrote:
(e-mail address removed) wrote:
I know how to calculate cagr though can someone please let me know if
this seems right for cagr with a six month lag?
((January 1, 1992 Value/July 1, 1991)^2)-1)*100
For some reason this has not been working for me...

Using your notation, try:

( ( (July 1 1992 value) / (January 1 1992 value) )^2 - 1 ) * 100

Note that you do not need "*100" if you are writing an Excel formula.
Simply format as Percentage with 2 decimal places, if you want (Format
Cells > Number).

Thanks for the response, Joe, but I didn't have any luck... here is the
series I am working on.. and below it is the correct figures I should
be getting with a 6 mth lagged cagr..I've tried everything and am
becoming desperate..


7/1/91 12.4545
8/1/91 12.5732
9/1/91 12.6147
10/1/91 12.6572
11/1/91 12.8421
12/1/91 13.0317
1/1/92 13.208
2/1/92 13.3711
3/1/92 13.4381
4/1/92 13.6484
5/1/92 13.8817
6/1/92 14.0936
7/1/92 14.3233
***********************

1/1/92 12.90030006
2/1/92 13.01828899
3/1/92 13.09353062
4/1/92 15.52380093
5/1/92 15.84093727
6/1/92 15.45566734
7/1/92 15.58660109

 

[joeu2004]

In order to duplicate the results in your table, you will need to define what a
'six month lagged cagr' is. As you can tell, most of us have never heard of the
term.

I assumed the OP is referring to a 6-month moving average. "Lagging"
means that the average for period N is based on the numbers for periods
1 through N -- i.e. the N-1 previous periods and the N-th period. A
"leading" moving average could be based on the 1st of N periods and the
N-1 following periods. Of course, CAGR is the compound annual growth
rate.

Because of the OP's original formula, I assumed the OP was computing
the CAGR based on the raw data values, not the periodic growth rates of
that data. But as David pointed, clearly that is not the case, given
the data and the expected results provided in the OP's subsequent
example. On the other hand, the data and expected results do not mesh,
even if they are (annualized?) periodic growth rates.

 

[joeu2004]

Clarification....

I wrote

I assumed the OP is referring to a 6-month moving average. "Lagging"
means that the average for period N is based on the numbers for periods
1 through N -- i.e. the N-1 previous periods and the N-th period. A
"leading" moving average could be based on the 1st of N periods and the
N-1 following periods.

I meant to say: a "leading" moving average for period N would be based
on the N-th period and the N-1 following periods.

 

[joeu2004]

Thanks for then response. The data are index values and I assume them
to be correct as they are taken from an application called AREMOS and
the code used to download the series is correct. My purpose is to
transfer these codes to another application which does not have a lag
function so I need to coerce these values in excel.

If you have access to the AREMOS product, not just its output, have you
considered using the "AREMOS XLS" add-in for this purpose? Of course,
that might be like killing an ant with a sledgehammer. I know nothing
more about AREMOS than what I read on their web page.

Thanks for the response, Joe, but I didn't have any luck... here is the
series I am working on.. and below it is the correct figures I should
be getting with a 6 mth lagged cagr..I've tried everything and am
becoming desperate.

My suggestion -- really just a correction of your attempted solution --
is effectively a simple moving average of the time series.
(Technically, it is the antilog of the SMA of the log period changes.)

Since AREMOS is a "business solution for working with time-series data"
that includes analytic tools "using powerful modeling and
regression-testing methods", it is possible that AREMOS uses other
moving average methods such as a weighted or exponential moving
average, possibly incorporating "X11 and X12 seasonal adjustment".

If that is the case, it might hopeless to try to duplicate their
results. There are infinite ways to implement those other moving
averages -- although there are some common methods that you might
experiment with.

Again, if you have access to the AREMOS product, you might look for
user options that control the method of computation. Or perhaps their
documentation explains their methods. Alternatively, you might call
their tech support. Although such methods are usually proprietary, I
know of one similar product (geared to investment management) that
documents their internal formulas in mathematical terms. (Surprise!)

Not the turn-key solution you were probably hoping for. But I hope it
is useful. Good luck!

 

[bpkaufman]

If you have access to the AREMOS product, not just its output, have you
considered using the "AREMOS XLS" add-in for this purpose? Of course,
that might be like killing an ant with a sledgehammer. I know nothing
more about AREMOS than what I read on their web page.



My suggestion -- really just a correction of your attempted solution --
is effectively a simple moving average of the time series.
(Technically, it is the antilog of the SMA of the log period changes.)

Since AREMOS is a "business solution for working with time-series data"
that includes analytic tools "using powerful modeling and
regression-testing methods", it is possible that AREMOS uses other
moving average methods such as a weighted or exponential moving
average, possibly incorporating "X11 and X12 seasonal adjustment".

If that is the case, it might hopeless to try to duplicate their
results. There are infinite ways to implement those other moving
averages -- although there are some common methods that you might
experiment with.

Again, if you have access to the AREMOS product, you might look for
user options that control the method of computation. Or perhaps their
documentation explains their methods. Alternatively, you might call
their tech support. Although such methods are usually proprietary, I
know of one similar product (geared to investment management) that
documents their internal formulas in mathematical terms. (Surprise!)

Not the turn-key solution you were probably hoping for. But I hope it
is useful. Good luck!

Joe,
Thanks very much for the response. The "base" values for the series
pulled from either application is the same...still stuck..