💵
$772K+
Daily Wire
📊
59,812
Transactions/Day
🏠
32,292
Active Loans
✓
17
Validation Checks
📑
15
Worksheets
🎯

What is Reconciliation?

Understanding the fundamental purpose

Reconciliation verifies that two sets of records match. For LendingPoint, CCBℹ Coastal Community Bank (CCB) The bank of record for LendingPoint loans. CCB holds the underlying loan accounts while LendingPoint originates and services them. acts as the bank of record for consumer and commercial loans. LendingPoint originates and services the loans, but CCB holds the underlying accounts. Every day, payments flow between borrowers, LendingPoint, and CCB. Reconciliation ensures every dollar is accounted for.

💡 The Core Question

At the end of each day, does the cash in our settlement accountℹ Settlement Account Bank account 1600020521 where wire transfers are received and payments processed. This is the central clearing account for all LendingPoint transactions. Account: 1600020521 match what the transactions say should be there? If not, why not, and how do we fix it?

📥 Money Coming In

Wire transfers from LendingPoint containing borrower payments ( Principalℹ Principal Payment The portion of a loan payment that reduces the outstanding loan balance. Pymt_File Column G: Principal , Interestℹ Interest Payment The portion of a loan payment that covers the cost of borrowing. Pymt_File Column H: Interest , Feesℹ Fee Payment Any additional fees charged on the loan (late fees, service fees, etc.). Pymt_File Column I: Fees )

📤 Money Going Out

Loan payments applied to accounts, refunds to borrowers, fees to service providers

⚖️ The Balance

After matching all ins and outs, the settlement account should balance to zero (or a known outstanding amount)

🔀

Two Reconciliation Streams

Different processes for different aspects

💵 Repayments Settlement (1600020521)

Tracks the daily wire transfers that settle borrower payments. File: DAILY LendingPoint Repayment workbook

📈 Purchase Parse (Macro Workbook)

Processes loan-level payment data, generates ATEℹ Accounting Template Entry (ATE) A formatted journal entry ready for posting to the General Ledger. Contains account numbers, amounts, and transaction codes. entries, tracks charge-offs. File: Macro Purchase Parse workbook

🔗 How They Connect

Repayments reconciles the cash (did we get the right wire?), while Purchase Parse reconciles the details (which loans did payments apply to?). Both must agree.

💡

Key Concepts

Essential terminology and definitions

📊 Day Tapes

Day1_Tape: Yesterday's loan snapshot (32,292 loans). Day2_Tape: Today's snapshot. Comparing reveals changes in loan balances, status, and delinquency.

🔢 Transaction Prefixes

LPT-ℹ Loan Payment Transaction Regular payments from borrowers. The most common transaction type. Pymt_File Column A: Txn_ID : Loan Payment Transaction
RTA-ℹ Return Transaction Payment reversals/returns when a payment bounces or fails (e.g., NSF check). Pymt_File Column A: Txn_ID : Return Transaction
ADJ-ℹ Adjustment Transaction Corrections, refunds, spread corrections, and other manual adjustments. Pymt_File Column A: Txn_ID : Adjustment

📦 Product Types

Consumer: DTCℹ Direct-to-Consumer (DTC) Personal loans originated directly to individual borrowers through LendingPoint's platform. Pymt_File Column N: Loan_Product_Type + POSℹ Point of Sale (POS) Consumer loans originated at retail point of sale for purchases. Pymt_File Column N: Loan_Product_Type
Commercial: eBay + JOOR

⏱️ Days Past Due (DPD) Thresholds

120 DPDℹ 120+ Days Past Due CCB internal charge-off threshold. Loans enter the 120Subledger for tracking. Day_Tape Column K: Days_Past_Due : CCB internal charge-off
180 DPDℹ 180+ Days Past Due LendingPoint official charge-off. The Charge_Off_Date field gets populated. Day_Tape Column T: Charge_Off_Date : LendingPoint official charge-off

💰 Excess Payments

When a borrower pays more than the amount due. These overpaymentsℹ Excess Payment Amount The amount by which a payment exceeds what was owed. Held in settlement until refunded to borrower. Pymt_File Column J: Excess are held in the settlement account until refunded.

📏 Out of Balance (OOB)

