How to automate funded-loan profitability reporting

Start with a signed population of funded loans, then reconcile loan-level revenue, direct cost, commissions, and approved overhead allocations against the finance ledger. Publish contribution and cost per funded loan with a separate register of entries the team cannot confidently match. That register shows why cohort totals can differ from the period ledger.

This guide uses a management reporting basis. The Controller owns accounting recognition, posting, and signoff. A lender's reported gain on sale can include components beyond one investor settlement, so a funded-loan profitability report needs an explicit bridge to the reporting basis Finance already uses. Rocket Companies annual report

Define the denominator first

Agree on the entity, channel, product, funding event, reporting period, and data cutoff with the Controller. One row in the cohort should represent one funded loan under that definition. Keep the loan's funding date, sale date, and ledger posting period as separate fields. Maintain an as-of version for a closed month and route later adjustments according to Finance's existing reopening and restatement policy.

What to automate

Freeze the funded cohort and report denominator

At the reporting cutoff, capture the authorized LOS export and accepted funding events. Use the lender’s agreed definition of a funded loan, entity perimeter, and date basis. Keep one current row per in-scope loan while retaining earlier events and amendments. Reconcile the count and principal against the approved funding control, then quarantine reversals, ambiguous revisions, and unexpected duplicates for the loan accountant. Record the cohort version so every downstream report uses the same denominator.

Reconcile revenue to funded loans and source totals

Join ledger and subledger lines to purchase advice and the frozen cohort using approved exact references first. Keep cash receipt and accrual components on their approved reporting basis and allow one payment to match several loans when the advice gives the split. Record partial and unmatched amounts with journal or settlement IDs. Give the loan accountant a bridge from the source control total to matched cohort revenue, separately identifying out-of-scope and unresolved entries.

Assemble direct costs and catch duplicate expense records

Bring together eligible ledger expenses, payroll, accounts payable, and commission schedules by entity and effective period. Apply the Controller’s account and loan matching rules. Retain reversal links and identify rows shown in both a supporting schedule and the ledger using their underlying source references. Send ambiguous matches and unsupported expenses to Accounting. Publish traceable direct costs for included loans and show unmatched or excluded costs in the reconciliation register.

Allocate approved overhead and reconcile each pool

Load cost pools and drivers with the approval, effective period, eligible population, and rounding policy. Apply the approved basis to each period and preserve the driver value beside the allocated amount. Sum the resulting rows back to the original pool, recording any rounding adjustment. Route empty populations, missing drivers, and unexplained differences to FP&A and the Controller. Keep the pool, rule, allocation rows, and residual in the workpapers.

Publish a reviewed profitability report and drill-down

Calculate contribution, profit after allocation, and cost per funded loan from the frozen cohort and accepted accounting lines. Bridge the included and excluded items to the appropriate source control totals and hold signoff on unexplained differences. Provide a report by approved dimensions and a loan-level drill-down to source entries. When the Controller approves the version, retain the underlying workbook or dataset, signed cutoff, and open exceptions with owners and disposition.

Separate matching, calculation, and approval

Build an event table for loan funding and sales and an accounting-line table for ledger and subledger entries. Preserve line identity, journal and account, posting date, entity, amount, classification, and source reference. Match approved exact identifiers first. A matching table should retain the rule version and allow one-to-many components, so commissions, corrections, and revenue lines remain traceable. Send ambiguous candidates to Finance with the underlying evidence.

Calculate contribution as matched cohort revenue less direct cost. Calculate profit after allocation as contribution less approved allocated overhead. For cost per funded loan, divide the included costs by the exact funded population. Record unmatched items and out-of-cohort activity in the reconciliation bridge, including why an entry was excluded and who owns the correction.

An assistant can propose an account mapping or summarize a variance using the reconciled records. Keep account treatment, policy, posting, and exception closure with Finance. Code should enforce the approved account map and allocation, including what happens if a pool has no eligible loan in a period.

Reproduce the result on a synthetic cohort

If the source revenue control total is $25,000, the bridge should show $24,000 matched and $1,000 unmatched. Test a commission appearing in both payroll and the ledger, a late reversal, and an investor payment covering more than one loan. The report should still tie to the approved source totals, with each recognized cost counted once and every unresolved row visible.

Validate with a closed month

