Calculating your real investment returns: absolute return, CAGR and XIRR

Why the same investment can show different returns

Open a broker app, a mutual fund statement and a spreadsheet, and you may see three different return figures for the same money. Usually none of them is wrong. They answer different questions: how much did my money grow in total, how fast did it grow each year, and how did it grow given that I invested at different times?

Understanding which figure you are looking at helps you compare investments fairly and avoid being impressed or alarmed by the wrong number. This article is educational and is not investment advice.

Absolute return: total growth, ignoring time

Absolute return is the simplest measure:

• Absolute return = (current value − amount invested) ÷ amount invested

If you invested ₹1,00,000 and it is now worth ₹1,50,000, the absolute return is 50%. That is accurate, but incomplete. A 50% gain over one year and a 50% gain over ten years are very different results, and absolute return treats them as identical. It is most useful for short holding periods or as a quick sanity check.

CAGR: the average yearly growth of a single investment

Compound annual growth rate answers the question: if this investment had grown at a steady rate every year, what would that rate be?

• CAGR = (ending value ÷ starting value) ^ (1 ÷ number of years) − 1

Using the same numbers over three years: (1,50,000 ÷ 1,00,000) ^ (1 ÷ 3) − 1 = 1.5 ^ 0.333 − 1 ≈ 14.47% per year. Notice that this is less than 50% divided by 3 (16.7%), because growth compounds: each year's gain also earns returns in later years.

CAGR works well when there is one investment at the start and one value at the end. It breaks down when you add or withdraw money along the way, which is how most people actually invest.

XIRR: returns when money goes in and out at different times

If you invest a fixed amount every month through a SIP, top up occasionally, or withdraw some money, each rupee has been invested for a different length of time. XIRR, the extended internal rate of return, accounts for this by looking at the date and amount of every cash flow.

Formally, XIRR is the annual rate at which the present value of all your investments and withdrawals, including today's value treated as a final withdrawal, adds up to zero. In practice you never solve this by hand. Spreadsheets do it for you with the XIRR function.

A worked example:

• 1 January 2023: invest ₹50,000
• 1 January 2024: invest another ₹50,000
• 1 January 2025: the portfolio is worth ₹1,20,000

Absolute return here is 20% (₹20,000 gain on ₹1,00,000). Yet the XIRR is about 12.8% per year. The difference arises because the second ₹50,000 was only invested for one year, so it had less time to earn its share of the gain. Neither absolute return nor a CAGR on the full ₹1,00,000 captures that.

Calculating XIRR in a spreadsheet

Google Sheets, Microsoft Excel and LibreOffice Calc all provide an XIRR function. To use it:

• Put dates in one column and amounts in the next.
• Enter money you invest as negative numbers (it leaves your pocket) and money you receive or withdraw as positive numbers.
• Add a final row with today's date and the current value of the investment as a positive number.
• Use =XIRR(amounts_range, dates_range) and format the result as a percentage.

If the function returns an error, check that there is at least one negative and one positive value, and that every date is a real date rather than text.

Which number should you look at?

• For a single lump sum held for several years, CAGR is clear and easy to compare.
• For SIPs, regular top-ups or any withdrawals, use XIRR.
• For holding periods under a year, annualised figures can exaggerate small movements; absolute return is often more honest.
• When comparing two investments, make sure both figures use the same method and cover the same period.

Also remember what these numbers leave out. Returns are usually shown before tax, and fees may or may not be included depending on the source. A higher return figure says nothing about the risk taken to achieve it.

Tracking every transaction with its date is what makes an accurate XIRR possible, which is one reason our Portfolio Management tool treats individual transaction records, not just current balances, as the foundation of every summary.