Logarithmic Linear Least Squares growth rates for dividends etc

[MDW1954]

Although programming languages can extend what is possible using spreadsheets, my own preference is to use programming only when necessary. YMMV, but the above provides a fairly simple implementation without any need for programming. Further, this implementation is also compatible with Libre Office and Google Sheets.

modellingman

Cheers, old friend.

Modellingman and I have known each other in real life since the late 1970s, and he shared the bones of the above post with me privately, over the weekend.

Back when I created those user-defined functions a few years back, I did so only because the BASIC code already existed, and porting it to Excel's VBA would be a fairly straightforward job. Which, with IAAG's help, it was. At the time, I wasn't aware of LINEST() or LOGEST() -- and indeed, I wasn't aware of LOGEST() at all, until Modellingman mentioned it.

To stress, the code exists solely because back in 1981, when I was doing my PhD, I needed a ready resource for performing LLLS calculations -- and while Microsoft Excel (or LibreOffice, or Google Sheets) would be a better choice than code today, Microsoft Excel at that point hadn't been invented!

To anyone looking at performing LLLS calculations themselves, I'd say that Modellingman's most recent Excel worksheet offers a superb platform for doing so, and renders redundant the need for a set of LibreOffice user-defined functions. If you want the charts, then there's the R/ggplot2 option, but only if you want the charts.

MDW1954

[1nvest]

I tend to reference long term data, log scaled.

For a quickie indicator I plot the data, log scale the Y axis, and add a exponential trend line and tick the show R-squared option.

Bit of quote added by modellingman
linest(ln( .... range of values) provides a indicator of the trend line slope (more meaningful indicator of the CAGR). The R-squared value provides a indicator of the variability around the trend line, closer to 1.0 the closer individual data points aligned to the trend line.

Then I think, from what you've said, that you're performing logarithmic linear least squares.

MDW1954

@1nvest is indeed using a standard log linear regression model via use of the LINEST() function on the log transformed data series. A slightly more direct approach is to use the LOGEST() function(*). This provides the following (amongst other things)

• the least squares estimate of the growth rate
• the r-squared value
• the F-value for hypothesis testing of the growth rate
• the degrees of freedom

The square root of the F-value is the t-value and the square root of the latter is the correlation co-efficient. The p-value can be obtained by applying the T.DIST.2T() function to the t-value. It is the same value as that obtained for the F-value by applying the F.DIST.RT() function - ie both the t-value and F-value are related statistics for the hypothesis test of significance of the growth rate.

Apart from the graphs, the only real bits in the R/ggplot2 output not available through LOGEST() are the parameters of the quadratic equations which provide the confidence intervals for the expected and/or actual values of the dependent variable as a function of time. I'm reasonably sure these are not too difficult to find and calculate but I would have to do bit more dredging to get to that point.

The next stage in the project is to do a LibreOffice version, which will require turning Excel's VBA into LibreOffice BASIC. If anyone knows of any handy LibreOffice BASIC training/ user resources, now is the time to holler.

Not quite the holler you have asked for but...

With not much more than the LOGEST() function, it is possible to calculate the results. Following the model provided by the worksheet that you posted here, formulae that can be used for the results cells are

Growth rate   =INDEX(LOGEST(data,,TRUE,TRUE),1,1)-1
R_Squared     =INDEX(LOGEST(data,,TRUE,TRUE),3,1)
Correlation   =SQRT(R_Squared)
t_Value       =SQRT(INDEX(LOGEST(data,,TRUE,TRUE),4,1))
Deg_Freedom   =INDEX(LOGEST(data,,TRUE,TRUE),4,2)
p_Value       =T.DIST.2T(t_Value,Deg_Freedom)

where

data

is the worksheet range containing the data values, for example B1:B17.

Although programming languages can extend what is possible using spreadsheets, my own preference is to use programming only when necessary. YMMV, but the above provides a fairly simple implementation without any need for programming. Further, this implementation is also compatible with Libre Office and Google Sheets.

I would agree that these formulae are not quite as straightforward or simple to apply in a workbook as what I'm sure are the UDF's that you created with IAAG's input. However, the Microsoft 365 version of Excel even allows this difficulty to be overcome without programming via use of the Name Manager and the LAMBDA() function.

A workbook containing the formula above and also using friendly function names created via the LAMBDA function is available here. To whet your appetite, as well as using the formula above, the Growth rate is also calculated using the formula

=LLLS.GrowthRate(B1:B17)

Similar LLLS functions are defined for the other results cells. The workbook will work on the web version of Excel (and will probably open automatically in it). Copies can also be downloaded for use with desktop products but the formulae labelled "Formula v1" will only work with the Microsoft 365 version of Excel on the desktop.

(*)Documentation of the LOGEST() function, both from Microsoft and other authors (eg this and this), is generally pretty weak on precisely how LOGEST() derives its estimates and completely fails to mention the relationship between LINEST() and LOGEST(). Microsoft does provide some insights in this article, though the article is largely about correction of the algorithm for LINEST() which, in certain circumstances, was incorrect in versions of Excel prior to Excel 2003. Both the Microsoft article and my own experimentation confirm that LOGEST() effectively produces the same results as LINEST() after transforming the dependent variable (known y's in Microsoft terminology) using natural logarithms and then fitting a standard linear regression model using the transformed dependent variable and the untransformed (ie linear) independent variable (time or known x's in MS-speak). The co-efficients of this model are estimated using a least squares criterion and together these explain why the the term "logarithmic linear least squares" is an appropriate one.

modellingman

Thanks modellingman. Nice to know (have a name for) what I do. My
maths comprehension is nowhere near up to your level such that I didn't even know what I do was called.