[NISM V-A]

Calculate XIRR for SIP: How to Measure Real Returns from Your Mutual Fund CAS

NISM Prep Research & Editorial Team·2026-09-03

Open your mutual fund app right now and look at the return it shows you next to your SIP. There's a good chance it's wrong — not fraudulent, just the wrong kind of math applied to money that didn't all arrive on the same day. This guide is the fifteen minutes it takes to fix that: pull your own CAS, calculate XIRR for your SIP by hand and in a spreadsheet, and know your real number instead of the app's approximation of it.

This guide is published strictly for educational and informational purposes and does not constitute personalized investment advice, financial planning recommendations, or regulatory legal opinions.

Executive Summary

Here's the trap, in one sentence: your SIP dashboard shows "Invested: ₹5,00,000 | Current Value: ₹6,50,000" and every instinct says "that's 30% over four years, call it 7% a year" — and that instinct is wrong, because it's the math for a lump sum, and you never put in a lump sum.

Each of your fifty-odd monthly installments has been invested for a different length of time — the first one for four years, the last one for a month — and no single "divide by years" shortcut can average that correctly. This guide walks through four ways people measure returns (Absolute Return, CAGR, XIRR, and Rolling Returns), shows you exactly where the first two quietly lie to SIP investors, and works a full Indian case study end to end so you can run the same math on your own CAS statement by the time you finish reading.

1. The Eyeball Trap: Why Absolute Return Misleads on Cash Flows

The most visible figure on investment dashboards is total cumulative gain, representing absolute return.

Why It Feels Right and Isn't

Absolute return measures the percentage change in asset value relative to total capital invested, ignoring holding duration entirely.

While suitable for periods under one year, absolute return fails over multi-year horizons. A 30% gain is strong over 18 months but poor over 10 years (under 2.7% annually). In an ongoing SIP, absolute return creates distortion by assuming all capital was deployed on day one. Comparing absolute return vs CAGR highlights that without annualization, returns cannot be benchmarked against inflation, bonds, or fixed deposits.

Formula

In plain terms: this is just "how much bigger is my money now, as a percentage of what I put in" — no clock involved at all. It treats a gain earned in six months and the identical gain earned in six years as the same number, which is exactly why it stops being useful once time enters the picture.

\text{Absolute Return} = \frac{\text{Current Value} - \text{Total Investment}}{\text{Total Investment}} \times 100

Where:

Current Value = Latest portfolio valuation at date $t$ (in INR).
Total Investment = Total cumulative cash invested across all tranches (in INR).

Worked Numerical Example

Consider an investor reviewing their portfolio:

Total Investment = ₹5,00,000
Current Value = ₹6,50,000

Applying the formula:

Absolute Return = ((6,50,000 − 5,00,000) / 5,00,000) × 100 = 30.00%

Practical Interpretation

The portfolio earned a gross profit of ₹1,50,000, representing a 30.00% absolute increase. However, this metric does not indicate how long the capital took to achieve that gain, making it inadequate for evaluating long-term performance.

2. CAGR: The Annualized Yardstick for Lump Sums (and Why It Breaks for SIPs)

To incorporate time into multi-year performance evaluation, investors use Compound Annual Growth Rate (CAGR).

Concept Explanation

CAGR calculates the constant geometric annual rate at which an investment would have grown if compounding steadily.

However, CAGR requires one initial outflow and one terminal value. It cannot accommodate periodic investments. Feeding cumulative SIP installments into a standard CAGR formula yields mathematically invalid results.

Formula

In plain terms: CAGR answers "what single, constant annual growth rate, applied every year without exception, would turn my starting amount into my ending amount?" It's a smoothed-out average, useful for one lump sum sitting untouched — but it silently assumes there was only ever one deposit, made on day one.

CAGR = \left(\frac{\text{Ending Value}}{\text{Beginning Value}}\right)^{1/n} - 1

Where:

Ending Value = Terminal valuation of the investment (in INR).
Beginning Value = Initial lump sum committed at time zero (in INR).
$n$ = Holding duration in years, calculated as (Elapsed Days / 365.25).

Worked Numerical Example