Run alongside the current reporting process with the same period, entity, and cohort. Compare funded volume, matched and unmatched revenue, direct-cost detail, and allocated pools. Trace a normal loan and an open item from the final report back to source. Measure active time spent preparing, correcting, and reviewing the report separately from the elapsed time to publish. Hold changes to the reported basis for Finance approval.

Build a traceable loan-to-ledger calculation

1. Freeze a reporting cohort. Write cohort(period, loan_id, funding_event_id, entity, product, channel, principal_cents, cohort_version) after the funding system and close owner accept the count and amount. Keep reversals as linked events. Treat late fundings after cutoff as a restatement or later-period adjustment under the controller’s policy.
2. Classify ledger lines before allocation. Use journal_id, line_id, entity, close_period, account, currency, signed_cents as the journal lineage. Map loan-level revenue and direct costs separately from allocable overhead and excluded accounts. Keep a reason code and approver on each manual match.
3. Bridge at source grain. Before rounding or aggregation, require matched cohort lines plus timing differences plus out-of-scope and unresolved lines to equal the selected GL control balance. Partial matches may allocate a journal line across loans only when the allocation sums to the original line and the controller approves the driver.
4. Allocate with reproducible rounding. Calculate weights on an approved denominator at its effective period. Use integer cents, assign residual cents by largest fractional remainder with a stable loan-ID tie break, and verify exact sum to the pool. An empty eligible denominator is a hold.
5. Expose the drill-through. Publish one signed period × loan × close_version record. Attach the journal IDs, source versions, allocation rules and open exceptions so finance can regenerate the report.

Reconcile the synthetic cohort in SQL

Run in SQLite. Dollar values are whole numbers for readability. Actual control tables should use minor units and separate currency.

WITH loans(loan_id, revenue, direct_cost, overhead) AS (
  VALUES ('A100', 14000, 8000, 2400), ('A200', 10000, 6000, 1600)
),
controls(gl_revenue, unassigned_revenue, overhead_pool) AS (
  VALUES (25000, 1000, 4000)
)
SELECT count(*) AS funded_loans,
       sum(revenue - direct_cost) AS contribution,
       sum(revenue - direct_cost - overhead) AS profit,
       sum(direct_cost + overhead) * 1.0 / count(*) AS cost_per_loan,
       sum(revenue) + unassigned_revenue = gl_revenue AS revenue_ties,
       sum(overhead) = overhead_pool AS allocation_ties
FROM loans CROSS JOIN controls;

The row should be 2 | 10000 | 6000 | 9000.0 | 1 | 1. Inject a duplicate commission and an unapproved reversal before acceptance. Both must be detected before they change the signed cohort figures.

Implement the work step by step

Key the funded cohort and finance events by entity and loan, tie direct and allocated amounts to the close, then publish under a dated report version. Keep unmatched amounts visible in the reconciliation. Apply the controller's revision policy to late source corrections.

FA-01 · Accept loan data from Operations

• FA-01.01 Join authorized LOS and finance extracts by loan, entity and channel. Join with legal entity, loan ID, channel and approved version. Read Authorized LOS and finance extracts, funding/sale events and entity/channel map. Record Joined loan event register. Owner Loan Accounting accepts cohort. Source owners fix the originating records. Check Different entities with identical loan IDs remain separate.
• FA-01.02 Validate status, event version, freshness, uniqueness and totals. Validate current status, freshness, uniqueness and principal control totals. Read Authorized LOS and finance extracts, funding/sale events and entity/channel map. Record Signed accepted and excluded totals. Owner Loan Accounting accepts cohort. Source owners fix the originating records. Check Duplicate and late event test reconciles counts and balances.
• FA-01.03 Quarantine failed rows. Write reject rows with check version and source pointers. Read Authorized LOS and finance extracts, funding/sale events and entity/channel map. Record Quarantined exceptions. Owner Loan Accounting accepts cohort. Source owners fix the originating records. Check Invalid status cannot enter the accepted funded cohort.
• FA-01.04 Assign source owner. Route each exception to the approved source owner. Read Authorized LOS and finance extracts, funding/sale events and entity/channel map. Record Owned remediation ticket. Owner Loan Accounting accepts cohort. Source owners fix the originating records. Check Expired escalation is surfaced without losing original owner.
• FA-01.05 Recheck approved resubmission. Rerun approved corrected events against the same controls. Read Authorized LOS and finance extracts, funding/sale events and entity/channel map. Record Rechecked and versioned event. Owner Loan Accounting accepts cohort. Source owners fix the originating records. Check Late correction produces a new result without mutating closed output.

