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?
find / -ipath "*01a087be*" 2>/dev/null
Submarket has no mf_pnl_benchmarks topic — only market-level. Let's pull submarket rent/occupancy for vacancy and market rent inputs.
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 row in range(1,60):
for col in ['A','B','C','D','E','F','G','H','I']:
c = ws[f'{col}{row}']
if c.value is not None:
print(f'{col}{row}: {repr(c.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['Pro Forma']
for row in range(1,55):
for col in ['A','B','C','D','E','F','G','H','I','J']:
c = ws[f'{col}{row}']
if c.value is not None:
print(f'{col}{row}: {repr(c.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 sheet in ['Loan Sizing','Prepayment','Defeasance','Refi Analysis','Recovery','Stress & Break-Even']:
ws = wb[sheet]
print('=====', sheet, ws.dimensions)
for row in range(1, min(ws.max_row,90)+1):
for col in ['A','B','C','D','E','F','G','H','I','J','K']:
c = ws[f'{col}{row}']
if c.value is not None and (isinstance(c.value,str) and not c.value.startswith('=') or not isinstance(c.value,str)):
print(f'{col}{row}: {repr(c.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 dv in ws.data_validations.dataValidation:
print(dv.sqref, dv.formula1)
"
cd /vercel/sandbox && cp agents/01a087be-a40c-70fb-88a9-40a61b0703c2/RealAI_Credit_Underwriting_Template_v3_clean.xlsx "Rancho San Joaquin_Credit_UW_2026-09-09_v1.xlsx" && ls -la *.xlsx
import openpyxl
from datetime import date
FN = "Rancho San Joaquin_Credit_UW_2026-09-09_v1.xlsx"
wb = openpyxl.load_workbook(FN)
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'] = "Lower of T12 and Market"
# ---- Sponsor & Guarantee ----
A['C18'] = "Undisclosed sponsor (not provided with the request)"
A['C19'] = "Partial / Springing"
A['C20'] = "No"
# ---- Growth & exit ----
A['C28'] = 0.03
A['C29'] = 0
A['C30'] = 0.02
A['C31'] = "Deep"
# ---- Takeout terms at maturity (Fannie Mae Conventional, 70% LTV interpolated) ----
A['C34'] = 0.0611
A['C35'] = 1.30
A['C36'] = 0.70
A['C37'] = 0.095
A['C38'] = 30
A['C41'] = "Deep"
# ---- Bridge / participation (not used - Acquisition-Term, Origination) ----
A['C55'] = 0
A['C56'] = 0
# ---- Loan request ----
A['F5'] = "Origination"
A['F6'] = "Acquisition-Term"
A['F7'] = date(2026, 10, 15)
# F8 (Requested Proceeds) set in pass 2 once lender value is known
A['F9'] = 0.0611
A['F10'] = 10
A['F11'] = 2
A['F12'] = 30
A['F14'] = 0.01
A['F15'] = "Non-Recourse"
# ---- Credit box (current agency quotes, 70% LTV tier interpolated between Fannie 65%/75% tiers) ----
A['F18'] = 0.75
A['F19'] = 1.25
A['F20'] = 1.20
A['F21'] = 0.085
A['F22'] = 0.10
A['F23'] = "user-provided"
A['F24'] = 50
A['F25'] = 50
# ---- Market benchmarks ----
A['F29'] = 0.0509 # South Irvine submarket vacancy (1 - 94.91% occupancy)
A['F30'] = 0.05 # MF asset-class vacancy norm
A['F32'] = 0.4303 # LA-Long Beach-Anaheim MSA MF OpEx ratio (% of EGI)
A['F33'] = 0.42 # MF asset-class OpEx norm
A['F35'] = "Yes"
A['F36'] = 0.0519 # LA-OC MSA multifamily cap rate, 2Q26 (lender cap)
A['F38'] = 3435.13 # South Irvine submarket in-place rent, $/unit/month
A['F39'] = 0
A['F40'] = 0
A['F41'] = 0.03
A['F44'] = "Modest"
# ---- Flag thresholds ----
A['F55'] = 0.90
A['F56'] = 0.30
A['F57'] = 0.10
A['F58'] = 0.03
# ---- Rollover (not applicable - multifamily) ----
A['F47'] = "No"
# ---- Pro Forma: T12 actuals (RealAI Ops Benchmarks) ----
PF['E5'] = 13059831.44 # Gross Potential Rent
PF['E6'] = 453500.70 # Vacancy loss
PF['E7'] = 0 # Concessions - not disclosed in dataset
PF['E8'] = 0 # Bad debt - not disclosed
PF['E9'] = 0 # Other income - not disclosed
for r in range(13, 23):
PF[f'E{r}'] = 0
PF['C39'] = 0.03 # Management fee % of EGI
PF['C40'] = 300 # Replacement reserves $/unit/yr
wb.save(FN)
print("Pass 1 written.")
wb.save(FN)
print("Pass 1 written.")# ---- Prepayment: hold-to-maturity acquisition, standard agency Yield Maintenance ----
PP['C4'] = 120 # exit month = full 10-yr term (hold to maturity)
PP['C5'] = 0.01
PP['C6'] = 0.02
PP['C7'] = 50
PP['C8'] = 0 # agency loans: no traditional lockout, YM governs instead
PP['C9'] = 3 # open period before maturity
PP['C10'] = "Yield Maintenance"
PP['C36'] = date(2026, 9, 8)
# Treasury par curve (web curve 2026-09-08, anchored to platform 10Y=4.77% on 2026-09-03)
curve = {27: 0.0400, 28: 0.0415, 29: 0.0439, 30: 0.0444, 31: 0.0457, 32: 0.0468, 33: 0.0477, 34: 0.0526, 35: 0.0525}
for row, rate in curve.items():
PP[f'D{row}'] = rate
stepdown = {49: 0.05, 50: 0.04, 51: 0.03, 52: 0.02, 53: 0.01, 54: 0.0, 55: 0.0, 56: 0.0, 57: 0.0, 58: 0.0}
for row, fee in stepdown.items():
PP[f'C{row}'] = fee
# ---- Defeasance: transaction costs (template defaults) ----
DF['C11'] = 25000
DF['C12'] = 40000
DF['C13'] = 10000
DF['C14'] = 15000
DF['C15'] = 10000
DF['C16'] = "Open Window"
wb.save(FN)
print("Pass 1 written.")cd /vercel/sandbox && python3 populate_pass1.py
Now recalculate and read the lender value to size proceeds at 70% LTV.
cd /vercel/sandbox && soffice --headless --calc --convert-to xlsx:"Calc MS Excel 2007 XML" --outdir recalc1 "Rancho San Joaquin_Credit_UW_2026-09-09_v1.xlsx" 2>&1 | tail -5
which libreoffice; ls /usr/bin | grep -i office; ls /opt 2>/dev/null
cd /vercel/sandbox && timeout 120 libreoffice --headless --convert-to xlsx:"Calc MS Excel 2007 XML" --outdir recalc1 "Rancho San Joaquin_Credit_UW_2026-09-09_v1.xlsx" 2>&1 | tail -10
cd /vercel/sandbox && python3 skills/xlsx/scripts/recalc.py "Rancho San Joaquin_Credit_UW_2026-09-09_v1.xlsx" 2>&1 | tail -30
cd /vercel/sandbox && cp agents/01a087be-a40c-70fb-88a9-40a61b0703c2/RealAI_Credit_Underwriting_Template_v3_clean.xlsx /tmp/orig.xlsx && python3 skills/xlsx/scripts/recalc.py /tmp/orig.xlsx 2>&1 | tail -20
Pre-existing template artifact on the hidden Version sheet, unrelated to our inputs. Now let's read the lender value to size proceeds at 70% LTV.
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('Rancho San Joaquin_Credit_UW_2026-09-09_v1.xlsx', data_only=True)
PF = wb['Pro Forma']
print('T12 GPR E5', PF['E5'].value)
print('G5 GPR', PF['G5'].value)
print('G6 Vacancy', PF['G6'].value)
print('G9 Other income', PF['G9'].value)
print('G11 EGI', PF['G11'].value)
print('G23 total opex(sum lines vs market)', PF['G23'].value)
print('G24 opex floor', PF['G24'].value)
print('G25 total opex lender', PF['G25'].value)
print('G28 NOI', PF['G28'].value)
print('C43 lender cap', PF['C43'].value)
print('C44 lender value', PF['C44'].value)
print('C45 value per unit', PF['C45'].value)
"
import openpyxl
FN = "Rancho San Joaquin_Credit_UW_2026-09-09_v1.xlsx"
wb = openpyxl.load_workbook(FN)
A = wb['Assumptions']
lender_value = 115685148.763692
requested_proceeds = round(0.70 * lender_value, 2)
A['F8'] = requested_proceeds
wb.save(FN)
print("Requested proceeds set to", requested_proceeds)
cd /vercel/sandbox && python3 populate_pass2.py && python3 skills/xlsx/scripts/recalc.py "Rancho San Joaquin_Credit_UW_2026-09-09_v1.xlsx" 2>&1 | tail -20
Now let's read back every mapped output and the integrity checks.
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('Rancho San Joaquin_Credit_UW_2026-09-09_v1.xlsx', data_only=True)
LS = wb['Loan Sizing']
PF = wb['Pro Forma']
AM = wb['Amortization']
RF = wb['Refi Analysis']
RC = wb['Recovery']
SB = wb['Stress & Break-Even']
A = wb['Assumptions']
print('--- Loan Sizing ---')
for cell in ['C14','C15','C16','C17','C18','C19','C20','C29','C30','C31','C32','C35','C36','C37','C38','C39','C40','C41','C42','C43']:
print(cell, LS[cell].value)
print('--- Pro Forma ---')
for cell in ['G28','G29','G30','G31','C43','C44','C45']:
print(cell, PF[cell].value)
print('--- Amortization ---')
print('N4 balance at maturity', AM['N4'].value)
print('N5 WAL', AM['N5'].value)
print('N6 lender APR', AM['N6'].value)
print('--- Refi Analysis ---')
for cell in ['C19','C20','C21','C22','C23','C24','C25','C26','C27','C28','C29','C30','C31','C32','C33','C36','C37','C38','C39','C40','C41']:
print(cell, RF[cell].value)
print('--- Recovery ---')
for cell in ['E13','E14','E16','C20','C21','C22','C23','C24']:
print(cell, RC[cell].value)
print('--- Stress & Break-Even ---')
for cell in ['C12','C13','C14','C17','C18','C19','C20','C21','C22','C23','C24','C28','C29','C30','C31','C32','C33']:
print(cell, SB[cell].value)
print('--- Assumptions Integrity Checks ---')
for r in range(23,48):
print('I'+str(r), A['I'+str(r)].value)
"
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('Rancho San Joaquin_Credit_UW_2026-09-09_v1.xlsx', data_only=True)
LS = wb['Loan Sizing']
for r in range(23,28):
print(r, [LS[f'{c}{r}'].value for c in ['B','C','D','E','F']])
print('J5:J13 term/DS')
for r in range(5,14):
print('I'+str(r), LS['I'+str(r)].value, 'J'+str(r), LS['J'+str(r)].value)
"
No — the 70% LTV request breaks agency coverage before it breaks agency leverage.
At a $80.98M loan on a $115.7M lender-case value (368 units, sizing to the 5.19% Los Angeles-Long Beach-Anaheim MSA multifamily cap rate on a haircut lender-case NOI of $6.00M), Day 1 DSCR is 1.21x on the interest-only basis that actually governs in years 1-2, but only 1.02x on the amortizing basis the agency sizing test runs against. That amortizing test — not leverage — is what fails: Fannie/Freddie's standard box wants 1.25x amortizing coverage and a 1.20x IO floor, and this loan clears IO (1.21x ≥ 1.20x) and clears straight LTV (70% ≤ 75% max) but misses the amortizing floor by 23 points and misses the 8.5% debt yield floor too — actual debt yield is 7.41%.
| Test | Floor/Limit | Result at Request | Pass/Fail |
|---|---|---|---|
| Amortizing DSCR | 1.25x | 1.02x1 | FAIL |
| Interest-Only DSCR | 1.20x | 1.21x1 | PASS |
| Debt Yield | 8.5% | 7.41%1 | FAIL |
| Loan-to-Value | 75% max | 70.0%1 | PASS |
The binding constraint is the amortizing DSCR test, which caps supportable proceeds at $65.98M — 57.0% of lender value, an $15.0M (18.5%) cut from the ask. The debt-yield test is nearly as tight ($70.6M max), so cutting proceeds is the only fix here — extending amortization can't rescue a debt-yield shortfall, and the model can't find an amortization term that holds the DSCR floor at the requested size either.
That's Approve-with-Structure territory, not Decline — 1.02x is above 1.00x, so the collateral clears its debt service, it just doesn't clear the agency cushion at this leverage. Two other things cap the verdict regardless of proceeds: sponsor financials weren't provided with the request (verdict caps at Approve with Structure pending guarantor review), and recovery at a 20% value decline comes in at 88.0% of par, below the 90% threshold lenders typically want as a cushion. On the upside, the maturity math works: even funding the full $80.98M ask, the balance amortizes to $71.2M by year 10 against $80.9M of stressed refi capacity — the refinance clears with room, so this is a sizing problem today, not a repayment problem at the back end.
Bottom line: counter at ~$66.0M (57% LTV) or equivalent — the $80.98M ask at 70% LTV doesn't meet the agency box on coverage even though it's within the leverage limit. The credit model behind these numbers is attached; T12 operating detail beyond gross potential rent and vacancy wasn't on file for this property, so the lender case leans on LA-OC MSA and South Irvine submarket benchmarks for expenses, other income, and market rent — a sponsor T12 would sharpen this materially, particularly on the expense side where the benchmark-driven OpEx ratio (51.6% of lender EGI) is doing most of the work in that 1.02x.
This is a read-only snapshot of a RealAI analysis.