Indirect cost recovery reconciliation

Every dollar of facilities and administrative (F&A) cost an institution recovers on a federal award is derived, not billed. The sponsor never sends an indirect-cost invoice; the institution computes what it is owed by applying its negotiated rate to a specific slice of the award’s direct spending, and the accounting system posts that number as an indirect charge. When the posted charge and the computed entitlement drift apart — because an equipment purchase was left in the base, a subaward was recovered on in full, or a rate change took effect mid-project — the institution either over-recovers (a finding waiting to happen in the next single audit) or under-recovers (real money left on the table). This guide builds the reconciliation layer that recomputes the entitlement independently and reconciles it against what the ledger actually posted. It is one of the reporting subsystems anchored to the parent guide on Compliance Reporting & Budget Reconciliation, and it consumes the validated, idempotent grant records produced upstream by Core Architecture & Policy Mapping for Research Grants.

University administrators, sponsored-programs accountants, research compliance officers, and Python automation developers use this subsystem to turn indirect-cost recovery from a spreadsheet reconstruction done once a quarter into a deterministic, re-runnable check. The two how-tos beneath this guide drill into the mechanics: Reconciling F&A Indirect-Cost Recovery Rates in Python computes the expected recovery from a modified total direct cost base and flags variance, and Detecting Cost Transfer Anomalies in Grant Ledgers catches the late and unallowable journal entries that quietly distort that base.

Problem framing #

Indirect-cost recovery goes wrong in quiet, mechanical ways. A lab buys a USD 42,000 confocal microscope and, because the object code was miscategorized as a supply, it stays in the base and pulls a full rate of indirect onto a capital asset that 2 CFR 200.1 explicitly excludes. A USD 300,000 subaward is set up, and the ledger recovers F&A on the whole amount instead of only the first USD 25,000. A multi-year award crosses the boundary where the institution’s negotiated rate steps from 54.5% to 56.0%, and every posting after that date is computed on the wrong rate because nobody prorated. None of these are fraud; they are the default behavior of a system that treats indirect cost as a percentage applied to “total spending” rather than to the precisely defined modified total direct cost base.

The reconciliation layer exists to catch that drift independently. Rather than trusting the ledger’s indirect posting, it recomputes the entitlement from first principles — direct costs, exclusions, base, rate, effective period — and compares. Three contracts hold the design together and are implemented in the rest of this guide:

• Independence. The expected indirect amount is computed from the direct-cost detail and the negotiated rate agreement, never read back from the posted indirect charge it is meant to check. A reconciliation that derives its answer from the thing under test proves nothing.
• Determinism and idempotency. The same award detail and the same rate agreement produce the same expected amount, the same variance, and the same status on every run. Records are keyed and upserted, so re-reconciling a period never doubles a ledger row.
• Flag, never correct. A variance beyond tolerance is written to a reconciliation ledger with a status and routed to a quarantine for a human. The layer does not silently adjust the base, post a correcting entry, or move money; it surfaces the discrepancy for an accountant.

Policy constraints #

Indirect-cost recovery is one of the most tightly regulated computations in federal grants accounting, and the reconciliation logic is a direct encoding of that regulation rather than an institutional convention. The rules that bound this workflow come from the Uniform Guidance and the institution’s own negotiated agreement; the broader regulatory matrix these map into is maintained in the University Policy Mapping Frameworks.

Operational boundary. Policy dictates what belongs in the base, which rate applies, and when — the negotiated agreement’s effective dates are not negotiable at reconciliation time. Implementation handles only the mechanical filtering, multiplication, comparison, and routing. The reconciler must never widen the base to make a posted charge reconcile, never round in the institution’s favor, and never apply a rate outside its effective window to close a gap. When a rate agreement is being renegotiated and a period is uncovered, postings in that gap are quarantined rather than reconciled against a guessed rate. Credential scoping and network isolation for the reconciliation workers follow the Security Boundary Configuration.

Data schema & field mapping #

The reconciliation record is a single flat row per award-period that carries the entire derivation, so an auditor can read the base, the rate, the effective window, the expected amount, the posted amount, and the resulting status without re-deriving anything. Every field is either mapped from the source ledger or computed deterministically from mapped fields; nothing is hand-entered.

The idempotency key is the pair (award_id, rate_effective_period): a single award that spans two rate periods produces two reconciliation rows, and re-reconciling either period updates its row in place rather than inserting a duplicate. Money is carried as Decimal, never float, so a base of 247_500.00 multiplied by 0.545 does not round to a cent that fails to match the posted charge.

Implementation #

The reconciler has four composable stages: an exclusion filter that turns a direct-cost total into an MTDC base, a rate lookup that resolves the negotiated rate for a posting’s effective period, an expectation-versus-posted comparison that produces a signed variance and a status, and an idempotent upsert that writes the row to the reconciliation ledger while routing beyond-tolerance rows to quarantine.

Building the MTDC base #

The exclusion filter is the compliance heart of the layer. Each exclusion is a named rule so that an auditor reading the code sees exactly which line of 2 CFR 200.1 removed which dollar. The subaward rule is the subtle one: only the first USD 25,000 of each subaward stays in the base, and the rule is applied per subaward, not once across the award.

