Learn Excel from MrExcel Episode 908 - Compound Growth Rates

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Dec 19, 2008.
As you approach year end, you might want to calculate compound growth rates. Episode 908 will show you how.

This video is the podcast companion to the book Learn Excel 97-2007 from MrExcel.
maxresdefault.jpg


Transcript of the video:
Welcome back to the MrExcel netcast, I'm Bill Jelen.
Well, year end is approaching; this is the time that we always want to see how big our company grew, our mutual funds grew, or anything like that.
So let's talk about growth rates.
Now, there's a simple calculation to figure out growth rate of one year over the previous year.
So here, we have something that was 100 and went to 120.
That formula is the later year divided by the earlier year.
Now, that's going to give us one 120%.
So you always need to subtract 1-- basically, the hundred percent-- so we subtract 1 and we see that that is 20% growth.
Well, this concept works great but let's say we have a couple of years.
So the later year divided by the earlier year, minus 1, we have 13% growth there; and then copy it down, 27% growth the second year.
But usually, we're talking about not the average growth rate, but something called a compounded growth rate.
And this is a little bit more difficult to calculate.
Basically, what we want to do is, we want to start out the same way; we want to take the latest year-- so in this case, Year 3-- divided by the earliest year-- Year 1-- and what we have to do is raise that to a fractional power-- raise that to a fractional power.
So an exponent is the carrot.
Shift+6.
And we're always going to put 1 divided by the number of years from the first year to the last year.
So in this case, Year 1, Year 3, is 2 years; we raise it to the second power, and then finally, again, this is going to give us a number with a hundred percent.
So subtract 1, and we see that that was 20% growth.
Now, let's extend this.
Let's say that we had 145,000 in Year 1; 615,000 in Year 5; what's the compound growth rate over the 4 years?
So we start out last year divided by first year, and we raise that, in this case, to the one-fourth power (1/4) because there's four years from Year 1 to Year 4-- so 1/4-- and again, subtract 1, and it says 43.5%.
Now, how do we test this?
Let's test and make sure that it's actually working.
So we take the Year 1-- and we're just going to create a test calculation out here-- Year 1 * 1.435.
And we had 208,000 in the theoretical Year 2.
Do that again to get the theoretical Year 3, and then the theoretical Year 4, and the theoretical Year 5; 614,964.8-- very close to the original number, probably a routing issue there in my first formula.
So when you're calculating compounded growth rates, the math is a little bit tough-- you always have to raise it to a fractional power, and the denominator of that fraction has to be the number of years from the first year until the last year.
Note that it really does not matter for this compound growth rate what happens in the intervening years; it doesn't matter whether you grew fast early on, or grew in the last year.
This calculation is only looking at the earliest year and the latest year, to figure out the math.
Well, hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
 

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top