FA-02 · Prepare loan-level accounting

• FA-02.01 Map funded principal and approved charges to GL dimensions. Apply approved effective-dated mapping by entity and loan event. Read Accepted events, GL dimension map, charges, accounting rules and prior posting keys. Record Proposed GL dimensions. Owner Controller approves journal policy and adjustments. Check Missing dimension prevents silent default.
• FA-02.02 Prepare disbursement, fee and accrual entries. Construct draft disbursement, fee and accrual entries from approved calculation. Read Accepted events, GL dimension map, charges, accounting rules and prior posting keys. Record Prepared journal support. Owner Controller approves journal policy and adjustments. Check Same idempotency key cannot create a second draft.
• FA-02.03 Validate debits, credits, posting dates and prior entries. Check balanced entries, open period, and prior postings. Read Accepted events, GL dimension map, charges, accounting rules and prior posting keys. Record Passed journal validation or exception. Owner Controller approves journal policy and adjustments. Check Duplicate and unbalanced examples fail.
• FA-02.04 Reconcile the posting batch and route adjustments for approval. Reconcile the batch to the subledger and route adjustments for approval. Read Accepted events, GL dimension map, charges, accounting rules and prior posting keys. Record Approved posting support or owned variance. Owner Controller approves journal policy and adjustments. Check Controller must approve corrected ledger treatment.

FA-03 · Reconcile investor sale settlements

• FA-03.01 Match purchase advice, loan and commitment to confirmed cash and prior accruals. Join advice to loan and commitment, cash receipt and accrual with exact references. Read Purchase advice, confirmation, commitment, bank cash and prior accruals. Record Linked settlement components. Owner Loan Accounting owns settlement tie-out. Treasury confirms cash and Capital Markets confirms terms. Check One bank receipt can link to approved multiple advice rows without duplication.
• FA-03.02 Compare principal, sale price, fees and deductions. Compare principal, sale price, charges and deductions at line level. Read Purchase advice, confirmation, commitment, bank cash and prior accruals. Record Explained settlement variance. Owner Loan Accounting owns settlement tie-out. Treasury confirms cash and Capital Markets confirms terms. Check Equal net amounts with different components still flag a break.
• FA-03.03 Investigate partial or unmatched payments. Identify partial or unmatched payment components and source evidence. Read Purchase advice, confirmation, commitment, bank cash and prior accruals. Record Owned open settlement item. Owner Loan Accounting owns settlement tie-out. Treasury confirms cash and Capital Markets confirms terms. Check Partial payment remains open until covered or accepted.
• FA-03.04 Tie cash with Treasury and terms with Capital Markets. Send cash discrepancy to Treasury and terms discrepancy to Capital Markets. Read Purchase advice, confirmation, commitment, bank cash and prior accruals. Record Source-owner acknowledgements. Owner Loan Accounting owns settlement tie-out. Treasury confirms cash and Capital Markets confirms terms. Check No cross-team correction changes the ledger without acceptance.
• FA-03.05 Route adjustments. Prepare candidate adjustment, route and retain disposition. Read Purchase advice, confirmation, commitment, bank cash and prior accruals. Record Approved adjustment or unresolved item. Owner Loan Accounting owns settlement tie-out. Treasury confirms cash and Capital Markets confirms terms. Check Unapproved adjustment is excluded from signed actuals.

FA-04 · Reconcile warehouse and bank accounting

• FA-04.01 Join facilities, draws, repayments, fees, bank and GL by facility, loan and date. Join facility and bank movements to ledger with loan/date identifiers. Read Bank transactions, GL, warehouse statements, facility and loan keys. Record Bank and warehouse-to-GL bridge. Owner Loan Accounting approves ledger tie-out. Treasury owns cash/facilities. Check Same transaction is counted only once.
• FA-04.02 Compare outstanding balances. Compare outstanding principal and cash by entity, facility and cutoff. Read Bank transactions, GL, warehouse statements, facility and loan keys. Record Balance reconciliation. Owner Loan Accounting approves ledger tie-out. Treasury owns cash/facilities. Check Cross-entity row does not clear a control.
• FA-04.03 Investigate carry and accrual breaks. Decompose interest/carry and accrual difference with policy versions. Read Bank transactions, GL, warehouse statements, facility and loan keys. Record Explained warehouse carry exception. Owner Loan Accounting approves ledger tie-out. Treasury owns cash/facilities. Check Offsetting errors remain separate.
• FA-04.04 Confirm correction with the facility owner and retain workpapers. Record Treasury-confirmed correction and preserve workbook and source references. Read Bank transactions, GL, warehouse statements, facility and loan keys. Record Closed or owned outstanding reconciliation. Owner Loan Accounting approves ledger tie-out. Treasury owns cash/facilities. Check Original and corrected schedules both reproducible.

