Mutual fund SIP book: registrations, stoppages and missed instalments

What the distributor or AMC team wants to know

A mutual fund distributor, a wealth platform or an AMC's sales team watching its SIP book has one practical worry: is the monthly inflow the book will generate next month going up or down, and why? Three quite different things reduce it. Investors cancel. Registrations reach the end of their tenure. And live registrations fail to collect because the bank debit bounced. Each has a different response (a retention call, a renewal prompt, a mandate or balance reminder), so the report must separate them.

Industry bodies and platforms publish their own SIP statistics with their own definitions. The ratios below are defined here for this analysis and should not be read as, or compared directly with, any published series.

Tables in a typical registrar or platform extract

Keep monthly frequency in scope and convert or exclude weekly and quarterly SIPs, or the rupee book mixes different periods.

Our definitions, written out

For a month M:

• Opening active: started before M, not cancelled before M, tenure not ended before M
• New: start_month = M
• Cancelled: cancel_month = M, split by cancel_by (investor or automatic after failures)
• Matured: tenure_end_month = M and not cancelled
• Closing = opening + new − cancelled − matured
• Cancellation rate = cancelled ÷ opening active
• All-exit rate = (cancelled + matured) ÷ opening active
• Exits per 100 new = (cancelled + matured) ÷ new × 100, a book-growth indicator
• Missed-instalment rate = bounced debits ÷ debits due, by count and by rupee value

A twelve-registration book, September 2026

The data is synthetic: one distributor's twelve registrations and their September instalments.

-- Synthetic SIP registrations for one distributor's book. Amounts in rupees.
CREATE TABLE sip (
  sip_id TEXT, investor_id TEXT, amount INTEGER,
  start_month TEXT, tenure_end_month TEXT, cancel_month TEXT, cancel_by TEXT
);
INSERT INTO sip VALUES
 ('S01','I01', 5000,'2023-04','2026-09',NULL,NULL),
 ('S02','I02', 2000,'2024-01','2029-12',NULL,NULL),
 ('S03','I02', 3000,'2025-06','2030-05','2026-09','investor'),
 ('S04','I03',10000,'2022-09','2027-08',NULL,NULL),
 ('S05','I04', 1000,'2026-02','2031-01','2026-09','auto_bounces'),
 ('S06','I05', 2500,'2025-11','2030-10',NULL,NULL),
 ('S07','I06', 1500,'2026-09','2031-08',NULL,NULL),
 ('S08','I07',25000,'2024-07','2029-06',NULL,NULL),
 ('S09','I08', 500,'2026-09','2031-08',NULL,NULL),
 ('S10','I09', 4000,'2021-10','2026-09',NULL,NULL),
 ('S11','I10', 2000,'2025-03','2030-02','2026-08','investor'),
 ('S12','I11', 7500,'2026-09','2031-08',NULL,NULL);

CREATE TABLE instalment (sip_id TEXT, due_month TEXT, status TEXT);
INSERT INTO instalment VALUES
 ('S01','2026-09','paid'),('S02','2026-09','paid'),('S03','2026-09','bounced'),
 ('S04','2026-09','bounced'),('S05','2026-09','bounced'),('S06','2026-09','paid'),
 ('S07','2026-09','paid'),('S08','2026-09','paid'),('S09','2026-09','bounced'),
 ('S10','2026-09','paid'),('S12','2026-09','paid');

-- September 2026 movement of the book: opening + new - cancelled - matured = closing
WITH m AS (SELECT '2026-09' AS mon),
flags AS (
  SELECT s.*,
    (start_month < mon AND (cancel_month IS NULL OR cancel_month >= mon)
                       AND tenure_end_month >= mon) AS open_flag,
    (start_month = mon) AS new_flag,
    (cancel_month = mon) AS cancelled_flag,
    (tenure_end_month = mon AND cancel_month IS NULL) AS matured_flag
  FROM sip s, m
)
SELECT SUM(open_flag) AS opening_count,
       SUM(new_flag) AS new_count,
       SUM(cancelled_flag) AS cancelled_count,
       SUM(matured_flag) AS matured_count,
       SUM(open_flag) + SUM(new_flag) - SUM(cancelled_flag) - SUM(matured_flag) AS closing_count,
       SUM(open_flag * amount) AS opening_book_rs,
       SUM(new_flag * amount) AS new_book_rs,
       SUM(cancelled_flag * amount) AS cancelled_book_rs,
       SUM(matured_flag * amount) AS matured_book_rs
