How to Calculate XIRR for SIPs, With a Worked Example

When an investor asks "how much has my SIP earned?", the honest answer depends on how you measure it. A SIP puts money in on many different dates, so a single percentage gain hides the fact that some instalments have been invested for a year and others for a month. To answer properly, you need to calculate XIRR, the extended internal rate of return. This guide explains what XIRR is, how to compute it in a spreadsheet, and the mistakes that most often produce a wrong number.

Why absolute return and CAGR mislead for SIPs

Absolute return is simply (current value minus amount invested) divided by amount invested. It ignores time completely. A 10% gain over six months and a 10% gain over five years look identical.

CAGR (compound annual growth rate) fixes the time problem, but only for a single investment made on one date and valued on another. It needs one start value, one end value and one period. A SIP has twelve, twenty-four or a hundred start dates, so there is no single "period" to plug into the CAGR formula.

XIRR solves both problems. It treats every instalment, redemption and payout as a separate cash flow with its own date and finds one annualised rate that ties them all together.

What XIRR actually is

XIRR is the annual rate r that makes the net present value of all cash flows equal to zero:

Sum of CF_i / (1 + r) ^ ((d_i - d_0) / 365) = 0

Where:

• CF_i is each cash flow (an instalment, a redemption, a payout, or the current value)
• d_i is the date of that cash flow
• d_0 is the date of the first cash flow
• 365 is the day-count basis used by Excel and Google Sheets (actual days divided by 365)

There is no closed-form solution, so spreadsheets find r by trial and error (iteration) until the equation balances.

The sign convention

This is where most people go wrong. Think of it from the investor's wallet:

1. Money leaving the investor is negative. Every SIP instalment and lump-sum purchase.
2. Money coming back to the investor is positive. Redemptions, IDCW payouts credited to the bank, and the current market value on the valuation date (treated as if the investor redeemed everything that day).

If every number has the same sign, XIRR cannot be computed and the spreadsheet returns an error.

How to calculate XIRR in Excel or Google Sheets

Put dates in one column and amounts in the next, then use:

=XIRR(values, dates)

For example, =XIRR(B2:B14, A2:A14). Format the result as a percentage. Both Excel and Google Sheets also accept an optional third argument, a starting guess, which rarely needs changing.

A worked example (hypothetical)

Suppose an investor ran a SIP of ₹5,000 on the 5th of every month from 05-01-2025 to 05-12-2025, twelve instalments in all, and the holding is worth ₹63,500 on 05-01-2026. These figures are a hypothetical illustration, not a projection of what any scheme will deliver.

• Total invested: ₹60,000
• Gain: ₹3,500
• Absolute return: 5.83%
• XIRR: about 10.90% per year

Why is XIRR nearly double the absolute return? Because on average the money was invested for only about six and a half months, not a full year. The ₹3,500 was earned on a much smaller "average balance", and annualising it gives a higher rate.

You can verify the number yourself with a few lines of Python using bisection:

from datetime import date

flows = [(date(2025, m, 5), -5000) for m in range(1, 13)]
flows.append((date(2026, 1, 5), 63500))
d0 = flows[0][0]

def npv(r):
    return sum(a / (1 + r) ** ((d - d0).days / 365) for d, a in flows)

lo, hi = -0.9, 5.0
for _ in range(200):
    mid = (lo + hi) / 2
    if npv(mid) > 0:
        lo = mid
    else:
        hi = mid
print(round(mid * 100, 2))  # 10.9

Handling the real-world cash flows

Real portfolios are rarely a clean twelve-row SIP. Here is how to treat the common events.

IDCW (dividend) payouts

An IDCW payout credited to the investor's bank account is a positive cash flow on the payout date. IDCW reinvestment is not a separate cash flow, because the money never left the scheme; the extra units are already in the current value.

Redemptions and partial withdrawals

A redemption is a positive cash flow on the redemption date, for the amount actually received. The remaining units are still valued in the current-value row.

Switches

For a single scheme's XIRR, a switch-out is a positive flow from the source scheme and a switch-in is a negative flow into the target scheme. At the portfolio level the two cancel out, so you can leave both out, provided the current value includes the target scheme.

STP (systematic transfer plan)

An STP is a series of switches. Treat it exactly like switches: per-scheme it creates flows on both sides; per-portfolio the transfers net to zero.

Why very short periods give extreme XIRR

Because XIRR is annualised, a 2% move in three weeks gets compounded to a full-year rate, which can show up as 40% or more, or as a deep negative. The number is mathematically correct but practically meaningless. Many reporting tools show absolute return for holdings under one year, or at least flag short-period XIRR. When reading a client report, check the holding period before reacting to the figure.

XIRR vs CAGR vs absolute return

For a single lump sum with no other flows, XIRR and CAGR give the same answer.

Common mistakes

1. Wrong signs. All instalments entered as positive along with a positive current value gives an error, or a meaningless number if one row was flipped.
2. Missing current-value row. Without the final positive flow, the spreadsheet only sees money going out.
3. Dates stored as text. A date typed as "05-01-2025" may be read as text, or as 1 May in a US-locale sheet. Use a real date format and check the locale.
4. Using units instead of rupees. Every value must be a rupee amount.
5. Mixing schemes inconsistently. If you include a switch-out from one scheme, include the matching switch-in or drop both.
6. Using a stale NAV. The current value must be based on the NAV for the valuation date you entered.

If you are pulling transactions from statements, our guide on CAS password formats explains how to open them. Back-office software such as IntelliWealthPro can calculate XIRR across every folio from transaction feeds, which saves rebuilding these sheets by hand for each client.

The bottom line

XIRR is the right tool whenever money moves on more than one date. Get the signs right, include the current value, use real dates, and read the result alongside the holding period. It describes past performance only; it says nothing about future returns.

Information only. Not investment advice. Mutual fund investments are subject to market risks; read all scheme-related documents carefully.