How to Master Cagr Excel Calculation [Step-by-Step Guide]
A cagr excel calculation turns raw investment data into a single annualized growth rate using spreadsheet formulas. Excel has no built-in CAGR function, but three approaches produce the same result: the power formula, the RATE function, and the RRI function. Each takes about 30 seconds to set up once you know the syntax.
This guide covers all three methods with practical examples so you can analyze any stock, portfolio, or financial metric directly in your spreadsheets.
Key Takeaways
- Excel has no native CAGR function, but three formulas replicate it exactly
- The power formula =(B2/B1)^(1/C1)-1 is the most transparent and widely used
- The RATE function =RATE(C1,0,-B1,B2) works identically but uses financial function syntax
- The RRI function =RRI(C1,B1,B2) is the simplest but only available in Excel 2013+
- Always format the result cell as Percentage with 1-2 decimal places
Step 1: Set Up Your Spreadsheet Layout
Open a new Excel workbook. Create the following structure:
| Cell | Label | Example Value |
|---|---|---|
| A1 | Beginning Value | |
| B1 | $10,000 | |
| A2 | Ending Value | |
| B2 | $18,500 | |
| A3 | Years | |
| B3 | 5 | |
| A4 | CAGR | |
| B4 | (formula goes here) |
This layout keeps your inputs clearly labeled and separated from the output. You will reference cells B1, B2, and B3 in each formula method.
Step 2: Method 1 - The Power Formula
This is the most popular cagr excel calculation method because it directly mirrors the mathematical CAGR formula.
In cell B4, enter:
=(B2/B1)^(1/B3)-1
Breaking this down:
- B2/B1 divides the ending value by the beginning value: $18,500/$10,000 = 1.85
- ^(1/B3) raises this to the power of 1 divided by years: 1.85^0.2
- -1 subtracts 1 to convert from a growth factor to a growth rate
Result: 0.1311 or 13.1% when formatted as a percentage.
This method is transparent. Anyone reading your spreadsheet can immediately see the CAGR formula at work.
Step 3: Method 2 - The RATE Function
Excel's RATE function is designed for financial calculations. It solves for an interest rate given present value, future value, and periods.
In cell B4, enter:
=RATE(B3,0,-B1,B2)
The arguments:
- B3 = number of periods (years)
- 0 = payment per period (none for CAGR, since we are not making regular contributions)
- -B1 = present value (negative because it represents cash outflow)
- B2 = future value (positive because it represents what you receive)
Result: 0.1311 or 13.1%.
The negative sign on B1 is required. RATE follows financial conventions where investments are outflows (negative) and returns are inflows (positive). Forget the negative sign and Excel throws an error.
Step 4: Method 3 - The RRI Function
The RRI (Realized Rate of Interest) function was built specifically for this type of calculation.
In cell B4, enter:
=RRI(B3,B1,B2)
Arguments:
- B3 = number of periods
- B1 = present value (no negative sign needed)
- B2 = future value
Result: 0.1311 or 13.1%.
RRI is the cleanest formula, but it only works in Excel 2013 and later versions. If you share spreadsheets with colleagues on older versions, stick with Method 1 or 2.
Step 5: Calculate CAGR for Real Stocks
Let's apply cagr excel calculation to actual stock data. Set up a comparison table:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Stock | Price 2019 | Price 2024 | Years | CAGR |
| 2 | AAPL | $73.41 | $192.53 | 5 | |
| 3 | MSFT | $157.70 | $378.91 | 5 | |
| 4 | BRK.B | $226.42 | $408.72 | 5 | |
| 5 | JNJ | $144.85 | $156.74 | 5 | |
| 6 | KO | $55.35 | $61.48 | 5 |
In cell E2, enter: =(C2/B2)^(1/D2)-1
Then copy this formula down to E3:E6.
Results:
- AAPL: 21.3% CAGR
- MSFT: 19.2% CAGR
- BRK.B: 12.5% CAGR
- JNJ: 1.6% CAGR
- KO: 2.1% CAGR
These price-only CAGRs exclude dividends. JNJ's 3.1% dividend yield and KO's 3.0% yield would add significantly to total return CAGR. Apple's ROIC of 45.1% and Piotroski score of 7 help explain its superior price appreciation.
Step 6: Build a Date-Based CAGR Calculator
For precise calculations where your holding period is not exact years, use dates.
| A | B | |
|---|---|---|
| 1 | Start Date | 03/15/2019 |
| 2 | End Date | 11/22/2024 |
| 3 | Beginning Value | $50,000 |
| 4 | Ending Value | $91,250 |
| 5 | Years (calculated) | =(B2-B1)/365.25 |
| 6 | CAGR | =(B4/B3)^(1/B5)-1 |
Cell B5 calculates the fractional years: approximately 5.69 years. Cell B6 uses this for a precise CAGR: 11.1%.
Using 365.25 (not 365) accounts for leap years. This matters when you are calculating over long periods.
Step 7: Create a Multi-Period CAGR Dashboard
Build a dashboard that calculates 1-year, 3-year, 5-year, and 10-year CAGR simultaneously.
| Period | Start Value | End Value | Years | CAGR |
|---|---|---|---|---|
| 1-Year | $85,000 | $91,250 | 1 | 7.4% |
| 3-Year | $62,000 | $91,250 | 3 | 13.8% |
| 5-Year | $50,000 | $91,250 | 5 | 12.8% |
| 10-Year | $30,000 | $91,250 | 10 | 11.8% |
This layout instantly shows whether performance is accelerating (recent CAGR higher than long-term) or decelerating (the opposite). An investor seeing 7.4% for the most recent year versus 12.8% over five years knows growth has slowed recently.
Step 8: Add Sensitivity Analysis
Extend your spreadsheet with a sensitivity table that models different ending values.
Use Excel's Data Table feature (What-If Analysis > Data Table) or build it manually:
| Ending Value | CAGR (5-Year) |
|---|---|
| $70,000 | 6.96% |
| $80,000 | 9.86% |
| $90,000 | 12.47% |
| $100,000 | 14.87% |
| $110,000 | 17.08% |
| $120,000 | 19.14% |
Starting from $50,000 over 5 years, the table shows that every $10,000 increase in ending value adds roughly 2-2.5 percentage points to CAGR. This kind of analysis helps set realistic portfolio targets.
You can also build similar models using the ValueMarkers DCF calculator, which handles compound growth projections alongside intrinsic value estimates.
Step 9: Handle Common Excel Errors
#NUM! Error: Usually caused by forgetting the negative sign in the RATE function or by entering impossible values (like a negative ending value).
#VALUE! Error: Occurs when cells contain text instead of numbers. Make sure your value cells are formatted as numbers, not stored as text.
Circular Reference Warning: Happens if your formula accidentally references the cell it is in. Double-check your cell references.
Incorrect Percentage Display: If you see 0.13 instead of 13.1%, format the cell as Percentage (Ctrl+1 > Number > Percentage > 1 decimal place).
Zero Result: If CAGR shows 0.0%, the beginning and ending values might be identical, or the years value might be extremely large. Verify your inputs.
Advanced Tip: Combining CAGR With Valuation Metrics
Once you can calculate CAGR in Excel, combine it with other metrics from ValueMarkers.
Create a stock analysis row that includes:
- 5-year earnings CAGR
- Current P/E ratio
- ROIC
- Piotroski F-Score
- Altman Z-Score
For Microsoft: 5-year EPS CAGR of approximately 16%, P/E of 32.1, ROIC of 35.2%, Piotroski of 8, Altman Z of 9.1. These numbers together tell a complete story. The high CAGR justifies a premium P/E, the ROIC confirms capital efficiency, the Piotroski score signals strong fundamentals, and the Altman Z-Score rules out financial distress.
The VMCI Score on ValueMarkers synthesizes exactly this kind of multi-factor analysis into a single composite rating across Value (35%), Quality (30%), Integrity (15%), Growth (12%), and Risk (8%).
Further reading: Investopedia · CFA Institute
Why CAGR formula Excel Matters
This section anchors the discussion on CAGR formula Excel. The detailed treatment, formula, and worked examples appear in the body of this article above. The points below summarize the most important takeaways for value investors who want to apply CAGR formula Excel in real portfolio decisions. ValueMarkers exposes the underlying data on every covered ticker via the screener and stock profile pages, so the concepts in this article translate directly into actionable filters.
Key inputs for CAGR formula Excel
See the main discussion of CAGR formula Excel in the sections above for the full treatment, including the inputs, the calculation methodology, the typical sector benchmarks, and the most common pitfalls to avoid. The ValueMarkers screener lets value investors filter the full universe of 100,000+ stocks across 73 exchanges using CAGR formula Excel alongside the rest of the 120-indicator composite, with sector percentiles and historical trends shown on every stock profile.
Sector benchmarks for CAGR formula Excel
See the main discussion of CAGR formula Excel in the sections above for the full treatment, including the inputs, the calculation methodology, the typical sector benchmarks, and the most common pitfalls to avoid. The ValueMarkers screener lets value investors filter the full universe of 100,000+ stocks across 73 exchanges using CAGR formula Excel alongside the rest of the 120-indicator composite, with sector percentiles and historical trends shown on every stock profile.
Related ValueMarkers Resources
- DCF Intrinsic Value — DCF captures how cheaply a stock trades relative to its fundamentals
- Enterprise Value to EBITDA (EV/EBITDA) — Enterprise Value to EBITDA is the metric used to how cheaply a stock trades relative to its fundamentals
- Graham Number — Graham Number captures how cheaply a stock trades relative to its fundamentals
- Reverse Cagr Calculator — related ValueMarkers analysis
- Cagr Calculator — related ValueMarkers analysis
- Net Margin And Net Profit Margin — related ValueMarkers analysis
Frequently Asked Questions
what does cagr stand for
CAGR stands for Compound Annual Growth Rate. It expresses the annualized rate of return for an investment, assuming profits are reinvested at the end of each year. In Excel, you calculate it with =(EndValue/StartValue)^(1/Years)-1, which produces the same result as the mathematical definition.
what is cagr in finance
In finance, CAGR is the standard metric for comparing investment performance across different time horizons and asset classes. It smooths out year-to-year volatility into a single annual figure. Professional analysts use CAGR to evaluate revenue growth, earnings growth, portfolio returns, and market index performance over specific periods.
what does cagr mean
CAGR means the constant annual rate at which an investment would need to grow to move from its starting value to its ending value over a given period. It is the geometric mean of annual returns. For a $10,000 investment becoming $16,105 over 5 years, the CAGR is 10.0%, even though individual year returns might range from -15% to +35%.
what is cagr growth rate
The CAGR growth rate is the compound annual rate calculated by the formula (EV/BV)^(1/n)-1. For the S&P 500, the nominal CAGR growth rate over the past 30 years is approximately 10.5% per year. In Excel, you replicate this with any of the three methods described above: the power formula, RATE, or RRI.
how to find the z score using excel
The Z-Score in Excel is calculated with the formula =(Value-AVERAGE(Range))/STDEV(Range). For the Altman Z-Score specifically, which predicts bankruptcy risk, the formula is Z = 1.2A + 1.4B + 3.3C + 0.6D + 1.0E, where each variable represents a financial ratio like working capital to total assets. Apple's Altman Z-Score of 8.2 indicates extremely low bankruptcy risk, as scores above 3.0 are considered safe.
how to build a stock portfolio in excel
Build a stock portfolio tracker by creating columns for ticker, shares owned, purchase price, current price, total cost, current value, gain/loss, and CAGR since purchase. Use CAGR formulas to track each position's performance. Link to a summary row that calculates portfolio-level CAGR using total beginning and ending values. For professional-grade analysis across 120+ indicators, ValueMarkers' screener can feed data into your Excel models.
Written by Javier Sanz, Founder of ValueMarkers
Last updated April 2026
Go beyond spreadsheets with automated valuation models. Try the ValueMarkers DCF Calculator to run discounted cash flow analysis on any stock and compare it to your Excel CAGR projections.
Ready to find your next value investment?
ValueMarkers tracks 120+ fundamental indicators across 100,000+ stocks on 73 global exchanges. Run the methodology above in seconds with our stock screener, or see today's top-ranked names on the leaderboard.
Related tools: DCF Calculator · Methodology · Compare ValueMarkers
Disclaimer: This content is for informational and educational purposes only and does not constitute investment advice, a recommendation, or an offer to buy or sell any security. Past performance does not guarantee future results. Consult a licensed financial advisor before making investment decisions.