FROM flags;

As a bridge:

The book shrank by ₹3,500 a month, and more than all of that came from maturities (₹9,000), not cancellations (₹4,000). S11 was cancelled in August, so it is excluded from the opening.

-- Ratios, with our own stated definitions
WITH flags AS (
  SELECT s.*,
    (start_month < '2026-09' AND (cancel_month IS NULL OR cancel_month >= '2026-09')
                             AND tenure_end_month >= '2026-09') AS open_flag,
    (start_month = '2026-09') AS new_flag,
    (cancel_month = '2026-09') AS cancelled_flag,
    (tenure_end_month = '2026-09' AND cancel_month IS NULL) AS matured_flag
  FROM sip s
),
t AS (
  SELECT SUM(open_flag) AS opening, SUM(new_flag) AS new_regs,
         SUM(cancelled_flag) AS cancelled, SUM(matured_flag) AS matured,
         SUM(cancelled_flag * (cancel_by = 'investor')) AS by_investor
  FROM flags
),
due AS (
  SELECT COUNT(*) AS due_n, SUM(status = 'bounced') AS bounced_n,
         SUM(s.amount) AS due_rs, SUM((status = 'bounced') * s.amount) AS bounced_rs
  FROM instalment i JOIN sip s USING (sip_id) WHERE i.due_month = '2026-09'
)
SELECT ROUND(100.0 * cancelled / opening, 1) AS cancel_rate_on_opening_pct,
       ROUND(100.0 * by_investor / opening, 1) AS investor_cancel_rate_pct,
       ROUND(100.0 * (cancelled + matured) / opening, 1) AS all_exits_on_opening_pct,
       ROUND(100.0 * (cancelled + matured) / new_regs, 1) AS exits_per_100_new,
       ROUND(100.0 * bounced_n / due_n, 1) AS missed_by_count_pct,
       ROUND(100.0 * bounced_rs / due_rs, 1) AS missed_by_value_pct
FROM t, due;

Reading September

The same month gives 25% and 133%. Both are "stoppage" figures someone could put on a slide; they measure different things. Cancellations as a share of the opening book say how many existing investors were lost. Exits per 100 new say the book is not replacing what leaves. Name the ratio in the heading, not in a footnote.

Half the cancellations were not the investor's choice. S05 was cancelled automatically after repeated failed debits. That is a collection problem, and a reminder before the debit date may have saved it; a retention call about fund performance would not.

Missed instalments differ by count and by value. Four of eleven debits bounced, but they were mostly small ones apart from S04's ₹10,000, so 23.4% of rupees due were missed. S09's bounce was its first instalment; check whether its mandate was active by the debit date, because new-registration bounces are often a mandate timing issue rather than an investor issue.

One registration is half the book. S08 at ₹25,000 a month is 48% of the opening book. With concentration like this, report the count bridge beside the rupee bridge, and name large registrations individually for the relationship manager.

Things that distort SIP books

• Step-up and amount changes. If the table stores only the current amount, the opening book is overstated for stepped-up SIPs. Use the amount effective in each month.
• Pauses. A paused SIP is neither cancelled nor collecting. Give it its own line rather than counting it as a missed instalment every month.
• Duplicate registrations. An investor who cancels and re-registers the same scheme at a higher amount shows as one cancellation and one new SIP. Match on investor and scheme within a short window and report it as a modification.
• Reporting month. Registrar extracts can arrive with a lag. Freeze the extract date and use the same cut-off every month, and keep financial-year columns consistent, as covered in Indian financial year and quarter columns.

For the head of distribution

The bridge table first, in rupees, with the count underneath. Then two numbers, clearly named: investor cancellation rate on the opening book, and missed-instalment rate by value. Then the action list: registrations maturing in the next three months (a renewal conversation), live SIPs with two consecutive bounces (a mandate check), and any new SIP whose first debit failed.

Collections teams in lending face the same count-versus-value choice; see NBFC collection efficiency metrics. Bridges and ratio definitions like these are practised in SQL throughout the Data Analyst programme.