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.
This video is the podcast companion to the book Learn Excel 97-2007 from MrExcel.
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.
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.
