CAGR Formula
The box for whatever you are solving for is locked, because that is the answer. The rate box carries six decimals so all four directions agree with each other on the page defaults.
| Cell | Label (column A) | Value or formula (column B) |
|---|
The line, and what each letter is
The compound annual growth rate is the single yearly rate that carries a beginning value to an ending value over a length of time. One line does it:
Two things about that line are worth saying out loud. The rate comes out per year even when the length is not a whole number of years — that is what the annual in the name means, and it is why a length in months has to be divided by twelve before it goes in. And the line knows nothing about the period in between: two values and a length, and every year in the middle is smoothed away.
On the numbers this page opens with — 10,000.00 becoming 15,000.00 over five years — the line reads (15,000.00 ÷ 10,000.00) to the power of one fifth, minus one. That is 1.5 to the power of 0.2, which is 1.0844717712, so 8.45%.
The same line, rearranged three more ways
Any three of the four quantities give you the fourth. The rate is only the usual one to ask for; the length, the beginning value and the ending value come out of the same line by moving the letters around. The tool above does all four, and these are the four lines it runs:
| Solving for | The line | Excel | Answer |
|---|---|---|---|
| Rate | r = (E / S) ^ (1/n) − 1 | =(B2/B1)^(1/B5)-1 | 8.45% |
| Length | n = ln(E / S) ÷ ln(1 + r) | =LN(B2/B1)/LN(1+B6) | 5.0000 years |
| Beginning value | S = E ÷ (1 + r) ^ n | =B2/(1+B6)^B5 | 10,000.00 |
| Ending value | E = S × (1 + r) ^ n | =B1*(1+B6)^B5 | 15,000.00 |
The length is the one that surprises people: it needs a logarithm, because the unknown sits in an exponent. Everything else is multiplication and a power. The Excel column above assumes years; if your length is typed in months, the length line carries a ×12 on the end, because the log gives you years and you asked for months.
All four answers here come off the same three numbers, which is the point of putting them on one page: switch what you are solving for and the other two do not move.
CAGR formula in Excel
This is the part most pages leave as a sentence. Here is the whole block: paste it into an empty sheet and column B does the work. It is tab-separated, so column A takes the labels and column B takes the numbers and the live formula.
Row 5 is the conversion: sixty months becomes five years, and the rate on row 6 then reads off row 5. Change B3 to 36 and the rate on row 6 changes with it, because it is a live reference and not a number typed in twice. The cell returns 0.0844717712; format it as a percentage and it reads 8.45%.
| To solve for | Put this in that cell | On these numbers |
|---|---|---|
| Length, in months | B3: =LN(B2/B1)/LN(1+B6)*12 | 60.00 |
| Length, in years | B3: =LN(B2/B1)/LN(1+B6) | 5.0000 |
| Beginning value | B1: =B2/(1+B6)^B5 | 10,000.00 |
| Ending value | B2: =B1*(1+B6)^B5 | 15,000.00 |
The power form is plain arithmetic and plain cell references, so it works the same in Google Sheets and in every other spreadsheet that has them. The function names in the table above are Excel's.
When you move a formula into B1 or B2, the cell it used to hold is now the answer, so put the rate you are testing into B6 first. The block above is the one the tool is showing live — it rebuilds itself for whichever direction and unit you picked.
Three functions that land on the same number, and where they differ
| Function | What you hand it | The bit that catches people |
|---|---|---|
| =RRI(5,10000,15000) | A count of periods, then the beginning value and the ending value. | It returns the rate for one period. Give it 5 and you get a yearly rate; give it 60 months and you get a monthly one. Both values go in as you wrote them. |
| =RATE(5,0,-10000,15000) | Periods, a payment, the present value, the future value. | It is an annuity function, so it follows the cash-flow sign convention: one of the two values has to go in negative. With the payment at zero it lands on the same rate. |
| =XIRR(B1:B2,A1:A2) | A range of values and a range of dates. | The one Microsoft's own page for this points to. It wants real dates, it counts the days between them, and it wants the cash-flow signs too — which is what makes it the right one when your period is not a tidy number of years. |
| =(B2/B1)^(1/B5)-1 | Nothing but the arithmetic. | Works in every version of Excel and in every spreadsheet that is not Excel, and it is the only one of the four that rearranges into the other three directions. |
Three of those four are about the arguments, not the arithmetic. RRI and the power form agree on the number; RATE and XIRR agree on the number too, once the signs are right, because they are solving the same compounding equation — they just come at it from the side of cash flows, where money out is negative. If a function hands back something that is not 8.45% on the defaults above, check the signs and the unit of the period before you check anything else.
A length that is not in years
The rate is quoted per year, so the length has to become years before it goes into the line. Days are divided by 365, weeks by 52, months by 12, quarters by 4. The same two values typed five ways, all of them coming back to the same rate:
| Unit | Length you type | Divided by | Length in years | CAGR |
|---|---|---|---|---|
| Years | 5 | 1 | 5.0000 | 8.45% |
| Quarters | 20 | 4 | 5.0000 | 8.45% |
| Months | 60 | 12 | 5.0000 | 8.45% |
| Weeks | 260 | 52 | 5.0000 | 8.45% |
| Days | 1,825 | 365 | 5.0000 | 8.45% |
Every row above divides by hand: 20 ÷ 4, 60 ÷ 12, 260 ÷ 52, 1,825 ÷ 365. That is the whole conversion, and it is the line that goes into row 5 of the block.
A shorter length is where the annual in the name starts doing work. 8,000.00 becoming 12,000.00 over eighteen months is a 50.00% move — the same 50.00% the five-year example has — but it took a year and a half, so the yearly rate is 31.04%, which is 1.5 to the power of one over 1.5, minus one. The rate for a single month inside that is 2.2781%, and twelve of those compound up to 31.04%.
Worked example you can check by hand
Ten thousand becoming fifteen thousand over five years, then the same three numbers read the other three ways:
| Direction | Arithmetic | Result |
|---|---|---|
| Multiple | 15,000.00 ÷ 10,000.00 | 1.5000× |
| Total | 1.5000 − 1 | 50.00% |
| Rate | 1.5000 ^ (1 ÷ 5) − 1 = 1.0844717712 − 1 | 8.45% |
| Length | ln(1.5000) ÷ ln(1.0844717712) = 0.4054651081 ÷ 0.0810930216 | 5.0000 years |
| Beginning value | 15,000.00 ÷ 1.0844717712 ^ 5 = 15,000.00 ÷ 1.5000 | 10,000.00 |
| Ending value | 10,000.00 × 1.0844717712 ^ 5 = 10,000.00 × 1.5000 | 15,000.00 |
| Rate for one month | 1.0844717712 ^ (1 ÷ 12) − 1 | 0.6781% |
The two logarithms are the only line there you cannot do on a napkin. 0.4054651081 divided by 0.0810930216 is 5, and that is the whole check: the rate and the length agree with each other, so the rearrangement is the same equation and not a second one.
Negative works the same way. 10,000.00 down to 8,000.00 over three years is 0.8 to the power of one third, minus one, which is −7.17%.
Where this one stops
- No deposits and no withdrawals. One sum goes in at the start and nothing moves after that. Money added in year three makes the rate describe the two values and not your return, and that figure is the money-weighted return instead.
- An ending value below zero has no answer. The root is not a real number, so the field stays blank rather than printing something invented. An ending value of exactly zero is −100%.
- A length of zero has no answer either, for any of the four directions, and a rate of zero never carries one value to a different one — so solving for the length with a zero rate comes back blank.
- A length under a year is an annualisation. The same 10% move reads as 10.00% over twelve months and 213.84% over one. Nothing new happened to the money; the arithmetic repeated the move until it filled a year.
- No fees, no tax, no currency. The values are the ones you type.
- No signal. This is arithmetic on your numbers, not a view on what anything does next.
On the nine pages we read for this word on 21 October 2026, two — Investopedia and Forex.com — returned 403 to us and we are claiming nothing about what they hold. Of the seven we could read: the first result, CalculatorSoup, carries the rate formula in words and can solve for the other three inside its own tool; FE Training prints one Excel line for a three-year period; Microsoft's own page points to XIRR and shows its example as a screenshot. None of the seven contains a spreadsheet function name at all apart from Microsoft's XIRR, none of them prints the length rearrangement — the one with the logarithm — and none gives a block that pastes into a sheet. That is what this page adds, and the claim stops there: those seven pages, that day.
The reverse solving on this page is not a difference either, and it is worth saying plainly: the first result already solves for all four. Matching it is the entry fee for the word, not the reason to be here. The rate formula itself is not ours and never was — it is one line of school arithmetic, and it is on every one of those pages.
What this one sits next to is the CAGR calculator, which takes the years one at a time instead of a single length and shows what the average of them hides, and the forex compounding calculator, which runs the same compounding forward from a fixed rate instead of reading it back off two values.
Questions people ask
What is the CAGR formula?The ending value divided by the beginning value, raised to the power of one over the length in years, minus one. On 10,000.00 becoming 15,000.00 over five years that is 1.5 to the power of one fifth minus one, which is 8.45%. The length has to be in years, so months are divided by twelve before they go in.
How do I calculate CAGR in Excel?Put the beginning value in B1, the ending value in B2 and the length in years in B5, then use =(B2/B1)^(1/B5)-1 and format the cell as a percentage. If your length is in months, put the months in B3, put =B3/12 in B5 and leave the formula alone. The tool above builds the whole six-row block for whichever direction you picked, and the copy button hands it to you tab-separated so it lands as two columns.
Can I solve for the number of years instead of the rate?Yes, and it needs a logarithm because the unknown is in the exponent: n = ln(ending / beginning) / ln(1 + rate). On the defaults that is 0.4054651081 divided by 0.0810930216, which is 5.0000 years. If you want the answer in months, multiply the result by twelve.
How do I use months instead of years in the formula?Divide the months by twelve before they go into the exponent, or let the sheet do it: put the months in B3, put =B3/12 in B5 and use =(B2/B1)^(1/B5)-1. On 10,000.00 becoming 15,000.00, sixty months is five years, so the rate is 8.45% — the same answer five years gives, because it is the same length. Solving for the length in months needs the log line with a x12 on the end: =LN(B2/B1)/LN(1+B6)*12.
Why does RRI give me a different number than my power formula?Almost always the period. RRI returns the rate for one period, so =RRI(60,10000,15000) hands back a monthly rate while =RRI(5,10000,15000) hands back the yearly one. Give it 5 for years and 60 for months and it agrees with the power form to the last decimal.
Why does RATE need one of my values to be negative?Because RATE is an annuity function and follows the cash-flow sign convention: money out is negative, money back is positive. Write it as =RATE(5,0,-10000,15000) and it returns 8.45%. XIRR wants the same signing, and it wants real dates alongside the values.
Is XIRR the same as CAGR?XIRR is the function Microsoft's own page for this points to, and on two values with no payments in between it lands on the same rate. It differs where it counts: XIRR works off the actual days between two dates, so it is the one to use when your period is not a tidy number of years or your cash flows are not evenly spaced. This page takes a length you type, not dates.
What if the value fell?The rate comes out negative, which is the honest answer. 10,000.00 down to 8,000.00 over three years is -7.17%. An ending value of exactly zero is -100%, and a value below zero is left blank, because the root is not a real number.
Does it handle money I added along the way?No. The rate assumes one sum went in at the start and nothing moved after that. Add or withdraw mid-period and this rate describes the two values rather than your return; the figure you want then is the money-weighted return.
Related guides
- CAGR calculator — the same rate read off a start and an end, and what the average of your years leaves out.
- Forex compounding calculator — the same compounding run forward from a fixed rate instead of read back off two values.
- DCA calculator — the expected share ratio against buying once, and how many paths leave the periodic buyer ahead.
- Drawdown calculator — what a bad stretch takes out of the balance, and what the recovery asks for.
- Profit factor calculator — what a journal of trades adds up to across the whole run.
- Forex, stock and crypto position size calculator