FA-05 · Account for commissions and operating costs

• FA-05.01 Apply versioned commission splits, tiers, reversals and cost centers. Apply effective-dated split, tier, reversal and cost-center rules. Read Payroll, AP, funded cohort, approved commission rules and GL cost centers. Record Attributed commission and direct-cost lines. Owner Payroll and accounting approve compensation treatment. Controller approves close exceptions. Check Reverse same original commission under approved treatment.
• FA-05.02 Reconcile commissions to payroll, expenses to AP and allocations to the GL. Reconcile source schedules to payroll, AP and GL. Dedupe supporting records. Read Payroll, AP, funded cohort, approved commission rules and GL cost centers. Record Cost pool and payroll bridge. Owner Payroll and accounting approve compensation treatment. Controller approves close exceptions. Check Ledger and supporting schedule cannot double count one amount.
• FA-05.03 Document overrides and signoff. Store exception reason, approved override and signoff. Read Payroll, AP, funded cohort, approved commission rules and GL cost centers. Record Signed expense support and open items. Owner Payroll and accounting approve compensation treatment. Controller approves close exceptions. Check Unapproved override cannot enter published costs.

FA-06 · Close the period

• FA-06.01 Freeze or version source windows. Freeze source versions using approved period and close timestamp. Read Closed-period calendar, subledger, GL trial balance, adjustments and controls. Record Reproducible period inputs. Owner Controller signs close and decides reopening or restatement. Check Late arrival cannot mutate signed snapshot.
• FA-06.02 Reconcile subledgers and control accounts. Run subledger-to-control-account reconciliation. Read Closed-period calendar, subledger, GL trial balance, adjustments and controls. Record Tie-out pack with open breaks. Owner Controller signs close and decides reopening or restatement. Check Missing opening or closing balance fails.
• FA-06.03 Review accruals, adjustments and unresolved items. Stage adjustment candidates and unresolved items for decision. Read Closed-period calendar, subledger, GL trial balance, adjustments and controls. Record Approved or deferred adjustment register. Owner Controller signs close and decides reopening or restatement. Check Closed item preserves review evidence.
• FA-06.04 Tie trial balance and approved reports. Tie trial balance and management views on signed definitions. Read Closed-period calendar, subledger, GL trial balance, adjustments and controls. Record Controller reconciliation. Owner Controller signs close and decides reopening or restatement. Check Out-of-basis items are disclosed in a bridge.
• FA-06.05 Publish signed actuals and retain evidence. Publish authorized actuals and retain signed data, controls and version. Read Closed-period calendar, subledger, GL trial balance, adjustments and controls. Record Signed period baseline for FP&A and reporting. Owner Controller signs close and decides reopening or restatement. Check Unapproved or unauthorized read denied.

FA-07 · Measure loan and branch profitability

• FA-07.01 Define cohort and dimensions. Version funded event, period, entity, product and channel definitions. Read Funded cohort, posted revenue and expense lines, commission and approved overhead policy. Record Signed funded cohort denominator. Owner Controller owns allocation and recognition. FP&A accepts report basis. Check Sold and funded periods stay distinct.
• FA-07.02 Match loan revenues and direct production costs. Match approved identifiers and approved one-to-many accounting components. Read Funded cohort, posted revenue and expense lines, commission and approved overhead policy. Record Matched cohort revenue and direct costs. Owner Controller owns allocation and recognition. FP&A accepts report basis. Check Do not duplicate the same ledger line.
• FA-07.03 Allocate overhead using a versioned approved basis. Allocate eligible pool with approved weights and retained residual. Read Funded cohort, posted revenue and expense lines, commission and approved overhead policy. Record Versioned overhead bridge. Owner Controller owns allocation and recognition. FP&A accepts report basis. Check Allocation sums to its pool with zero-eligible-case handling.
• FA-07.04 Retain unmatched records with owners. Retain unmatched or excluded lines, amount, reason and owner. Read Funded cohort, posted revenue and expense lines, commission and approved overhead policy. Record Open exception register. Owner Controller owns allocation and recognition. FP&A accepts report basis. Check $1k unmatched remains visible beside $24k matched.
• FA-07.05 Calculate contribution, profit and cost per loan. Compute contribution, allocated profit and cost per loan on signed cohort. Read Funded cohort, posted revenue and expense lines, commission and approved overhead policy. Record Loan and grouped profitability report. Owner Controller owns allocation and recognition. FP&A accepts report basis. Check Synthetic two-loan example yields $6k profit and $9k cost per funded loan.
• FA-07.06 Trace totals to approved sources. Reconcile line items and group totals to source and retain drill-through. Read Funded cohort, posted revenue and expense lines, commission and approved overhead policy. Record Accepted report and control bridge. Owner Controller owns allocation and recognition. FP&A accepts report basis. Check Trace funded count and one exception to exact source rows.