Penny roundingℹ Out of Balance (OOB) Small discrepancy where Principal + Interest ≠ Total Transaction Amount due to rounding. Typically ±$0.01 per transaction. Maximum $0.09/payment. Pymt_File Column AA: OOB where Principal + Interest ≠ Total. Maximum $0.09/payment. Auto-adjusted via the OOB_Filter worksheet.

🌊

Daily Repayments Flow

How borrower payments move through settlement

Every business day, LendingPoint collects payments and sends a wire to CCB's settlement account (1600020521). The reconciliation verifies this wire matches expected payment amounts.

BorrowersPayments LendingPointCollects & Remits Wire TransferLP MASTER TRUST Settlement1600020521 Loan PymtPA/PURCH Excess/RefOverpay AdjustmentsOOB

📄 Data Flow Details

▼

The payment file (Pymt_File) contains 59,812 transactions daily with these key fields:

Field NameColumnDescription
Transaction ID (Txn_ID)AUnique ID with prefix (LPT-, RTA-, ADJ-)
Loan Number (loan_number)BLAI-XXXXXXXX format identifier
Date Remitted (Date_remitted)ESettlement date - KEY FILTER FIELD
Transaction AmountFTotal payment amount
PrincipalGPrincipal portion of payment
InterestHInterest portion of payment
FeesIFee portion of payment
ExcessJOverpayment amount (triggers Excess_Filter)
Loan Product TypeNDTC, POS, eBay, or JOOR
Out of Balance (OOB)AAPenny rounding discrepancies

⚖️ The Fundamental Equation

Wire In (Credit) + Loan Payment (Debit) = ~$0.00
Any difference should be within rounding tolerance (typically less than $0.10 per day)

✅

Wire Matching

How credits and debits balance

Wire In (Credit)
-$1,505,584.72
+
Loan Payment (Debit)
+$1,505,584.80
=
Wire Difference
+$0.08

✓ Perfect Match

Wire + Payment = $0.00 - transactions clear immediately.

~ Rounding (OOB)

Small differences from Out of Balanceℹ Out of Balance (OOB) Penny discrepancies that accumulate. Tracked in OOB_Filter worksheet and auto-adjusted via Parse sheet row 25. Pymt_File Column AA: OOB penny allocations. Accumulated for monthly true-up.

✗ Large Variance

Significant differences require investigation. Check Parse!N16 and Parse!O16 for variance details.

📋

Daily Reconciliation Steps

Workflow performed each business day

1

Download Transactions

Pull data from Horizon system, including prior day for backdated entries.

  • Access Horizon or H360 extract
  • Set date range (include prior day)
  • Export to Horiz_Trx sheet
2

Import to Workbook

Append transactions applying conversion formulas.

=IF(Debit_Column="",Credit_Column*-1,Debit_Column)
Converts separate Debit and Credit columns into a single Net Amount
3

Create Daily Sheet

Copy prior day's sheet, update Settlement Date and Horizon balance.

4

Match & Clear

Pair wires with payments, move matched pairs to "Cleared Items" section.

  • Wire In + Loan Payment (Participation Agreement) = ~$0
  • Wire In + Loan Payment (Purchased) = ~$0
  • Group and border each cleared set
5

Record Adjustments

Enter Out of Balance (OOB) adjustments and wire differences.

✓

Verify Balance

Final check: Horizon Balance + Uncleared Items must equal Zero.

=Horizon_Balance + Sum_of_Uncleared_Items
Horizon Balance plus Uncleared Items MUST equal $0.00
💰

General Ledger Account Reference

Complete listing by category

Settlement Accounts

Purchased Loan General Ledger Accounts

Program Loan General Ledger Accounts

📋

Daily Accounting Template Entry (ATE) Process

Complete step-by-step workflow

1

Daily_1: Receive Data Sources

Download files from SFTP and Fed activity

  • Incoming Wires from Fed
  • NACHA Origination Credits
  • Daily Settlement Files from LendingPoint SFTP
  • Files go to: Y:\File Transfers\Lending Point\Incoming
2

Daily_2: Process Disbursements & Payments

Parse payment files and generate settlement entries

  • Paste reports into Excel parse tools
  • Parse by: Consumer (DTC/POS) vs Commercial (eBay/JOOR)
  • Wire CREDITS Settlement Account 1600020521
  • DEBIT Principal GL Accounts: 10226050 (Consumer), 10215050 (Commercial)
3

