Interpretation of Percent Change
Over time I've found that the interpretation of percent change is one of the most simple and misunderstood calculations in finance. As an example, take the following data:
The formula in cell C3 is
=(B3-B4)/B4
This is the formula that I've found most people use. The formula works great when you're comparing positive numbers. However, a problem occurs when you bring in the negatives. Looking at cell C11, is it correct to say that the increase from -45 to -35 is -22.2%? If -35 is a larger number than -45, how can the increase be a negative?
If you look at all numbers, positive and negative, as being on a single scale the calculation is easier to see. Now we'll take the same data above and apply a slightly different formula:
The formula in cell C3 is
=(B3-B4)/(ABS)B4
When you look at column C, all of the percent increases are positive percentages. Likewise, when you look at column D all of the percent decreases are negative percentages. Positive percentages correspond to moving up the number scale and negative percentages correspond to moving down the number scale.
To conclude, the formula that includes Excel's ABS function is the correct formula. The ABS function insures that the denominator in the formula is always positive which allows the formula to return the correct percentage change. If you use this formula and think of the number scale, you'll never have to worry about the calculation of percent change and / or how to explain it to those that may not understand how to interpret it.
