cat agents/01a087be-a40c-70fb-88a9-40a61b0703c2/RealAI_Credit_Underwriting_Template_manifest_v2.md | head -200

sed -n '200,420p' agents/01a087be-a40c-70fb-88a9-40a61b0703c2/RealAI_Credit_Underwriting_Template_manifest_v2.md

cp agents/01a087be-a40c-70fb-88a9-40a61b0703c2/RealAI_Credit_Underwriting_Template_v3_clean.xlsx /vercel/sandbox/Modern_Apts_SanDiego_Underwriting.xlsx
python3 -c "
import openpyxl
wb = openpyxl.load_workbook('Modern_Apts_SanDiego_Underwriting.xlsx')
print(wb.sheetnames)
ws = wb['Assumptions']
for row in range(1,60):
    for col in ['B','C','E','F']:
        cell = ws[f'{col}{row}']
        if cell.value is not None:
            print(col,row,repr(cell.value))
"

python3 -c "
import openpyxl
wb = openpyxl.load_workbook('Modern_Apts_SanDiego_Underwriting.xlsx')
for sheetname in ['Assumptions','Prepayment','Defeasance']:
    ws = wb[sheetname]
    print('---', sheetname)
    for dv in ws.data_validations.dataValidation:
        print(dv.sqref, dv.formula1)
"

python3 -c "
import openpyxl
wb = openpyxl.load_workbook('Modern_Apts_SanDiego_Underwriting.xlsx')
ws = wb['Pro Forma']
for row in range(1,55):
    for col in ['B','C','D','E','F','G','H','I','J']:
        cell = ws[f'{col}{row}']
        if cell.value is not None:
            print(col,row,repr(cell.value))
"

python3 << 'EOF'
# Key computations for underwriting assumptions
unit_count = 369
egi = 13070570.87
gpr = 12485572.08
noi = 9000616.35
vacancy_loss = 482471.36
other_income = 1067470.15
total_opex = 4069954.51
mgmt_fee_pct = 0.0250
capex = 128677.91
tax_pct = 0.1086

vacancy_rate_pct_of_gpr = vacancy_loss/gpr
other_income_pct_gpr = other_income/gpr
opex_ratio_pct_of_egi = total_opex/egi
reserves_per_unit = capex/unit_count
market_cap_rate = 0.0493
lender_value = noi/market_cap_rate
requested_ltv = 0.70
requested_proceeds = lender_value*requested_ltv
print("vacancy_rate_pct_of_gpr", round(vacancy_rate_pct_of_gpr,4))
print("other_income_pct_gpr", round(other_income_pct_gpr,4))
print("opex_ratio_pct_of_egi", round(opex_ratio_pct_of_egi,4))
print("reserves_per_unit", round(reserves_per_unit,2))
print("lender_value", round(lender_value,0))
print("lender_value_per_unit", round(lender_value/unit_count,0))
print("requested_proceeds", round(requested_proceeds,0))
print("requested_proceeds_per_unit", round(requested_proceeds/unit_count,0))

# bear-case rent growth CAGR from forecast downside_case
start = 2897.170591
y5 = 2722.76
cagr = (y5/start)**(1/5)-1
print("bear_case_noi_growth_cagr", round(cagr,4))

# base case CAGR for reference
y5_base = 3115.02
cagr_base = (y5_base/start)**(1/5)-1
print("base_case_growth_cagr", round(cagr_base,4))
EOF

python3 -c "
import openpyxl
wb = openpyxl.load_workbook('Modern_Apts_SanDiego_Underwriting.xlsx')
ws = wb['Assumptions']
for row in range(46,59):
    for col in ['B','C','E','F']:
        cell = ws[f'{col}{row}']
        print(col,row,repr(cell.value))
print('---F thresholds---')
for row in [55,56,57,58]:
    print('F',row, ws[f'F{row}'].value)
print('--- prepayment sheet defaults ---')
ws2 = wb['Prepayment']
for row in range(4,80):
    c = ws2[f'C{row}']
    if c.value is not None:
        print('C',row, repr(c.value))
"

python3 -c "
import openpyxl
wb = openpyxl.load_workbook('Modern_Apts_SanDiego_Underwriting.xlsx')
for sheetname in ['Loan Sizing','Recovery','Stress & Break-Even','Refi Analysis']:
    ws = wb[sheetname]
    print('===',sheetname)
    for row in ws.iter_rows():
        for cell in row:
            if cell.value is not None:
                print(cell.coordinate, repr(cell.value))
" | head -260