Daily_3: Calculate Accrued Interest

Delta calculation between Day1_Tape and Day2_Tape

Day2(Accrued_Interest + Interest_Paid) - Day1(Accrued_Interest + Interest_Paid)
Compare Accrued_Interest (Column N) + Interest_Paid (Column P) between tapes
DEBIT Accrued Interest GL (10426050, 10415050)
CREDIT Interest Income GL (40046050, 40025050)
4

Daily_4: Process Canceled Loans

Write-off loans that were canceled

  • Delta Parse: Day2 (Canceled) not in Day1 (Canceled)
  • Filter by: Loan_StatusℹLoan Status FieldCurrent status of the loan. "Canceled" indicates the loan was voided/rescinded.Day_Tape Column L: Loan_Status = "Canceled"
  • DEBIT LendingPoint Operating Account 1600018566
  • CREDIT Principal GL and Accrued Interest GL
5

Daily_5-6: Process 120 Days Past Due Charge-Offs

Internal charge-off at 120+ days past due

  • Daily_5: PURCHASED portfolio
  • Daily_6: PROGRAM portfolio
  • Filter: Days_Past_DueℹDays Past Due (DPD)Number of days since payment was due. 120+ triggers CCB internal charge-off.Day_Tape Column K: Days_Past_Due > 119 AND Charge_Off_Date is blank
  • DEBIT Reserve for Loan Loss GL 10290000
  • CREDIT Principal Contra GL Accounts
6

Daily_7-8: Process 180 Days Past Due Charge-Offs

LendingPoint official charge-off processing

  • Daily_7: PURCHASED Consumer & Commercial
  • Daily_8: PROGRAM Consumer & Commercial
  • Filter: Charge_Off_DateℹCharge-Off DateDate when LendingPoint officially charged off the loan (typically at 180 DPD).Day_Tape Column T: Charge_Off_Date is not blank
  • DEBIT LendingPoint Cash Collateral 1600076143
  • CREDIT Account Receivable GL 10807100
✓

Complete ATE and Upload

Copy entries to ATE templates and upload to O-drive

  • Validate: Total Debits = Total Credits
  • Upload completed ATE to O-drive
⚙️

Purchase Parse Workbook

Automated processing of loan-level data

The Macro Purchase Parse workbook processes daily loan data, generates journal entries (ATE), tracks charge-offs, and validates all transactions reconcile properly.

📊
15
Worksheets
📝
~60K
Payment Records
📈
32K
Loan Snapshots
🔄
VBA
Macro Automation
📑

Transaction Categories

Types of transactions processed

LPT- (Loan Payment Transaction)

Regular payments: PrincipalℹPrincipal (Column G)The portion of payment that reduces the loan balance. + InterestℹInterest (Column H)The portion of payment for borrowing costs. + FeesℹFees (Column I)Any additional charges (late fees, etc.).

LPT-0123456789 = $500.00

RTA- (Return Transaction)

Reversals when payments bounce or fail (e.g., insufficient funds, closed account)

RTA-0123456789 = -$500.00

ADJ- (Adjustment Transaction)

Corrections, refunds, spread corrections, SCRA adjustments

ADJ-0123456789 = -$25.00

🏷️ Product Types (Loan_Product_Type - Column N)

Consumer: DTC (Direct-to-Consumer), POS (Point of Sale)
Commercial: eBay, JOOR
Each product type maps to different General Ledger accounts for proper categorization.

🧮

Key Formulas Explained

Parse sheet calculation logic with field names

💵 Consumer Principal Payment Calculation

▼

Sums all principal payments for Consumer products (DTC/POS) on the settlement date

SUMIFS(Principal, Date_remitted=Settlement_Date, Payment_Type≠"CO Sale Proceeds", Txn_ID="LPT*", Loan_Product_Type={"DTC","POS"})

This formula calculates Consumer Principal by summing:

Principal (Column G) from Pymt_File where:
• Date_remitted (Column E) = Settlement Date
• Payment_Type (Column K) is NOT "CO Sale Proceeds"
• Txn_ID (Column A) starts with "LPT"
• Loan_Product_Type (Column N) is "DTC" or "POS"

💵 Consumer Interest + Fees Calculation

▼

Sums Interest and Fees for Consumer LPT transactions