FA-09 · Publish management and applicable external reports

• FA-09.01 Maintain report definitions, recipients and authority. Version report definitions, approved audience and publication cutoff. Read Signed actuals or approved management data, reporting definitions and access rules. Record Report contract and access control list. Owner Controller/FP&A approve release. Reporting owners verify recipient authority. Check Unknown recipient cannot be included.
• FA-09.02 Generate exports from signed actuals or documented management data. Render report from approved close or management cohort version. Read Signed actuals or approved management data, reporting definitions and access rules. Record Reviewable management package. Owner Controller/FP&A approve release. Reporting owners verify recipient authority. Check Version ID travels with every export.
• FA-09.03 Reconcile totals and controls. Compare report totals against controlled basis and approved bridge. Read Signed actuals or approved management data, reporting definitions and access rules. Record Passed publication control or explicit hold. Owner Controller/FP&A approve release. Reporting owners verify recipient authority. Check Missing exception schedule holds release.
• FA-09.04 Record approvals, receipt and access restrictions. Record signoff and delivery receipts against authorized audience. Read Signed actuals or approved management data, reporting definitions and access rules. Record Authorized delivery history. Owner Controller/FP&A approve release. Reporting owners verify recipient authority. Check Retry does not send duplicate report.
• FA-09.05 Retain output versions. Archive export, run manifest and version with approved retention. Read Signed actuals or approved management data, reporting definitions and access rules. Record Reproducible report record. Owner Controller/FP&A approve release. Reporting owners verify recipient authority. Check Later correction receives a new version.

Build it yourself with AI

Rebuild one closed month from synthetic exports

Start with local funded_loans.csv, ledger_lines.csv, purchase_advice.csv, commission_schedule.csv, and overhead_pools.csv. Write an accounting_rules.yml with the Controller-approved population, account mapping, exact identifiers, duplicate policy, and allocation driver. Use fabricated entries while developing and store approval decisions separately from model output.

Prompt 1. Draft a traceable matching plan

Read the input schemas and accounting_rules.yml. Propose a matching table that supports partial payments, one purchase advice covering several loans, reversals, and a commission present in both the ledger and a schedule. Show which source keys justify each automatic match and route uncertain mappings to exceptions.csv. Ask for approval on ambiguous policies.

Prompt 2. Generate a reproducible close script

Build a local Python or SQL command that freezes one funded cohort, reconciles approved revenue and cost mappings, applies the approved overhead allocation, and writes profitability.csv, the source-control bridge, and exceptions.csv. Keep line IDs and rule versions. Assert each allocated pool balances and each duplicate expense contributes once. Use no accounting-system write access.

Prompt 3. Create the close and signoff playbook

Draft a Controller review checklist from the generated outputs. Trace one loan and one unmatched line to its source ID and show the calculation of cost per funded loan. For every open item, return its amount, reason, owner, and where to record disposition. Leave accounting treatment, journals, and publication for the Controller.

Try it. Use two funded loans, $24,000 of matched revenue, $14,000 of direct cost, and $4,000 of allocated overhead. Include $1,000 of unmatched revenue in the source total. Expect $6,000 of profit, $9,000 of cost per funded loan, and a $25,000 revenue bridge made up of $24,000 matched and $1,000 unresolved. Add a duplicate commission from the supporting schedule and confirm costs stay at $14,000. Have Accounting reconcile the same cohort and period against the incumbent close process before sharing the report.

Need help automating this?

See how Mage brings funded-loan revenue, direct costs and approved allocations together for finance review. See Mage's loan profitability reporting solution.