How to Calculate XIRR

A practical guide to calculating XIRR in Excel, Google Sheets, and online — with a worked SIP example you can copy.

Step 1: List every cash flow

Two columns: Date and Amount. Investments are negative (money leaving your pocket); redemptions and the current portfolio value are positive.

Step 2: Use the XIRR function

=XIRR(B2:B38, A2:A38)
// → returns the annualised return as a decimal, e.g. 0.118 = 11.8%

Works identically in Microsoft Excel and Google Sheets. Format the cell as a percentage.

Step 3: Verify against this calculator

Paste the same dates and amounts into our XIRR calculator — the result will match Excel to 4 decimal places. We use the same Newton-Raphson method.

Worked example

Plug this into Excel: =XIRR(B2:B38, A2:A38) → 11.83%.

Common mistakes

• Forgetting the negative sign on investments.
• Missing the final portfolio value (the positive cash flow).
• Mixing date formats — keep them all ISO (YYYY-MM-DD).
• Comparing XIRR across different time horizons without context.