How to Calculate Growth Rate in Excel (Formula)

If I tell you my business made a $500 million revenue this year and expects to make a revenue of $750 the next year.

Can you tell me how much revenue growth am I expecting in my business’s revenue over the coming year?

Don’t go on grabbing your calculator – This step-by-step guide teaches you how to do it in Excel in no time 🧮

Instead, grab the free practice sample workbook for this tutorial here, and let’s go spread-sheeting.

Oh, and by the way, that’s a 50% annual growth rate 😄 [($750-$500)/$500]

Table of Contents

What is growth rate (concept and formula explained)

Very simply, growth rate refers to the rate at which a number increases over time.

Now this number could be anything – your salary, expenses, stock prices, the profits of your business, the value of your business, or anything. You name it.

The growth rate is expressed as a percentage. For example, a 10% growth rate in profits tells that the profit of a business for a given year is 10% higher since the last year 📈

The growth rate can be calculated through two formulas:

Formula A:

And

Formula B:

• Closing value is the value as at the end of the period / of the successive period.
• Opening value is the value at the beginning of the period / of the past period.

The hundred with both formulas is used to convert the result into a percentage. In Excel, since we can apply the percentage formatting to numbers, you may skip the multiplication with 100.

Let’s now put both these formulas into action to calculate the growth rate in Excel 👇

Calculating Growth Rate in Excel

We don’t have any built-in functions in Excel to calculate growth rates. But using the formula for the growth rate discussed above, we can calculate it in Excel than you think.

Ready? ⛑

This is Company ABC with their net asset value for 5 years. A quick look into the numbers shows the business is doing all good. It’s time that numbers tell the tale of growth.

Step 1) To calculate the growth rate for each year, starting from Year 1, write the following formula (Formula A):

We have divided the Year 1 net asset value by the initial investment (net asset value at the beginning of the year). Deduct one from it to reach the percentage of change in net asset value.

The result by default will be in decimals. To convert it into a percentage:

Step 2) Go to the Home tab > Number format > Percentage Icon.

Excel would convert the decimal into a percentage 💪

The growth rate for Year 1 is 20%. This tells that the Net asset value of the business has grown by 20% over year 1 (since the initial investment).

Step 3) For the growth rate of Year 2, again write the following formula.

Since we are now finding how much the value has grown over Year 2, we will take the closing net asset value of Year 1 (i.e., 1200) and see the change in it in Year 2 by comparing it with the closing net asset value of Year 2 (i.e., 1850).

Change from Year 1 to Year 2 represents the growth rate of Year 2, which is 54%. That’s why we call it the Year-over-year (YoY) growth rate.

Btw, a 54% annual growth rate is nothing but fabulous 🎯

Step 4) For the remaining years, simply drag and drop the formula to the coming cells like here.

Excel automatically calculates the growth rate for all the years.

For Year 3, the growth rate is -14%. This shows that in Year 3, the business didn’t do well, and instead of growing from the existing value of $1850, it saw a 14% decline👎

That’s how easy it is to calculate growth rates in Excel.

Another way how you can calculate growth rates in Excel is by using Formula B:

Step 1) Calculate the difference in value since the last year by using the following formula:

Closing Value – Beginning Value

Step 2) Divide the change in value by the beginning value (the value for last year).

Returns what proportion is the change of the opening value. And that’s 20% – the business value in Year 1 has grown by 20%.

Pro Tip!

Make sure to enclose the subtraction operation (B4-B3) in parenthesis to indicate to Excel that subtraction needs to be performed first. And then the division operation ((B4-B3)/B3).

If there’s no parenthesis, following the DMAS rule, Excel will perform division before subtraction and produce incorrect results.

Step 3) Drag and drop the results to all the remaining cells.

Both formulas yield the same results. Choose and apply anyone you like!

Growth Rate vs. AAGR vs. CAGR

After we have calculated the growth rates for 5 years in the above example, put yourself in a situation where you have to tell someone about the performance of this business.

Weird to say that the business grew by 20% in Year 1, 54% in Year 2, saw a decline of 14% in Year and so on 🤔

There should be a single percentage to define the growth of a business over the years (be it 2 or 20). The following two rates help you with that.

AAGR

AAGR stands for Average Annual Growth Rate.

Just like the name says, it is the average of the annual growth rates of a business.

To find the AAGR for the above example:

Step 1) Write the AVERAGE function to calculate the average of the YoY growth rates.

The Average annual growth rate comes out to be 32%.

Tells that this business grew by an average of 32% each year – that’s how you can define the growth of numerous years in a single percentage.

However, the average growth rate ignores the effect of compounding.

CAGR

CAGR stands for Compounded Annual Growth Rate.

Unlike the average growth rate, it doesn’t simply average out the growth rates of each year.

It gives the rate of return at which if the initial investment is compounded annually for the given number of years, it will grow to the closing value.

 CAGR is calculated through the following formula:

To find the CAGR for the above example:

Step 2) Write the above formula using the relevant cell references like below.

Ending value means the value of the business at the end of the entire period of time over which the growth is evaluated ($3600). And beginning value means the value on Day 1 (Investment of $1000).

The number of years in our example was 5 so we have raised the above formula to the inverse exponential growth of 5.

The Compound Annual Growth Rate comes out to be 29% 🏍

Tells that if you start compounding $1000 on 29% every year, it will become $3600 by year 5.

Conclusion

Growth rate is a measure of evaluating how a business grew over a certain period. Calculating it in Microsoft Excel is super simple if you know how to put together the formula to have it calculated.

It’s just like finding the percentage difference between two numbers. Check out my Excel tutorial on how to Find the Percentage Difference Between Numbers in Excel and How to Calculate Percentages in Excel (Percentage Formula) to learn more about it.