Suppose an investor invested a single lump sum of ₹5,00,000 on January 10, 2022, holding until March 10, 2026.

Beginning Value = ₹5,00,000
Ending Value = ₹6,50,000
Elapsed Days = 1,520 days
Duration (n) = 1,520 / 365.25 = 4.1615 years

Step 1: Calculate the growth ratio: Ending Value / Beginning Value = 6,50,000 / 5,00,000 = 1.3000

Step 2: Raise the ratio to the power of (1 / 4.1615) = 0.2403: (1.3000)^0.2403 = 1.0650

Step 3: Subtract 1 to establish the annualized rate: CAGR = 1.0650 − 1 = 0.0650 or 6.50%

Practical Interpretation

If ₹5,00,000 had been invested as a single lump sum on day one, growing to ₹6,50,000 over 4.16 years represents a CAGR of 6.50%.

Applying this formula to an SIP leads investors to mistakenly conclude their return was only 6.50%. Comparing this to a 7.0% deposit, they might abandon equities. Yet in an SIP, the final installment was invested for only 30 days. The average rupee was deployed for roughly two years. Staggered cash flows require XIRR.

3. XIRR: The True Internal Rate of Return for Staggered Cash Flows

Extended Internal Rate of Return (XIRR) is the universal metric for evaluating portfolios with periodic or irregular cash flows.

Why Staggered Cash Flows Need Their Own Formula

Monthly SIP debits do not occur at uniform intervals due to weekends, bank holidays, and leap years. XIRR calculates the exact annual discount rate that equates the present value of all cash outflows (purchases) with the terminal portfolio valuation. It acts as an annualized, money-weighted rate of return where each installment is weighted by the exact number of days it spent compounding.

Formula

In plain terms: instead of assuming one deposit on one date, XIRR gives every single installment credit for the exact number of days it actually spent invested — a rupee in for eleven months earns less "annualized credit" than a rupee in for four years, even though both are just "a rupee." XIRR is the single annual rate that, if you'd earned it consistently on every rupee for exactly as long as that rupee was invested, would reproduce your real final result. Formally, it's the discount rate r that makes the present value of every cash flow (each one discounted back by its own exact holding period) sum to zero:

\sum_{i=1}^{N} \frac{C_i}{(1+r)^{(d_i - d_1)/365}} = 0

Where:

$N$ = Total number of cash flows, including the final valuation.
$C_i$ = Cash flow amount for transaction $i$ . Outflows are negative ( $-C_i$ ); inflows are positive ( $+C_i$ ).
$r$ = Annualized internal rate of return (XIRR).
$d_i$ = Date of cash flow $i$ .
$d_1$ = Baseline date of the initial transaction.
$(d_i - d_1)/365$ = Time fraction in years between transaction $i$ and transaction 1.

Because r appears in fractional exponents, it is solved using numerical algorithms like Newton-Raphson.

Worked Numerical Example: A 3-Cash-Flow Scenario

Consider a 3-cash-flow portfolio calculated by hand:

Date 1 (2024-01-01): First SIP purchase of ₹50,000 (C_1 = −50,000, day 0).
Date 2 (2024-07-01): Second SIP purchase of ₹50,000 (C_2 = −50,000, day 182).
Date 3 (2025-01-01): Valuation of ₹1,15,000 (C_3 = +1,15,000, day 366).

Total capital deployed = ₹1,00,000. Current value = ₹1,15,000. Absolute return = 15.00%.

The discounting formula is:

NPV(r) = \frac{-50{,}000}{(1+r)^0} + \frac{-50{,}000}{(1+r)^{182/365}} + \frac{1{,}15{,}000}{(1+r)^{366/365}} = 0

Testing trial rates:

At r = 15.00%: NPV = −50,000.00 − 46,633.96 + 99,964.36 = +3,330.40 (rate too low).
At r = 22.00%: NPV = −50,000.00 − 45,290.68 + 94,210.55 = −1,080.13 (rate too high). Interpolating between 15% and 22% yields r = 20.21% (NPV = −50,000.00 − 45,640.85 + 95,640.85 = 0.00).

Practical Interpretation

