2.0 - How to Calculate CAGR in Excel for Investment Performance
- Compounding Investor
- Apr 7
- 11 min read
Updated: Jun 28
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.
Most investors believe CAGR is simply a calculation. Structured Compounders see it differently.
CAGR is one of the clearest indicators of whether an investment process is actually working. The strongest investors don’t just know their CAGR. They understand what it says about them.
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 |
Investor Progression | Understanding what CAGR reveals about your Investor Type and compounding process |
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 CAGR Audit
If you cannot answer these questions quickly, your tracking system probably has hidden blind spots:
✓ Do you know your portfolio CAGR?
✓ Do you know your benchmark CAGR?
✓ Do you know whether you are outperforming?
✓ Do you know what type of investor you are?
✓ Could you explain why your CAGR occurred?
✓ Is your CAGR repeatable?
Most investors know their return.Far fewer understand what created it. That is exactly what the Investor Assessment was designed to reveal.
Free 2-minute assessment • manually reviewed • delivered within 24 hours
Why CAGR Reveals Your Investor Type
Most investors expect the assessment to reveal their Investor Type.
What surprises them is discovering how closely Investor Type correlates with long-term CAGR outcomes. The assessment doesn’t simply classify investors.
It identifies the behaviours driving future compounding.
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 The Assessment Reveals
Most investors think CAGR tells the whole story. It doesn’t.
The Investor Assessment reveals:
✓ Investor Score
✓ Benchmarking Discipline
✓ Process Maturity
✓ Biggest Weakness
Because understanding CAGR is useful. Understanding why your CAGR exists is transformational.
Free assessment • manually reviewed • delivered within 24 hours
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.
This is why Structured Compounders never analyse CAGR in isolation.
They analyse CAGR alongside allocation, benchmarking, diversification and investor behaviour.
The assessment provides the first visibility into those factors.
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.
Understanding how to track performance is essential — if you haven’t already, see my step-by-step guide on how to track your investment portfolio in Excel.
Many investors complete the assessment expecting to discover their CAGR. Instead they discover the behaviours that are producing it.
In many cases the biggest limitation is not stock selection. It is process quality.
Most Investors Focus On The Wrong Number
Many investors obsess over whether CAGR is:
8%
10%
12%
The better question is: Can it be repeated?
A repeatable 10% CAGR is usually worth more than a temporary 15% CAGR driven by luck. That distinction sits at the heart of the Investor Progression Model.
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
Real Investor Mini Case Study (Singapore 🇸🇬): The 14% Return That Wasn’t
A Singapore investor had been investing consistently for just over six years. Their portfolio had grown from US$120,000 to US$201,000.
The investor proudly described the portfolio as having achieved a 68% return.
Friends agreed it was excellent performance. The investor believed they were comfortably outperforming the market.
A structured performance review told a different story.
What The Review Revealed
The portfolio had indeed increased by 68%. But that figure represented total return, not annual investment performance. When the portfolio was analysed properly:
Starting Value: US$120,000
Ending Value: US$201,000
Investment Period: 6 years
Total Return: 68%
True Portfolio CAGR: 8.9% per year
The investor had been describing their performance as a 68% return. Their portfolio had actually compounded at 8.9% annually.
Those are very different measures.
Why It Matters
A 68% return sounds impressive. But without knowing how long it took to achieve that return, it tells you almost nothing. The review demonstrated:
A 68% return over 6 years = 8.9% CAGR
The benchmark delivered 10.2% CAGR over the same period.
Although the portfolio had made money, it had underperformed the benchmark by 1.3% per year.
That was something the investor had never measured.
The Real Issue
The issue wasn’t:
stock selection
long-term investing
The issue was:
performance measurement.
The investor knew how much money had been made.
They didn’t know how efficiently that money had compounded.
Until CAGR was calculated, they couldn’t answer one of the most important investing questions:
“Am I genuinely outperforming, or just looking at a larger portfolio value?”
What Changed
The investor introduced:
Portfolio CAGR tracking
Benchmark CAGR comparisons
Annual performance reporting
Scheduled portfolio reviews
Nothing changed about the portfolio.
Nothing changed about the investments.
Everything changed about how performance was measured.
For the first time, the investor stopped asking:
“How much have I made?”
and started asking:
“How well has my money compounded?”
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.
What Type Of Investor Are You?
Most investors know their CAGR. Few know what it says about them.
The Investor Assessment reveals:
✓ Investor Type
✓ Investor Score
✓ Compounding Strengths
✓ Compounding Weaknesses
✓ Dashboard Preview
✓ Recommended Next Step
Assessment
→ Intelligence Report
→ System
→ Membership
Takes less than two minutes.
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..
Explore The Full Framework
The Investor Progression Model White Paper |
This article forms part of the Investor Progression Model — a framework for identifying how investors progress from reactive decision-making to structured long-term compounding. Inside the white paper: ✓ The four investor types ✓ The progression pathway ✓ The five dimensions of investor maturity ✓ How Structured Compounders build repeatable systems ✓ The research behind the Investor Assessment |
⬇ READ THE WHITE PAPER ⬇ |
Related Guides
Comments