What is the math used in Excel's GROWTH function?

Question

I am trying to implement Microsoft Excel's GROWTH function in JavaScript. This function calculates predicted exponential growth by using existing data. What makes it tricky is that it must work with multiple sets of known_x's values. I could not find any reference equation. Any suggestions? The part that might be relevant is the reference equation. I cross-posted the question here because someone suggested it in response to the same question originally posted on stackoverflow.

Thanks in advance for your help.

Answer 1

Looks like the Excel GROWTH function is doing two things:

1. fitting a function of the form $y(x) = b \cdot m^x$, where $b$ and $m$ are unknown constants, to a set of $(x_i,\,y_i)$ data points, and
2. evaluating the resulting function at a given set of points.

Step 2 is trivial once you've found $b$ and $m$, so I won't go into that. The documentation for GROWTH doesn't specify how the data fitting is done, but a simple and obvious method would be to reduce the exponential fit to a linear one by taking the logarithm of the $y$ values, and then using simple linear regression to fit the function $\log y(x) = \alpha + \beta \cdot x$, where $\alpha = \log b$ and $\beta = \log m$, to the data points $(x_i,\, \log y_i)$.

(The values of $b$ and $m$ obtained in this way don't generally minimize the sum of squared errors $\sum_i (y(x_i) - y_i)^2$, but they do minimize the sum of squared logarithmic errors $$\sum_i (\log y(x_i) - \log y_i)^2 = \sum_i \left(\log \frac{y(x_i)}{y_i}\right)^2,$$ which one might argue to be a more natural measure for fitting an exponential growth function to data, anyway.)

Ps. I've posted an actual JavaScript implementation of this method on Stack Overflow. The results of the function match the example worksheet on the GROWTH documentation to within the numerical accuracy shown (which, admittedly, isn't much), suggesting that this may indeed be the method used by Excel.

(In fact, while I don't have Excel to test it with, downloading the worksheet and testing it in LibreOffice Calc gives results that match the JS code to about 12 significant digits. The remaining discrepancy is likely due to numerical inaccuracies in one or both implementations.)