How to Calculate Annual Growth Rate in Excel
Gather the data relevant to the growth you want to calculate., Total all the relevant numbers for the area on which you want to run this calculation., Enter the earliest year for which you have numbers in line 2, column A of your Excel spreadsheet...
Step-by-Step Guide
-
Step 1: Gather the data relevant to the growth you want to calculate.
You might want to calculate overall growth of an investment, growth of a certain expense base, growth of sales or any other facet of your business or personal investments.
You will need information for at least 2 complete and consecutive years if you want to calculate meaningfully comparable annual growth rates.
If calculating the annual growth of gross income for a business, you would need all the income for all departments of your business, for example sales receipts or bank statements showing all deposits. -
Step 2: Total all the relevant numbers for the area on which you want to run this calculation.
Calculate each year separately.
For example, calculating sales growth in a department will mean totaling all sales numbers for that department for each year, but not sales numbers for other departments or expense numbers.
For robust analysis of your business performance, you will want to do this for gross income, gross profit, net profit and meaningful statistics from each location and/or department. , Enter the next year in line 3, column A. , of line
1.
If you're only doing 1 comparison, you only need 1 column. , For example, if you had 2007 in line 2, 2008 in line 3, sales in column A and net profit in column B.
The total sales for 2008 would go in cell 3A and the net profit for 2007 would go in cell 2B. , This will instruct Microsoft Excel to enter the difference between performance for the 2 years, expressing as percentage growth. , Excel will automatically change the formula to reflect the new location. -
Step 3: Enter the earliest year for which you have numbers in line 2
-
Step 4: column A of your Excel spreadsheet.
-
Step 5: Enter departments and areas in column B
-
Step 6: Enter the appropriate totals from earlier steps in the appropriate cells.
-
Step 7: Enter the following formula in line 4
-
Step 8: column B: "(+B3/B2*100)-100".
-
Step 9: Copy and paste the contents of cell B4 to other cells where you need the annual growth rate.
Detailed Guide
You might want to calculate overall growth of an investment, growth of a certain expense base, growth of sales or any other facet of your business or personal investments.
You will need information for at least 2 complete and consecutive years if you want to calculate meaningfully comparable annual growth rates.
If calculating the annual growth of gross income for a business, you would need all the income for all departments of your business, for example sales receipts or bank statements showing all deposits.
Calculate each year separately.
For example, calculating sales growth in a department will mean totaling all sales numbers for that department for each year, but not sales numbers for other departments or expense numbers.
For robust analysis of your business performance, you will want to do this for gross income, gross profit, net profit and meaningful statistics from each location and/or department. , Enter the next year in line 3, column A. , of line
1.
If you're only doing 1 comparison, you only need 1 column. , For example, if you had 2007 in line 2, 2008 in line 3, sales in column A and net profit in column B.
The total sales for 2008 would go in cell 3A and the net profit for 2007 would go in cell 2B. , This will instruct Microsoft Excel to enter the difference between performance for the 2 years, expressing as percentage growth. , Excel will automatically change the formula to reflect the new location.
About the Author
Diana Wright
Dedicated to helping readers learn new skills in creative arts and beyond.
Rate This Guide
How helpful was this guide? Click to rate: