Formulas: Calculating Compound Annual Growth Rate (CAGR) in Numbers
August 17th, 2007 | by Michael |The compound annual growth rate measures the growth rate of an investment as if it grew at a steady rate over time.
For example, a savings account that pays a 3.00% APY will grow at three percent, year after year, until the bank changes the rate. Therefore, the CAGR on the savings account would be 3.00%. Simple. But, what if you bought a stock for $10 per share three years ago, and it is now worth $15. What is your gain? 50% right? 15-10/10 = .5. The 50% is your overall gain, but your money was tied up for 3 years. What was your annual rate of return. You can use CAGR to calculate to calculate this.
For this example we will have three inputs: beginning-value, ending-value, and number-of-years.
The formula for calculating CAGR is:
=((ending-value/beginning-value)^(1/number-of-years))-1
If you plug in our numbers from the stock example, $10 growing to $15, over a three year period, gives you a 14.47% compounded annual return. You could then use this number to compare your return to what the broad market averages returned during the same time frame.
4 Responses to “Formulas: Calculating Compound Annual Growth Rate (CAGR) in Numbers”
By Allan on Oct 8, 2007 | Reply
I think a small mistake has sneaked into Michaels formula. The formula to use is:
=((ending-value/beginning-value)^(1/number-of-years))-1
By Michael on Oct 8, 2007 | Reply
Allan,
Whoops! Thanks, making the edit now.
- Michael
By Glenn on Jan 2, 2008 | Reply
How do you download the CAGR template? I can’t seem to find the link……
By Michael on Jan 3, 2008 | Reply
Glenn,
I didn’t upload a template for this, I was only sharing the formula.
If you would like some help on it, leave a question in the forum.
- Michael