Although absolute return was 15.00%, the true annualized return (XIRR) was 20.21%. The second installment compounded for only six months, delivering strong gains in a shorter timeframe and raising the overall money-weighted yield.

4. How to Calculate XIRR for SIP Using Spreadsheet Formulas

Spreadsheet programs calculate XIRR instantly without manual iterations.

The Spreadsheet Formula

To calculate XIRR in Google Sheets or Microsoft Excel, enter:

=XIRR(values, dates, [guess])

Parameter Guidelines

values (Required): Continuous cell range of cash flows. Enter purchase debits as negative numbers (e.g., −10000), current portfolio market value as a positive number (e.g., 650000), and past dividend receipts or redemptions as positive numbers on their dates.
dates (Required): Matching range of transaction dates formatted as standard date serials (YYYY-MM-DD).
guess (Optional): Initial rate estimate for the solver (defaults to 0.1, or 10%).

Worked Numerical Example: Spreadsheet Setup

Row	Column A (Date)	Column B (Cash Flow in INR)	Description
1	Date	Cash Flow	Table Header
2	2022-01-10	−10000	Monthly SIP 1
3	2022-02-10	−10000	Monthly SIP 2
...	...	...	Staggered Monthly Debits
51	2026-02-10	−10000	Monthly SIP 50
52	2026-03-10	650000	Current Market Value

In cell B53, enter =XIRR(B2:B52, A2:A52). Formatting the cell as a percentage displays 12.61%.

5. Rolling Returns vs Point-to-Point Returns: Seeing the Real Risk and Consistency

While XIRR provides an accurate measure of personal cash flows, it remains a point-to-point metric tied to specific dates.

Concept Explanation

Point-to-point returns are vulnerable to timing luck. An evaluation during a market rally shows an inflated XIRR, while an evaluation during a market drop shows an artificially depressed return.

A point to point return vs rolling return analysis resolves this issue. Rolling returns measure performance across multiple overlapping windows of fixed duration (such as 3-year or 5-year periods over a decade), shifting forward by one day or week at a time.

Conducting a rolling returns mutual fund analysis shows historical maximum, minimum, and median returns, alongside downside probabilities across cycles.

Formulation and Rolling Methodology

In plain terms: instead of measuring the return for just one fixed start and end date (which can flatter or punish a fund purely by luck of timing), calculate the same "k-year CAGR" over and over — starting on every possible trading day in the history — and look at the whole spread of outcomes, not just one. Let P_t represent fund NAV on trading day t. For a rolling window of k years (k × 250 trading days), rolling return is:

\text{Rolling CAGR} = \left(\frac{P_t}{P_{t-k\times250}}\right)^{1/k} - 1

Worked Numerical Example

Evaluating an active equity fund over a 7-year cycle:

Point-to-Point 3-Year Return (March 2020 low to March 2023): Starting NAV = ₹42.50, Ending NAV = ₹88.20. Point-to-Point CAGR = (88.20 / 42.50)^(1 / 3) − 1 = 27.55%.
Rolling 3-Year Return Analysis across 1,000 Trading Days:
- Maximum 3-Year Return: 29.40%
- Minimum 3-Year Return: 3.10%
- Median 3-Year Return: 13.80%
- Negative 3-Year Periods: 0.00%

Practical Interpretation

The 27.55% figure was driven by bottom-tick market entry. The rolling median of 13.80% offers a realistic baseline, preventing emotional reactions to cyclical volatility.

6. Comprehensive Case Study: Rajesh's ₹5 Lakh SIP vs Fixed Deposit

This is the exact calculation that started this guide — a real pattern, run on realistic numbers, for a specific person.

Profile and Investment Parameters

Rajesh Sharma is an engineer in Pune. On January 10, 2022, he set up an auto-debit into a Flexi Cap Fund — ₹10,000 on the 10th of every month — mostly because a colleague was doing the same thing and he didn't want to overthink it. Four years later, he's the one being asked for advice, and he wants an actual number before he gives any.

Monthly SIP Outflow: ₹10,000 across 50 months (total invested: ₹5,00,000).
First Installment: January 10, 2022 | 50th Installment: February 10, 2026.
Evaluation Date: March 10, 2026 | Portfolio Valuation: ₹6,50,000.

