Use CAGR for a clean start-to-end investment without intervening cashflows. Use a dated-cashflow method such as XIRR when contributions or withdrawals occur at different times.
ROI answers how much, not how fast
Turn RM10,000 into RM12,100 with no intervening cashflows: the gain is RM2,100 and ROI is 21%. The same 21% could happen over one year or ten. Always state the measurement period and include costs consistently. When money was added during the period, a simple profit divided by total deposits can still be a descriptive ratio, but does not account for how long each deposit was invested.
CAGR smooths a clean start-to-end path
For exactly two years, (12,100 ÷ 10,000)^(1 ÷ 2) − 1 equals a 10% compound annual growth rate. It is a smoothed equivalent, not evidence the investment earned 10% in each year. CAGR is unsuitable if you ignore intervening contributions or withdrawals, because part of the ending balance may have come from your new money rather than growth.
XIRR includes actual dates
In a hypothetical example, pay RM10,000 on 1 January 2025 and another RM10,000 on 1 January 2026. Value the account at RM22,000 on 1 January 2027. Mark investments negative and the terminal value positive. The overall profit is RM2,000, or 10% of total deposits, while XIRR is about 6.52% a year because the second deposit was invested for less time.
| Measure | Question answered | Example |
|---|---|---|
| ROI | Overall gain on a clean initial investment | RM10,000 → RM12,100: 21% |
| CAGR | Equivalent annual growth with no intervening flows | Same investment over 2 years: 10% |
| XIRR | Annualised return using dated cashflows | Two dated deposits, RM22,000 terminal value: ~6.52% |
Use the downloadable example correctly
The CSV puts dates in column A and amounts in column B, with headers in row 1. Import the dates as real spreadsheet dates, then enter =XIRR(B2:B4,A2:A4) in a blank cell and format it as a percentage. The terminal valuation is a hypothetical closing cashflow for the calculation, not an actual withdrawal. Microsoft’s XIRR uses dated cashflows on a 365-day basis and requires at least one positive and one negative value.
Background source: Microsoft Support: XIRR function
Avoid misleading annualised results
Keep all flows in one chosen currency using a stated conversion policy. Count external deposits and withdrawals; trades within the same portfolio are not new external contributions. Include distributions separately only if they leave the measured portfolio. Complex sign-changing cashflows can give ambiguous or failed XIRR results. A very short-period annualisation can also look extreme; show dates, cash profit and the method alongside the rate.
Download the worksheet
Put it into practice
The Numbrite ROI tool covers its displayed simple return inputs; it does not calculate XIRR. Use the downloadable dates-and-cashflows sheet with a spreadsheet for that example.
Open the ROI calculator