from decimal import Decimal, ROUND_HALF_UP
from dataclasses import dataclass

EQUIPMENT_THRESHOLD = Decimal("5000.00")   # 2 CFR 200.1 capital equipment floor
SUBAWARD_INCLUDED = Decimal("25000.00")    # first 25k of each subaward stays in base


@dataclass(frozen=True)
class DirectLine:
    """One direct-cost line already classified by object category."""
    category: str          # 'equipment' | 'subaward' | 'participant_support' | ...
    amount: Decimal


def mtdc_exclusions(lines: list[DirectLine]) -> Decimal:
    """Sum the amount excluded from the MTDC base per 2 CFR 200.1.

    Deterministic: the same lines always yield the same excluded total.
    """
    excluded = Decimal("0.00")
    for line in lines:
        if line.category == "equipment" and line.amount > EQUIPMENT_THRESHOLD:
            excluded += line.amount                       # whole capital item excluded
        elif line.category == "subaward":
            # Only the remainder OVER the first 25k of THIS subaward is excluded.
            excluded += max(Decimal("0.00"), line.amount - SUBAWARD_INCLUDED)
        elif line.category in {
            "participant_support", "tuition_remission",
            "scholarship", "rent_space", "capital_expenditure",
        }:
            excluded += line.amount                       # fully excluded categories
    return excluded


def mtdc_base(lines: list[DirectLine]) -> Decimal:
    direct_total = sum((l.amount for l in lines), Decimal("0.00"))
    return direct_total - mtdc_exclusions(lines)

Resolving the rate and computing expectation #

A multi-year award crosses rate-agreement boundaries, so the negotiated rate is never a constant — it is a function of the posting’s effective period. The lookup rejects a posting whose date falls in no covered period rather than silently reusing the nearest rate.

from datetime import date


@dataclass(frozen=True)
class RatePeriod:
    negotiated_rate: Decimal
    effective_start: date
    effective_end: date


def resolve_rate(posting_date: date, agreement: list[RatePeriod]) -> RatePeriod | None:
    """Return the rate period covering posting_date, or None if uncovered.

    A None result must route to quarantine — never fall back to a default rate,
    which would compute an entitlement the NICRA does not authorize.
    """
    for period in agreement:
        if period.effective_start <= posting_date <= period.effective_end:
            return period
    return None


def expected_indirect(base: Decimal, rate: Decimal) -> Decimal:
    """Deterministic F&A entitlement per 2 CFR 200.414, rounded to cents."""
    return (base * rate).quantize(Decimal("0.01"), rounding=ROUND_HALF_UP)

Comparing and upserting to the reconciliation ledger #

The comparison produces a signed variance and a status, and the upsert commits the record on the (award_id, rate_effective_period) key so a re-run updates in place. A variance whose absolute value exceeds the tolerance is both written to the ledger and handed to the quarantine callback — the ledger keeps the immutable record of what was found, and the quarantine routes it to a human.

import logging
from sqlalchemy.dialects.postgresql import insert as pg_insert
from sqlalchemy.orm import Session

logger = logging.getLogger("indirect_reconciler")

TOLERANCE = Decimal("0.50")   # cents of drift accepted before flagging


def classify(variance: Decimal) -> str:
    if abs(variance) <= TOLERANCE:
        return "reconciled"
    return "over_recovered" if variance > 0 else "under_recovered"


def reconcile_award(
    award_id: str,
    lines: list[DirectLine],
    posted_indirect: Decimal,
    posting_date: date,
    agreement: list[RatePeriod],
    session: Session,
    quarantine,           # callable: (record: dict) -> None
) -> dict:
    period = resolve_rate(posting_date, agreement)
    base = mtdc_base(lines)
    excluded = mtdc_exclusions(lines)

    if period is None:
        record = {
            "award_id": award_id,
            "rate_effective_period": "UNCOVERED",
            "direct_cost": sum((l.amount for l in lines), Decimal("0.00")),
            "excluded_amount": excluded,
            "mtdc_base": base,
            "negotiated_rate": Decimal("0"),
            "expected_indirect": Decimal("0.00"),
            "posted_indirect": posted_indirect,
            "variance": posted_indirect,
            "status": "uncovered_period",
        }
        quarantine(record)
        _upsert(record, session)
        return record

    rate_key = f"{period.effective_start.isoformat()}/{period.effective_end.isoformat()}"
    expected = expected_indirect(base, period.negotiated_rate)
    variance = (posted_indirect - expected).quantize(Decimal("0.01"))
    status = classify(variance)

    record = {
        "award_id": award_id,
        "rate_effective_period": rate_key,
        "direct_cost": sum((l.amount for l in lines), Decimal("0.00")),
        "excluded_amount": excluded,
        "mtdc_base": base,
        "negotiated_rate": period.negotiated_rate,
        "expected_indirect": expected,
        "posted_indirect": posted_indirect,
        "variance": variance,
        "status": status,
    }
    if status != "reconciled":
        logger.warning("indirect variance award=%s variance=%s status=%s",
                       award_id, variance, status)
        quarantine(record)
    _upsert(record, session)
    return record


