2.0 - How to Calculate CAGR in Excel for Investment Performance
- Compounding Investor
- Apr 7
- 9 min read
Updated: 2 days ago
Most investors track returns badly. They look at share price movement, account value, or a rough “I’m up” figure — but that doesn’t tell them whether their portfolio is actually compounding properly.
The problem is simple: total return shows what happened. CAGR shows the rate at which it happened. That makes CAGR one of the most important numbers in any serious portfolio tracking system.
Who This Guide Is For
This guide is for investors who:
• want to measure real portfolio performance, not just price movement
• use Excel or spreadsheets to track investments
• want to compare stocks, ETFs, funds, and portfolios consistently
• care about long-term compounding rather than short-term gains
• want to understand whether their portfolio is actually performing well
What You'll Learn | |
What CAGR actually measures | So you can see your true annualised return |
How to calculate CAGR in Excel | So you can see your true annualised return |
CAGR vs Total Return | So you don’t mistake growth for compounding |
Portfolio-level CAGR | So you can judge your whole strategy, not just individual holdings |
Common CAGR mistakes | So your spreadsheet doesn’t give misleading results |
Contents
What CAGR actually means
Why CAGR matters more than total return
CAGR vs total return (the key difference)
The CAGR Excel formula explained
How to calculate CAGR in Excel step-by-step
Portfolio CAGR vs individual holding CAGR
Why most investors track performance incorrectly
Common CAGR mistakes to avoid
Without vs with a proper tracking system
FAQ
This is how I track returns across my portfolio — including CAGR — so I can measure performance properly over time.
Want to know whether your own portfolio is compounding at the rate you think it is? I’ll review your holdings and show you your real performance, allocation risks, and hidden weak spots. See How To Build a Portfolio that Compounds for more guidance.
Quick Portfolio Audit
If you cannot answer these questions quickly, your tracking system probably has hidden blind spots:
What is your portfolio CAGR?
What percentage of your portfolio is in your top 3 holdings?
Are you outperforming the index after adjusting for risk?
How much overlap exists across your ETFs?
Most investors cannot answer these accurately.
Free portfolio health check • manually reviewed • delivered within 24 hours
Why CAGR Reveals Your Investor Type
Most investors think CAGR is simply a calculation. In reality, CAGR often reveals how investors make decisions.
A Reactive Investor rarely knows their CAGR.
A Lucky Investor may achieve a strong CAGR during a bull market but confuse luck with skill.
A Conservative Compounder tracks CAGR consistently but may not benchmark it properly against risk and allocation decisions.
A Structured Compounder uses CAGR as one part of a broader system including allocation, benchmarking, portfolio reviews and blind spot analysis.
The difference is not intelligence. The difference is structure.
What is CAGR
CAGR stands for Compound Annual Growth Rate. In plain English, it is the annual return you would have needed each year to turn your starting value into your ending value.
That matters because investment returns are rarely smooth. One year may be strong, another weak, and another flat. CAGR turns that messy path into one clean number you can compare across holdings, funds, ETFs, and your whole portfolio.
CAGR Formula
CAGR = (Ending Value / Starting Value) ^ (1 / Years) - 1
Example:
If £10,000 grows to £16,000 over 5 years:
CAGR = (£16,000 / £10,000) ^ (1 / 5) - 1
This gives a comparable annualised return.
Why CAGR Matters for Investors
Total return tells you how much you made. CAGR tells you how efficiently your money compounded over time and whether you have hidden portfolio problems.
That distinction matters because two investments can have the same total return but very different performance.
Example:
• Investment A returns 50% over 5 years
• Investment B returns 50% over 2 years
The total return is the same. The compounding rate is completely different. CAGR makes that obvious.
CAGR becomes even more powerful when combined with portfolio allocation tracking, benchmarking, and systematic portfolio reviews. Most investors calculate returns in isolation when the real insight comes from understanding how allocation decisions, diversification, and contribution behaviour influence long-term compounding.
In my system, CAGR is used as the performance engine. It helps compare every holding against the portfolio’s target return and shows whether the overall strategy is actually working.
CAGR Alone Does Not Create Better Investors
Many investors know their CAGR. Very few understand what is driving it.
A Lucky Investor may produce a 15% CAGR because technology stocks are rising. A Conservative Compounder may produce an 8% CAGR with significantly lower risk.
A Structured Compounder understands:
allocation decisions
diversification
contribution behaviour
all influence CAGR over time.
The goal is not simply achieving a high CAGR. The goal is achieving a repeatable CAGR.
How to Calculate CAGR in Excel (Step-by-Step)
In a basic spreadsheet, CAGR can be calculated with one formula. In a proper portfolio system, the same calculation should be applied consistently across every holding and at total portfolio level.
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
The standard CAGR formula in Excel is:
=(Ending Value / Starting Value) ^ (1 / Years) - 1
For example, if:
• Starting value = £10,000
• Ending value = £16,000
• Years = 5
The formula would be:
=(16000 / 10000) ^ (1 / 5) - 1
Format the result as a percentage.
Portfolio CAGR vs Individual Holding CAGR
You can calculate CAGR at two levels:
1. Individual holding CAGR
This shows how one stock, ETF, or fund has compounded over time.
2. Portfolio CAGR
This shows how the whole portfolio has compounded after combining all holdings.
Both matter. A portfolio can contain several strong holdings but still produce weak overall results if allocation is poor, contributions are inconsistent, or too much capital sits in underperforming assets.
This is why many investors unknowingly become concentrated in a handful of positions after strong performance periods. Without a structured allocation process and regular portfolio reviews, portfolio CAGR can quietly deteriorate even when individual holdings appear successful.
Related guide How To Track Portfolio Performance
Why Total Return Can Mislead You
Total return can make performance look better than it really is because it ignores time. A 60% return over 2 years is very different from a 60% return over 10 years.
Total return answers:
“How much did I make?”
CAGR answers:
“How fast did my money compound?”
For long-term investors, the second question is usually more important.
This is exactly how I calculate returns across each position and at portfolio level.
Take the free 2-minute Investor Assessment
Common CAGR Mistakes
Confusing CAGR with total return: CAGR is annualised; total return is the overall gain.
Ignoring contributions: new money added to the portfolio can make performance look better than it really is - this is called contribution distortion.
Using the wrong time period: a small error in the number of years can distort the result.
Comparing inconsistent periods: don’t compare a 2-year CAGR with a 10-year CAGR as if they are equivalent.
Only calculating CAGR for winners: the whole portfolio needs to be measured, not just selected holdings.
Not using it consistently: if CAGR is calculated differently across holdings, the comparison becomes meaningless.
Another major mistake is failing to benchmark performance properly. Many investors compare themselves emotionally to headlines, social media, or isolated winning stocks instead of measuring performance against their own long-term strategy and risk profile - emotional investing becomes the enemy that undermines long-term performance.
CAGR Through The Investor Progression Model
Investor Type | Typical Behaviour | Typical CAGR |
Reactive Investor | Tracks prices not performance | 4–6% |
Lucky Investor | Strong returns but no system | 10–15% temporarily |
Conservative Compounder | Structured but incomplete measurement | 7–10% |
Structured Compounder | Full system with review process | 12–15%+ sustainable |
CAGR is not juist a performance metric. It is often a reflection of investor behaviour.
Why Most Investors Don’t Track CAGR Properly
Most investors don’t ignore CAGR because it is unimportant. They ignore it because it feels manual.
They track holdings in one place, dividends somewhere else, contributions inconsistently, and performance loosely through broker apps.
That creates a problem:
The portfolio may look like it is growing, but the investor still doesn’t know whether it is compounding properly.
That is why CAGR needs to sit inside a repeatable portfolio tracking system, not as a one-off calculation.
In reality, most investors operate disconnected spreadsheets and fragmented apps rather than a complete investment operating system. They may track prices, but not allocation drift, ETF overlap, benchmarking, or portfolio-level risk exposure.
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.
Who This Is NOT For
This guide is probably not for you if:
you only care whether your portfolio is up today
you are focused on short-term trading
you don’t want to measure performance consistently
you are not interested in long-term compounding
Hidden Portfolio Blind Spots
Most portfolios contain at least 2–3 of these issues.
Not Sure Where You Stand
Option 1: Take the Investor Assessment
Discover whether you’re a:
Reactive Investor
Lucky Investor
Conservative Compounder
Structured Compounder
Takes Less Than 2-Minutes
Option 2: Get a Free Portfolio Health Check
Receive a personalised review of:
allocation
diversification
concentration
benchmarking
compounding effectiveness
Free portfolio health check • manually reviewed • delivered within 24 hours
These blind spots compound over time because they are rarely reviewed systematically. Small allocation drift, hidden concentration, and contribution distortions eventually become major drivers of portfolio underperformance.
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.
What is a good CAGR for a portfolio?
A good CAGR depends on your risk level, time horizon, and asset mix. A defensive portfolio will usually have a lower expected CAGR than a growth-focused portfolio. The important thing is to compare your CAGR against a realistic target and benchmark, not an arbitrary number.
Can CAGR be negative?
Yes. If your ending value is lower than your starting value over the measured period, CAGR will be negative. This shows that the investment has compounded downward over that period.
Should I use CAGR for ETFs?
Yes. CAGR is useful for ETFs because it lets you compare fund performance over time on the same annualised basis as individual stocks or your full portfolio.
Does CAGR include dividends?
It should, if you are measuring total return. For dividend-paying investments, reinvested or received dividends should be included if you want an accurate view of total performance.
Why is CAGR useful in a portfolio tracker?
CAGR helps turn uneven year-by-year performance into a consistent annualised return. This makes it easier to compare holdings, track progress against targets, and judge whether your portfolio is compounding properly whilst you avoid mistakes..
Related Guides
Comments