The Eyeball Reaction

Rajesh opens the app on March 10, 2026, does the subtraction in his head, and doesn't love what he sees:

Total Invested: ₹5,00,000 | Current Value: ₹6,50,000 | Total Profit: ₹1,50,000

30% over four years. He divides by four, rounds down for safety, and lands on "about 7% a year" — CAGR = 6.50% if he's precise about it. His cousin's bank RD pays 7.00%. "Four years of market risk," he tells himself, "to end up exactly where a fixed deposit would've put me." He's about to recommend his colleague get out. Before he does, he pulls his CAS instead of trusting the app's headline number.

Step 1: Mapping the True Cash Flows

Rajesh's capital entered across 50 dates:

Installment 1 (2022-01-10): −₹10,000 (invested for 1,520 days)
Installment 12 (2022-12-10): −₹10,000 (invested for 1,186 days)
Installment 25 (2024-01-10): −₹10,000 (invested for 790 days)
Installment 38 (2025-02-10): −₹10,000 (invested for 393 days)
Installment 50 (2026-02-10): −₹10,000 (invested for 28 days)
Valuation (2026-03-10): +₹6,50,000

The average holding period for his capital was roughly 2.08 years, not 4.16 years.

Step 2: Calculating True XIRR

Setting up the cash flows in a spreadsheet:

\sum_{i=1}^{N} \frac{C_i}{(1+r)^{(d_i - d_1)/365}} = 0

Solving via Newton-Raphson yields: XIRR = 12.61%. Rajesh's actual annualized return was 12.61%, well above the 6.50% CAGR he assumed.

Step 3: Comparing SIP vs Fixed Deposit Return

What would Rajesh have earned in a bank recurring deposit (RD) yielding 7.00% per annum compounded quarterly? Quarterly compounding means the bank adds interest four times a year instead of once, so the rate you actually earn over a full year — the effective yield — ends up a touch higher than the quoted 7.00%:

\text{Effective Yield} = \left(1 + \frac{0.07}{4}\right)^4 - 1 = 7.186\%

Summing 50 monthly deposits of ₹10,000 at 7.00% p.a. yields: Terminal Value (Bank RD) = ₹5,79,168.51

Comparing the outcomes, with tax applied consistently on both sides — 30% slab + 4% cess (31.2% effective) on the RD's interest income, and 12.5% + 4% cess (13% effective) on the equity fund's LTCG above the ₹1.25 lakh exemption:

Metric	Naive Eyeball View	Bank Recurring Deposit (7.0% p.a.)	Equity Mutual Fund SIP
Total Invested	₹5,00,000	₹5,00,000	₹5,00,000
Duration	50 months	50 months	50 months
Terminal Value	₹6,50,000	₹5,79,168.51	₹6,50,000.00
Pre-Tax Profit	₹1,50,000	₹79,168.51	₹1,50,000.00
Annualized Rate	6.50% (Invalid CAGR)	7.00% (Effective Yield)	12.61% (True XIRR)
Post-Tax Wealth (30% Slab + Cess)	Not calculated	₹5,54,468 (Tax: ₹24,701)	₹6,46,750 (After LTCG)*

*Taxation context: Bank deposit interest is taxed at the investor's marginal slab (31.2% effective on ₹79,168.51 interest, including 4% cess). Long-Term Capital Gains (LTCG) on equity mutual funds held over 12 months qualify for an annual exemption of up to ₹1.25 Lakh under Section 112A, with excess taxed at 12.5% plus 4% cess (13% effective). On a gain of ₹1,50,000, only ₹25,000 is taxable, resulting in ₹3,250 in tax.

Case Study Findings

Rajesh's cousin's fixed deposit was never the comparison he thought it was. The math he ran in his head — 30% divided by four years — was quietly measuring the wrong thing: it treated his last installment, invested for 28 days, as if it had been sitting in the fund since 2022. The real number, weighted by how long each rupee actually worked, was double what he'd talked himself into believing:

The equity SIP delivered an annualized 12.61% XIRR, outpacing the bank deposit by 561 basis points.
The SIP produced ₹1,50,000 in gross profit versus ₹79,168.51 in the bank deposit — nearly double the rupee wealth.
Accounting for Indian tax rules on a like-for-like basis, net post-tax gains from the equity SIP were still well over double the bank deposit's.

Rajesh didn't tell his colleague to sell. He sent him this calculation instead.

7. From PDF to Spreadsheet: Extracting Your CAS Statement

To calculate XIRR for your portfolio, pull your transaction records from an official Consolidated Account Statement (CAS).

(For candidates studying for the NISM-Series-X-A: Investment Adviser certification or NISM-Series-V-A: Mutual Fund Distributors certification, the CAS structure corresponds to investor reporting and performance evaluation modules under SEBI regulations.)

Step 1: Requesting Your CAS

Investors can generate a unified statement covering CAMS and KFintech mutual fund folios free of charge:

Visit the CAMS Online or KFintech investor portal.
Select Statements → CAS (Consolidated Account Statement) → Detailed Statement.
Enter your registered email and PAN, set the date range from your first investment to today, and submit.
For demat holdings, download your monthly CAS from NSDL or CDSL.

Step 2: Transferring Transactions to Excel or Google Sheets

Open the CAS PDF using your password.
Locate the specific mutual fund folio you want to analyze.
Copy the transaction table and paste it into Excel or Google Sheets.
Clean the data:
- Filter for purchase rows ("SIP Purchase" or "Purchase").
- Place transaction dates in Column A and debit amounts in Column B.
- Enter all debit amounts as negative values (e.g., −10000).
- Add a final row at the bottom: enter today's date in Column A and your current portfolio market value as a positive number in Column B.

Step 3: Compute Your XIRR

In an empty cell, apply =XIRR(B2:B52, A2:A52). The spreadsheet will calculate your exact money-weighted return.

8. Frequently Asked Questions

What is the difference between IRR and XIRR in mutual funds?

Internal Rate of Return (IRR) assumes cash flows occur at strictly equal intervals. Because real-world SIP debits vary due to weekends and holidays, Extended Internal Rate of Return (XIRR) incorporates exact calendar dates and day-count fractions to determine true annualized returns.

Why does my mutual fund app show a different XIRR than my spreadsheet calculation?

Discrepancies usually stem from three factors:

NAV Allotment Date vs. Debit Date: Apps track unit allotment dates rather than bank debit dates.
Stamp Duty Deductions: The 0.005% stamp duty reduces net deployed capital.
Day-Count Conventions: Some software uses 360-day or 365.25-day baselines, while spreadsheets use 365 days.

When should I use CAGR instead of XIRR?

CAGR is designed strictly for single lump sums with zero intermediate cash flows. XIRR is required whenever multiple inflows or outflows occur over time, such as SIPs, SWPs, or dividend payouts.

Can XIRR be negative even if absolute return is positive?

Yes. This occurs when substantial capital is deployed right before a market drop. If an early ₹10,000 doubles to ₹20,000, but a subsequent ₹5,00,000 deposit falls 10%, absolute profit may stay positive while XIRR turns negative because the bulk of your capital endured losses.

How does dividend payout or IDCW impact my SIP XIRR?

Under an Income Distribution cum Capital Withdrawal (IDCW) plan, cash payouts are cash inflows. You must enter each dividend as a positive number on its credit date. Omitting dividends artificially depresses calculated XIRR.

9. The Investor's Homework

Evaluating investment performance should not be limited to casual glances at portfolio dashboards. Auditing your returns builds real financial discipline.

Take thirty minutes this week to complete this practical task:

Log in to CAMS or KFintech and download your detailed consolidated account statement.
Open a blank spreadsheet with columns for Date, Description, and Cash Flow.
Enter your historical SIP debits as negative numbers, enter your current portfolio market value as a positive number on today's date, and execute =XIRR().
Compare your result with the post-tax yield of bank deposits and benchmark rolling returns.

Once you see your actual XIRR, you will know with mathematical certainty whether your portfolio is delivering appropriate compensation for the risks you take.