SUMIFS(Interest, ...) + SUMIFS(Fees, ...) for DTC/POS LPT transactions
Interest (Column H) + Fees (Column I)
Same filters as Principal: Date_remitted, Payment_Type, Txn_ID, Loan_Product_Type

🔄 Return (RTA) Principal Calculation

▼

Absolute value of returned Consumer Principal (returns are negative in source)

ABS(SUMIFS(Principal, Txn_ID="RTA*", Loan_Product_Type={"DTC","POS"}, ...))
Uses ABS() because return amounts are negative in the source data.
Filters by Txn_ID (Column A) starting with "RTA"

📊 Out of Balance (OOB) Adjustments

▼

Calculates penny rounding adjustments by product and transaction type

Sum of OOB amounts grouped by: Consumer LPT, Consumer RTA, Commercial LPT, Commercial RTA, etc.
OOB (Column AA) contains penny discrepancies
The OOB_Filter sheet isolates all transactions where OOB ≠ 0
Parse sheet row 25 captures the net OOB adjustment
📉

Charge-Off Lifecycle

When loans become severely delinquent

When borrowers stop making payments, loans progress through stages of delinquency. The charge-off process ensures non-performing assets are properly tracked and written off according to regulatory requirements.

✓
Active
Current
⏰
Delinquent
30-89 DPD
⚠️
120+ DPD
CCB Subledger
📋
LP Charge-Off
180 DPD
🔚
Resolution
Sold/Settled

Active Loan Status

Loan is current - borrower making payments as scheduled. Loan_StatusℹLoan Status (Column L)Active status in Day_Tape indicates the loan is performing normally. shows "Active" or "Current".

Early Delinquency (30-89 Days Past Due)

Missed payments but not severely delinquent. Collections efforts begin. Days_Past_DueℹDays Past Due (Column K)The number of days since payment was due. Increases daily until payment received. is between 30-89.

120+ Days Past Due - CCB Internal Recognition

At 120 DPD, CCB recognizes impairment. Enters 120-ChargeOff sheet and 120Subledger for tracking.

Filter: Days_Past_Due > 119 AND Charge_Off_Date is blank AND Loan_Status not "Canceled" or "Closed - Obligations met"
Days_Past_Due (Column K) > 119
Charge_Off_Date (Column T) is empty (not yet LP charged off)
Loan_Status (Column L) excludes Canceled and Closed statuses

LendingPoint Charge-Off (180 Days Past Due)

LendingPoint formally charges off the loan. The Charge_Off_DateℹCharge-Off Date (Column T)The date LendingPoint officially charged off the loan. Populated at ~180 DPD. field gets populated. Loan moves to 180-ChargeOff and 180Subledger.

Final Resolution

Loan reaches one of these endpoints:

  • CO Sale Proceeds: Sold to debt buyers (Payment_Type = "CO Sale Proceeds")
  • Settled: Borrower settles for less than full balance
  • Obligations Met: Paid in full by borrower
🧮

5-Section Reconciliation

Tracking balance changes between Day1 and Day2

#1 New Loans

Loans entering the 120+ DPD bucket for the first time (in Day2 but not Day1)

#2 Balance Changes

Changes in Outstanding_PrincipalℹOutstanding Principal (Column M)Current loan principal balance in Day_Tape. / Accrued_InterestℹAccrued Interest (Column N)Unpaid interest that has accumulated on the loan. on existing loans

#3 Charged Off by LP

Loans moved to 180-ChargeOff (Charge_Off_Date now populated)

#4 Dropped - Not Charged Off

Left unexpectedly - requires investigation

#5 Obligations Met

Paid off in full by borrower (Loan_Status = "Closed - Obligations met")

📊 The Reconciliation Formula

Day2 Balance = Day1 Balance + New + Changes - Charged Off - Dropped - Paid Off

This 5-section breakdown explains every dollar of movement in the charge-off portfolio.

⚖️

Settlement Account Reconciliation

Daily reconciliation of 1600020521

The Repayments Settlement Account receives daily wires and must reconcile to zero after all entries are posted.

Excess Payment Tracking

3,639 records | 465 unique loans | -$110,283 net outstanding

Excess payments from ExcessℹExcess Column (J)Amount by which payment exceeds loan balance. Tracked in Excess_Filter worksheet. column (J) in Pymt_File

Extract Loan ID

First 12 characters of description = LAI-XXXXXXXX

