Clients Actual Investments

What this tool answers: "How much did the client actually put in from their pocket?" — the question your broker file doesn't answer directly because "Invested Capital" usually shows only the current cost basis, which gets reset after any sell+rebuy or switch transaction.

📂 Step 1

Holdings / Portfolio Summary File

Upload the broker's Portfolio Summary file (.xlsx) — example: NEW##_Client_holdings_All_*.xlsx. This shows current "Invested Capital" + "Current Value" per fund per investor.

📂 Step 2

Capital Gain Report File

Upload the broker's Realized Capital Gain Report (.xlsx) — example: NEW##_Client_Capital-Gain_*.xlsx. Used to detect net realized gains/losses from switches.

📊 As Reported by Broker

Invested Capital (displayed)—

Current Value—

Apparent Gain / (Loss)—

⚠ This is the cost basis of currently held units, NOT lifetime out-of-pocket.

🔄 Realized Activity (from Capital Gain Report)

Total Sell Proceeds—

Total Original Purchase Cost—

Net Realized Gain / (Loss)—

From — switch / redemption transactions.

✏️ Manual Adjustments (Optional)

Additional bank → MF inflows NOT captured in files (₹)

MF → bank redemptions where client took cash out (₹)

Use these only if you know the client added fresh money or withdrew real cash to bank outside what's in the holdings/capital-gain files.

💎 TRUE Out-of-Pocket Investment

Displayed Invested Capital—

Less: Net Realized Gain (or + if loss)—

+ Manual Bank Inflows—

− Manual Bank Outflows—

TRUE Out-of-Pocket—

Current Value—

TRUE Lifetime Gain / (Loss)—

TRUE Return %—

📋 Currently Held Funds — Detail

Fund	Units	Invested	Current Value	Apparent G/L

🔄 Realized Switches — Detail

Sell Date	Fund	Type	Sell Amount	Original Cost	Realized G/L