Simple Formula to Calculate Growth Rate in Excel

Are you trying to calculate the growth rate in Microsoft Excel? Whether you need to analyze sales data, investment returns, population growth, or any other type of data, knowing how to calculate percentage growth in Excel is an essential skill. In this article, we’ll explain the growth rate formula and walk through several examples of how to calculate growth rate in Excel.

What is Growth Rate?

Before we dive into the specifics of calculating growth rate in Excel, let’s define what growth rate means. Growth rate measures the change in a value over a period of time and expresses that change as a percentage. Growth rate is commonly used in business, investing, economics, and various other fields to quantify and analyze growth.

The basic formula for growth rate is:

Growth Rate = (End Value – Start Value) / Start Value

This formula calculates the growth rate between two values, a starting value and an ending value.

Excel Functions for Calculating Growth Rate

Excel provides several built-in functions that make it easy to calculate growth rate:

Basic Growth Rate Formula

To calculate the basic growth rate between two values in Excel, you can use a simple formula like this:

=(End Value – Start Value) / Start Value

For example, if the value of your investment grew from $1,000 to $1,500, you could calculate the growth rate in Excel like this:

=(1500-1000)/1000

The formula returns a growth rate of 50%, indicating that the investment value grew by 50% over the period.

RATE Function

Excel’s RATE function can be used to calculate the compound annual growth rate (CAGR) for an investment over a period of multiple years.

The syntax for the RATE function is:

=RATE(nper, pmt, pv, fv)

• nper is the total number of periods (years)
• pmt is the payment made each period (0 if none)
• pv is the present value
• fv is the future value

For example, to calculate the CAGR of an investment that grew from $10,000 to $20,000 over 5 years:

=RATE(5,0,-10000,20000)

This returns an annual growth rate of 14.87%.

XIRR Function

To calculate an annualized growth rate when the timing of cash flows is irregular, you can use Excel’s XIRR function.

The XIRR function syntax is:

=XIRR(values, dates, guess)

• values are the cash flow amounts. Outflows are expressed as negative, inflows as positive.
• dates are the dates corresponding to each cash flow
• guess is an estimate for what the return will be (can be left blank)

XIRR is commonly used to calculate annualized returns for investments with irregular cash flows, like capital calls and distributions in private equity.

How to Calculate Growth Rate in Excel

Now let’s walk through some examples of calculating growth rate in Excel for different scenarios.

Calculate Revenue Growth in Excel

Imagine you want to calculate the year-over-year growth in quarterly revenue. Here’s how the data might look:

To calculate the growth rate between Q1 2022 and Q1 2023, you could use this formula:

=(Q1 2023 Revenue – Q1 2022 Revenue) / Q1 2022 Revenue

Assuming the data is laid out as shown above, the formula in Excel would be:

=(E2-B2)/B2

This returns a growth rate of 50%, indicating that revenue grew 50% between Q1 2022 and Q1 2023.

To calculate quarter-over-quarter growth rates, you could add a new column with a formula like this:

=(Revenue – Previous Quarter Revenue) / Previous Quarter Revenue

For Q2 2022, the formula would be =(C2-B2)/B2, for Q3 would be =(D2-C2)/C2, etc.

Calculate Stock Price Growth in Excel

Another common usage of growth rate in Excel is to measure the growth in a stock price or other investment over time.

For example, let’s say you want to calculate the growth in Apple’s stock price between January 2, 2020 and January 4, 2021. The prices were:

• January 2, 2020: $75.09
• January 4, 2021: $131.01

To calculate the growth rate, set up the data like this:

Then use this formula:

=(End Price – Start Price) / Start Price

So in Excel it would be:

=(B3-B2)/B2

This returns 74.47%, the total growth rate over the 1-year period.

To convert to an annualized growth rate, raise the result to the power of (1/years). In Excel:

=((B3-B2)/B2)^(1/1)

The 74.47% annualized growth rate means the price increased at an annual rate of 74.47% during 2020.

Calculate Population Growth Rate in Excel

Growth rates are often used to quantify population changes as well. An annual population growth rate can be calculated from population levels at the beginning and end of the year.

For example, imagine a city’s population was 1,000,000 on January 1, 2020 and grew to 1,020,000 by January 1, 2021.

In Excel, set up the data like this:

Then use the growth rate formula, =(End Value – Start Value)/Start Value:

=(B3-B2)/B2

This returns a 2% annual population growth rate for the city in 2020. The population grew by 2% during the year.

Calculating Growth Rate by Comparing Two Time Periods

A slightly more complex growth rate calculation involves comparing values for two time periods, like months, quarters, or years. An example would be calculating the growth in sales from Q1 last year to Q1 this year.

Let’s say we want to calculate the year-over-year growth in quarterly sales for a company. Here’s the data:

To calculate the growth rate between Q1 2021 and Q1 2022, use this formula:

=(Q1 2022 Sales – Q1 2021 Sales) / Q1 2021 Sales

In Excel:
=(C2-B2)/B2

This returns 10%, the growth in sales between the two Q1 periods. Repeat this for the other quarters to calculate a growth rate for each one.

Using Growth Rates for Forecasting

One of the key uses of growth rates is for forecasting future values. If you assume that a value will continue to grow at a constant rate, you can project what it will be in the future.

The formula for this is:

Future Value = Current Value * (1 + Growth Rate)^Number of Periods

For example, let’s say a company’s revenue is currently $1,000,000 and you believe it will grow at 10% per year for the next 5 years. Here’s how you would calculate the projected revenue in Excel:

=1000000*(1+0.10)^5

This returns a future value of $1,610,510. The company’s revenue would grow to over $1.6 million in 5 years, assuming a constant 10% annual growth rate.

Keep in mind that this kind of projection assumes the growth rate stays constant, which is often not realistic, especially over longer periods. It’s a simplification but can still be useful for rough estimates.

Interpreting Growth Rates

When working with growth rates, it’s important to interpret them correctly. Here are a few key things to keep in mind:

1. Time Period: Always consider the time period the growth rate applies to. An annual growth rate of 10% is very different from a monthly growth rate of 10%.
2. Absolute vs. Relative: Remember that growth rates represent relative changes, not absolute ones. A 10% growth on a base of $1,000,000 is much larger in absolute terms than 10% growth on a base of $100,000.
3. Negative Growth Rates: A negative growth rate indicates a decline. A growth rate of -5% means the value decreased by 5% over the period.
4. Compounding: When growth rates are applied over multiple periods, they compound. This means that the absolute amount of growth gets larger each period, even if the growth rate stays the same.

Understanding these nuances is key to correctly interpreting and using growth rates.

Common Mistakes When Calculating Growth Rate in Excel

There are a few common errors to watch out for when calculating growth rates in Excel:

1. #DIV/0 Error: This occurs if you accidentally divide by zero. Make sure the denominator in your growth rate formula (the start value) is never zero.
2. Mixing up periods: When comparing two periods, like Q1 to Q1, make sure you’re using the correct values for each period in your formula. Double-check that the formula is referring to the intended cells.
3. Using the wrong formula: Be sure to use the right formula for what you’re trying to calculate. The basic percentage change formula works in many cases, but CAGR requires RATE or XIRR.
4. Forgetting to format as percentage: After calculating the growth rate, format the result as a percentage to display it properly. Select the cell and click the % button in Excel’s Home tab.

By following the formulas and examples outlined here and watching out for these common mistakes, you’ll be able to confidently calculate growth rates in Excel to analyze changes over time in revenue, investments, population, or any other value. Expressing growth in percentage terms is an effective way to measure and compare performance.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.