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 && cp "agents/01a087be-a40c-70fb-88a9-40a61b0703c2/RealAI_Credit_Underwriting_Template_v3_clean.xlsx" ./rancho.xlsx && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('rancho.xlsx', data_only=False)
print(wb.sheetnames)
ws = wb['Assumptions']
for row in ws.iter_rows(min_row=1, max_row=60, min_col=1, max_col=9):
for c in row:
if c.value is not None:
print(c.coordinate, repr(c.value))
"
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('rancho.xlsx', data_only=False)
ws = wb['Assumptions']
for r in range(4,60):
for col in ['C','F']:
c = ws[f'{col}{r}']
if c.value is not None:
print(c.coordinate, repr(c.value))
"
cd /vercel/sandbox && cat > populate.py << 'EOF'
import openpyxl
from datetime import date
wb = openpyxl.load_workbook('rancho.xlsx', data_only=False)
A = wb['Assumptions']
PF = wb['Pro Forma']
P = wb['Prepayment']
D = wb['Defeasance']
TODAY = date(2026,9,11)
# --- 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'] = '97.6% / 97.6%'
A['C15'] = 'Market Averages'
# --- Loan request ---
A['F5'] = 'Origination'
A['F6'] = 'Acquisition-Term'
A['F7'] = TODAY
A['F8'] = 1000000 # placeholder, corrected after pass 1
A['F9'] = 0.0611
A['F10'] = 10
A['F11'] = 0
A['F12'] = 30
A['F14'] = 0.01
A['F15'] = 'Non-Recourse'
# --- Credit box ---
A['F18'] = 0.75
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
# --- Sponsor ---
A['C20'] = 'No'
# --- Growth & exit ---
A['C28'] = 0.03
A['C29'] = 0
A['C30'] = 0.02
A['C31'] = 0.02
# --- Takeout terms at maturity ---
A['C34'] = 0.0611
A['C35'] = 1.25
A['C36'] = 0.75
A['C37'] = 0.095
A['C38'] = 30
A['C41'] = 'Normal'
# --- Market benchmarks ---
A['F29'] = 0.0509
A['F30'] = 0.05
A['F32'] = 0.4303
A['F33'] = 0.42
A['F35'] = 'Yes'
A['F36'] = 0.0519
A['F38'] = 3698.16
A['F39'] = 0.01
A['F40'] = 0.01
A['F41'] = 0.03
A['F44'] = 'Modest'
# --- Rollover (non-MF): not applicable ---
A['F47'] = 'No'
# --- Flag thresholds ---
A['F55'] = 0.90
A['F56'] = 0.30
A['F57'] = 0.10
A['F58'] = 0.03
# --- Pro Forma: management fee rate & reserves $/unit (T12 columns left blank - no sponsor T12) ---
PF['C39'] = 0.0393
PF['C40'] = 275
# --- Prepayment ---
P['C4'] = 60
P['C5'] = 0.01
P['C6'] = 0.02
P['C7'] = 50
P['C8'] = 24
P['C9'] = 3
P['C10'] = 'Yield Maintenance'
curve = {
27: (0.5, 0.0395), 28: (1, 0.0411), 29: (2, 0.0437), 30: (3, 0.0443),
31: (5, 0.0455), 32: (7, 0.0465), 33: (10, 0.0477), 34: (20, 0.0522), 35: (30, 0.0522)
}
for row,(tenor,rate) in curve.items():
P[f'C{row}'] = tenor
P[f'D{row}'] = rate
P['C36'] = TODAY
stepdown = [0.05,0.04,0.03,0.02,0.01,0,0,0,0,0]
for i,v in enumerate(stepdown):
P[f'C{49+i}'] = v
# --- Defeasance ---
D['C11'] = 25000
D['C12'] = 40000
D['C13'] = 10000
D['C14'] = 15000
D['C15'] = 10000
D['C16'] = 'Open Window'
wb.save('rancho.xlsx')
print("pass1 saved")
EOF
python3 populate.py
cd /vercel/sandbox && soffice --headless --convert-to xlsx --outdir ./recalced rancho.xlsx 2>&1 | tail -5 || true ls recalced/ 2>/dev/null
cd /vercel/sandbox && python3 skills/xlsx/scripts/recalc.py rancho.xlsx 2>&1 | tail -30
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)
print(wb['Version']['D22'].value)
"
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('rancho.xlsx', data_only=False)
print(repr(wb['Version']['D22'].value))
wb2 = openpyxl.load_workbook('rancho.xlsx', data_only=True)
print(repr(wb2['Version']['D22'].value))
"
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('rancho.xlsx', data_only=True)
PF = wb['Pro Forma']
print('G28 Lender NOI', PF['G28'].value)
print('G11 EGI', PF['G11'].value)
print('G23 OpEx', PF['G23'].value)
print('C44 Lender value', PF['C44'].value)
print('C43 lender cap', PF['C43'].value)
print('G30 haircut pct', PF['G30'].value)
LS = wb['Loan Sizing']
print('LS C29 supportable', LS['C29'].value)
print('LS C30 binding', LS['C30'].value)
"
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('rancho.xlsx', data_only=False)
PF = wb['Pro Forma']
for r in range(4,35):
for col in ['C','D','E','F','G','H','I','J']:
c = PF[f'{col}{r}']
if c.value is not None:
print(c.coordinate, repr(c.value))
"
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('rancho.xlsx', data_only=False)
A = wb['Assumptions']; PF = wb['Pro Forma']
A['C15'] = 'Lower of T12 and Market'
PF['E5'] = 13059831.44
PF['E6'] = 453500.70
A['F39'] = 0.005
A['F40'] = 0.005
wb.save('rancho.xlsx')
print('updated')
"
python3 skills/xlsx/scripts/recalc.py rancho.xlsx 2>&1 | tail -10
python3 -c "
import openpyxl
wb = openpyxl.load_workbook('rancho.xlsx', data_only=True)
PF = wb['Pro Forma']
print('G5 lender GPR', PF['G5'].value)
print('G6 lender vacancy', PF['G6'].value)
print('G9 lender other income', PF['G9'].value)
print('G11 lender EGI', PF['G11'].value)
print('G23 lender opex', PF['G23'].value)
print('G28 lender NOI', PF['G28'].value)
print('C44 lender value', PF['C44'].value)
print('G31 opex ratio', PF['G31'].value)
print('G32 vacancy rate', PF['G32'].value)
"
cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('rancho.xlsx', data_only=False)
A = wb['Assumptions']
A['F8'] = 0.70 * 112959446.643466
wb.save('rancho.xlsx')
print('F8 set to', A['F8'].value)
"
python3 skills/xlsx/scripts/recalc.py rancho.xlsx 2>&1 | tail -10cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('rancho.xlsx', data_only=True)
LS = wb['Loan Sizing']
A = wb['Assumptions']
for cell in ['C14','C15','C16','C17','C18','C19','C20','C24','C25','C26','C27','D24','D25','D26','D27','E24','E25','E26','E27','F24','F25','F26','F27','C29','C30','C31','C32','C35','C36','C37']:
print(cell, LS[cell].value)
print('---Assumptions integrity---')
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.xlsx', data_only=True)
A = wb['Assumptions']
for r in range(5,21):
print('I'+str(r), A.cell(row=r,column=8).value,'->', A.cell(row=r,column=9).value)
SB = wb['Stress & Break-Even']
print('NOI breakeven', SB['C18'].value)
print('NOI cushion pct', SB['C21'].value)
REC = wb['Recovery']
print('Recovery pct par -20%', REC['E14'].value)
"
cd /vercel/sandbox && python3 skills/xlsx/scripts/workbook_integrity_scan.py rancho.xlsx 2>err.log; echo "EXIT:$?"; tail -60 err.log
cd /vercel/sandbox && cp rancho.xlsx "Rancho San Joaquin Apartment Homes_Credit_UW_2026-09-11_v1.xlsx" && ls -la "Rancho San Joaquin Apartment Homes_Credit_UW_2026-09-11_v1.xlsx"
python3 -c "
import openpyxl
wb = openpyxl.load_workbook('Rancho San Joaquin Apartment Homes_Credit_UW_2026-09-11_v1.xlsx', data_only=True)
LS=wb['Loan Sizing']; PF=wb['Pro Forma']; A=wb['Assumptions']
print('DSCR', LS['C17'].value)
print('DebtYield', LS['C18'].value)
print('LTV', LS['C19'].value)
print('SupportableProceeds', LS['C29'].value)
print('Binding', LS['C30'].value)
print('LenderNOI', PF['G28'].value)
print('LenderValue', PF['C44'].value)
print('Requested', A['F8'].value)
"
Now saving and presenting the workbook, then writing the memo.
Rancho San Joaquin doesn't have a disclosed T12, so the coverage test below runs off the property's actual reported revenue and market-benchmarked expenses, capitalized at the current Los Angeles-CBSA multifamily cap rate — a standard lender-case build, not a sponsor pro forma.
Lender-case NOI comes in at $5.86M , which caps the as-is value at $112.96M ($5.86M ÷ 5.19% cap) — about $307K/unit. 70% of that is a $79.07M loan . At a blended agency rate of 6.11% (interpolated between Fannie Mae's 65% and 75% LTV quotes) , 30-year amortization, that debt service produces a Day-1 DSCR of 1.02x — against the standard agency amortizing floor of 1.25x. It also produces a debt yield of 7.41%, against the 9.5% floor . LTV alone (70.0%) clears the 75% agency max, but that's the only test that does.
| Test | At 70% LTV Request | Agency Floor | Result |
|---|---|---|---|
| DSCR (amortizing) | 1.02x1 | 1.25x min | Fails |
| Debt Yield | 7.41%1 | 9.50% min | Fails |
| LTV | 70.0% | 75% max | Passes |
| Supportable Proceeds | $61.71M1 | n/a | Binding constraint: Debt Yield Test |
Why it breaks: debt yield, not DSCR, is the binding constraint — proceeds are capped independent of amortization. The math only clears at $61.7M, or roughly 54.6% LTV — an $17.4M gap to the $79.07M request . Amortizing longer or adding an IO period doesn't fix this; debt yield is a proceeds-only test.
Two things make this tighter than it first looks:
What would change this: cut proceeds to the ~$61.7M debt-yield-supportable level (54.6% LTV) and the deal clears comfortably — DSCR jumps well above 1.25x at that balance. Short of that, an interest-only period buys nothing against the debt yield floor, but a higher-basis appraisal that supports the sponsor's assumed value, or hard evidence of in-place rent understating true market rent at this property (South Irvine asking rents run ~26% above this asset's in-place average ), could move the needle on the NOI side.
Full detail — including the amortization schedule, refi-at-maturity gap, and prepayment/defeasance analysis — is in the attached workbook.
This is a read-only snapshot of a RealAI analysis.