How To Expertly Calculate Growth Rate in Excel

What is growth rate?

Growth rate is an essential SaaS metric to track because it shows the increases (or decreases) in KPIs over a certain period of time. Many finance leaders associate growth rate with revenue growth, but in reality, you can use these formulas to track any variable that can change over time, such as subscriber numbers, employee wages, team sizes, expenses, and more. 

Calculating growth rate leaves us with a percentage, and the most straightforward way to calculate growth rate is using the equation:

• Growth rate = (past value - present value / past value) x 100

Using this growth equation, you can calculate more advanced growth metrics, such as average and compound annual growth rates. Understanding these growth rates is critical to financial modeling and data analysis, especially if you like profits! 

In this article, explore how to use Microsoft Excel to calculate these growth rate equations and why your SaaS business should use this data to make more informed decisions. 

Why SaaS businesses should calculate growth rate

Understanding and calculating growth rates help SaaS leaders assess current financial health, estimate future growth, make strategic business decisions, and develop accurate financial modeling presentations. 

For start-ups looking for additional cash flow, calculating growth rates can help outside investors evaluate their rate of return potential, especially during series funding stages. Plus, it can help you determine how and where to deploy company resources to sustain that growth. 

Finally, business leaders love growth rate because it pairs well with other growth metrics, such as: 

• Monthly and annual recurring revenue (MRR & ARR)
• Customer acquisition costs (CAC)
• Customer lifetime value (CLV)
• Sign-up rates
• Expansion revenue
• Churn rates
• Net promoter scores (NPS)

How to calculate growth rate in Excel

Microsoft Excel is a powerful business tool that uses Visual Basic for Applications (VBA) or programming Microsoft uses to make their office products. As a result of decades of improvement, Excel offers one of the best ways to evaluate reports. Here’s how you can use Excel to create a spreadsheet with both average annual growth rate (AAGR) and compound annual growth rate (CAGR). 

Calculating average annual growth rate

‍

‍

Average annual growth rate is the mean increase in the value of a variable annually. While leaders can use AAGR to track growth in subscribers, employees, or service areas, most managers use it to track financial information, like revenue, profits, or expenses. 

AAGR is a linear measure that helps you evaluate long-term trends and can help outside investors forecast their rate of return. However, it is not ideal for projecting your SaaS company’s stock price or volatility. 

Before calculating AAGR, you must first know your growth rate over the period, usually a year. The growth rate formula looks like this: 

• Growth Rate = (ending value - beginning value / beginning value) x 100

Now, to calculate the average annual growth rate, you need to calculate the growth rate of individual years. For example, your starting value would be the revenue at the beginning of the year, and the ending value would be the revenue at the end of the year. 

Armed with your growth rates for the number of years you wish to analyze, you can input your data points into the following average annual growth rate formula:

• AAGR = (GR₁ + GR₂ + GR₃…GR_n / N) 

In this equation, GR represents the growth rate of each year. N is the number of years in your evaluation period. With these equations, you can build a Microsoft Excel template to evaluate your AAGR. To develop your Excel document, follow this step-by-step tutorial: 

1. Open a new Excel document and start creating a table of values. 
2. In column A, enter the dates of the years you are analyzing, you can use year 1, year 2, etc., or you can use actual dates.  
3. In column B, label your starting values, such as revenue at the beginning of year 1. 
4. In Column C, label your ending values, such as the revenue at the end of year 1. 
5. In column D, build out your formula to calculate your growth rates if you don’t already have those percentages. If you already have that data set, feel free to skip to step 9. 
6. Highlight column D by clicking the top. Under the “Home” tab, select the “Percent Style” button, which is the “%” button, to automatically shift decimal places to display percentages instead of general information. 
7. Then, double-click the cell in column D next to your first set of data points. Build your equation by typing this formula: =(C2-B2)/B2 and hit enter. As you’ll notice, this Excel formula looks just like our percentage growth rate equation from earlier. 
8. Repeat this formula for each row (i.e., C3-B3/B3, C4-B4/B4).
9. Now that you have your growth percentages for each period you want to analyze, you can now find the average annual growth rate. In a new cell or the cell directly below your growth percentages, type: =AVERAGE(D2:D6). 
10. Alternatively, you can use a variation of this Excel formula by manually entering the data. For example, you would type: =(D2+D3+D4+D5+D6)/5. 

Once you have your table, the growth percentage equation, and the average annual growth rate, you can evaluate your average year-over-year growth rate. However, it’s essential to understand that there are limitations to calculating AAGR and using it for decision-making purposes.

Calculating compound annual growth rate

‍

‍

Compound annual growth rate (CAGR) is a representational percentage that smooths out erratic data points to make it easier to view current investment value and forecast future value. Essentially, it’s the average annual growth rate required to grow from your initial value to your present value. 

Where AAGR has limitations in that you have misleading information, especially if growth slows, CAGR is one of the most accurate methods of calculating the growth rate, especially when compared to a benchmark metric. 

You will need a more complex formula to calculate the compound annual growth rate. The CAGR formula looks like this:

• CAGR = (present value / initial value)^(1/number of periods of time) – 1

When entering this formula into Excel, it will be crucial that you include parentheses around your exponent “(1/number of periods of time) to prevent incorrect calculations. To calculate your CAGR in Excel, use this step-by-step tutorial: 

1. Start with a blank Excel page, or use your template with data collected from your AAGR table. 
2. Select a new blank cell and format it for percentages by selecting the home tab and clicking the Percent Styling button that looks like “%.” 
3. You can type the CAGR Excel formula in two ways. The first method involves using manual numbers, and the formula is: =(650/100)^(⅕)-1. 650 is the present value, 100 is the initial value, and 5 is the number of years between the two data points. 
4. Or, you can use another method that uses the variable call feature, which looks like: =(G11/F7)^(1/E11)-1. Again, G11 is our present value, F7 is our initial value, and E11 is the number of years between data points. 

Either way, you get the same CAGR value, but the second method is more useful if you pull data sets from an already established table, such as the table for AAGR. It’s also important that you put the equal sign first; otherwise, you will end up with a syntax error that only shows your formula and with no results. 

Streamline your growth rate calculations with Facta 

With your Excel worksheets built and saved, you can return to them periodically to update data points and re-evaluate your financial models. Although AAGR and CAGR display different results, they are essential metrics your SaaS company should track and calculate! 

Still looking for the best SaaS metrics platform? Facta’s single source of truth capabilities makes the Facta Insights platform an industry leader in performance and accuracy. Request a demo today to see it in action! 

‍