Calculate a Growth Rate

March 08, 2022 - by Bill Jelen

Problem: I work for a quickly growing company. In the first year, we had $970,000 in sales. In the fifth year, we had $6,175,000 in sales. I need to determine our compounded annual growth rate.

Strategy: Sales in the fifth year are 6,175/970 higher than in the first year. The formula for growth is (Year5/Year1) - 100% or 537%.

Here is a tricky calculation. Revenue in Year 1 was $970 thousand. Revenue in Year 6 is 6.175 million. Over the four years, revenue grew by (B6/B2)-1 or 537%. But that is not the compounded growth rate. — Figure 363. Five-year growth rate.

However, a compounded growth rate is a number, x, that will calculate like this:

Year1 * (100% + x) * (100% + x) * (100% + x) * (100% + x) = Year5

This is the same as: Year1 * (100% + x)^4 = Year5

So, in order to calculate x, you have to be able to find the fourth root of (Year5/Year1). The formula to find the fourth root is to raise the number to the 1/4 power. Thus, the formula to calculate the compounded growth rate is: (Year5/Year1)^(1/4)-100% = x.

To prove that this formula is working, multiply year 1 by 1.5884235 four times. The answer should be very close to Year 5.

To calculate the compounded growth rate, use =(B6/B2)^(1/4)-100%. The result is 58.8%. If you started with Year 1 revenue and multiplied it by 158.8% four times, you would get the fifth year revenue of 6,175,000 — Figure 364. Compounded growth rate.

To verify the previous formula, use =B2*1.5884235*1.5884235*1.5884235*1.5884235. The answer is 6,175,000. — Figure 365. Prove that the 58.84% growth rate is accurate.

This article is an excerpt from Power Excel With MrExcel

Title photo by Markus Spiske on Unsplash