Basis points on a moving balance: modeling asset-based fees

TL;DR

• A line like 20 bps per annum on assets under custody hides a dozen decisions: which balance, valued how, which day count, tiered or banded, and the order minimums and discounts apply in.
• Accrue daily even when billing is monthly, with effective-dated pricing schedule versions, so mid-month renegotiations and revenue recognition fall out naturally.
• Same client, same balances, same rate card: four defensible methods bill between $2,740 and $3,879 for one month. Only one of them matches the contract.
• Banded pricing has a cliff that clients will game; marginal tiers do not. And never let a missing price become a zero.
• Reconcile three numbers every period, the model fee, the invoiced fee and GL revenue, with a reason code for every difference and an unexplained column of zero.

Every asset-based fee starts as one line on a pricing sheet: “20 basis points per annum on assets under custody.” A basis point (bp) is a hundredth of a percent, so that is 0.20% a year. It looks like arithmetic. In practice it is a dozen small decisions, each of which moves real money on someone else’s invoice, and most of them are made implicitly by whoever wrote the first query.

I have spent a lot of time on this problem at the intersection of data modeling, pricing, accounting and client trust. This post is how I model it so that the fee on the invoice, the fee in the warehouse and the revenue in the general ledger are the same number, or differ for a reason I can name.

The deceptively simple formula

fee = rate_bps / 10,000 × average_daily_balance × days / day_count_basis

Every term hides a decision.

Which balance? End-of-day snapshot, time-weighted average, or something else, and at which cutoff and in which time zone. For digital assets the balance changes every block, so there is no natural end of day. A client who deposits $40M at 23:00 UTC and withdraws it at 01:00 UTC is a full day of custody under a midnight-UTC snapshot and two hours under time-weighting.

Valued how? Which price source, which fallback when it is missing, what to do with illiquid tokens, and whether stablecoins are valued at par or at market. These are pricing-policy questions, but the data team ends up encoding them.

Which days? The day-count convention: divide the year into 365 days, 360, or the actual number (actual/365, actual/360, actual/actual). In a leap year, actual/365 bills one extra day that nobody priced.

Which rate? Tiered (marginal, like income tax) or banded (the whole balance at one rate), possibly per asset class, possibly with negotiated overrides.

Minimums, caps and discounts, and the order they apply in.

None of these have a universal right answer. They have a contractual answer, and the model’s job is to implement the contract exactly, then make every assumption visible.

The model

I accrue daily even when billing is monthly. Revenue recognition needs daily accruals anyway, mid-period pricing changes are impossible to handle otherwise, and daily rows are where reconciliation differences become explainable.

fct_balance_valued        daily, per client × asset × wallet, with price provenance
   │  exclusions: fee-exempt assets, in-transit between own wallets, staked-but-excluded
   ▼
fct_fee_basis_daily       eligible balance per client × fee schedule × day
   │  × tier rates, ÷ day-count basis, schedule version effective on that day
   ▼
fct_fee_accrual_daily     one fee row per client × schedule × day
   │  sum over the period, then discounts, minimums, caps, credits
   ▼
fct_fee_invoice_period    what the client should be billed, and why

Pricing schedules are effective-dated and versioned, and each contract references a schedule version, never a copy of the rates. When a client renegotiates on the 15th, the first fourteen days accrue on the old version and the rest on the new one, with no special handling. The valued balance layer comes straight from a ledger-first architecture, so every dollar of fee traces back to ledger entries and a specific price.

A worked example: one client, one month

Take a toy client at a fictional custodian, on a toy schedule:

Tier	Balance range	Rate
1	$0 to $10M	20 bps
2	$10M to $50M	12 bps
3	above $50M	8 bps

The contract has a $1,500 monthly minimum and uses actual/365. In a 30-day month, the client’s eligible balance moves in three steps: $8M for days 1–10, $30M for days 11–20 after a large deposit, and $60M for days 21–30. The average daily balance is $32.67M.

Under marginal tiers, each slice of the balance gets its own rate. At $8M the annual fee is $16,000. At $30M it is $20,000 on the first $10M plus $24,000 on the next $20M, so $44,000. At $60M it is $20,000 + $48,000 + $8,000 = $76,000.

Under banded pricing, the whole balance takes the rate of the band it lands in: $8M × 20 bps = $16,000, $30M × 12 bps = $36,000, $60M × 8 bps = $48,000.

Then there is a second choice: apply the schedule to each day’s balance and sum, or apply it once to the month’s average balance. Contracts say both. Here is what each combination bills for the same month:

Method	Calculation	Monthly fee
Tiered, accrued daily	(16,000 + 44,000 + 76,000) × 10 / 365	$3,726.03
Tiered, on average balance	47,200 × 30 / 365	$3,879.45
Banded, accrued daily	(16,000 + 36,000 + 48,000) × 10 / 365	$2,739.73
Banded, on average balance	39,200 × 30 / 365	$3,221.92

Same client, same balances, same rate card: a $1,140 spread, more than 40% of the lowest figure. Tiered on the average beats tiered daily because the tier curve is concave: averaging the balance first means the high-balance days never get the cheapest rate on their own. None of these numbers is wrong. Only one of them is what the contract says.

