Quick answer
The CAGR formula calculates the compound annual growth rate between a beginning and ending value.
=(B3/B2)^(1/B4)-1Syntax
Use this syntax as the pattern, then replace the sample arguments with your own cells, ranges, and criteria.
=(Ending Value / Beginning Value)^(1 / Years) - 1When to use it
Financial formulas turn business and investment assumptions into numbers you can compare. They are useful for loans, budgets, pricing, margin analysis, investment decisions, and business cases.
- Calculate monthly loan payments
- Estimate return on investment
- Calculate gross margin and markup
- Evaluate investment cash flows
Example worksheet layout
Test the formula in a small worksheet first. This makes it easier to confirm the result before copying the formula down hundreds of rows.
| Column or range | What it represents |
|---|---|
| A | Assumption label |
| B | Input value such as rate, periods, or amount |
| C | Financial result |
Copy-paste examples
These examples move from simple to more practical versions. Start with the beginner examples, confirm the result, then adapt the intermediate and advanced versions.
Basic CAGR example
=(B3/B2)^(1/B4)-1Use the simple version to check the business logic first.
Use locked assumption cells
=($B$3/$B$2)^(1/$B$4)-1Lock assumptions when every row should use the same rate, cost, target, or starting value.
Avoid missing-input errors
=IFERROR((B3/B2)^(1/B4)-1,0)Useful when some rows are incomplete or when a denominator can be zero.
Create a display label
=IFERROR("Rate/result: "&TEXT((B3/B2)^(1/B4)-1,"0.00%"),"Check inputs")Use TEXT only for a display cell; keep the raw numeric result for calculations.
Use LET for auditability
=LET(result,(B3/B2)^(1/B4)-1,result)LET makes financial model formulas easier to review.
Dashboard status label
=IFERROR(IF((B3/B2)^(1/B4)-1>=0,"Positive","Negative"),"Check inputs")Use a label when the calculation feeds a dashboard status or exception report.
How to use it step by step
- Put your raw data in clear columns with headers such as Amount, Date, Status, Region, or Customer.
- Paste the starting formula into the first result cell, usually row 2.
- Replace sample references such as A2, B2, or $F$2:$F$100 with the cells in your own worksheet.
- Check the result on a few rows where you already know the expected answer.
- Copy the formula down only after the first row returns the right result.
Common mistakes and fixes
- Percentages are entered as whole numbers instead of percentages, such as 12 instead of 12%.
- Cash inflows and outflows use inconsistent signs.
- The result cell is not formatted as currency, number, or percentage as appropriate.
- Test the formula on a few known rows before using it across a full report.
Beginner tips
- Use a small sample first so you can see exactly how the CAGR formula behaves.
- Convert your source range into an Excel Table when the data will grow over time.
- Add a short note near important formulas so future users know what each input represents.