PUZZLED - Daily Percentage Change and Cumulative

[Stuart]

Dear All,

I was never any good with maths and this just puzzles me!

I have date starting from 100 down column B with the date in column A.

The last number is 117.50.

I make this an increase of 17.5%....however each day it may have moved
down 1% or up 2%. When I summate all the daily percentage changes I
was expecting to see 17.5% however I can see around 20%.

The formulas are all correct but I really need to know how to work out
the daily percentage change AND the cumulative percentage change from
100.

I hope someone can assist me.

S

 

[Don Guillett]

rounding?

 

[Bernard Liengme]

That's the way the math goes. You seem to be comparing apples & oranges. Try
this:
In A1:A10 enter the numbers 100, 101,102,...110
In B2 enter =(A2-A1)/A1 and format as percent, to find the increase in one
period;
Copy this down the column; you get 1.00%, 0.99%, ....0.92%
The ten percentages sum to 9.58% while the change over the entire period is
(110-100)/100 = 10.00%
best wishes

 

[ShaneDevenshire]

Hi,

I think what you are asking is why the cumuliative percent change does not
equal the sum of the individual percent changes?

Example:

Suppose you buy a $100 shirt which says 50% off - the cost would be $50.
But suppose there was a sign that said for today only take an additional 50%
off - what would your cost be? 50%+50%=100%? true but not the correct
answer. The second 50% is applied against the $50 cost or 50%*$50 = $25.
The shirt will cost $25%. What was your total saving $75 or 75/100 = 75%

 

[Erik Veldkamp]

To track the cumulative percent change, add 100% to the daily percent
change, then multiply (not sum).

100 100%
110 10% 110% 110%
115 5% 105% 115%
125 9% 109% 125%


so 109% * 115% = 125%. Subtract again 100% to get 25%

 

[joeu2004]

I have date starting from 100 down column B with the date in column A.
The last number is 117.50.

I make this an increase of 17.5%....however each day it may have moved
down 1% or up 2%.  When I summate all the daily percentage changes I
was expecting to see 17.5% however I can see around 20%.

You cannot sum daily percentage changes and expect that to equal the
cumulative change. Instead, you multiply the daily change.

Actually, you multiply 1 plus daily change, then subtract 1. Consider
the following simple sequence: 100, 110, 90. The interval changes
are +10% and -18.18%. The cumulative change is (1+10%)*(1-18.18%) =
-10%. (Of course, "-18.18%" is really 90/110-1.)

One way to do that is with the following array formula (commit with
ctrl-shift-Enter):

=PRODUCT(B2:B100 / B1:B99) - 1

where B1:B100 is the data (100, 110, and 90 in my example).

However, PRODUCT has poor numerical characteristics for large data.
That can be avoided by computing the sum of the log, then computing
the antilog. Big words; easier to write (again, an array formula):

=10 ^ SUM(LOG(B2:B100 / B1:B99)) - 1

Of course, format the result as Percentage.

HTH.

 

[joeu2004]

PS....

However, PRODUCT has poor numerical characteristics for large data.
That can be avoided by computing the sum of the log, then computing
the antilog.  Big words; easier to write (again, an array formula):
=10 ^ SUM(LOG(B2:B100 / B1:B99)) - 1

You can avoid the array formula by entering the following normal
format (commit with just Enter):

=10 ^ SUMPRODUCT(LOG(B2:B100 / B1:B99)) - 1

 

[joeu2004]

Errata....

Actually, you multiply 1 plus daily change, then subtract 1.  Consider
the following simple sequence:  100, 110, 90.  The interval changes
are +10% and -18.18%.  The cumulative change is (1+10%)*(1-18.18%) =
-10%.  (Of course, "-18.18%" is really 90/110-1.)

Obvious typo: forgot to "then subtract 1". Klunk! The cumulative
change evaluation should read: (1+10%)*(1-18.18%) - 1 = -10%.