The compound annual growth rate worked from a five-year record, the volatility drag that separates it from the average, and the Excel formulas for both.
CAGR = (ending value ÷ starting value)1/years − 1, the single steady rate that turns the start into the finish. On an illustrative five-year record of +18, −12, +25, −8 and +14 per cent, 10,000 becomes 13,613: the average return is 7.40 per cent but the CAGR is 6.36 per cent. The gap, 1.04 points, is the cost of volatility, and it is the CAGR that reaches your account.
Worked in full in Stock Market Investing for Beginners by Julian R. Sterling, with every figure reproduced in a free workbook.See the book on Amazon →
Fund factsheets, broker apps and newspaper tables quote both figures, often without saying which. They answer different questions. The average tells you what a typical year looked like; the compound annual growth rate tells you what the money actually did.
All returns are illustrative, chosen to look like an ordinary run of equity years: three up, two down.
| Year | Return | Value at year end |
|---|---|---|
| Year 1 | +18% | 11,800 |
| Year 2 | −12% | 10,384 |
| Year 3 | +25% | 12,980 |
| Year 4 | −8% | 11,942 |
| Year 5 | +14% | 13,613 |
Arithmetic average = sum of yearly returns ÷ number of years
= (18 − 12 + 25 − 8 + 14) ÷ 5 = 37 ÷ 5 = 7.40%
Excel, with the returns in B2:B6: =AVERAGE(B2:B6)
CAGR = (end ÷ start)1/n − 1
= (13,613 ÷ 10,000)1/5 − 1 = 1.36130.2 − 1 = 6.36% (6.3637% unrounded)
Excel: =(C6/10000)^(1/5)-1, or =RRI(5, 10000, C6), or from the returns alone =GEOMEAN(1+B2:B6)-1
Check: 10,000 × 1.06365 = 13,613.
The check is the point. Compound 10,000 at the average instead and you get 14,290, 676 more than the account ever held. The average cannot be used to project money; the CAGR can, because it is defined as the rate that reproduces the ending value.
A loss needs a larger gain to undo it. Go up 50 per cent and then down 50 per cent: the average is zero, but 100 becomes 150 and then 75. The CAGR is −13.40 per cent a year. The fall of 50 per cent is taken from a larger base than the rise of 50 per cent was added to, and that asymmetry always works against the investor.
A useful approximation ties the two figures together: CAGR ≈ average − variance ÷ 2. The record's yearly returns have a standard deviation of 14.69 per cent (population), so the variance over two is 1.08 points and the approximation gives 6.32 per cent, close to the exact 6.36. The more the returns swing, the wider the gap.
Take four investments with the same 7 per cent average, alternating one year above and one below by a fixed swing.
| Swing | Good year | Bad year | CAGR | Drag | Value after 10 years |
|---|---|---|---|---|---|
| 0 | +7% | +7% | 7.00% | 0.00 | 19,672 |
| 10 | +17% | −3% | 6.53% | 0.47 | 18,827 |
| 20 | +27% | −13% | 5.11% | 1.89 | 16,467 |
| 30 | +37% | −23% | 2.71% | 4.29 | 13,063 |
The drag grows with the square of the swing: doubling the swing from 10 to 20 points roughly quadruples it, from 0.47 to 1.89. An investment that advertises a high average with large swings can compound more slowly than a duller one with a lower average.
The exponent is one over the number of years, whatever that number is. 10,000 growing to 11,500 in 30 months is a total gain of 15 per cent over 2.5 years, so the CAGR is 1.1512/30 − 1 = 1.150.4 − 1 = 5.75 per cent. Dividing the total gain by the years instead gives 6.00 per cent, which ignores compounding and overstates the rate. In Excel with dates, =(end/start)^(365/(end_date-start_date))-1 does the same job.
The CAGR hides the path. The book's companion files include a case with ten years at nothing and thirty years at 9.44 per cent: the growth factor over forty years is 14.9721, a CAGR of 7.00 per cent, exactly the steady path. The arithmetic average is 7.08. A single sum invested at the start ends in the same place either way round. A saver adding money every month does not: with the lost decade first, every contribution she makes still gets the full thirty good years and she finishes far ahead; with it last, her largest balances sit still for a decade. CAGR measures one lump sum from start to finish; it says nothing about money added along the way, which is measured by a money-weighted return instead.
The CAGR is also the right rate for the rule of 72 and for compounding projections: see how accurate the rule of 72 is, and for monthly saving at a steady rate, what 500 a month grows to in 30 years.
Use the CAGR to describe what money did and to project what it might do: (end ÷ start)1/n − 1, 6.36 per cent on this record. Use the average only to describe a typical year, and expect it to sit above the CAGR by about half the variance. The illustrations behind these figures, with the lost decade placed first or last, are in the free workbook and cases for this book.
=(end/start)^(1/years)-1, or =RRI(years,start,end). From a column of yearly returns use =GEOMEAN(1+range)-1, entered as an array formula in older Excel. For 10,000 growing to 13,613 over five years all three give 6.36 per cent. Do not use AVERAGE on the returns: it gives 7.40 per cent on the same record.
No, not on the same yearly returns. The geometric mean is never above the arithmetic mean, and they are equal only when every year's return is identical. The gap is roughly half the variance: with alternating returns of +27 and -13 per cent, averaging 7 per cent, the CAGR is 5.11 per cent.
It depends on the risk taken and the period, so compare a CAGR only with another CAGR over the same years. As an illustration, a steady 7 per cent compounds 10,000 to 19,672 in ten years, while the same 7 per cent average with swings of 30 points compounds at 2.71 per cent and reaches only 13,063.
This article is one calculation from Stock Market Investing for Beginners. The book takes the same case from first principles to the decision, chapter by chapter, and every figure it prints is a live formula in the free companion workbooks.
Get the book on Amazon →Free companion files
Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada
Reading guide: personal finance and investing for beginners → · All 1340 articles →
If this book helped, or didn’t, a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The workbook stays free either way.