The following is the simplified version of an actual model. It is only meant to exemplify the issue I am having.
I have a large MS-Excel sheet of 29953 rows containing the daily closing values of the Dow Jones (DJIA) since 1900/01/03. Row 1 contains the Column headers. Column A contains the Date, column B the DJIA, Column C the daily change percentages. The entire C column contains the daily closing percentage gain/loss using the following formula starting at C3 and copied all the way down: =(B3-B2)/B2.
Calculation_A. If I wish, for example, to compute the percentage gain between 1900/01/03 and now (2016-11-29), I use the following formula: =(B29953-B2)/B2 which using the actual values translates to (19121.6 - 66.6) / 66.6 = 286.068. This is, I think, the correct result.
Calculation_B. Calculation_B is the sum of the daily percentage changes (calculated in column C) corresponding, in Excel, to the formula =SUM(C2:C29953) resulting to 767.812.
Intuitively, I would have assumed that Calculation_A = Calculation_B.
My question are as follows, why (mathematically) are Calculation_A and Calculation_B yielding different results? Is there a way to make calculation_B consistent with Calculation_A?
I don't consider myself particularly optuse, but I am having a hard time solving this seemingly trivial problem. Are integrals the answer? Thanks in advance!
Update#1. Following the very good comments and answers from Brian and DougM, thank you, I was able to reconcile the two calculations on my initial PL1DAY column.
I now have another issue along the same line: I have a Excel column named PL20DAYS which calculates, for each trading day, the percentage gain/loss 20 days forward: i.e. (DAYn+20 - DAYn)/DAYn. Why would the compounding of the PL20DAYS column not equate the compounding of the PL1DAY?
Look at a very simple example: suppose that some quantity increases by $100\%$ and then again by $100\%$ . A $100\%$ increase means that the quantity doubles. Here it doubles and then doubles again. Thus, if its initial value is $x$, it first doubles to $2x$ and then doubles again to $2\cdot 2x=4x$. It is now $400\%$ of its original value, not $100\%+100\%=200\%$.
Successive percentage increases are multiplicative, not additive. When something increases by $p$ percent, it is multiplied by $1+\frac{p}{100}$. If you now increase it by $q$ percent, you're multiplying the increased amount by $1+\frac{q}{100}$. The combined effect is to multiply the original quantity by
$$\left(1+\frac{p}{100}\right)\left(1+\frac{q}{100}\right)=1+\frac{p+q}{100}+\frac{pq}{10000}\;;$$
assuming that $p\ne 0\ne q$, this is clearly not the same as multiplying by $1+\frac{p+q}{100}$ to get an increase of $p+q\%$.