Banded pricing also has a cliff that marginal tiers do not. At $49.9M the banded annual fee is $59,880. At $50.1M it is $40,080. Adding $200K of assets cuts the fee by almost $20,000 a year. Clients notice this, and sooner or later someone will park assets for a week to cross a band. If I get a vote on the rate card, I vote for marginal tiers.

The minimum did not bind this month. In a quiet month at a flat $5M, the tiered fee is $821.92, so the $1,500 minimum applies. Add a 10% negotiated discount and the order matters: discount first, then minimum, gives max($739.73, $1,500) = $1,500. Minimum first, then discount, gives $1,350. A $150 difference that recurs every quiet month, decided by the wording of one sentence.

Tiered and banded in SQL

I store the method on the schedule (tier_method = 'marginal' | 'banded') and compute both in one pass over the tier table. The top tier has upper_usd = null, which the arithmetic has to handle explicitly.

-- Daily fee accrual per client × schedule. Tiers are [lower_usd, upper_usd);
-- the top tier has upper_usd = null (unbounded).
with basis as (
  select
    b.client_id,
    b.d,
    b.schedule_id,
    b.basis_usd,                       -- NUMERIC, eligible balance after exclusions
    s.tier_method,                     -- 'marginal' or 'banded'
    s.day_count_basis                  -- 365 or 360
  from {{ ref('fct_fee_basis_daily') }} b
  join {{ ref('dim_pricing_schedule') }} s
    on s.schedule_id = b.schedule_id
   and b.d >= s.valid_from
   and (s.valid_to is null or b.d < s.valid_to)
),
priced as (
  select
    b.client_id,
    b.d,
    b.schedule_id,
    t.rate_bps,
    case b.tier_method
      when 'marginal' then greatest(
        least(b.basis_usd, coalesce(t.upper_usd, b.basis_usd)) - t.lower_usd,
        0)
      when 'banded' then if(
        b.basis_usd >= t.lower_usd
          and (t.upper_usd is null or b.basis_usd < t.upper_usd),
        b.basis_usd,
        0)
    end as charged_usd,
    b.day_count_basis
  from basis b
  join {{ ref('dim_pricing_tier') }} t
    on t.schedule_id = b.schedule_id
)
select
  client_id,
  d,
  schedule_id,
  sum(charged_usd * rate_bps / 10000 / day_count_basis) as fee_usd
from priced
group by client_id, d, schedule_id

Two details are worth calling out. If tier_method holds anything unexpected, charged_usd is null and the day’s fee is null rather than zero, so a not-null test catches it instead of a client getting a free day. And nothing is rounded here. Rounding happens once, at the invoice, at the step the contract specifies.

What goes wrong in practice

A missing price becomes a zero. Somewhere a coalesce(price, 0) turns a feed outage into a free day of custody. It is the single most expensive line of SQL in fee modeling. A missing price should fail the run or fall back to a documented source, never default to zero.

Cutoff mismatch. The balance snapshot is midnight UTC, the price is a 4 p.m. New York mark, and the invoice says “end of business day.” Each is reasonable alone; together they produce differences nobody can reproduce.

Late prices after the invoice went out. Prices get corrected, and a valuation restated after billing changes the model’s fee but not the invoice. The invoice is as-was; the corrected fee is as-of. Keep both, and book the difference as an adjustment instead of rewriting history. This is the bitemporal problem in its most visible form.

Schedule versions loaded late. A contract amended on the 15th but entered on the 30th produces two weeks of wrong accruals that look right. Track when the version was recorded as well as when it is effective.

Assets in transit counted twice. A transfer between two of the client’s own wallets can appear in both balances on the cutoff day if the ledger is not the source of truth for the snapshot.

Minimums at the wrong level. Is the minimum per account, per legal entity or per relationship? A client with three small accounts can owe three minimums or one. This depends on resolving who the client actually is, which is the subject of one customer, five systems.

Rounding daily. Rounding each day’s accrual to the cent drifts a few cents a month from the invoice. Harmless, but it makes every reconciliation report non-zero, and a report that is never zero stops being read.

Reconciliation is three numbers

For every client and period I want three figures side by side: the model fee, the invoiced fee, and the revenue booked in the general ledger. They should agree. When they do not, every difference gets a reason code in one table: manual credit, minimum applied, schedule change not yet loaded, price restated after billing, rounding. An unexplained difference is a bug until proven otherwise, and the goal is a reconciliation where the unexplained column is zero, every period.

This does more than catch errors. It is how you earn the right to automate billing from the model, because you can show finance a history where the model and the invoice matched, with every exception explained.

The invoice is the interface

Most data models are read by colleagues who will forgive a rounding quirk. A fee model is read by clients, on an invoice, and they check the math. That changes the standard. Every term in the formula needs a decision written down, every decision needs to match the contract, and every number on the invoice needs a path back to a ledger entry, a price and a schedule version. Get that right and fee disputes become a lookup. Get it wrong and you find out from the client.