Logarithmic Linear Least Squares growth rates for dividends etc

[MDW1954]

Most readers of this board will be familiar with the shortcomings of compound annual growth rates (CAGR), which rely on only two data points from a time series -- the first, and the last. Most of the data is thrown away, so the choice of "first" and "last" is crucial.

It might be imagined that geometric mean calculations would be superior, but alas, no. Because with a little thought we see that the geometric mean is simply the average growth rate that transforms the first data point into the last! So it's the same result as CAGR, but arrived at by different means.

For my own purposes, I've generally used a method known as logarithmic linear least squares (LLLS), which uses all the data points, and calculates a linear regression growth coefficient. I first used this publicly back in the TMF days, which resulted in Gengulphus getting lots of PMs to pronounce on it. I passed the test, fortunately! But I was reluctant to repeat the exercise.

Recently, I've begun sharing LLLS growth rates occasionally on the HYP board. An example is here:





The reaction has been mostly favourable. The chart makes it more intuitively obvious. Several people have remarked that it's something that they learned at university, but haven't pursued (or had the tools to do so) since.

But I have several tools that I use for producing LLLS calculations, and I'm getting quite close to sharing them with interested Lemons on this board (where I hope they might fall on fertile soil).

For reasons of my own I'm leaning at the moment towards doing this via Github, rather than Kiloran's and IAAG's software repository. Nothing sinister, it just means that on the odd occassion when I change anything, there's only one piece of source code to change.

Basically, I have:

1) Code in BASIC. I developed this to expedite LLLS calculations in back 1981, when I was doing my PhD. It runs under Microsoft QuickBASIC 4.5, QBasic, and Microsoft BASIC PDS 7.1. As a result, it also runs under the modern, well-maintained, free, open-source, 64-bit Windows version of BASIC, QB64. Two versions are available: a version which accepts data from the keyboard, and a version which pulls it in from a file. Porting to Python would be relatively straightforward, and I might do it one day.

2) An "empty" spreadsheet with LLLS capabilities coded as functions using Excel's VBA. (It's the same code, basically.) You get the growth rate, R-squared, correlation coefficient, and t-statistic -- but no charts. (I did it like this because despite the admirable IAAG's help, I couldn't build a transferable Excel macro library. YMMV.)

3) The ability to produce charts like the one you see above, which comes from code written for R and ggplot2, both of which are free and open source. You'll want R Studio, as well, as an easy-to-use IDE. Again, free and open source. Basically, you copy from a spreadsheet, and the code automatically picks up the data from the clipboard.

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.

Watch this space. Obviously, if anyone is desperate to try it out now, just ask. Everything referred to in (1) to (3) above is stable and has been for some years -- decades in some cases!

Thanks again to IAAG, and also TJH, for sharing some real-world dividend data.

MDW1954

[kiloran]

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.
MDW1954

The standard guide to OpenOffice/LibreOffice Basic is https://www.openoffice.org/documentatio ... tro_v1.pdf
There's also https://wiki.openoffice.org/wiki/Docume ... ASIC_Guide among others.
Oh, and this: https://openoffice3.web.fc2.com/index.html

I started out using Basic with LibreOffice but found various issues, especially with web-scraping, so I always use Python these days. There's no Python IDE included with LibreOffice. It can be interfaced to an IDE such as Eclipse, but I'm happy to just use a standard text editor and dump debug data to a file or a message box.

Have fun! Shout if you need any help or advice.

--kiloran

Edit.... if you get it set up on Github, I'm more than willing to add a link to it from the Weebly software repository

[moorfield]

The reaction has been mostly favourable. The chart makes it more intuitively obvious. Several people have remarked that it's something that they learned at university, but haven't pursued (or had the tools to do so) since.

But I have several tools that I use for producing LLLS calculations, and I'm getting quite close to sharing them with interested Lemons on this board (where I hope they might fall on fertile soil).

For reasons of my own I'm leaning at the moment towards doing this via Github, rather than Kiloran's and IAAG's software repository. Nothing sinister, it just means that on the odd occassion when I change anything, there's only one piece of source code to change.

Yes we've discussed these before and these plots were supremely useful on the HYP1 "Acculumation" thread that Gengulphus (RIP) started.

Another vote for Github here. I use that on nearly a daily basis and more than happy to assist with any admin / contribution if needed in future, just shout.

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.

Watch this space. Obviously, if anyone is desperate to try it out now, just ask. Everything referred to in (1) to (3) above is stable and has been for some years -- decades in some cases!