=LEFT(Description,12)

Month-End Certification

Bank Balance + Reconciling Items = $0.00

Bank_Balance + Sum_of_Reconciling_Items = Final_Balance
Final_Balance MUST equal $0.00
✓

Validation Matrix

All checks that must pass

#Check NameLocationExpectedDescriptionStatus
1Credits = DebitsParse!N21YTotal Credits must equal Total Debits (balanced journal)✓ Pass
2Sub-entries Match Top DebitParse!N15YSum of ATE sub-entries equals calculated wire total✓ Pass
3Top Debit Match WireParse!N16YCalculated total matches actual wire received✗ -$0.02
4Remitted Amount = WireParse!N18YSum of Remitted_Amount (Column R) equals wire✗ -$0.02
5Excess Entries MatchParse!N25YATE excess entries match Excess_Filter totals✓ Pass
6Charge-Off Sold Entries MatchParse!N32YCO Sale Proceeds in ATE match Pymt_File✓ Pass
7Returns Match RTAParse!N34YReturn entries match RTA transaction totals✓ Pass
8120-ChargeOff Principal Match120-CO!AE1YPrincipal delta matches calculated change✓ Pass
9120-ChargeOff Interest Match120-CO!AE2YInterest + Capitalized Interest delta matches✗ Fail
10120-CO Recon Principal120-CO!AE3YReconciled principal matches Sub-ledger delta✓ Pass
11120-CO Recon Interest120-CO!AE4YReconciled interest matches Sub-ledger delta✓ Pass
12180-ChargeOff Principal Match180-CO!AT20Y3-way validation of principal deltas✓ Pass
13180-ChargeOff Interest Match180-CO!AU20Y3-way validation of interest deltas✗ Fail
14180 TrueUp PrincipalParse!L7Y180-CO TrueUp principal matches 180-ChargeOff!AT18✓ Pass
15180 TrueUp InterestParse!L8Y180-CO TrueUp interest matches 180-ChargeOff!AU18✓ Pass
16Canceled Principal MatchParse!V13YCanceled loan principal matches validation total✗ Fail
17Canceled Interest MatchParse!W13YCanceled loan interest matches validation total✗ Fail

Critical: Credits = Debits (N21)

MUST always pass. If it fails, journal is unbalanced and cannot be posted to the General Ledger.

Wire Variance (-$0.02)

Typically caused by Out of Balance (OOB) penny rounding. Auto-captured in Parse row 25 and adjusted monthly.

🔥

Known Problem Areas

Documented reconciliation issues requiring resolution

The following issues have been identified during the reconciliation process. Each issue includes source file references, specific locations within workbooks, and detailed context to support investigation and resolution efforts.

🚨

Issue #1: $1.3M Legacy Carry Over Balance

Critical - Unresolved since August 2023

Outstanding Amount

$1,332,205.21

Age of Issue

2+ Years

Status

Manually Excluded Daily

Description

In August 2023, LendingPoint migrated to a new servicing platform. During this migration, a balance of approximately $1.33 million was carried forward into the reconciliation that does not tie out to any identifiable transactions. This balance has remained unresolved and is manually excluded from daily reconciliation to allow processing to continue.

Source Files & References

File/LocationWorksheetCell/RangeDescription
DAILY LendingPoint Repayment.xlsxDaily Settlement SheetUncleared Items sectionLegacy balance excluded from daily reconciliation
Macro Purchase Parse.xlsmParseRow 24 (Historical)Original migration adjustment entry
Historical RecordsAugust 2023 filesVariousPre-migration reconciliation records

Root Cause Analysis

Primary Factors Identified:
  • Canceled Loans Not Reported: Loans that were canceled/rescinded prior to migration were not properly reported in the new system, leaving orphaned payment records.
  • Excess Payments Not Refunded: Overpayments (Column J: Excess) from the legacy system were not refunded or properly transferred during the migration.
  • Timing Differences: Transactions in-flight during the cutover were not properly reconciled between old and new systems.
  • Data Mapping Issues: Some loan identifiers (LAI numbers) may not have mapped correctly between platforms.

Business Impact

  • Audit Risk: External auditors have flagged this balance as a material reconciling item requiring documentation.
  • Operational Overhead: Manual exclusion required every day, adding processing time and error risk.
  • Obscures Other Issues: The large unresolved balance makes it difficult to identify newer, smaller discrepancies.
  • Regulatory Concern: Potential questions from bank examiners regarding unreconciled balances.
