Spreadsheets

CAGR formula in Excel: calculate annual growth between two values

Calculate CAGR in Excel with the power formula or RRI, and use it on grocery bills, fees or salary to see the steady yearly growth between two values.

Editorial illustration for CAGR formula in Excel: calculate annual growth between two values

CAGR is the steady yearly growth rate that turns a starting value into an ending value. In Excel, =(End/Start)^(1/Years)-1 or =RRI(Years, Start, End) gives it. A grocery bill that rose from ₹6,000 to ₹9,000 a month over 5 years has a CAGR of about 8.45%.

The two ways to write it

  • Power formula: =(B2/B1)^(1/B3)-1, with start value in B1, end value in B2 and years in B3. It works in every spreadsheet.
  • RRI function: =RRI(B3, B1, B2). The arguments are number of periods, present value, future value, in that order. It is available in Excel 2013 and later and in Google Sheets.
  • Format the result cell as a percentage, or you will see 0.0845 instead of 8.45%.

Both give identical answers. RRI is shorter; the power formula teaches you what is happening and works in any older sheet you inherit.

Where do the years come from?

Years means the gap between the two values, not the count of values. If you have yearly figures for 2021 to 2026 there are six numbers but 5 years between the first and last. For two dates in cells, use =YEARFRAC(A1, A2) or =(A2-A1)/365. For monthly data, divide the number of months between by 12.

What does it look like on household numbers?

Three hypothetical examples, with the formula =(End/Start)^(1/Years)-1 applied to each row:

ItemStartEndYearsCAGR
Monthly grocery bill₹6,000₹9,00058.45%
Yearly school fee₹80,000₹1,12,00048.78%
Annual take-home pay₹4,80,000₹6,60,00065.45%

The comparison is the useful part. If the grocery bill grows faster than your pay, you are feeling it in the real budget, and the sheet shows by how much without anyone arguing from memory.

Why does the formula show an error?

  • #NUM! usually means a zero or negative start or end value. CAGR needs both to be positive.
  • #DIV/0! means the start cell is empty or zero.
  • A wrong answer with no error often means years was entered as a count of values, or the percentage format is missing.
  • Text that looks like a number, such as a figure pasted with a rupee symbol, breaks the arithmetic; clean it with Paste Special or retype it.

Why not just average the yearly increases?

Because the average of percentages can mislead. Say a monthly bill goes from ₹6,000 to ₹9,000 in year one (up 50%) and back to ₹6,000 in year two (down 33.3%). The plain average of the two changes is about 8.3%, yet the bill ended exactly where it began. CAGR over the two years is =(6000/6000)^(1/2)-1, which is 0%, and that matches reality. Use CAGR whenever you describe growth between a start and an end.

Can you project forward with the same rate?

Yes, in reverse. If you assume the grocery bill keeps growing at the same rate, =B2*(1+CAGR)^N estimates N years ahead. Label it as an assumption in the sheet, and try a lower and a higher rate beside it. A projection is only as sound as the rate you feed it.

Before you share the number

  1. Check that both values cover the same thing, such as the same household size or the same items in the grocery list.
  2. Confirm the years between the two points.
  3. Use whole-year anniversaries for comparisons where possible, such as April to March to April to March.
  4. Note the source of each value beside it so you can check later.

The raw data is the harder part. If your spending is spread over bank and card statements, SpendixAI can import them and give you category totals by month, which you can paste into this sheet as the start and end values.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports