Best Fit to Stock Prices     an appendix to Best Straight Line Fit

As before, we assume that our stock grows according to:

P(n) = C (1 + R)n

where we start with a price $C and, after n months (days? years?), the price is $P(n), where R is some "Best Fit" monthly return.
In order to find the "Best" values for C and R, we set k = 1+R and consider the sum of the squares of the deviations of the actual prices, yn, from our "Best Fit", and take this as a measure of our "Error", which we'll call E(C,k):

    E(C,k) = Σ{ yn - C kn }2

As before, we set the two derivatives to zero, like so:

(1)     d/dC E(C,k) = 2Σ{ yn - C kn }kn = 0
and
(2)     d/dk E(C,k) = 2Σ{ yn - C kn }C n kn-1 = 0

These may be rewritten, like so:

(3)     Σ yn kn - C Σk2n = 0
and
(4)     Σ n yn kn-1 - C Σn k2n-1 = 0

Hence we can solve for C, like so:

(5)     C = Σ yn kn / Σk2n = Σ n yn kn-1 / Σn k2n-1

from which we get an equation to solve for k = 1 + R

If we define:
f(k) = Σ yn kn   and   g(k) = Σk2n   then we can then rewrite equation (5) like so:

(6)     C = f(k) / g(k) = f '(k) / [(1/2) g '(k)]

Now we cross-multiply, in equation (6), and define:

F(k) = f(k) g '(k) - 2 f '(k) g(k)     and we wish to solve   F(k) = 0   for k.

We can solve this using Newton's Method, which requires iterating: k - F(k) / F '(k).

Note that:

F '(k) = f(k) g ''(k) - f '(k) g(k) - 2 f ''(k) g(k)

Let's recall our definitions:

  1. f(k) = Σ yn kn
  2. g(k) = Σk2n     ignoring the fact that there's a formula for the sum of this geometric series
  3. f '(k) = Σ n yn kn-1 = (1/k) Σ n yn kn
  4. g '(k) = Σ2n k2n-1 = (2/k) Σn k2n
  5. f ''(k) = Σ n (n-1) yn kn-2 = (1/k2) Σ n2 yn kn - (1/k2) Σ n yn kn = (1/k2) Σ n2 yn kn - (1/k) f '(k)
  6. g ''(k) = Σ2n (2n-1) k2n-2 = (4/k2) Σn2 k2n - (2/k2) Σn k2n = (4/k2) Σn2 k2n - (1/k) g '(k)

Now, using Newton's Method, starting with an initial guess given by the Excel functions:

k = INDEX(LOGEST(yn, n), 1)     C = INDEX(LOGEST(yn, n), 2)     we get both "Best Fit" charts  

Note: The Excel function LOGEST(y:array, x:array) gives the parameters for the "Best Fit to the logarithm".