An NPL is a loan that is past due beyond the lender’s threshold or unlikely to be repaid without using collateral. An NPL deal model is a spreadsheet that converts loan-level data, legal timelines, and costs into expected collections, a cash waterfall, and a bid price. Excel remains the workbench because it is flexible, transparent, and easy to audit when inputs, logic, and outputs are cleanly separated.
This guide explains the end-to-end mechanics that actually move price. You will learn how to structure the purchase vehicle, map legal and cash flow rules into a state machine, model recoveries and costs, and convert collections into a defensible price backed by scenarios and contract linkages.
What qualifies as an NPL and why modeling accuracy pays
Banks and funds trade NPL pools across secured mortgages, secured commercial loans, unsecured consumer or SME claims, and charged-off paper. Reperforming loans are different, though many NPL pools carry a reperforming tail. Investor recourse rests on acquired claims and collateral, and origination or modification powers are limited to what loan documents and governing law permit.
Market context shapes both timelines and recoveries. EU significant institutions reported an average NPL ratios level around the low single digits in recent quarters, but dispersion by jurisdiction is material. As a result, the model must be parameterized by country and asset class. Hard-coding a single market curve is a fast way to misprice a pool.
Legal form that protects the bid
Most buyers acquire via a bankruptcy-remote SPV on a true-sale basis to isolate risk and support downstream financing. A special purpose vehicle is standard because it simplifies cash controls and improves lender comfort. Transfer tools vary and carry different consent and control implications.
- Assignment: Transfers rights and usually collateral title. Borrower notice may be required under local law.
- Novation: Replaces the lender, often heavier on consents and common in syndicated loans.
- Sub-participation: Transfers economics without title. It helps where assignment is restricted, but servicing and enforcement get trickier.
Commercial terms often sit under English or New York law, while perfection and transfer mechanics follow local asset law. In the EU, the Credit Servicers Directive sets servicer authorization standards. In the U.S., state licensing regimes and CFPB Regulation F govern consumer collection practices. Plan governance against those rails before you price.
Cash flow mechanics that drive equity returns
Capital typically comes in as SPV equity, sometimes paired with financing or a future securitization. Post-close, collections hit controlled accounts and flow down a priority of payments that pays taxes and bank fees first, then trustee or cash manager, then financing costs if any, then servicer fees, operating costs, and finally residual to equity. Triggers can redirect cash to amortization if performance slips, while sale documents govern repurchases for ineligible loans or document defects.
Documents you must map before pricing
Winning bidders tie model assumptions to the contract pack. Anchor the deal with an NDA and data room protocols, a clear data tape spec and Q&A process, an offer letter that leads to a loan sale or receivables purchase agreement, local transfer instruments and filings, a servicing agreement with well-defined authorities, and cash management agreements for controlled accounts. If you finance, add a trustee and intercreditor controls.
Fees, accounting, and tax: price what sticks
All-in costs include servicer base and incentive fees, legal and court costs, valuations, preservation, brokerage, property taxes, borrower notices, SPV administration, and local taxes. Some are recoverable from borrowers, but many are not. Price what the SPV will actually keep.
Accounting and tax affect both reported returns and cash. Funds often carry NPLs at fair value through profit or loss under IFRS 9 guidance, while operating companies may apply purchased credit-deteriorated rules with credit-adjusted yields. Securitizations trigger derecognition and consolidation analyses. Taxes can treat discount capture as ordinary income, levy VAT on servicing, impose stamp duty on mortgage transfers, and apply withholding cross-border. Model taxes explicitly and by jurisdiction.
Regulatory rails to underwrite in the model
- EU servicer rules: The Credit Servicers Directive sets authorization and information standards. NPL securitizations use dedicated templates.
- U.S. consumer rules: State debt collection licensing and CFPB Regulation F shape communications and records.
- Data protection: GDPR in the EU and U.S. state privacy laws dictate data use, transfer, and breach notices. Move PII with a lawful basis and document it.
Major risks that move the price
- Title gaps: Chain-of-title defects or junior liens booked as senior can destroy recoveries.
- Limitations: Statute expiries and defective prior actions can void tolling.
- Court shifts: Delays, procedural changes, or moratoria extend time and push down NPV.
- Collateral issues: Encumbrances, environmental liabilities, or occupancy reduce net proceeds.
- Consumer caps: Interest and fees may be limited in consumer loans.
- Litigation drag: Setoff and borrower litigation soak up settlements.
- Servicer ramp: Underpowered early execution drags IRR.
Seven steps to build a practical Excel model
Step 1: Define the bid perimeter and clean the tape
Start by fixing the cut-off date and treatment of collections between cut-off and close. Price only post cut-off cash you own, less leakage for timing, rejections, and repurchases. Reserve working capital for boarding and early legal. Build a data dictionary that includes unique ID, product, collateral, lien and seniority, guarantor flags, origination, interest rate, legal status, last pay date, days past due, principal and accrued split, collateral valuation and date, enforcement status, restructure details, court identifiers, and servicer notes.
Run kill tests before you model. Flag missing legal stage on more than 10 percent of GBV, unmatched collateral IDs, outdated valuations on large portions of secured GBV, missing notes or acknowledgments, exposures beyond statute without tolling, and juniors where the senior is unknown. If failures are heavy, revise the perimeter or pass. Stratify the pool with pivots by collateral, LTV, geography, legal stage, size, borrower segment, and vintage. Track concentration metrics, such as top 10 exposures as a percent of GBV and single-county share of secured GBV.
Excel build: create Raw_Tape, Clean_Tape, Strats, and Params tabs. Use power query to standardize fields. Store parameter named ranges, version the workbook, and snapshot the tape. For quick reference, link the phrase data tape to a field checklist so the team stays aligned.
Step 2: Map loans to resolution channels with transitions
Define exclusive channels. For secured loans, allocate to voluntary settlement, restructure, foreclosure to REO, auction, or claim sale. For unsecured loans, allocate to settlement, payment plan, litigation and judgment, garnishment, or judgment sale. Give each loan a starting legal state and apply monthly transition probabilities that respect prerequisites and statutory steps.
Parameterization should be jurisdiction specific and data driven. Calibrate from servicer history by asset class and country, and adjust for LTV, employment, legal stage, and collateral liquidity. Enforce legal precedence. When a loan restructures, move it to a new state with cure and redefault curves, and do not double count cash. Excel build: use a State_Machine tab with rows per loan and columns per month, pulling probabilities via lookups from Params. Conserve probability mass and use binary flags to stop transitions at terminal states.
Step 3: Build timing curves for courts and ramp
Resolution timing often matters more than haircut debates. Encode statutory steps and typical delays by jurisdiction, breaking foreclosure into pre-trial, trial, post-judgment, and registration buffers. Reduce early-month rates to reflect servicer ramp while teams board, skip-trace, and triage. Add seasonality where REO closes cluster in Q2 to Q3 and litigation slows during recess periods.
Excel build: create Timing_Curves with months-to-resolution by channel and stage plus volatility bands. Apply via lookups that adjust transitions or receipt lags. A global Ramp_Factor can reduce probabilities in months 1 through 6.
Fresh angle: consider hazard-rate timing rather than fixed months-to-resolution. Survival curves fit naturally with state transitions and capture the long tail of contested cases better than simple averages.
Step 4: Model gross recoveries and cost-to-collect
Price net, not gross. For secured loans, start from collateral value and haircut for valuation age, forced-sale discount, senior liens, taxes or HOA dues, occupancy, and litigation risk. Deduct enforcement fees, court costs, preservation, brokerage, and transfer taxes. For restructures, build a reperforming stream with redefault probability and a second-path resolution. For unsecured loans, vary by age, balance, and borrower profile. Early settlements can be low single-digit percent of GBV, with litigation improving rates but raising costs. Include only enforceable interest and fees, because consumer rules cap post-default charges in many places.
Excel build: use a Recovery_Params tab with gross to net factors and cost items by channel. Link secured loans to LTV bands and haircut tables that degrade with valuation age. Compute cash inflow and outflow separately for each event.
Step 5: Build the waterfall and servicer pay
Cash sticks or slips in the waterfall. Sequence taxes and bank fees, trustee or cash manager, financing costs, servicer base, servicer incentive, operating costs, and then residual. If success fees apply to gross, pay them before operating costs unless the contract says otherwise. Model multi-tier hurdles or per-channel KPIs, and implement true-ups and clawbacks if later underperformance trips them. Add performance triggers. If collections trail underwritten curves for two quarters, flip to a protective priority.
Excel build: the Waterfall tab aggregates loan cash monthly. A Servicer_Fee_Calc sub-schedule should read actual collections. Keep references clean so counsel and the servicer can trace them.
Step 6: Convert cash to price
Aggregate monthly net cash after all fees and costs, including financing if modeled at the asset level. Discount using XNPV or XIRR to match the target equity IRR. Price equals the present value of net cash plus terminal working capital release minus onboarding and initial working capital. If the seller keeps pre cut-off collections, include leakage and roll-forward math. Value holdbacks and earn-outs by the expected payout and the time lag.
Excel build: consolidate flows on the Price tab and use Solver to find price per GBV that matches your target XIRR. Sanity check the result. Late-stage unsecured paper rarely prices above single-digit percent of GBV without unusually strong litigation outcomes. For more on structured testing, see sensitivity analysis vs scenario design.
Micro-case: for a secured pool with GBV 100 and target IRR 18 percent, gross collections of 35 over 48 months, operating or legal costs of 8, and servicer fees at 10 percent of gross, net equals 35 minus 3.5 minus 8, or 23.5, weighted to months 12 through 36. A price around 18 to 19 per GBV can work. Add four months to foreclosure and the bid may fall by 1 to 2 points. Timing beats optimism.
Step 7: Sensitivities, scenarios, and contract linkage
Underwrite surprises rather than base cases. Run tornado sensitivities on LTV haircuts, timing, settlement rates, legal cost inflation, ramp, and incentive fees. Show price per GBV and IRR deltas for plus or minus 10 to 20 percent moves. Build Base, Fast, Slow, and Legal Stress scenarios. In Legal Stress, add 25 to 50 percent to judicial timelines, raise per-action costs, and lower REO liquidation rates in weak quarters. Add a Data Defect case where 5 percent of secured GBV is ineligible and is replaced or removed pending repurchase.
Map each pricing assumption to a clause. Tie warranties to enforceability, perfection, and data completeness. Confirm the repurchase cap and survival period. If file completeness is disclaimed or repurchases cap at 3 percent of GBV, lower price or carve out. Offer bids by segment if permitted, all-cash versus staged closing with interim collections sharing, forward flow options with pricing grids, and upside-sharing on high-value REOs to bridge valuation gaps. For a practical lens on cross-checks, see how private equity funds price non-performing loans.
Governance, timeline, and structures that scale
Good models are readable and enforceable. Separate inputs, logic, and outputs. Lock logic sheets, add a change log, reconcile the tape to model totals and concentrations, assign every loan a resolution channel, and ensure no cash strands in the waterfall. Produce a one pager summarizing price per GBV, price by segment, expected net collections, duration, IRR, top sensitivities, and contract asks.
Execution follows a simple cadence. Weeks 0 to 1: NDA, data room, and initial tape or dictionary. Weeks 1 to 2: tape cleaning, kill tests, stratifications, and Q&A. Weeks 2 to 3: parameterization, first model pass, draft bid. Weeks 3 to 4: management Q&A, site checks for large secured exposures, finalize bid with scenarios and contract ties. Weeks 4 to 6: confirmatory diligence, file sampling, model refresh, negotiate the loan sale and servicing agreements. Weeks 6 to 8: signing, pre-close conditions, borrower notices setup, cash management, onboarding plan. Weeks 8 to 12: closing, boarding, ramp, and 30 or 60 or 90 day variance tracking.
Consider alternatives when fit or constraints differ. A whole-loan SPV purchase is fastest for control and confidentiality and fits sellers seeking derecognition and buyers with servicing muscle. Forward flows smooth deployment and should price flexibly with macro or legal shifts. Sub-participations help when assignment or consent blocks true sale but complicate control and accounting. NPL securitizations finance large pools and spread risk, with EU templates heavier but often lowering funding cost. Seller financing or JVs bridge price gaps where collateral values are volatile.
Edge cases and investor protections
Complex pools need crisp guardrails. Antitrust clean teams may be needed if competitor borrower data could be shared. Export controls and CFIUS reviews can be relevant if collateral or borrowers tie to sanctioned parties or sensitive geographies. Cross-border PII transfers require lawful bases and local notices. Negotiate eligibility tied to enforceability, perfection, and data completeness, with repurchases at price plus costs or replacement assets. Seek warranties with clear knowledge qualifiers and survival matched to expected defect discovery, timely repurchase processes, escrow or holdbacks sized to expected defect rates, access to originals or notarized copies, rights to cure low-cost perfection issues at seller expense, and a block on seller setoff after close.
Execution after close
Performance discipline starts on day 1. Track daily boarding KPIs and 30 or 60 or 90 day action rates by channel. Compare performance to the Base by channel and region, and pivot by month two if variance persists. Align incentives with the servicer if the mix shifts, and enforce clawbacks where underperformance is material. When communication pace and recordkeeping rules change, update contact strategies because outreach throughput and cost will move.
Benchmarks, references, and bid checks
Anchor parameters to a source. Track ECB Supervisory Banking Statistics for top-down metrics, and use EBA NPL transaction templates to frame data requests. If securitization is on the table, align to S&P and Moody’s current NPL ABS timing and recovery stresses. In the U.S., model communication pace and recordkeeping under CFPB Regulation F, because they affect effort and cost. In certain EU markets, programs such as GACS and HAPS changed exit economics and should inform both bid and financing strategy.
Before submitting a bid, reconcile GBV and composition to the latest tape, confirm cut-off and leakage math and the treatment of collections between cut-off and closing, verify every loan’s resolution path and timing with no Other buckets, re-run sensitivities after any document change that shifts warranties or repurchases, and confirm licensing and data transfer compliance by jurisdiction. Tie price to assumptions in the bid letter and reserve re-price rights for data defects, delayed closing, or macro or legal shifts beyond agreed thresholds.
Fresh angle: add a light-touch ML assist to the tape-cleaning step. A simple classifier can flag mismatches between legal stage narratives and expected numeric states, while name-entity recognition can detect missing court identifiers. Use these as review accelerators, not black boxes, and keep your audit trail intact.
Conclusion
A robust NPL model separates the tape from the state machine, recoveries from costs, and the waterfall from pricing. When each layer is modular and tied to contract clauses, you can change one assumption without breaking the rest. Price to variance, not to the base case, and you will keep bids competitive without taking hidden timing risk.