A sponsor is requesting acquisition financing for Rancho San Joaquin Apartment Homes in Irvine CA at 70% LTV. Based on the property's financials, what's the Day 1 DSCR and does this deal meet agency underwriting standards?
cd /vercel/sandbox && pip install openpyxl >/dev/null 2>&1; python3 -c "
import openpyxl
wb = openpyxl.load_workbook('agents/01a087be-a40c-70fb-88a9-40a61b0703c2/RealAI_Credit_Underwriting_Template_v3_clean.xlsx', data_only=False)
ws = wb['Assumptions']
for row in range(1,60):
for col in ['B','C','E','F','H','I']:
cell = ws[f'{col}{row}']
if cell.value is not None:
print(f'{col}{row}: {repr(cell.value)}')
"
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('agents/01a087be-a40c-70fb-88a9-40a61b0703c2/RealAI_Credit_Underwriting_Template_v3_clean.xlsx', data_only=False)
ws = wb['Assumptions']
for addr in ['C5','C6','C7','C8','C9','C10','C11','C13','C14','C15','C18','C19','C20','F5','F6','F7','F8','F9','F10','F11','F12','F14','F15','F18','F19','F20','F21','F22','F23','F24','F25','F29','F30','F32','F33','F35','F36','F37','F38','F39','F40','F41','F44']:
c = ws[addr]
print(addr, c.number_format, c.value)
print('---data validations---')
for dv in ws.data_validations.dataValidation:
print(dv.sqref, dv.formula1)
"
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('agents/01a087be-a40c-70fb-88a9-40a61b0703c2/RealAI_Credit_Underwriting_Template_v3_clean.xlsx', data_only=False)
ws = wb['Pro Forma']
for row in range(1,55):
for col in ['A','B','C','D','E','F','G','H','I','J']:
cell = ws[f'{col}{row}']
if cell.value is not None:
print(f'{col}{row}: {repr(cell.value)}')
"
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('agents/01a087be-a40c-70fb-88a9-40a61b0703c2/RealAI_Credit_Underwriting_Template_v3_clean.xlsx', data_only=False)
for sheetname in ['Loan Sizing','Prepayment']:
ws = wb[sheetname]
print('====',sheetname)
for row in range(1,90):
for col in ['A','B','C','D','E','F','G','H','I','J','K']:
cell = ws[f'{col}{row}']
if cell.value is not None:
print(f'{col}{row}: {repr(cell.value)}')
"
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('agents/01a087be-a40c-70fb-88a9-40a61b0703c2/RealAI_Credit_Underwriting_Template_v3_clean.xlsx', data_only=False)
for sheetname in ['Refi Analysis','Recovery','Stress & Break-Even','Defeasance']:
ws = wb[sheetname]
print('====',sheetname)
for row in range(1,65):
for col in ['A','B','C','D','E','F','G']:
cell = ws[f'{col}{row}']
if cell.value is not None:
print(f'{col}{row}: {repr(cell.value)}')
"
mkdir -p /vercel/sandbox/work && cp "/vercel/sandbox/agents/01a087be-a40c-70fb-88a9-40a61b0703c2/RealAI_Credit_Underwriting_Template_v3_clean.xlsx" "/vercel/sandbox/work/Rancho San Joaquin Apartment Homes_Credit_UW_2026-09-10_v1.xlsx" ls -la /vercel/sandbox/work
import openpyxl
from datetime import date
path = "work/Rancho San Joaquin Apartment Homes_Credit_UW_2026-09-10_v1.xlsx"
wb = openpyxl.load_workbook(path, data_only=False)
A = wb['Assumptions']
PF = wb['Pro Forma']
PP = wb['Prepayment']
DF = wb['Defeasance']
# ---------------- Assumptions: Collateral ----------------
A['C5'] = "Rancho San Joaquin Apartment Homes"
A['C6'] = "20 Pergola"
A['C7'] = "Irvine, CA 92612"
A['C8'] = "Multifamily"
A['C9'] = 368
A['C10'] = 349600
A['C11'] = "Units"
A['C13'] = 1976
A['C14'] = 0.9755
A['C15'] = "Market Averages"
# ---------------- Sponsor & guarantee ----------------
A['C20'] = "No"
# ---------------- Growth & exit ----------------
A['C28'] = 0.03
A['C29'] = 0
A['C30'] = 0.02
A['C31'] = "Deep"
# ---------------- Takeout terms at maturity ----------------
A['C34'] = 0.0611
A['C35'] = 1.25
A['C36'] = 0.65
A['C37'] = 0.095
A['C38'] = 30
A['C41'] = "Deep"
# ---------------- Loan request ----------------
A['F5'] = "Origination"
A['F6'] = "Acquisition-Term"
A['F7'] = date(2026, 10, 1)
A['F8'] = 1000000 # placeholder, corrected after lender value is computed
A['F9'] = 0.0611
A['F10'] = 10
A['F11'] = 0
A['F12'] = 30
A['F14'] = 0.01
A['F15'] = "Non-Recourse"
# ---------------- Credit box (default) ----------------
A['F18'] = 0.65
A['F19'] = 1.25
A['F20'] = 1.20
A['F21'] = 0.095
A['F22'] = 0.10
A['F23'] = "default"
A['F24'] = 50
A['F25'] = 50
# ---------------- Market benchmarks ----------------
A['F29'] = 0.0509 # South Irvine submarket vacancy (1 - 0.9491 occupancy)
A['F30'] = 0.05 # MF asset-class norm
A['F32'] = 0.4303 # LA CBSA benchmark opex ratio (submarket not carried)
A['F33'] = 0.42 # MF asset-class norm
A['F35'] = "Yes"
A['F36'] = 0.0519 # LA CBSA multifamily cap rate, 2Q26
# F37 appraisal cap rate - left blank, no appraisal on file
A['F38'] = 3603.33 # property's own asking rent avg (n=9, EXCELLENT confidence)
A['F39'] = 0.01 # concessions - AI estimate, no datamart line
A['F40'] = 0.01 # bad debt - AI estimate
A['F41'] = 0.0302 # other income benchmark (LA CBSA, pct of net rent used as proxy)
A['F44'] = "Modest"
# ---------------- Pro Forma rate assumptions ----------------
PF['C39'] = 0.0393 # management fee % of EGI, LA CBSA benchmark
PF['C40'] = 250 # replacement reserves $/unit, stabilized MF estimate
# ---------------- Prepayment ----------------
PP['C4'] = 120 # exit month = loan maturity (120 mo) -> hold-to-maturity case
PP['C5'] = 0.01
PP['C6'] = 0.02
PP['C7'] = 50
PP['C8'] = 24
PP['C9'] = 3
PP['C10'] = "Yield Maintenance"
curve = {27: (6, 0.0397), 28: (12, 0.0412), 29: (24, 0.0436), 30: (36, 0.0441),
31: (60, 0.0454), 32: (84, 0.0465), 33: (120, 0.0477), 34: (240, 0.0523), 35: (360, 0.0522)}
for row, (months, rate) in curve.items():
PP[f'C{row}'] = months
PP[f'D{row}'] = rate
PP['C36'] = date(2026, 9, 4)
stepdown = [0.05, 0.04, 0.03, 0.02, 0.01, 0, 0, 0, 0, 0]
for i, v in enumerate(stepdown):
PP[f'C{49+i}'] = v
# ---------------- Defeasance ----------------
DF['C11'] = 25000
DF['C12'] = 40000
DF['C13'] = 10000
DF['C14'] = 15000
DF['C15'] = 10000
DF['C16'] = "Open Window"
wb.save(path)
print("Pass 1 written.")
cd /vercel/sandbox && python3 work/populate.py && soffice --headless --convert-to xlsx --outdir work/recalc "work/Rancho San Joaquin Apartment Homes_Credit_UW_2026-09-10_v1.xlsx" 2>&1 | tail -5
which libreoffice soffice.bin 2>&1; ls /usr/bin | grep -i office; ls /opt 2>&1
cd /vercel/sandbox && libreoffice --headless --convert-to xlsx --outdir work/recalc "work/Rancho San Joaquin Apartment Homes_Credit_UW_2026-09-10_v1.xlsx" 2>&1 | tail -10
cd /vercel/sandbox && python3 skills/xlsx/scripts/recalc.py "work/Rancho San Joaquin Apartment Homes_Credit_UW_2026-09-10_v1.xlsx" 2>&1 | tail -30
cd /vercel/sandbox && cp "agents/01a087be-a40c-70fb-88a9-40a61b0703c2/RealAI_Credit_Underwriting_Template_v3_clean.xlsx" work/orig_check.xlsx && python3 skills/xlsx/scripts/recalc.py work/orig_check.xlsx 2>&1 | tail -20
Pre-existing template error on the hidden Version sheet, not something I introduced. Now reading back the lender value to size proceeds at 70% LTV, then finalizing.
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('work/Rancho San Joaquin Apartment Homes_Credit_UW_2026-09-10_v1.xlsx', data_only=True)
pf = wb['Pro Forma']
print('Lender NOI G28', pf['G28'].value)
print('Lender Value C44', pf['C44'].value)
print('G11 EGI', pf['G11'].value)
print('G25 opex', pf['G25'].value)
"
A['C15'] = "Market Averages"
A['C15'] = "T12 Actuals"
# ---------------- Pro Forma rate assumptions ---------------- PF['C39'] = 0.0393 # management fee % of EGI, LA CBSA benchmark PF['C40'] = 250 # replacement reserves $/unit, stabilized MF estimate
# ---------------- Pro Forma: T12 actuals (platform property financials) ----------------
PF['E5'] = 13059831.44 # Gross Potential Rent (actual, RealAI Ops Benchmarks)
PF['E6'] = 453500.70 # Vacancy loss (actual)
PF['E7'] = 0 # Concessions - not disclosed
PF['E8'] = 0 # Bad debt - not disclosed
PF['E9'] = 380751 # Other income - estimated at LA CBSA benchmark ratio (3.02% of net rent) since not disclosed
for r in range(13, 23):
PF[f'E{r}'] = 0 # Full T12 expense detail not carried by the platform; lender-case OpEx floors at the benchmark ratio (Pro Forma!G24)
# ---------------- Pro Forma rate assumptions ----------------
PF['C39'] = 0.0393 # management fee % of EGI, LA CBSA benchmark
PF['C40'] = 250 # replacement reserves $/unit, stabilized MF estimatecd /vercel/sandbox && rm -f "work/Rancho San Joaquin Apartment Homes_Credit_UW_2026-09-10_v1.xlsx" && cp "agents/01a087be-a40c-70fb-88a9-40a61b0703c2/RealAI_Credit_Underwriting_Template_v3_clean.xlsx" "work/Rancho San Joaquin Apartment Homes_Credit_UW_2026-09-10_v1.xlsx" && python3 work/populate.py && python3 skills/xlsx/scripts/recalc.py "work/Rancho San Joaquin Apartment Homes_Credit_UW_2026-09-10_v1.xlsx" 2>&1 | tail -20
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('work/Rancho San Joaquin Apartment Homes_Credit_UW_2026-09-10_v1.xlsx', data_only=True)
pf = wb['Pro Forma']
print('E11 EGI', pf['E11'].value)
print('E28 T12 NOI', pf['E28'].value)
print('G11 Lender EGI', pf['G11'].value)
print('G23 lender opex sum', pf['G23'].value)
print('G24 opex floor', pf['G24'].value)
print('G25 opex lender case', pf['G25'].value)
print('Lender NOI G28', pf['G28'].value)
print('G29/G30 haircut', pf['G29'].value, pf['G30'].value)
print('Lender Value C44', pf['C44'].value)
print('F43 mark to market', wb['Assumptions']['F43'].value)
a = wb['Assumptions']
print('I27 basis sourced', a['I27'].value)
print('I28 t12 reconciles', a['I28'].value)
"
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('work/Rancho San Joaquin Apartment Homes_Credit_UW_2026-09-10_v1.xlsx', data_only=False)
A = wb['Assumptions']
lender_value = 139820866.390277
proceeds = round(0.70*lender_value, 0)
print('proceeds', proceeds)
A['F8'] = proceeds
wb.save('work/Rancho San Joaquin Apartment Homes_Credit_UW_2026-09-10_v1.xlsx')
"
python3 skills/xlsx/scripts/recalc.py "work/Rancho San Joaquin Apartment Homes_Credit_UW_2026-09-10_v1.xlsx" 2>&1 | tail -20Now let's read back all the key metrics, integrity checks, and headline outputs.
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('work/Rancho San Joaquin Apartment Homes_Credit_UW_2026-09-10_v1.xlsx', data_only=True)
A = wb['Assumptions']
LS = wb['Loan Sizing']
PF = wb['Pro Forma']
RA = wb['Refi Analysis']
REC = wb['Recovery']
SB = wb['Stress & Break-Even']
AM = wb['Amortization']
print('--- Key metrics dashboard ---')
for r in range(5,21):
print('I'+str(r), A['I'+str(r)].value)
print('--- Loan Sizing ---')
for c in ['C17','C18','C19','C20','C29','C30','C31','C32','C35','C36','C37','C38','C42','C43']:
print(c, LS[c].value)
print('--- Coverage tests ---')
for r in range(24,28):
print(LS['B'+str(r)].value, 'floor', LS['C'+str(r)].value, 'maxloan', LS['E'+str(r)].value, 'result', LS['F'+str(r)].value)
print('--- Pro forma ---')
print('Lender NOI', PF['G28'].value, 'Lender value', PF['C44'].value, 'haircut%', PF['G30'].value)
print('--- Refi analysis maturity ---')
for c in ['C19','C20','C21','C22','C23','C24','C28','C29','C30','C31','C32','C33']:
print(c, RA[c].value)
print('--- Recovery ---')
for c in ['C14','D14','E14','F14','E13','E16']:
print(c, REC[c].value)
print('--- Stress & Break-even ---')
for c in ['C12','C18','C19','C20','C21','C23','C24','C29','C31','C32','C33']:
print(c, SB[c].value)
print('--- Amortization ---')
print('N4 balance at maturity', AM['N4'].value)
print('--- Integrity checks ---')
for r in range(23,48):
print('H'+str(r), A['H'+str(r)].value, '=>', A['I'+str(r)].value)
"
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('work/Rancho San Joaquin Apartment Homes_Credit_UW_2026-09-10_v1.xlsx', data_only=True)
LS = wb['Loan Sizing']
print('C15 DSCR-Amort', LS['C15'].value)
print('C16 DSCR-IO', LS['C16'].value)
print('J12 governing basis', LS['J12'].value)
print('J10 annual DS amort', LS['J10'].value)
print('J11 annual DS IO', LS['J11'].value)
print('J6 IO months', LS['J6'].value)
"
A['F44'] = "Modest"
A['F44'] = "Modest" A['F47'] = "No" # rollover analysis not applicable - multifamily # ---------------- Flag thresholds (template default) ---------------- A['F55'] = 0.90 A['F56'] = 0.30 A['F57'] = 0.10 A['F58'] = 0.03
cd /vercel/sandbox && rm -f "work/Rancho San Joaquin Apartment Homes_Credit_UW_2026-09-10_v1.xlsx" && cp "agents/01a087be-a40c-70fb-88a9-40a61b0703c2/RealAI_Credit_Underwriting_Template_v3_clean.xlsx" "work/Rancho San Joaquin Apartment Homes_Credit_UW_2026-09-10_v1.xlsx" && python3 work/populate.py && python3 skills/xlsx/scripts/recalc.py "work/Rancho San Joaquin Apartment Homes_Credit_UW_2026-09-10_v1.xlsx" 2>&1 | tail -10
python3 -c "
import openpyxl
wb = openpyxl.load_workbook('work/Rancho San Joaquin Apartment Homes_Credit_UW_2026-09-10_v1.xlsx', data_only=True)
pf = wb['Pro Forma']
print('Lender value C44', pf['C44'].value)
"Now setting proceeds at 70% LTV and doing final recalc.
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('work/Rancho San Joaquin Apartment Homes_Credit_UW_2026-09-10_v1.xlsx', data_only=False)
A = wb['Assumptions']
A['F8'] = round(0.70*139820866.390277,0)
wb.save('work/Rancho San Joaquin Apartment Homes_Credit_UW_2026-09-10_v1.xlsx')
"
python3 skills/xlsx/scripts/recalc.py "work/Rancho San Joaquin Apartment Homes_Credit_UW_2026-09-10_v1.xlsx" 2>&1 | tail -10cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('work/Rancho San Joaquin Apartment Homes_Credit_UW_2026-09-10_v1.xlsx', data_only=True)
A = wb['Assumptions']
LS = wb['Loan Sizing']
PF = wb['Pro Forma']
RA = wb['Refi Analysis']
REC = wb['Recovery']
SB = wb['Stress & Break-Even']
DF = wb['Defeasance']
print('Requested proceeds F8', A['F8'].value)
print('Lender NOI I5', A['I5'].value)
print('NOI haircut I6', A['I6'].value)
print('Lender value I7', A['I7'].value)
print('Supportable proceeds I8', A['I8'].value)
print('Binding constraint I9', A['I9'].value)
print('Gap I10', A['I10'].value)
print('Counter-offer I11', A['I11'].value)
print('DSCR at request I12', A['I12'].value)
print('Debt yield at request I13', A['I13'].value)
print('LTV at request I14', A['I14'].value)
print('Balance at maturity I15', A['I15'].value)
print('Stressed refi proceeds I16', A['I16'].value)
print('Refi gap I17', A['I17'].value)
print('NOI break-even I18', A['I18'].value)
print('NOI cushion% I19', A['I19'].value)
print('Recovery -20% I20', A['I20'].value)
print()
for r in range(23,48):
print(A['H'+str(r)].value, '=>', A['I'+str(r)].value)
print()
print('Defeasance cheaper route', DF['C47'].value, 'savings', DF['C48'].value)
"
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('work/Rancho San Joaquin Apartment Homes_Credit_UW_2026-09-10_v1.xlsx', data_only=True)
LS = wb['Loan Sizing']
print('per unit at request', LS['C20'].value)
print('per unit supportable', 76386347.0068986/368)
print('NOI per unit', 7256702.96565537/368)
"
Sizing a $97.9M acquisition loan against Rancho San Joaquin Apartment Homes (368 units, Irvine, CA) at 70% of a $139.8M lender value, quoted off current Fannie Mae conventional multifamily pricing (~6.11% all-in, interpolated between the 65%/75% LTV brackets since 70% sits between tiers; 10-year term, 30-year amortization, no IO) against the default 65% LTV / 1.25x DSCR / 9.5% debt yield credit box.
No — at 70% LTV this loan doesn't clear agency coverage. Day-1 DSCR is 1.02x, roughly 20% short of the 1.25x floor.
no-op
No-op task: no analysis was requested or performed.
At the requested $97.9M proceeds, the deal fails every agency coverage test at once — not just DSCR:
| Metric | At Request | Agency Floor | Result |
|---|---|---|---|
| DSCR (Amortizing, governing)1 | 1.02x | 1.25x | FAIL |
| Debt Yield | 7.41% | 9.50% | FAIL |
| LTV | 70.0% | 65.0% max | FAIL |
| DSCR vs 1.00x | 1.02x | 1.00x | PASS |
Lender-case NOI comes in at $7.26M (haircut driven by the LA-CBSA 43% OpEx-ratio floor, since the platform carries only revenue actuals for this property — no expense detail — so the lender case grosses actual GPR/vacancy loss through the market benchmark, the conservative read absent a T12). Capitalized at the LA-metro multifamily cap rate of 5.19%, that's a $139.8M lender value ($380K/unit) — so 70% of value is $97.9M, but the debt yield test caps supportable proceeds at $76.4M (54.6% LTV), a $21.5M gap. Debt yield binds ahead of LTV because it's the more conservative constraint at this NOI level; LTV alone would still let $90.9M through.
Two more flags worth naming: NOI cushion to 1.00x DSCR is a thin 1.8% — almost no room before coverage breaks — and recovery at a 20% value decline is 83.9% of par, under the 90% threshold. The maturity refi, by contrast, clears comfortably (+$19.5M gap under a +50bp rate/cap stress), because 10 years of amortization and 3%/yr NOI growth do a lot of work by then — Day 1 is the pinch point here, not the back end.
Counter-offer: $76.4M (54.6% LTV) is what the collateral supports today, debt-yield-bound. Getting to $97.9M requires either a materially higher basis (a real T12 showing lower actual OpEx than the 43% benchmark floor would help, since that floor is doing most of the haircutting) or accepting sub-agency terms (bridge/debt fund at a wider spread, or a cash-management/sweep structure a life co might accept below its usual floor). Sponsor financials weren't provided, which caps any verdict here at Approve with Structure pending guarantor review even before the proceeds gap is addressed.
This is a read-only snapshot of a RealAI analysis.