Cursory Research and Indicative Pricing for Non-Performing Note Portfolios

What Is Cursory Research and Why Does It Matter?

Before you commit to deep due diligence on a bulk note portfolio, you need a fast, defensible way to assign prices to every loan and present an indicative bid to the seller. Cursory research is that step. It sits between your initial tape review and the advanced due diligence you perform after receiving exclusivity -- and its purpose is to get a letter of intent in front of the seller quickly enough to stay competitive while being thorough enough to avoid overbidding.

The output of this process is a loan-level pricing sheet built on formulas, not guesswork. You stratify the portfolio by lien position, equity coverage, occupancy, and senior lien status, assign a price to each combination, and let the spreadsheet calculate an indicative offer on every loan. When you adjust an assumption, every price updates dynamically. That flexibility is what makes this approach so powerful for portfolios with 100 to 200+ loans where manual pricing simply is not practical.

How Should You Organize the Data Tape Before Pricing?

Start by sorting the tape by unpaid principal balance in descending order -- highest balances first. If you have limited time for research before submitting your bid, this ensures you are spending it on the loans that carry the most dollar exposure. A mistake on a $300,000 loan matters far more than one on a $5,000 loan.

Next, bring in any supplemental seller data. Sellers sometimes provide additional columns -- property value, senior lien status, senior balance, property type -- in a separate file. Merge those into your working tape using VLOOKUP formulas tied to the loan number. After pasting, always spot-check by comparing the loan numbers side by side. Shifting data by even one row can assign the wrong property value to the wrong loan, and that kind of error cascades through every formula downstream.

Once the merge is clean, pull in a state-by-state reference sheet that includes judicial versus non-judicial foreclosure status, time to foreclosure, redemption periods, and the statute of limitations for promissory notes. You will need this reference data for the viability checks that come next.

How Do You Flag Statute of Limitations Risk?

The statute of limitations is one of the most important viability checks in bulk portfolio analysis. If the statute has expired on a promissory note, enforceability becomes an existential risk. The loan may still have value in certain circumstances, but it needs to be priced accordingly -- often at pennies on the dollar.

The formula compares three data points:

1. The estimated last payment date -- if the tape does not include a last payment date, subtract 30 days from the next payment due date as a conservative proxy
2. The statute of limitations for the property's state -- pulled from your state reference sheet using a VLOOKUP on the state abbreviation
3. Today's date

The logic: if the last payment date plus the state's statute of limitations period minus today's date is less than zero, the loan is out of statute. If the result is positive but less than 365 days, the loan is approaching statute and should be flagged for closer review.

Running this formula across a 190-loan portfolio might reveal that one loan is already out of statute and several others are within a year of expiring. In one example, a Pennsylvania loan with a last payment estimated in November 2017 had already passed the state's statute of limitations -- a critical finding that would have been easy to miss without the formula.

The takeaway: build this check into your process early. A loan that is out of statute requires a fundamentally different pricing approach, and you need to know about it before you submit your bid -- not after.

What Is Reinstatement Risk and How Do You Estimate It?

Reinstatement risk is the possibility that the borrower brings the loan current -- paying all missed payments, fees, and accrued interest in a lump sum -- and resumes regular monthly payments. While reinstatement is generally a positive outcome, it can create a problem for the investor if the purchase price assumed a deep discount and a longer resolution timeline. A quick reinstatement at 52 cents on the dollar produces a very different return profile than one at 15 cents.

To estimate reinstatement exposure, calculate the number of months between the last payment and today, then multiply by the monthly payment amount. If the tape does not include the monthly payment, you can estimate it using the PMT function in Excel with the interest rate, loan term, and original mortgage amount. A simpler approximation is 1% of the original loan amount per month, which tends to be conservative but directionally correct.

The reinstatement figure tells you how much cash the borrower would need to bring the account current. A loan with a $237,000 estimated reinstatement amount is far less likely to reinstate than one with $12,000 outstanding. That distinction should influence both your pricing and your expected resolution strategy.

How Do You Build a Pricing Matrix?

The pricing matrix is the engine that drives your indicative offer. It assigns a percentage-of-UPB price to every possible combination of four key characteristics:

The Four Stratification Categories

Before you can use this matrix, you need to normalize the raw data in your tape into these standardized categories. The seller's data might say "Late 30 Days" or "30 Days Late" -- both need to map to "Semi-Current." Seller data showing "Late 120 Days" or "FC Initiated" might both map to "FC Initiated" if you want to be conservative. Use nested IF and OR functions to handle the various text strings a seller might use.

Calculating Equity Coverage

Equity coverage standardizes your equity analysis across loans of different sizes. The formula is:

Equity Coverage = (Property Value - Senior Balance) / UPB

A result greater than 1.0 means full equity -- the property value minus the senior balance covers the entire junior lien UPB and then more. A result between 0.25 and 1.0 is partial equity. Below 0.25 is no equity. Below zero means the property is underwater -- the senior balance alone exceeds the property value.