🚨

Issue #2: Broken Certification Sheet Formulas

Critical - Prevents month-end validation

Description

The month-end certification worksheet, which is used to formally certify that the reconciliation is complete and accurate, contains multiple #REF! errors. These errors prevent the automated validation checks from functioning and require manual workarounds to complete the certification process.

Source Files & References

File/LocationWorksheetAffected CellsError Type
DAILY LendingPoint Repayment.xlsxCertificationBank Balance section#REF! - Missing source reference
DAILY LendingPoint Repayment.xlsxCertificationReconciling Items#REF! - Broken cell references
DAILY LendingPoint Repayment.xlsxCertificationFinal Certification#REF! - Formula dependencies broken

Root Cause Analysis

Likely Causes:
  • Sheet Restructuring: Rows/columns were inserted or deleted in source sheets, breaking relative references.
  • File Corruption: Possible file corruption during saves or transfers.
  • Missing Named Ranges: Formulas may reference named ranges that were deleted or renamed.
  • Cross-Workbook Links: External references to other files that have been moved or renamed.

Business Impact

  • Month-End Delays: Cannot complete formal certification without manual calculations.
  • Control Weakness: Automated validation controls are not functioning as designed.
  • Documentation Gap: Certification sheets are key audit evidence; errors undermine their reliability.
⚠️

Issue #3: Excess/Refund Matching Backlog

High Priority - Growing backlog of unmatched items

Total Records

3,600+

Net Outstanding

$110,283.81

Oldest Items

October 2024

Description

When borrowers overpay on their loans, the excess amount is recorded in the Pymt_File (Column J: Excess) and tracked in the Excess_Filter worksheet. These overpayments should be matched against corresponding refunds (RTA- transactions) or adjustments (ADJ- transactions). A significant backlog of unmatched excess payments has accumulated, with many items remaining open for months.

Source Files & References

File/LocationWorksheetColumn/FieldDescription
Macro Purchase Parse.xlsmPymt_FileColumn J (Excess)Source of excess payment amounts
Macro Purchase Parse.xlsmExcess_FilterFull worksheetFiltered view of all excess transactions
Macro Purchase Parse.xlsmPymt_FileColumn A (Txn_ID)RTA- prefix indicates refund transactions
Macro Purchase Parse.xlsmParseRow 25Net Excess amount for ATE entry

Breakdown by Period

PeriodOutstanding AmountNotes
October 2024 - June 2025-$83,796.58Older items, likely require research
July 2025 - Current-$14,574.86More recent, may self-resolve
Other/Miscellaneous-$11,912.37Various periods

Business Impact

  • Customer Impact: Borrowers may be owed refunds that have not been processed.
  • Reconciliation Noise: Unmatched items create ongoing variances that must be tracked.
  • Manual Effort: Current matching process is manual and time-consuming.
  • Aging Risk: Older items become increasingly difficult to research and resolve.
⚠️

Issue #4: SCRA Adjustments Pending Resolution

Medium Priority - Requires compliance guidance

Description

The Servicemembers Civil Relief Act (SCRA) provides protections for active-duty military members, including interest rate caps on pre-service debts. When a borrower qualifies for SCRA benefits, LendingPoint processes adjustments to reduce interest charges. These adjustments appear in the reconciliation as ADJ- transactions but there is no clear protocol for how CCB should handle them from a settlement perspective.

Source Files & References

File/LocationWorksheetColumn/FieldDescription
Macro Purchase Parse.xlsmPymt_FileColumn A (Txn_ID)ADJ- prefix with SCRA notation
Macro Purchase Parse.xlsmPymt_FileColumn H (Interest)Negative interest amount (credit to borrower)
Daily LendingPoint filesVariousSettlement entriesSCRA adjustments in daily wire

Example Transactions

Transaction IDAmountTypeStatus
ADJ-0106548647$3,583.44SCRA Interest AdjustmentPending resolution
ADJ-0106673829$263.63SCRA Interest AdjustmentPending resolution

Open Questions

  • Refund to Borrower? Should the adjustment amount be refunded directly to the servicemember?
  • Transfer to LendingPoint? Does LendingPoint absorb this as a cost of SCRA compliance?
  • Write-Off? Should CCB write off the amount as a regulatory compliance cost?
  • Documentation: What documentation is needed to support the disposition of these adjustments?