Apropos of nothing, I've been doing a lot of work recently building Excel "Add-ins". These are rather bleeding edge stuff at the moment which work on the newer versions of Excel, generally built with JavaScript rather than the old VBA - but good fun. Again, happy to share ideas.

Apropos of nothing (2), I've also mentioned here I use Google Sheets for all my own record keeping. I wonder if there is any appetite here start using in future to make various spreadsheets easily available online/oncloud.

[MDW1954]

Yes we've discussed these before and these plots were supremely useful on the HYP1 "Acculumation" thread that Gengulphus (RIP) started.

In an ideal world I'd like one or two IT-savvy HYPers to use the R/ggplot2 capability, as that produces those handy charts.

Download R, R Studio, and a couple of R libraries, and you're good to go. Copy the relevant data from your spreadsheet (or wherever) and the program does the rest.

The spreadsheet function is straightforward, but it just produces the descriptive statistics quoted in the chart, not the chart itself.

MDW1954

[MDW1954]

Apropos of nothing (2), I've also mentioned here I use Google Sheets for all my own record keeping. I wonder if there is any appetite here start using in future to make various spreadsheets easily available online/oncloud.

I've wondered the same thing with the web-scraping REIT spreadsheets that I post occasionally.

MDW1954

[Newroad]

Hi Malcolm.

I am not a statistician or mathematician - 2nd year university level maths was my highest academic achievement. Also, I was much better at solutions than proofs (which made Discrete Structures 204 an interesting subject for me, where I got near 100% on the Finite State Automaton, Turing Machine and similar stuff and much closer to 0% on the Pumping Lemma and similar stuff, but I digress ...).

