Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
CAGR with six month lag
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
CAGR with six month lag
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). |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
CAGR with six month lag
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
CAGR with six month lag
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. David Biddulph wrote: 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 wrote in message ups.com... wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
CAGR with six month lag
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 wrote in message ups.com... 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. David Biddulph wrote: 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 wrote in message ups.com... wrote: 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
CAGR with six month lag
Fred Smith wrote:
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
CAGR with six month lag
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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
CAGR with six month lag
wrote:
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. wrote earlier: 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! |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
CAGR with six month lag
wrote: wrote: 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. wrote earlier: 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! Joe, Thanks very much for the response. The "base" values for the series pulled from either application is the same...still stuck.. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match Last day of this month to last day of last month | Excel Discussion (Misc queries) | |||
Finding values based on current month | Excel Worksheet Functions | |||
How to create a rolling 6 month average? | Excel Discussion (Misc queries) | |||
Working days left in the month compared to previous months | Excel Worksheet Functions | |||
Month Year Date Format | Excel Worksheet Functions |