python3 -c "
import openpyxl
wb = openpyxl.load_workbook('Modern_Apts_SanDiego_Underwriting.xlsx')
ws = wb['Refi Analysis']
for row in ws.iter_rows():
    for cell in row:
        if cell.value is not None:
            print(cell.coordinate, repr(cell.value))
" 

cd /vercel/sandbox && python skills/xlsx/scripts/workbook_integrity_scan.py Modern_Apts_SanDiego_Underwriting.xlsx 2>&1 | tail -80

cd /vercel/sandbox && python skills/xlsx/scripts/recalc.py Modern_Apts_SanDiego_Underwriting.xlsx 2>&1 | tail -40

cd /vercel/sandbox && cp agents/01a087be-a40c-70fb-88a9-40a61b0703c2/RealAI_Credit_Underwriting_Template_v3_clean.xlsx /tmp/orig_check.xlsx
python skills/xlsx/scripts/recalc.py /tmp/orig_check.xlsx 2>&1 | tail -20

cd /vercel/sandbox && python3 -c "
import openpyxl
wb = openpyxl.load_workbook('Modern_Apts_SanDiego_Underwriting.xlsx', data_only=True)
pf = wb['Pro Forma']
ls = wb['Loan Sizing']
ra = wb['Refi Analysis']
sb = wb['Stress & Break-Even']
rec = wb['Recovery']
asum = wb['Assumptions']

print('Lender-case NOI G28:', pf['G28'].value)
print('Lender value C44:', pf['C44'].value)
print('Lender value/unit C45:', pf['C45'].value)
print()
print('Requested proceeds F8:', asum['F8'].value)
print('DSCR at request C17:', ls['C17'].value)
print('Debt yield at request C18:', ls['C18'].value)
print('LTV at request C19:', ls['C19'].value)
print('Supportable proceeds C29:', ls['C29'].value)
print('Binding constraint C30:', ls['C30'].value)
print('Surplus/(gap) C35:', ls['C35'].value)
print('Counter-offer C36:', ls['C36'].value)
print()
print('--- Refi Analysis annual DSCR (row 11), years 1-10 ---')
for col in ['E','F','G','H','I','J','K','L','M','N']:
    yr = ra[f'{col}4'].value
    dscr = ra[f'{col}11'].value
    noi = ra[f'{col}8'].value
    print(col, 'yearidx',yr, 'NOI', noi, 'DSCR', dscr)
print()
print('Maturity month C19:', ra['C19'].value)
print('Balance at maturity C20:', ra['C20'].value)
print('NOI at maturity C21:', ra['C21'].value)
print('Stressed takeout rate C22:', ra['C22'].value)
print('Stressed cap at maturity C23:', ra['C23'].value)
print('Stressed value at maturity C24:', ra['C24'].value)
print('Stressed refi proceeds C28:', ra['C28'].value)
print('Binding takeout constraint C29:', ra['C29'].value)
print('Refi gap C30:', ra['C30'].value)
print('Refi gap % C31:', ra['C31'].value)
print('Refi coverage x C32:', ra['C32'].value)
print('Maturity test result C33:', ra['C33'].value)
print()
print('--- Stress & Break-even grid ---')
print('DSCR floor applied C12:', sb['C12'].value)
for r in range(6,12):
    rate = sb[f'B{r}'].value
    row = [sb[f'{c}{r}'].value for c in ['C','D','E','F','G']]
    print('rate',rate, row)
print('NOI breakeven C18:', sb['C18'].value)
print('NOI cushion C20/C21:', sb['C20'].value, sb['C21'].value)
print('Breakeven occ C23:', sb['C23'].value)
print('Breakeven rent C24:', sb['C24'].value)
print()
print('Recovery E14 (-20%):', rec['E14'].value)
print('Recovery E13:', rec['E13'].value)
print('Recovery E16 loss severity:', rec['E16'].value)
"

python3 -c "
import numpy_financial as npf
" 2>/dev/null || pip -q install numpy-financial 2>/dev/null
python3 << 'EOF'
import numpy_financial as npf
loan = 89124733.233792
rate = 0.0606
n = 360
pmt_monthly = -npf.pmt(rate/12, n, loan)
annual_ds = pmt_monthly*12
print('annual debt service at counter-offer', annual_ds)
noi_y1 = 8362707.40642655
noi_y10 = 7481219.30579932
noi_y0 = 8466849.65721024
print('DSCR at counter-offer, year0 (unstressed):', noi_y0/annual_ds)
print('DSCR at counter-offer, year1 bear case:', noi_y1/annual_ds)
print('DSCR at counter-offer, year10 bear case:', noi_y10/annual_ds)
EOF