seeking assistance calculating trendline percent

[jonathanwsmith]

Hello - I'm seeking assistance calculating the percent of my exponential trendline. My chart appears ~ Row 259 of the attached worksheet.fredgraph (13).xls My Linest since beginning is .002, and my linest since 1/1985 is .00523. Any help would be appreciated.

Thanks,

Jonathan Smith

[MrShorty]

I find that I must first understand the math before I can begin to program something into Excel. From your description, I am having a difficult timing understanding what you are trying to do. You claim your "linest" function is returning .002 and 0.005, but your file does not include any linest functions. The graph you show is using an exponential trendline. Without an example of the linest function you are using, we cannot know if you are using a straight line (y=mx+b) for your regression function or if you are using y=a*exp(bx) or what equation your regression is expected to use. It is also difficult to know what you mean by "percent of exponential trendline".

I'm sure we can help you, but we need you to better explain what your problem is.

[jonathanwsmith]

I find that I must first understand the math before I can begin to program something into Excel. From your description, I am having a difficult timing understanding what you are trying to do. You claim your "linest" function is returning .002 and 0.005, but your file does not include any linest functions. The graph you show is using an exponential trendline. Without an example of the linest function you are using, we cannot know if you are using a straight line (y=mx+b) for your regression function or if you are using y=a*exp(bx) or what equation your regression is expected to use. It is also difficult to know what you mean by "percent of exponential trendline".

I'm sure we can help you, but we need you to better explain what your problem is.

Thank you urging me to clarity. My revised worksheet includes a result of using LINEST function beginning at Row 31 =LINEST(B31:B280) and beginning at line 164 =LINEST(B164:B280). I believe I am using using y=a*exp(bx) for my regression function. Instead of writing "I'm seeking assistance calculating the percent of my exponential trendline," I should have said I am seeking to know the annual percentage rate of change of my trendline. I hope I have uploaded the revised worksheet correctly.

Thanks,

Jonathan

[shg]

=LOGEST(B31:B280) returns 1.002611, which means that the average growth rate per quarter (the period of the data) is 0.2611%.

[MrShorty]

I am not a financials guy -- so I sometimes get lost in financial jargon. One of the first keys to getting help for a financial problem from a non-financial guy is to define terms and functions. For example, I cannot be certain what you mean by "annual growth rate" unless you describe it in some detail. I can assume an expression for growth rate, but I cannot be certain that my "intuitive" definition of growth rate is the same as a financial analysts definition.

My revised worksheet includes a result of using LINEST function beginning at Row 31 =LINEST(B31:B280) and beginning at line 164 =LINEST(B164:B280). I believe I am using using y=a*exp(bx) for my regression function.

I still see no use of the LINEST() function in the revised spreadsheet.

First observation -- this is incorrect. The LINEST() function regresses a linear y=ax+b type function (http://office.microsoft.com/en-us/ex...in=HA010277524). My first suggestion would be to look at both the LINEST() and the LOGEST() functions (http://office.microsoft.com/en-us/ex...in=HA010277524) and decide which function you want to use. They both use the same underlying regression algorithm -- it is just about understanding the transformation from y=Aexp(Bx) to ln(y)=ln(A) + Bx. If you can see that exp(B) in your expression is the same as m in LOGEST()'s regression, then LOGEST() might be the easiest function to use here.

2nd observation -- You are not supplying a "known_x" argument. This means Excel will use known_x={1,2,3,...}. An important part of interpreting regression results is understanding what exactly is meant by "known_x". In this case, since it appears that the data are "quarterly" data, x would mean "time in quarters" since the initial date. It is not necessarily a big deal, other than you the user need to recognize this so you can properly convert the regression results from x in quarters to an annual (over four quarters) growth rate.

I should have said I am seeking to know the annual percentage rate of change of my trendline.

In many ways, this seems more like a math question than an Excel question. I might first start with a little algebra, using your response to the "what does annual growth mean" and "what does x mean" questions, and get an algebraic expression in terms of a and b for annual growth rate. If I assume growth rate is a simple ratio y1/y0=Aexp(B*4)/Aexp(B*0) {A's cancel, exp(0)=1, etc}, I calculate y1/y0=exp(4B). Then I can use B from the regression equation to calculate this variation on the annual growth rate.

[jonathanwsmith]

Thanks SHG and MrShorty for your patience and clear explanations. When I calculate =LOGEST(B164:B280) (1985 till now) I get .7289, which when crudely annualized by multiplying by 4, yields 2.916%, roughly the rate of inflation during this term. As the time series in column B is ((Market Value of All Equities) / GDP), said trendline should roughly equal inflation. You've helped me greatly.

I still don't understand how I failed to upload my revised version of the Excel file showing =LINEST and =LOGEST calculations.

Kind regards,

Jonathan Smith

[shg]

You're welcome.