For first liens without a senior balance to subtract, the calculation simplifies to Property Value divided by UPB. If the property value or senior balance is missing, classify the equity tranche as "Unknown."

Creating Granular Tranches

Once every loan has a normalized value for lien position, equity, occupancy, and senior status, concatenate the four fields into a single string. For example: "second - partial equity - owner occupancy - current". This granular tranche label is what connects each loan to its corresponding price in the matrix.

The number of possible combinations across four categories with three to five variables each produces approximately 200 unique granular tranches. That sounds like a lot, but the pricing matrix generates them automatically using nested formulas. You do not need to type out all 200 by hand.

How Do You Assign Prices to Each Tranche?

Each of the four categories receives a value expressed as a decimal percentage of UPB. The matrix then sums those values to produce a composite price for every granular tranche.

Here is an example starting framework:

These values are starting points, not gospel. A second lien with full equity, an owner-occupied property, and a current senior might price at 65 cents on the dollar using these inputs. A second lien with no equity, a vacant property, and a delinquent senior might come in at 35 cents. Those spreads reflect the real risk differentials between those two profiles.

The power of this approach is that adjusting a single category -- say, dropping second liens from 0.17 to 0.12 -- reprices every second lien in the portfolio instantly. You can watch the total portfolio price move from 61 cents down to 52 cents in real time as you fine-tune your assumptions.

How Do You Apply the Matrix to Generate Loan-Level Pricing?

Once the pricing matrix is complete, use a VLOOKUP to match each loan's granular tranche string to the corresponding composite price. The formula looks up the tranche label in your matrix and returns the percentage-of-UPB value.

Multiply that percentage by the loan's updated principal balance, and you have a dollar-denominated indicative offer on every loan in the portfolio.

A quick sanity check at this stage: sum all the indicative offers and divide by the total UPB. For a typical non-performing loan pool of mixed junior liens, a blended price in the range of 45 to 55 cents on the dollar is common, though it depends entirely on the portfolio composition. If the blended number comes back at 80 cents, your assumptions are too aggressive. If it is at 10 cents, you are likely too conservative or the portfolio is unusually distressed.

What Should You Review Before Submitting Your Bid?

Before packaging the bid, re-sort the tape by indicative offer amount (highest to lowest) and spot-check the top loans. These are the ones driving the most dollar exposure in your bid, and errors here are the most expensive.

Look for:

• Equity tranches that do not match the underlying data -- a loan showing 0.8x equity coverage classified as "Full Equity" when it should be "Partial Equity" is a formula error that inflates your price
• Unknown or N/A values that defaulted to the wrong tranche -- missing senior balance data on a second lien should map to "Unknown," not "Full Equity"
• Statute of limitations flags on high-balance loans -- these may need to be manually repriced outside the matrix
• Unsecured loans that were not excluded from the standard tranche logic -- unsecured debt supersedes all other categories and should be priced at 1 cent on the dollar or less, regardless of equity or occupancy

This review stage is where the formula-driven approach pays off. Instead of pricing 190 loans individually, you are verifying that the formulas are correctly categorizing the data. Fix a formula error in the equity tranche column and every affected loan reprices automatically.

How Do You Submit the Letter of Intent?

The seller will specify their preferred format, but nearly every seller expects a loan-level bid breakdown -- a list of loan numbers with a corresponding indicative price for each. Copy the loan numbers and indicative offer amounts into a clean sheet and paste as values (not formulas) so the seller receives static numbers.

Some sellers also want to see your granular tranches or a cover letter explaining your pricing methodology and terms. At a minimum, your letter of intent should include:

• Loan-level pricing for every loan in the portfolio
• Total portfolio offer amount
• Key terms and expectations -- exclusivity period, due diligence timeline, closing conditions
• Your entity information and proof of funds if requested

After submitting, follow up with a call within a day or two. Confirm receipt, reiterate your interest, and set expectations that you can move quickly. The sellers who grant exclusivity tend to favor buyers who are organized, responsive, and demonstrate a clear process -- and a well-structured indicative bid backed by a pricing matrix signals exactly that.

What Happens After the Seller Accepts Your Indicative Bid?

Acceptance of your indicative bid does not mean you are locked into those prices. It means the seller is granting you exclusivity to proceed with advanced due diligence -- ordering BPOs, pulling credit reports, running title searches, and refining every assumption in your pricing matrix with real data instead of estimates.

The groundwork you laid during cursory research -- the equity tranches, occupancy classifications, senior status categories, and statute of limitations flags -- becomes the scaffolding for your deep dive. As new information comes in, you update the underlying data, the granular tranches shift, and the pricing adjusts dynamically. That is the entire point of building the system this way: the structure you create now carries you through the rest of the acquisition process.

The investors who build repeatable pricing systems for bulk portfolios are the ones who can evaluate large tapes quickly, submit competitive bids on time, and refine their numbers with confidence as better data becomes available. The ones who price loans one at a time are the ones who miss deadlines, overbid on bad loans, and underbid on good ones.