I am working on a year-over-year financial report and need to determine the growth rate between last year’s revenue and this year’s. Is there a specific Excel function for this, or should I use a manual mathematical formula? Additionally, if I have data spanning five years, how do I calculate the average annual growth rate (CAGR) to show a consistent trend?
3 answers
For a simple period-to-period growth rate (like Year-over-Year), you don't need a complex function; a basic mathematical formula is best. The logic is:
$$(New Value - Old Value) / Old Value$$
In Excel, if your old value is in cell A2 and the new value is in B2, your formula would be =(B2-A2)/A2. Make sure to format the resulting cell as a Percentage using the "%" button on the Home tab. This is the standard approach used in professional Data Science and accounting to visualize performance changes instantly.
If you are looking at growth over multiple years, shouldn't you use the CAGR (Compound Annual Growth Rate) formula instead of just averaging the yearly growth rates?
Don't forget that if your "Old Value" is zero, you'll get a #DIV/0! error. I usually wrap my growth formulas in an IFERROR function to keep my dashboards looking clean!
I agree with Sarah; IFERROR is a lifesaver. Using =IFERROR((B2-A2)/A2, 0) ensures that new projects starting from zero don't break your entire spreadsheet!
Marcus is absolutely right. Simply averaging yearly percentages can be misleading due to the effects of compounding. To calculate CAGR in Excel, the easiest way is using the RRI function: =RRI(number_of_periods, start_value, end_value). For example, if you have 5 years of data, your periods would be 4. This provides a smoothed annual growth rate, which is the gold standard in Software Development for tracking user growth or in Finance for investment returns. It effectively tells you what the steady growth rate would have been if the value grew at the same rate every year.