Part of the Sanchayam series.

Why FIFO

FIFO (first in, first out) is the standard cost basis method for Indian equities and mutual funds for tax purposes. When you sell shares, the oldest lots you bought are assumed to be the ones sold first. The cost basis of those oldest lots determines your taxable gain.

Sanchayam implements FIFO at sell time: when a sell lot is recorded, the backend walks the buy lots in chronological order, consuming them until the sell quantity is satisfied, and computes the aggregate cost.

Lot Schema

CREATE TABLE lots (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  holding_id        UUID NOT NULL REFERENCES holdings(id),
  transaction_type  lot_transaction_type NOT NULL,  -- 'buy' | 'sell'
  quantity          NUMERIC(38,8) NOT NULL,
  remaining_quantity NUMERIC(38,8),
  price_per_unit    NUMERIC(38,0) NOT NULL,
  cost_basis_minor  NUMERIC(38,0),
  transaction_date  DATE NOT NULL,
  ...
);

remaining_quantity is only set on buy lots. It starts equal to quantity and decreases as subsequent sells consume it. A buy lot with remaining_quantity = 0 is fully consumed.

cost_basis_minor is only set on sell lots. It stores the FIFO-computed cost of the shares being sold, in minor currency units (paise for INR). Realized P&L for a sell lot is always sell_proceeds - cost_basis_minor.

price_per_unit is stored in minor units on both buy and sell lots.

FIFO Walk at Sell Time

When a sell lot is submitted, the backend walks buy lots in chronological order:

const buyLots = await sql`
  SELECT id, quantity, remaining_quantity, price_per_unit
  FROM lots
  WHERE holding_id = ${holdingId}
    AND transaction_type = 'buy'
    AND remaining_quantity > 0
    AND is_deleted = false
  ORDER BY transaction_date ASC, created_at ASC
`

let remaining = sellQuantity
let costBasisMinor = 0

for (const lot of buyLots) {
  if (remaining <= 0) break
  const available = parseFloat(lot.remaining_quantity)
  const consumed = Math.min(available, remaining)

  costBasisMinor += consumed * parseFloat(lot.price_per_unit)

  await sql`
    UPDATE lots
    SET remaining_quantity = remaining_quantity - ${consumed}
    WHERE id = ${lot.id}
  `
  remaining -= consumed
}

After the walk, cost_basis_minor is set on the sell lot row. Realized P&L for that sell = sell_quantity * sell_price_per_unit - cost_basis_minor.

The walk is done inside a transaction. If the sell quantity exceeds the available buy quantity (an error in data entry), the transaction is rolled back and the user sees a validation error.

Realized P&L Summary

To compute total realized P&L for a holding, the backend sums across all sell lots:

SELECT
  SUM(quantity * price_per_unit - cost_basis_minor) AS realized_pnl_minor
FROM lots
WHERE holding_id = ${holdingId}
  AND transaction_type = 'sell'
  AND is_deleted = false

This query is fast regardless of lot count because it scans only sell lots and all values are already computed at insert time. There is no re-walking of buy lots at query time.

trimLotsToCurrentPosition

XIRR measures the return of your current position. If you bought 100 shares in 2015, sold them all in 2018, and bought again in 2020, the 2015 lots are irrelevant to your current XIRR. Including them would dilute the return calculation with cash flows from a position you no longer hold.

trimLotsToCurrentPosition finds the last point where the running quantity reached zero - the last full exit - and returns only the lots after that point:

export function trimLotsToCurrentPosition(lots: any[]): any[] {
  if (lots.length === 0) return lots
  let runningQty = 0
  let lastExitIdx = -1

  for (let i = 0; i < lots.length; i++) {
    const qty = parseFloat(lots[i].quantity)
    if (lots[i].transaction_type === 'buy') runningQty += qty
    else runningQty -= qty

    if (runningQty <= 0 && i < lots.length - 1) {
      lastExitIdx = i
      runningQty = 0
    }
  }

  return lastExitIdx >= 0 ? lots.slice(lastExitIdx + 1) : lots
}

If the running quantity never reached zero - you have held the position continuously since the first buy - all lots are returned unchanged. If it reached zero at index i, only lots from i+1 onward are returned. Those lots represent the current position and nothing before it.

This function is called at snapshot time before computing per-holding XIRR, and again at portfolio XIRR computation before flattening all lots into a single cash flow series.

Next: XIRR from Scratch: Newton-Raphson on a Portfolio