def _upsert(record: dict, session: Session) -> None:
    """Idempotent write keyed on (award_id, rate_effective_period)."""
    stmt = pg_insert(ReconciliationRecord).values(**record)
    stmt = stmt.on_conflict_do_update(
        index_elements=["award_id", "rate_effective_period"],
        set_={k: stmt.excluded[k] for k in (
            "direct_cost", "excluded_amount", "mtdc_base", "negotiated_rate",
            "expected_indirect", "posted_indirect", "variance", "status",
        )},
    )
    session.execute(stmt)
    session.commit()

The upsert’s on_conflict_do_update on the composite key is what makes the reconciliation ledger idempotent: reconciling a period twice with unchanged inputs produces one row with identical values, a corrected posted charge updates the same row and re-derives the variance, and a genuinely new award-period inserts once. Because the derived fields are all recomputed from Decimal inputs, the second run’s variance is bit-for-bit identical to the first.

Integration points #

The reconciler sits downstream of ingestion and upstream of the audit deliverables, and it never writes to the source financial ledger — it reads posted charges and writes an independent reconciliation ledger that adjacent systems consume by key.

• ERP / financials. The reconciler reads direct_cost detail and the posted_indirect charge from the ERP by award_id, and writes back nothing. Any correcting entry is a human action in the ERP, driven by the quarantined record, never an automated posting.
• Effort and personnel reconciliation. Salary and wage lines that enter the direct-cost base are the same lines certified through Effort Reporting & Certification, so an uncertified salary charge that later reverses will move the base and re-open a reconciliation.
• Audit deliverables. Reconciled and flagged rows are the raw material for the immutable reports built in Immutable Audit Report Generation; the reconciliation ledger is what a sponsor’s auditor pins an F&A finding back to.

An example record routed to quarantine for an over-recovery caused by equipment left in the base:

{
  "award_id": "NS-2025-004417",
  "rate_effective_period": "2025-07-01/2026-06-30",
  "direct_cost": "247500.00",
  "excluded_amount": "0.00",
  "mtdc_base": "247500.00",
  "negotiated_rate": "0.545",
  "expected_indirect": "134887.50",
  "posted_indirect": "157782.50",
  "variance": "22895.00",
  "status": "over_recovered"
}

Here the excluded_amount of zero is the tell: a USD 42,000 microscope was never filtered out, so the base is USD 42,000 too high and the posted indirect over-recovered by exactly the rate applied to that equipment.

Verification & audit #

The reconciliation ledger is append-and-update on a stable key, and every derived value can be recomputed from the stored inputs, so any reconciliation run can be independently reproduced.

1. Base parity. For every row, direct_cost - excluded_amount must equal the stored mtdc_base. A mismatch means the base was written from a different input set than the exclusion sum — a defect, not an accepted state.
2. Expectation reproducibility. Recompute round(mtdc_base * negotiated_rate, 2) and confirm it equals the stored expected_indirect. Because both are Decimal, the recomputation must be exact.
3. Variance sign discipline. Confirm variance = posted_indirect - expected_indirect and that the status matches the sign: positive beyond tolerance is over_recovered, negative is under_recovered, within tolerance is reconciled.
4. Idempotency dry-run. Re-run the reconciler over the same period; the row count must not change and every field must be identical. A changed field on a second pass means an input is non-deterministic.

from sqlalchemy import select


def verify_period(session: Session, period: str) -> dict[str, int]:
    rows = session.execute(
        select(ReconciliationRecord).where(
            ReconciliationRecord.rate_effective_period == period
        )
    ).scalars().all()
    flagged = sum(1 for r in rows if r.status != "reconciled")
    return {"rows": len(rows), "flagged": flagged}

Reconciliation ledgers supporting F&A recovery are financial records subject to the federal retention period; retain them for at least three years after the final financial report is submitted, per 2 CFR 200.334, with checksums applied so a stored row cannot be altered without detection.

Failure modes & recovery #

Every recovery path is idempotent-safe: correcting the input and re-reconciling updates the existing row rather than creating a second one.

Role boundaries. Sponsored-programs accountants own the negotiated rate agreement and the tolerance, and they adjudicate every quarantined variance; they do not edit reconciliation code. Python automation developers own determinism, idempotency, and routing; they do not change the rate or the exclusion rules. Compliance officers own the retention and audit posture. When a rate agreement is under renegotiation, postings in the uncovered window stay in quarantine until the new NICRA is executed, and extended source-system outages divert acquisition through the Fallback Routing Protocols.

Frequently asked questions #

Related #

• Parent guide: Compliance Reporting & Budget Reconciliation
• Reconciling F&A Indirect-Cost Recovery Rates in Python — computing expected recovery and flagging variance step by step
• Detecting Cost Transfer Anomalies in Grant Ledgers — catching the transfers that distort the base
• Immutable Audit Report Generation — the audit deliverables this ledger feeds
• Effort Reporting & Certification — certifying the salary lines that enter the base
• University Policy Mapping Frameworks — the regulatory matrix these rules encode