Calculate CAGR from underlying metrics

SheetsFinance does not provide a built-in CAGR function, but you can calculate compound annual growth rate from the underlying historical data the add-on supplies. Pull the starting value, ending value, and number of years, then apply the standard spreadsheet formula.

The CAGR formula

Use the standard spreadsheet CAGR formula with your start and end values:

=(End / Start) ^ (1 / Years) - 1

SheetsFinance gives you the Start and End values from historical financial statements or price series. You supply the number of periods and compute the result in the sheet.

Calculate fundamentals CAGR from financial statements

For a revenue, net income, or EPS CAGR, pull the annual financial statements across the years you want, then reference the first and last values in the formula.

Step 1: pull multi-year statement data

Request the metric and fiscal year for the range you want. Use "NH" to remove the header row so the first row is data.

=SF("AAPL", "income", "fiscalYear&revenue", "2020-2024", "NH")

This returns a table where each row is a metric and each column is a year. The first row contains the fiscal years and the second row contains the revenue values. For example, the 2020 revenue is in column 2 of the revenue row, and the 2024 revenue is in the last column of the revenue row.

Calendar years: If you need calendar-year alignment instead of fiscal-year alignment, add the "calYear" option. See Company Growth for details.

Step 2: extract the start and end values

Assuming the formula above is in cell A1, the start year is in the first row, second column, and the end year is in the first row, last column. The start revenue is in the second row, second column, and the end revenue is in the second row, last column.

=INDEX($A$1#, 1, 2)

=INDEX($A$1#, 1, COLUMNS($A$1#))

=INDEX($A$1#, 2, 2)

=INDEX($A$1#, 2, COLUMNS($A$1#))

Spilled ranges: In Excel, $A$1# references the entire spilled array. In Google Sheets, the output fills the adjacent cells; reference the specific cells directly rather than using the spilled-range syntax.

Step 3: compute the CAGR

With the start and end values in cells, apply the CAGR formula. For the 2020-to-2024 example, the period is 4 years.

=(D2 / C2) ^ (1 / (B2 - A2)) - 1

You can also write it in one cell with LET so the SF call happens only once:

=LET(
  data, SF("AAPL", "income", "fiscalYear&revenue", "2020-2024", "NH"),
  startYear, INDEX(data, 1, 2),
  endYear, INDEX(data, 1, COLUMNS(data)),
  startRev, INDEX(data, 2, 2),
  endRev, INDEX(data, 2, COLUMNS(data)),
  (endRev / startRev) ^ (1 / (endYear - startYear)) - 1
)

Calculate price CAGR from historical closes

For a stock-price CAGR, use a time series or historical end-of-day prices to get the start and end closing prices.

Option A: use a 1-year inter-day time series

SF_TIMESERIES with the "1year" period returns one row per year. There are no date-range limits for inter-day data.

=SF_TIMESERIES("AAPL", "2020-12-31", "2024-12-31", "1year", "close")

This returns five rows, one per year. Use the first and last rows as the start and end prices, then compute the CAGR across the 4-year span.

Sheet space: SF_TIMESERIES returns multiple rows. Clear the target area before entering the formula to avoid a #REF! error.

Batching note: SF_TIMESERIES cannot be batched across symbols in a single call. If you need price CAGR for many stocks, use a separate formula per symbol or use Historical EOD batching.

Option B: use Historical EOD for exact dates

For precise control over the anchor dates, use SF with the "historical" type. Pass the exact start and end dates as separate formulas.

=SF("AAPL", "historical", "close", "2020-12-31")

=SF("AAPL", "historical", "close", "2024-12-31")

If the date falls on a weekend, SheetsFinance returns the most recent trading day. Then compute the CAGR in another cell:

=(C2 / B2) ^ (1 / 4) - 1

Option C: use Historical EOD batch for a list of symbols

If you need start and end prices for many symbols at once, pass a range of tickers to SF with "historical". The entire range counts as a single function call.

=SF(A2:A100, "historical", "close", "2020-12-31")

=SF(A2:A100, "historical", "close", "2024-12-31")

Place the start prices in one column, the end prices in the next, and the CAGR formula in the third. The batch length depends on your plan level.

Tip: Add the "NH" option to remove the header row when you are feeding historical data into another formula.

Which inputs to use

Revenue or EPS CAGR — use the income type with revenue or eps. You can also use balancesheet or cashflow types for metrics like totalStockholdersEquity or freeCashFlow.
Net income CAGR — use the income type with netIncome.
Stock price CAGR — use SF_TIMESERIES with "close" or "adjClose". Use "adjClose" if you want dividend-adjusted returns.

Pre-built growth metrics are not CAGR

SheetsFinance provides single-period growth metrics such as revenueGrowth and epsgrowth through the growth data type, and multi-year metrics such as fiveYRevenueGrowthPerShare. These are calculated as absolute percentage changes between two points, not as compounded annual growth rates. Use the formulas above when you need a true CAGR.

What is next

For more on pulling multi-year financial data, see Company Growth. For the exact formula definitions used by SheetsFinance, see Financial Formulas. For year-end price returns, see Calculate yearly or end-of-year returns.

Was this helpful?