How this instrument works
Compound annual growth rate answers one narrow question precisely — at what steady yearly rate would the starting figure have to grow, compounding on itself, to land exactly on an ending figure after t years? Because growth multiplies rather than adds, that rate is a geometric mean of each year's growth factors, not an average of yearly percentages. One holding up 50 percent then down 50 percent shows an arithmetic mean of zero, yet its dollar has become 75 cents; CAGR reports −13.40 percent, and CAGR is right.
Shape of the formula follows from that. Divide ending by beginning to get the total growth factor, raise it to power 1⁄t so that factor spreads evenly across t years, then subtract 1 so that no change reads as zero rather than as one. Roots handle ragged periods without complaint, so 3.5 years is perfectly legitimate as an exponent. What breaks it is any beginning figure at or below zero, or an ending figure below zero — no real constant rate connects positive starts to negative finishes, which is why stakes wiped out entirely have no meaningful growth rate at all.
Every path is erased in exchange for that tidiness. Two endpoints are read and nothing between them, so a position that limped sideways four years then tripled in year five reports identically to one grinding out even gains. It also assumes nothing entered or left. Add monthly contributions and this figure stops being a return; money-weighted measures are needed instead. Fees, taxes and inflation sit outside as well, unless you have already netted them out of your ending figure.
- Enter Beginning value, $ — what a position was worth on day one, before any purchase costs.
- Enter Ending value, $ — what it is worth now, with dividends folded in only if you want them counted as growth.
- Set Years to elapsed time between those two dates. Decimals are welcome, so eighteen months is 1.5.
- Read Compound annual growth rate, % — a single steady rate linking start to finish.
- Check it by hand: apply that rate t times over to your beginning figure and you should land back on your ending figure.
Worked example — doubling inside twelve months
Beginning value, $ 10,000. Ending value, $ 20,000. Years 1. Total growth factor is 20,000 ⁄ 10,000 = 2. Raise 2 to power 1⁄1 and it stays 2; subtract 1 and you are left with 1, which is 100 percent. Compound annual growth rate, % reads 100.
That case earns its keep because you can verify it without a machine — money doubling in exactly one year grew exactly 100 percent, no compounding subtlety involved. Stretch an identical doubling across five years instead and this figure falls to 14.87 percent, since 1.1487 raised to a fifth power is 2. Same dollars, same total gain, wildly different rate — which is why any headline growth claim means nothing until somebody states a period alongside it.
Questions
Why does CAGR differ from the average of my yearly returns?
Because returns multiply rather than add. Arithmetic averaging treats +50 percent and −50 percent as cancelling out; compounding does not, since a later loss applies to a bigger base. Across those same two years an arithmetic mean of 0 percent sits against a compound rate of −13.40 percent. Gap between them widens with volatility, and compound growth is always lower unless every yearly return is identical.
Does this account for money I added along the way?
No. Only two endpoints are read, so deposits made in year three look exactly like investment gains. Feed savings topped up by monthly transfers into this sheet and you will get flattering nonsense. Dated cash flows in or out call for money-weighted measures — internal rate of return, or your spreadsheet's XIRR function — which solve for whichever rate discounts every flow back to zero.
Is this a real return or a nominal one?
Nominal, unless you deflate your two figures before entering them. A 7 percent compound rate earned while prices rose 3 percent a year left purchasing power growing nearer 3.88 percent, because real growth divides rather than subtracts: 1.07 ⁄ 1.03 = 1.0388. Tax sits outside too — a taxable account surrenders part of each gain, and IRS treatment turns on how long a position was held.
Should Years count calendar labels or elapsed time?
Elapsed time, always. Growth from the 2019 year-end figure to the 2024 year-end figure spans 5 years, not 6, even though six annual statements are involved. Counting data points instead of intervals is one common slip, and it quietly understates growth by roughly one fifth across five-year runs. Where dates are ragged, express your gap in days and divide by 365.25 before entering it.
Can beginning value be zero or negative?
No, and this sheet blocks both. Division by zero has no answer, and a negative start makes fractional powers land outside real numbers entirely. A matching trap catches anything ending at zero: total loss has no finite constant rate, only a limit heading down forever. A business metric that legitimately crosses zero — operating profit, say — needs absolute change instead.
Why doesn't my fund's published annualised return match?
Published figures usually run to a fixed month end, net of an expense ratio, with distributions treated as reinvested on their ex-dividend dates. Your own two balances may carry a purchase spread, a different start date, or dividends taken as cash. Line up dates first, then check whether distributions sit inside your ending balance — that single item explains most gaps on equity holdings.
References
- SEC investor.gov — Compound interest calculator
- IRS — Topic no. 409, Capital gains and losses
- Federal Reserve — Economic research data releases
Read this first: This instrument shows arithmetic, not advice. Real offers add fees, taxes and terms that vary by lender and place — verify the figures against your actual paperwork before deciding anything.