Business Impact

  • Compliance Risk: SCRA is a federal regulation; improper handling could create regulatory issues.
  • Accumulating Balance: Without clear guidance, SCRA adjustments continue to accumulate.
  • Cross-Functional Dependency: Resolution requires input from Compliance, Legal, and LendingPoint.
⚠️

Issue #5: Charge-Off Interest Reconciliation Discrepancy

Medium Priority - Affects validation checks #9 and #13

Failing Check #9

120-ChargeOff Interest Match

Location: 120-CO!AE2

Failing Check #13

180-ChargeOff Interest Match

Location: 180-CO!AU20

Status

Under Investigation

Description

The charge-off interest reconciliation fails for both 120-day and 180-day charge-off subledgers. While principal amounts reconcile correctly (checks #8 and #12 pass), the interest components including capitalized interest do not match between the subledger delta calculations and the validation totals.

Source Files & References

File/LocationWorksheetCell/RangeDescription
Macro Purchase Parse.xlsm120-ChargeOffAE2120-day interest + capitalized interest delta validation
Macro Purchase Parse.xlsm180-ChargeOffAU20180-day 3-way interest delta validation
Day_Tape (Daily)ChargeOff recordsInterest columnsSource interest amounts from LP
Horizon Subledger120/180 CO accountsInterest balanceCCB's recorded interest balance

Potential Root Causes

  • Capitalized Interest Timing: Interest capitalization may occur at different times between LP and CCB systems.
  • Accrual Method Differences: LP may use a different interest accrual methodology than CCB's subledger.
  • Partial Payments Applied Differently: When partial payments are received on charged-off loans, the interest allocation may differ.
  • Backdated Adjustments: Late adjustments to charge-off records may not flow through both systems consistently.

Business Impact

  • Subledger Integrity: Charge-off subledgers may not accurately reflect interest recovery on bad debt.
  • Financial Reporting: Interest income from charge-off recoveries may be misstated.
  • Validation Reliability: Two of seventeen validation checks consistently failing reduces confidence in the overall reconciliation.
⚠️

Issue #6: Canceled Loan Validation Failures

Medium Priority - Affects validation checks #16 and #17

Failing Check #16

Canceled Principal Match

Location: Parse!V13

Failing Check #17

Canceled Interest Match

Location: Parse!W13

Related To

Issue #1 (Legacy Balance)

Description

The canceled loan validation checks fail because canceled/rescinded loan transactions are not properly reconciling between the payment file totals and the validation worksheet calculations. This is directly related to Issue #1, where "Canceled Loans Not Reported" was identified as a root cause of the legacy carry-over balance.

Source Files & References

File/LocationWorksheetCell/RangeDescription
Macro Purchase Parse.xlsmParseV13Canceled loan principal validation check
Macro Purchase Parse.xlsmParseW13Canceled loan interest validation check
Macro Purchase Parse.xlsmPymt_FileCanceled transactionsCANC- prefixed transaction records
Daily LP FilesVariousRescission recordsLoan cancellation/rescission notifications

Connection to Issue #1

The legacy carry-over balance (Issue #1) specifically identified "Canceled Loans Not Reported" as a primary factor. These validation failures confirm that the canceled loan reconciliation remains problematic:

  • Historical Orphaned Records: Canceled loans from the August 2023 migration were never properly closed out.
  • Ongoing Discrepancies: New canceled loans may also fail to reconcile if the same systemic issue persists.
  • Manual Workaround Impact: The exclusion of the $1.33M balance may mask ongoing canceled loan issues.

Business Impact

  • Rescission Compliance: Canceled/rescinded loans have regulatory timelines for fund returns.
  • Settlement Account Accuracy: Unreconciled canceled loan amounts remain in the settlement account.
  • Audit Trail: Inability to validate canceled loans creates documentation gaps for examiners.
ℹ️

Issue #7: Wire Variance from Penny Rounding

Low Priority - Accepted variance, documented for completeness

Current Variance

-$0.02

Maximum Expected

$0.09/payment

Status

Accepted / Auto-Adjusted

Description

Validation checks #3 (Top Debit Match Wire) and #4 (Remitted Amount = Wire) fail by $0.02 due to Out of Balance (OOB) penny rounding. This occurs when Principal + Interest does not exactly equal the Total Payment amount due to rounding at different precision levels. This is an expected and accepted variance that is auto-captured and adjusted.

Source Files & References

File/LocationWorksheetCell/RangeDescription
Macro Purchase Parse.xlsmParseN16Check #3: Top Debit Match Wire validation
Macro Purchase Parse.xlsmParseN18Check #4: Remitted Amount = Wire validation
Macro Purchase Parse.xlsmPymt_FileColumn AA (OOB)Out-of-Balance penny discrepancy per transaction
Macro Purchase Parse.xlsmOOB_FilterAll rowsFiltered view of all OOB transactions
Macro Purchase Parse.xlsmParseRow 25Net OOB adjustment captured in ATE

Why This Is Accepted

  • Industry Standard: Penny rounding variances are common in payment processing and typically immaterial.
  • Auto-Captured: The OOB_Filter worksheet isolates all transactions where OOB ≠ 0, and Parse row 25 captures the net adjustment.
  • Monthly True-Up: Accumulated OOB amounts are settled with LendingPoint during the monthly true-up process.
  • Bounded Variance: Maximum OOB per payment is $0.09, making the risk of material accumulation minimal.

Monitoring Recommendation

  • Alert Threshold: If wire variance exceeds $1.00, investigate for non-OOB causes.
  • Monthly Review: Verify OOB adjustments are included in monthly true-up with LP.
  • Documentation: This issue formalizes the accepted variance for audit trail purposes.
📖

Terms & Definitions

Key terminology with column references

ATE (Accounting Template Entry)
Formatted journal entry for General Ledger posting. Generated by Parse sheet.
CCB (Coastal Community Bank)
Bank of record for LendingPoint loans. Holds underlying accounts.
DPD (Days Past Due)
Number of days since payment was due. Classifies delinquency severity.
Day_Tape Column K: Days_Past_Due
Excess Payment
When borrower pays more than due. Held in settlement until refunded.
Pymt_File Column J: Excess
Horizon
CCB's core banking system (FIS Horizon/H360). Source of transaction data.
LAI (Loan Account Identifier)
Unique loan identifier in format LAI-XXXXXXXX.
Pymt_File Column B: loan_number
LP (LendingPoint)
Partner that originates and services loans. Sends daily payment files.
OOB (Out of Balance)
Penny rounding where Principal + Interest ≠ Total. Max $0.09/payment.
Pymt_File Column AA: OOB
PA (Participation Agreement)
Contractual arrangement for loan participation. CCB participates vs. owns.
PURCH (Purchased)
Loans CCB has purchased outright (vs. participated). Uses GL suffix 050.
SCRA
Servicemembers Civil Relief Act. Requires interest rate caps for military.
Settlement Account
Bank account 1600020521 where wires received and payments processed.
Subledger
Detailed subsidiary ledger tracking individual loans. 120/180 track charge-offs.
True-Up
Monthly adjustment to settle accumulated small differences with LP.
🏷️

Transaction Codes

Horizon system codes

Code 30 - Debit

Outgoing payments, adjustments, excess. Money leaving settlement account.

Code 45 - Wire

Incoming wire transfers from LendingPoint. Primary inflow source.

Code 60 - Credit

Refunds, OOB adjustments, true-ups. Money entering settlement account.

📋

Payment File Column Reference

Pymt_File structure (59,812 rows)

ColumnField NameDescription
ATxn_IDTransaction ID with prefix (LPT-, RTA-, ADJ-, CHG-)
Bloan_numberLoan identifier (LAI-XXXXXXXX format)
CLoan_StatusCurrent loan status
DCreated_DateTransaction creation date
EDate_remittedSettlement/remittance date - KEY FILTER FIELD
FTransaction_AmountTotal transaction amount
GPrincipalPrincipal portion of payment
HInterestInterest portion of payment
IFeesFee portion of payment
JExcessOverpayment amount (triggers Excess_Filter)
KPayment_TypeACH, Debit Card, Check, Refinance, Excess, CO Sale Proceeds
NLoan_Product_TypeDTC, POS, eBay, JOOR
QDMC_FeeDebt Management Company fee
RRemitted_AmountAmount actually remitted
AAOOBOut-of-Balance amount (penny discrepancies)