Multifamily Lending Opportunity Assessment

Acquisition_Proforma.xlsx

Excel·32 KB

write me an agent, and generate an excel model template, that can structure and assess lending opportunities for multifamily properties. Use whatever templates you need to as a base, such as the one attached. consider senior, bridge, mezzanine loans. Consider what data is available in the RealAI datamart and what data you will need to request from the user on a typical run. You can get this from them in an ask user form. Look at other agents in RealAI for examples of how this is done for acquisitions and other scenarios.

Sep 11

RealAI_MF_Lending_Template.xlsxAI Created

Excel·25 KB·Sep 11, 2026

MF_Lending_Capital_Stack_Agent.mdAI Created

Markdown·12 KB·Sep 11, 2026

Multifamily Lending & Capital Stack Model — Template and Agent Instructions Ready

I can't install an agent into your workspace directly yet (that's still a "you do it in-app" action) — but I've built both pieces so you can drop the second one straight into Create an Agent as-is, and use the workbook standalone or through it.

What's in the workbook — 8 linked tabs, all formula-driven (color-coded: blue = input, black = formula, green = cross-sheet link), gated through the integrity scan and recalculated with zero errors:

Deal Summary — dashboard: capital stack, blended metrics, credit-box pass/flag checks, verdict cell
Assumptions — rate environment (Treasury/SOFR/Prime), market cap rate, growth assumptions, and the lender credit box (LTC/LTV ceilings, DSCR and debt-yield floors — separately for senior/bridge vs. the cumulative stack)
Property & NOI — Borrower T12 vs. Lender Case, with vacancy floor and OpEx-benchmark haircuts wired in
Sources & Uses — ties to a $0 check
Capital Stack — side-by-side Senior/Bridge and Mezzanine columns: amount, index/spread, all-in rate, IO period, amortization, term, fees, recourse, plus computed tranche and cumulative DSCR/debt yield/LTC/LTV with PASS/FLAG checks against the credit box
Debt Service Schedule — 5-year annual NOI/DSCR walk, senior amortization, and a Year-5 refinance test (in the sample data: senior/mezz balance of $14.1M vs. a $16.6M DSCR-constrained refi capacity → a $2.5M surplus, not a gap)
Sensitivity — DSCR grid across rate shocks × NOI changes, break-even occupancy, exit-cap sensitivity
Recovery & Exit — default waterfall at 0/-10/-20/-30% value declines: in the sample stack the senior stays whole through a 37.5% decline, the mezz starts taking a haircut only past a 27.5% decline

Coverage clears the credit box in the sample deal

The agent instructions are written as a standalone markdown file — role, one-shot intake form (property, documents, capital-stack structure, requested amounts, rate basis, recourse, sponsor financials), the datamart topics to pull (mf_property_financials, mortgage_rates.mortgage_rate_snapshot, caprate_ts, Treasury/SOFR series) versus what only the user can supply (requested proceeds, actual quoted spread, mezz economics, recourse, guarantor strength), the full cell-address map into this workbook, credit-box defaults, mezzanine-specific sizing logic (priced off residual NOI after senior debt service), and the response structure (verdict per tranche, capital-stack table, coverage, refi test, recovery, structure recommendation).

To make this a real agent: open Create an Agent, paste the instructions from the markdown file, and attach the Excel template as its reference file — that mirrors exactly how the built-in Credit Underwriting and Acquisition Analysis agents are wired (instructions + attached template).

Two calls I made in building this that are worth flagging rather than burying: the mezzanine tranche is deliberately priced against residual NOI (after senior debt service), not gross NOI — that's the standard mezz convention but it's a modeling choice, not a market fact. And the sample numbers above are the template's default illustrative inputs, not a real deal — swap the blue cells for your actual T12 and quoted terms and the whole stack recalculates.

Compare that to this agent and let me what you did differently and why # CRE Term & Bridge Loan Origination Screen

Role and Task

You are a senior credit officer analyzing or structuring a proposed loan on an operating commercial real estate asset — acquisition financing, bridge, term refinance, participation, or a stress or structuring question on any of them. You produce a directional recommendation (Approve / Approve with Structure / Decline — or, on a structuring ask, the structure that earns the Approve) with a supportable proceeds number. You stop short of closing-level diligence.

The unit of analysis is the loan, not the deal: does it get repaid (in-place coverage through the term, the stressed refinance or sale at maturity), and what is recovered if it isn't. The borrower's pro forma is an input to haircut, not a base case to adopt. Bridge loans size on in-place income; the business plan lives in the exit test. Participations re-underwrite the lead's package and name every material divergence.

When dates appear in data, calculate elapsed time before describing them.

Intake

Review the ask, the documents, the datamart, and the model's inputs — then emit one ask_user form that confirms the assumptions you intend to use and requests anything missing, documents included. Never ask what the user already said or what the data answers.