Though the intended use is broadly the same, it seems to me Geometric Mean (GM) and Logarithmic Linear Least Squares (LLLS) are doing subtlety different things: GM is trying be specifically right (but will likely be specifically wrong as a predictor - though I don't mean this critically - I use it more as a measuring stick, rather than as a predictor) whereas LLLS is trying to be approximately right as a predictor and is likely to succeed.

I wonder if using LLLS it would be possible (if you don't do it already) to more heavily weight the recent figures, a bit like game and sport ranking systems do? If so, I suspect it would be an even more reliable indicator of likely future trend.

Regards, Newroad

[Hallucigenia]

Porting to Python would be relatively straightforward, and I might do it one day.

I couldn't build a transferable Excel macro library. YMMV.

3) The ability to produce charts like the one you see above, which comes from code written for R and ggplot2, both of which are free and open source. You'll want R Studio, as well, as an easy-to-use IDE. Again, free and open source. Basically, you copy from a spreadsheet, and the code automatically picks up the data from the clipboard.

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.

A casual observer with no experience of R but who has used the others, writes :

Is learning LibreOffice BASIC the optimal way to go? Wouldn't it make more sense to use another language that's not tied to one spreadsheet? AIUI LibreOffice can also use Python, Java and JavaScript directly.

And of course Google Sheets uses Javascript, and although I've never used it, I imagine that converting from VBA is so important for them that their converter should work pretty well? So going via Google's VBA conversion might be an easy way to generate a Javascript version that would also work with LO? Or if you say that a Python version would be straightforward, that again would be a flexible way to cater for non-Excel people, and would allow you to carry on using ggplot2 etc.

And there are ways and means to get python and R directly into Google Sheets...

[MDW1954]

Hi Malcolm.

I am not a statistician or mathematician - 2nd year university level maths was my highest academic achievement. Also, I was much better at solutions than proofs (which made Discrete Structures 204 an interesting subject for me, where I got near 100% on the Finite State Automaton, Turing Machine and similar stuff and much closer to 0% on the Pumping Lemma and similar stuff, but I digress ...).

Though the intended use is broadly the same, it seems to me Geometric Mean (GM) and Logarithmic Linear Least Squares (LLLS) are doing subtlety different things: GM is trying be specifically right (but will likely be specifically wrong as a predictor - though I don't mean this critically - I use it more as a measuring stick, rather than as a predictor) whereas LLLS is trying to be approximately right as a predictor and is likely to succeed.

I wonder if using LLLS it would be possible (if you don't do it already) to more heavily weight the recent figures, a bit like game and sport ranking systems do? If so, I suspect it would be an even more reliable indicator of likely future trend.

Regards, Newroad

I think the biggest problem with the geometric mean is that despite using all the data, the eventual outcome is still dependent on the choice of first and last data points. Being pragmatic, one might as well cut to the chase and use CAGR. There may be circumstances in which the geometric mean is useful, but it will be 40+ years since I last deliberated them! At the time, I used LLLS because that was deemed to be the theoretically 'best' approach to the specific callenges of calculating productivity growth rates in economic models used by America's Bureau of Labour Statistics.

Re: weighting the more recent observations, I don't think that LLLS lends itself to this, because it is a regression technique that determines a least-squares fit between data points in a time series. Oddly enough, though, back in 2006 or so I did look at something similar to what you're suggesting. There's a type of econometric model that is essentially a weighted moving average combined with logarithms to make forecasts based on a time series -- autoregressive moving average, or ARMA.

Sledgehammers and nuts come to mind, though. And what you get is more of a forecast than a long-term trend. So I didn't pursue it, in the end. Using something like R, though, it would be child's play.

MDW1954

[MDW1954]

Porting to Python would be relatively straightforward, and I might do it one day.

I couldn't build a transferable Excel macro library. YMMV.

3) The ability to produce charts like the one you see above, which comes from code written for R and ggplot2, both of which are free and open source. You'll want R Studio, as well, as an easy-to-use IDE. Again, free and open source. Basically, you copy from a spreadsheet, and the code automatically picks up the data from the clipboard.

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.

A casual observer with no experience of R but who has used the others, writes :

Is learning LibreOffice BASIC the optimal way to go? Wouldn't it make more sense to use another language that's not tied to one spreadsheet? AIUI LibreOffice can also use Python, Java and JavaScript directly.

And of course Google Sheets uses Javascript, and although I've never used it, I imagine that converting from VBA is so important for them that their converter should work pretty well? So going via Google's VBA conversion might be an easy way to generate a Javascript version that would also work with LO? Or if you say that a Python version would be straightforward, that again would be a flexible way to cater for non-Excel people, and would allow you to carry on using ggplot2 etc.

And there are ways and means to get python and R directly into Google Sheets...

I am also a fan of the Towards Data Science site, H, and get a Medium feed every morning in my email. But I've obviously missed the capability referred to in your final para. Food for thought.

I mentioned LibreOffice for two reasons: one, people here seem to use it for spreadsheets; and two, VBA code already exists. There's even a "VBA Support" command that one can specify in an "Options" line. So it ought (in theory) to be fairly straightforward, although I'm currently wrestling with LibreOffice's equivalent of VBA's "DIM variable AS RANGE" command, which crashes.

I'm totally open to suggestions. I shall carry on using what I currently use (a mixture of all three, depending on circumstances), but I'm aware that R and BASIC place me in a minority. I have some Python, but it's fairly rudimentary. On the other hand, so is the code.

MDW1954

[Hallucigenia]

I mentioned LibreOffice for two reasons: one, people here seem to use it for spreadsheets; and two, VBA code already exists. There's even a "VBA Support" command that one can specify in an "Options" line. So it ought (in theory) to be fairly straightforward, although I'm currently wrestling with LibreOffice's equivalent of VBA's "DIM variable AS RANGE" command, which crashes.

I'm totally open to suggestions. I shall carry on using what I currently use (a mixture of all three, depending on circumstances), but I'm aware that R and BASIC place me in a minority. I have some Python, but it's fairly rudimentary. On the other hand, so is the code.

Oh I get why you wanted to have a solution for LO users, I was just wondering whether LO Basic was the right way to do it. If the LO converter is causing problems then maybe it's worth seeing how the Google Sheets converter turns it into Javascript, and then if that works in LO?

Python is a bit frustrating to get to grips with - it really misses having the equivalent of the PHP manual where not only does it do a reasonable job of explaining things in newbie language, but they allow comments below the line which cover most of the gotchas. I'm now at the stage with it where I can do useful stuff with StackOverflow holding my hand, and sometimes borrow SWMBO's copy of the O'Reilly Python for Finance book which is quite helpful.

On the flip side, it seems to be what everyone's using these days, so there's libraries for anything you're ever likely to want to do.

There's a type of econometric model that is essentially a weighted moving average combined with logarithms to make forecasts based on a time series -- autoregressive moving average, or ARMA.

Hah, that takes me back to a few weeks when SWMBO was doing a course where our pillowtalk consisted of how to do exercises using ARIMA, GARCH and their friends. Who doesn't want a bit of autoregressive conditional heteroskedasticity before bedtime? Fortunately she's now moved on to quantum computing, which is rather easier to get your head round...

[kiloran]

I mentioned LibreOffice for two reasons: one, people here seem to use it for spreadsheets; and two, VBA code already exists. There's even a "VBA Support" command that one can specify in an "Options" line. So it ought (in theory) to be fairly straightforward, although I'm currently wrestling with LibreOffice's equivalent of VBA's "DIM variable AS RANGE" command, which crashes.

I'm totally open to suggestions. I shall carry on using what I currently use (a mixture of all three, depending on circumstances), but I'm aware that R and BASIC place me in a minority. I have some Python, but it's fairly rudimentary. On the other hand, so is the code.

Oh I get why you wanted to have a solution for LO users, I was just wondering whether LO Basic was the right way to do it. If the LO converter is causing problems then maybe it's worth seeing how the Google Sheets converter turns it into Javascript, and then if that works in LO?

Python is a bit frustrating to get to grips with - it really misses having the equivalent of the PHP manual where not only does it do a reasonable job of explaining things in newbie language, but they allow comments below the line which cover most of the gotchas. I'm now at the stage with it where I can do useful stuff with StackOverflow holding my hand, and sometimes borrow SWMBO's copy of the O'Reilly Python for Finance book which is quite helpful.

On the flip side, it seems to be what everyone's using these days, so there's libraries for anything you're ever likely to want to do.

There's a type of econometric model that is essentially a weighted moving average combined with logarithms to make forecasts based on a time series -- autoregressive moving average, or ARMA.

Hah, that takes me back to a few weeks when SWMBO was doing a course where our pillowtalk consisted of how to do exercises using ARIMA, GARCH and their friends. Who doesn't want a bit of autoregressive conditional heteroskedasticity before bedtime? Fortunately she's now moved on to quantum computing, which is rather easier to get your head round...

Although Python has loads of libraries available, I don't think you can use them if you are embedding a Python script in a LibreOffice document for distribution to other users, you are restricted to plain vanilla Python 3. I'd like to think I am wrong
It may be possible to add a library to your own LibreOffice installation, though I've never tried.

--kiloran

[MDW1954]

I'm aware that I've set a few hares running, and that the discussion is splintering in multiple directions.

So it's probably helpful to sketch out a bit more what we're talking about. The Excel LLLS capability that we're talking about porting to LibreOffice looks like this:





As you can see, there are four custom LLLS functions. If I could get a fix for the "AS RANGE" problem, I think there's a reasonable chance that the rest of the code would be fairly straightforward. It's pretty simple stuff, and the level of inbuilt function usage is minimal. Remember, this code started life in the 1970s! If one doesn't need charts, then it's a case of job done. No need for Python or libraries.

Re: Python "as Python", and not embedded in LibreOffice, then replicating that same functionality shouldn't be difficult, either. Plus, I have two skilled Python resources available for question-answering. With "pure" Python, though, there's the opportunity to use libraries, and matplotlib could replicate the charting. That would take me some time; I'm rusty. And I'd only do it if there was demand.

MDW1954

[Hallucigenia]

Although Python has loads of libraries available, I don't think you can use them if you are embedding a Python script in a LibreOffice document for distribution to other users, you are restricted to plain vanilla Python 3. I'd like to think I am wrong

Just for clarity I wasn't saying to rely on libraries from within LibreOffice, I was just looking at it from the POV of "if @MDW1954 needs to learn new stuff, which technology would be best able to serve his different audiences?"

If vanilla Python can do all the "simple" stuff for scripting in LibreOffice that you would otherwise do in Libre BASIC, but it can also plug in to libraries like ggplot2 to do fancier stuff outside LibreOffice, then that would seem to be a win, both in terms of learning curve and simplifying the codebase rather than maintaining a version in LibreBASIC and another version in something fancier.

[not that I particularly have a love of Python, but if you can use it directly in LibreOffice then that's got to be an attractive option for non-legacy work if LibreOffice users are an important "market"]

[Dod101]

I must say that I have no idea what practical benefit LLLSGR gives but I have managed without it for the last 30 years. Maybe it would have given me an investing edge but too bad. I have lost out.

Dod

[Newroad]

Hi Hallucigenia et al.

Personally, I quite like Python (though haven't had as much cause as I would like to use it in anger) and if I were starting out programming now, I suspect it would be my tool of choice. I say this is a guy firmly from a C & SQL background.

On the use of LLLS and trying to simply weight it somehow, I was musing on this last night. I was thinking that you could do rows in an Excel spreadsheet, which might look like

1
2 2
3 3 3
4 4 4 4
etc

where you insert a new row for each new measurement, then do an LLLS square for each column, sum the outputs (at least in terms of growth rates) then divide by the number of columns. In the example above, that would give full weight to row/measurement 4 then 3/4 for row three etc.

What you could then do is get even more nuanced in terms of those weights, e.g. make the rows weight as, say, 1,2,4,8 etc respectively, then divide the total by 15 (or any other set of weighting proportions you might choose).

Once set up, it would be fairly easy to propagate each month or whatever.

My apology if I have explained this poorly, but I hope you get the drift.

Regards, Newroad

[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. That trend line relative to the start and end date points provides a indicator of whether the CAGR is relative to a low start date, high end date measure (over-measured), or high start date/low end date measure (under-measured).

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.

As a risk/reward measure linest(ln .. value divided by r-squared is somewhat a Sharpe'ish relative comparison indicator.

Don't know Python, my coding days were many years ago. And my need for precision is low such that the above suffices.

[modellingman]

I'm aware that I've set a few hares running, and that the discussion is splintering in multiple directions.

So it's probably helpful to sketch out a bit more what we're talking about. The Excel LLLS capability that we're talking about porting to LibreOffice looks like this:





As you can see, there are four custom LLLS functions. If I could get a fix for the "AS RANGE" problem, I think there's a reasonable chance that the rest of the code would be fairly straightforward. It's pretty simple stuff, and the level of inbuilt function usage is minimal. Remember, this code started life in the 1970s! If one doesn't need charts, then it's a case of job done. No need for Python or libraries.

Re: Python "as Python", and not embedded in LibreOffice, then replicating that same functionality shouldn't be difficult, either. Plus, I have two skilled Python resources available for question-answering. With "pure" Python, though, there's the opportunity to use libraries, and matplotlib could replicate the charting. That would take me some time; I'm rusty. And I'd only do it if there was demand.

MDW1954

Hello, old friend

Providing LibreOffice has similar statistical worksheet functions to Excel this does not seem too difficult and I am pretty sure that it does not need any programming requirement for implementation.

I assume that the underlying model that you are using is x(t) = P×(1+R)^t where x(t) is the value at time t, R is the interest rate per unit time and P is the starting value at time 0. Taking natural logs of both sides yields

ln(x(t)) = ln(P) + (ln(1+R))×t

which is a linear relationship between ln(x(t)) and t and so is amenable to modelling as a simple linear regression model using ordinary least squares to estimate the co-efficients α and β. Estimates of P and R are then simply:

P = exp(α) and R = exp(β) - 1

I plugged your 17 data points into an Excel worksheet and courtesy of the SLOPE(), INTERCEPT(), RSQ() functions (and some dredging of my knowledge of mathematical statistics from around half a century ago) I was able to reproduce your results without too much difficulty...



modellingman

[MDW1954]

Hello, old friend

Providing LibreOffice has similar statistical worksheet functions to Excel this does not seem too difficult and I am pretty sure that it does not need any programming requirement for implementation.

I assume that the underlying model that you are using is x(t) = P×(1+R)^t where x(t) is the value at time t, R is the interest rate per unit time and P is the starting value at time 0. Taking natural logs of both sides yields

ln(x(t)) = ln(P) + (ln(1+R))×t

which is a linear relationship between ln(x(t)) and t and so is amenable to modelling as a simple linear regression model using ordinary least squares to estimate the co-efficients α and β. Estimates of P and R are then simply:

P = exp(α) and R = exp(β) - 1

I plugged your 17 data points into an Excel worksheet and courtesy of the SLOPE(), INTERCEPT(), RSQ() functions (and some dredging of my knowledge of mathematical statistics from around half a century ago) I was able to reproduce your results without too much difficulty...



modellingman

Hello to you, too, old friend!

Exactly so. The functions simply minimise the chance of user error through model mis-specification. Plus, I already had the code, and thought: why not?

I'd done a VBA course online, and IAAG helped by carving the code up into calculation-specific functions.

But yes, two ways of achieving the same thing.

Left to my own devices, I tend to lean towards the R/ggplot2 version -- copy the data straight out of the spreadsheet, and run R. Job done, and handy chart to boot. But, as I say, the code exists and it seems rude not to share it. Other people might benefit.

Our daughter is heading out your way on Saturday, oddly enough.

Cheers,

M

[MDW1954]

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.

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

MDW1954

[modellingman]

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