How to Calculate CAGR in Excel (Step-by-Step Guide)
- Compounding Investor
- Apr 7
- 4 min read
Updated: 4 days ago
Most investors track returns poorly. They watch the share price, glance at a percentage gain, and assume they know how well an investment is doing. But real performance is about what your money actually did over time — and if you don’t measure it properly, you end up guessing.
That’s why CAGR (Compound Annual Growth Rate) is one of the most useful performance metrics in investing. It turns a messy multi-year return into a single, comparable annual number — so you can judge positions, funds, and your whole portfolio on the same basis.
This is how I track returns across my portfolio — including CAGR — so I can measure performance properly over time.
If you want to use a ready-built system that calculates this automatically, I’ve created one you can use immediately.
What is CAGR
CAGR stands for Compound Annual Growth Rate. In plain English, it’s the “true annual return” of an investment over a period of time. Instead of saying “I’m up 40%”, CAGR answers: “What yearly return would I have needed, every year, to turn my starting value into my ending value?” It smooths out the ups and downs and gives you one clean number you can compare across different investments and time periods.
Why CAGR Matters for Investors
Total return tells you how much you made overall. CAGR tells you how efficiently you made it per year.
Example:
Investment A returns 50% over 5 years.
Investment B returns 50% over 2 years.
Same total return, completely different performance. CAGR makes that obvious — and that’s why it’s so useful for portfolio tracking.
How to Calculate CAGR in Excel (Step-by-Step)
You can calculate CAGR in Excel (or any spreadsheet) with four simple inputs.
Step 1: Starting value
This is what you started with (for example, the value of your investment on the day you bought it).
Step 2: Ending value
This is what it’s worth today (or at the end of the period you’re measuring).
Step 3: Number of years
Use the number of years between the start date and end date. If you want to be more precise, you can calculate years as days/365, but keep it consistent across your tracking.
Step 4: Use the CAGR Excel formula
Here’s the standard CAGR Excel formula:
= (Ending Value / Starting Value) ^ (1 / Years) - 1
This is the same compound annual growth rate Excel calculation used in most investment returns Excel templates — it’s simple, reliable, and easy to audit.
This is exactly how I calculate returns across each position and at portfolio level.
Common Mistakes
Confusing CAGR with total return: CAGR is annualised; total return is the overall change.
Using the wrong time period: a small error in “years” can distort the result.
Not using it consistently: if you calculate CAGR for some holdings but not others, comparisons become meaningless.
Without vs With a System
Without a system
Guessing performance based on price moves
Inconsistent tracking across accounts and holdings
No clear way to compare positions over time
With a system
Clear annualised returns (CAGR) for every holding
Consistent measurement across your entire portfolio
A reliable way to track progress and make better decisions
Why Most Investors Don’t Track This Properly
Most people don’t calculate CAGR because it feels too manual. They track in different places, use inconsistent dates, and don’t have a repeatable process. Without a structured spreadsheet, it’s easy to stop updating — and then you’re back to guessing.
Who This Is For
You want to track performance properly (not just “up or down”)
You already use spreadsheets and want a cleaner method
You want clarity across positions and at portfolio level
You don’t need advanced Excel skills — this can be done with a simple formula or a structured spreadsheet.
If you want a structured way to track your investment performance — including automatic CAGR calculation — I’ve built a complete system that does this for you.
FAQ
What is CAGR in investing?
CAGR in investing is the compound annual growth rate — the annualised return that shows what yearly growth rate would turn your starting value into your ending value over a given period.
How do I calculate CAGR in Excel?
Use the CAGR Excel formula: = (Ending Value / Starting Value) ^ (1 / Years) - 1. Enter your starting value, ending value, and the number of years, and Excel will return the compound annual growth rate.
Is CAGR better than total return?
CAGR isn’t “better” in every situation, but it’s usually more useful for comparing investments because it accounts for time. Total return tells you the overall gain; CAGR tells you the annualised performance.
Related Guides
Comments