Percent Change formula

The formula is New Value divided by Old Value minus 1.

So if the Old Value is in B2 and the New Value is in C2 and you want the percentage change D2 you would use:

=C2/B2-1

And format D2 as Percentage

SG

Easier and less error prone to just use the simplest form. It's the most common form used by financial analysts.

Not needed, but the derivation is perhaps of interest. It involves taking the complex form and multiplying by 1 to get something easier to use:

(New-Old) 1/Old New/Old -1

———— x —— = ————— ===> New/Old-1

Old 1/Old 1

SG

And here is an illustration of SG's solution, posted first above:

Kappy's solution won't give the right result and Paul's doesn't illustrate Kappy's and involves extra typing and thus is easier to get wrong. It's algebraically equivalent to my originally suggested:

=New/Old-1

Or in this example,

=C2/B2−1

SG

Hi Kappy,

No sense in needlessly complicating things by suggesting you've demonstrated that =New/Old-1 won't give the correct result.

Note that it's =New/Old-1 just as I posted, not the =Old/New-1 you demonstrated doesn't work.

I do these kinds of rate of change calculations for a living in my day job.

SG

If you simply look at the example you see that new = 120 which is a 20 point increase over old = 100. Divide 20 by 100 then that is a 20% increase. And, that is the answer you get with my formula. Your formula is 120/100 -1 = .20. Both give the same result. I don't recall if you stated it differentaly at first or if I read it wrong. It was my error in saying your way was wrong for which I apologize. We are both right.

(old - new) / old = %change as a decimal amount. If you want the percentage displayed then set the result cell for % by multiplying the result by 100.

Kappy's explanation shown in a screenshot of a table:

for Cell D3: format is AUTOMATIC

for Cell E3: format is PERCENTAGE

Paul.

Actually, my formula is correct for showing a percentage chain. Dividing old by new only shows old as a percentage of new. Not the same end result. For example, if old = 100 and new = 120:

old/new -1 = 0.16667

((old - new) / old) / old = 0.20

Sorry, a typo. I left off the sign which should, of course, be negative. Thanks for bringing it to my attention. The formula I use is the same as any other program for computing a percentage change such as a stock's or portfolio's annual or monthly rate of return. I've been doing this stuff for years so I know the arithmetic or algebra is correct.

As you have seen demonstrated your formula provides a different result that is incorrect if you want a correct percentage or rate of change from one period to the next.

You win!! 😁

Kappy,

Garry Kaye has the last word.

Paul.

SG,

of course your formula is algebraically the shortest and 100% correct.

I just wanted to show the different steps for getting to a percentage in this case for non-mathematicians.

Paul.

Kappy,

I wonder how you get a positive value from your formula if New > Old as in your example.🙂

Again, the easiest, as originally posted, is just:

=New/Old-1.

SG