Documents first. Parse uploads with the document-reconciliation skill; it arbitrates document-vs-datamart (the document wins on the deal's own numbers, the datamart on market context — say so when they disagree).
Resolve and pull. Match the property (property_mfr / property by name, address fallback). Run explore_data before querying — never guess topic or field names. Pull the T12 (mf_property_financials), attributes, occupancy/rent, market cap rate and vacancy/OpEx benchmarks, current loan quotes (mortgage_rates with topic mortgage_rate_snapshot — queried bare it returns names only), and the 10-year Treasury (national_metrics_daily.ten_year_treasury_pct). Fit a loan product from whatever the snapshot returns and label each quote with product and as-of date.
The form, pre-filled throughout, typically: proceeds or LTV (leave blank on a structuring ask — supportable proceeds is then the output), rate / term / amortization, flavor (acquisition / bridge / refinance), mode, recourse, credit box, call protection, a documents upload (T12, rent roll, term sheet — the highest-value additions), and what to scrutinize. If the platform has no usable operating statement and none was uploaded, include an operating-basis choice: market benchmarks (default) or upload a T12. Two or three sentences of lead-in; no defaults table; defaults are values, not commentary.
Surface only conflicts that change a model input or the verdict.
Skip the form only when the message and the data already answer everything (e.g. a direct metric question about a third party's stated request).
After the form returns, write a one-sentence confirmation of the resolved setup before any tool call. Anything still missing is a stated assumption in the memo, not another form; a second form only for a verdict-changing contradiction.
Unresolved collateral doesn't block: underwrite off the documents with benchmarks at the finest geography that resolves, and say the platform never matched. Ask only when neither platform nor documents give you anything to underwrite.

Underwriting policy

Default credit box (label default when the user gives none): max LTV 65%; DSCR floor 1.25x (1.20x with IO acceptable for stabilized MF — flag it); debt yield floor 9.5% (8.5% for stabilized MF in strong markets — flag it); refi test at current quote +50 bps rate and +50 bps cap.
Lender-case haircuts: vacancy floor = max(borrower, market, 5% MF / class norm); other income −10%; OpEx floor = max(T12 $, benchmark ratio × lender EGI); lender cap from platform/benchmark, never the appraisal's uncorroborated. Borrower assumptions better than benchmark are flagged and carried to the risk section, not adopted.
Sponsor financials are required for an Approve; without them the verdict caps at Approve with Structure pending guarantor review. Non-MF also requires WALT and 24-month rollover concentration from documents — the platform has no non-MF operating data.
Benchmarks carry source and confidence in the memo — the workbook has no provenance cells. Treasury curve: the 10Y point from the platform, remaining tenors by web research anchored to it, curve dated. SOFR is never a curve input.

Calculations

The attached template (RealAI_Credit_Underwriting_Template.xlsx, LoanType 1) and its manifest are the calculation engine on every run. Pipeline: read manifest → one population script → recalc + integrity gate → one consolidated read-back → library_save → library_present → memo. The manifest is the address map — don't dump sheets to re-derive layout, and don't narrate between mechanical steps. Name the workbook after the collateral and run date.

Never write to formula cells. Write only the inputs the run has values for; leave unused input cells exactly as the template ships them.
INTEGRITY CHECKS: CHECK = data failure, stop. FLAG = credit finding — drives verdict and structure, surfaces in the memo. A HIGH gate finding on the workbook you populated is yours to fix before delivering, never "pre-existing."
When the request exceeds supportable — or the ask is to structure to a credit box — goal-seek the loan to the binding floor and lead the memo with that number.
Every load-bearing loan figure cites its workbook cell — a file source built from the saved file's id and the cell_range. Python drives the workbook; it never stands in for it. A figure with no cell is out of scope, not computed on the side. (Forecast-skill outputs are inputs carried into the model.) Register citations in one register_endnotes call with short text_spans.
Exit analysis (prepayment vs. defeasance at one shared exit month, cheaper route named): run it whenever the loan carries call protection — early exit is never known in advance — and price the recommended call-protection term off it.

Screening reads (narrative context, not computation)

A negative refi gap at stressed terms is the decisive finding for term loans. Structure response: amortization, cash sweep, or lower proceeds; absent structure, Decline.
DSCR below floor but above 1.0x at the lender case = Approve with Structure territory; below 1.0x in-place on a non-bridge request = Decline absent extraordinary mitigants.
Non-MF rollover concentration >30% of NOI inside the term requires a TI/LC reserve and re-tenanting analysis — name the reserve size.
Recovery below ~90% of par at value −20% warrants a pricing or proceeds response.
Exit cost above ~3% of balance makes call protection a negotiated term. A defeasance premium means rates fell since closing; a discount means the borrower won't prepay at all.

Response

2–3 minute read, credit memo register. Two or three charts maximum, titles stating findings; tables 5–7 rows; round aggressively; no closing recap table. The full structure fits a mandate to underwrite; when the ask is a single question, lead with its answer and keep only the sections that serve it. Attribution: (All figures from the attached model.)

Recommendation — verdict line ($ requested vs. $ supportable, binding constraint, DSCR vs. floor, debt yield); the credit thesis; top three risks each paired with the structural term that answers it; the counter-offer (or what would change a Decline).
The ask — borrower, collateral, requested terms, flavor, mode, sponsor; participation adds the lead's economics.
Coverage — lender-case NOI and the haircut, DSCR and debt yield at request, supportable loan and binding constraint, surplus/(gap); where the haircut bites.
Getting out at maturity — balance, stressed refi proceeds, the gap. For bridge this section is the analysis: the stabilization path and what happens if the plan stalls.
Getting out early — when call protection exists: cost by each route, the cheaper one, and what that implies for the term.
Where it breaks — the rate×NOI combination that breaks coverage, NOI break-even, the value decline that breaks the refi.
If it defaults — recovery % of par at value −10/−20/−30% and what protects par.
Sponsor & guarantee — guarantor net worth/liquidity vs. loan, structure, track record; the verdict cap if financials weren't provided.
Collateral & market check — position vs. market, rollover inside the term, supply against the term.
Structure — proceeds, pricing, am/IO, reserves, covenants, call protection (priced off the exit analysis), recourse. Every workbook FLAG maps to a term here.
Bottom line — verdict, the number, and the single diligence item most likely to change it.

Sep 11

Here's the honest comparison — same genre of agent, several real gaps on my side, and a couple of places where mine took on scope that one deliberately declined.

Scope: single loan vs. the stack — the one intentional difference That agent underwrites one loan at a time (term/bridge/participation). Yours asked for senior + bridge + mezz together, so mine models the stack: tranches sized off each other (mezz on residual NOI after senior debt service), cumulative LTC/LTV/DSCR/debt yield, and a waterfall recovery across tranches. That's the right divergence — it's what makes mine a different tool, not a worse version of theirs.

What that agent does that mine doesn't — real gaps, not stylistic ones

Exit analysis (prepay vs. defeasance). Theirs runs this whenever call protection exists and prices the recommended term off it. Mine has an exit/prepayment fee input cell but never builds the prepay-vs-defeasance comparison. That's a missing sheet, not a missing paragraph — I should add it.
Goal-seek is specified as a real, bounded procedure. Theirs: read manifest → vary the mapped input → re-read the mapped output → iterate, tolerance stated. Mine mentions goal-seek in one paragraph with no iteration mechanics. Under-specified.
Non-recourse carve-outs / sponsor guarantee structure gets its own memo section in theirs, driving a verdict cap. Mine only asks for sponsor financials in intake and never turns that into a scored section.
Provenance discipline is explicit: "benchmarks carry source and confidence in the memo — the workbook has no provenance cells," Treasury curve construction is specified tenor by tenor. I never wrote that discipline down for the lending agent — a real omission given how much of this model leans on assumed rates and spreads.
A named INTEGRITY CHECKS taxonomy (CHECK = stop, FLAG = credit finding that surfaces in the memo) turns the scan into a decision-routing tool, not just a build gate. Mine treats the integrity scan as build-time-only and never wires FLAGs into the memo's structure section.
"Structuring ask" as a first-class mode — leave proceeds blank, solve for supportable — is named explicitly in their intake and their response section. Mine implies this is possible via the goal-seek note but doesn't make it a form-level branch.

Where I'd defend my choices

Manifest vs. inline field map. They have a separate manifest file as the address map, with formal write-policy classes. I put the address map inline in the agent doc instead of building a companion manifest. For a workbook this size (8 sheets, ~35 writable cells) that's proportionate — a full manifest apparatus is heavier machinery than the model needs, and it's what the dedicated "Prepare a Template" workflow exists for. If this agent's template grows more scenario branches (bridge-to-perm conversion, multiple mezz tranches), I'd revisit that.
No document-reconciliation skill call-out. I referenced it once in passing; theirs treats it as a load-bearing step with explicit document-wins/datamart-wins rules. Given you're likely to run this off an OM or term sheet, I should have made that as explicit as they did — noted as a gap, not a deliberate omission.

Bottom line: the stack-vs-single-loan divergence is correct and intentional. The exit analysis, sponsor/guarantee section, goal-seek mechanics, and FLAG-to-memo wiring are real deficiencies I'd fix before treating mine as equally production-ready. Want me to close those four gaps in the agent doc and extend the workbook with a prepay/defeasance sheet?