How to calculate cagr with multiple investments?
I have multiple investments made at different times with different amounts. For example, I invested as below:
- Rs. 10,000 in January 2020,
- Rs. 15,000 in July 2020, and
- Rs. 20,000 in March 2021.
Their current value is Rs. 60,000. How do I calculate the single CAGR for my total portfolio instead of calculating CAGR for each investment separately?
When you invest money at different times, calculating a single CAGR gets tricky.
The traditional CAGR formula [Final Value/Initial Value)^(1/years) – 1] assumes you invested everything at once, which isn’t true in this example.
For multiple investments made over time, you need to use the XIRR formula instead.
It accounts for the timing and amount of each investment.
Step-by-Step Excel or Google Sheets Calculation
Set up Your Data (4 Simple Columns).
Create four columns:
- (Column A) Transaction Date,
- (Column B) Transaction Type (negative for investment, positive for current value),
- (Column C) Amount Invested or Current Value,
- (Column D) Formula reference.
For your example:
- Row 1: Is the header as explained above.
- Row 2: 01-Jan-2020, Investment, -10000
- Row 3: 01-Jul-2020, Investment, -15000
- Row 4: 01-Mar-2021, Investment, -20000
- Row 5: 25-Nov-2025 (today), Current Value, +60000
The XIRR Formula
In an empty cell, type: =XIRR(C2:C5, A2:A5)
- C2:C5 are your amounts and
- A2:A5 are your dates. Press Enter.
- Your result appears as a decimal. If you get 0.22, that’s 22% CAGR.
