alro65/AR-House
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
AR-House/finance_calculator.py
T
alro65 047c05287a feat: AR-House initial commit
2026-07-03 12:24:58 -04:00

1500 lines
68 KiB
Python
Raw Permalink Blame History

"""finance_calculator.py - Calculos financieros EXACTOS en Python.
PROBLEMA QUE RESUELVE:
El LLM (DealAnalyzer en Ollama) se confunde con aritmetica simple. En el smoke
test del fix #2 calculo MAB=$114K vs starting bid $120K y dijo "PASS" cuando la
regla NO BID debia activarse (starting > MAB*0.95). El razonamiento conceptual
fue correcto, la comparacion numerica fallo.
SOLUCION:
Computar TODOS los numeros en Python antes de llamar al DealAnalyzer. Pasarle
los resultados como "DATOS CALCULADOS - usalos exactos, NO recalcules" en el
prompt. El LLM solo interpreta y razona sobre la estrategia ganadora.
FUNCIONES IMPLEMENTADAS:
- calculate_piti(loan_amount, rate, years, tax_annual, insurance_annual, hoa_monthly)
- calculate_dscr(noi_annual, piti_annual)
- calculate_cash_flow(rent_monthly, piti_monthly, vacancy_pct, mgmt_pct, maint_pct, capex_pct)
- calculate_cap_rate(noi_annual, purchase_price)
- calculate_coc_return(annual_cash_flow, total_cash_invested)
- calculate_buy_and_hold(deal_inputs) # combinacion de todo lo de arriba
- calculate_brrrr_metrics(price, rehab, arv, refi_ltv)
- calculate_mao_wholesale(arv, rehab)
- calculate_section8_rent(market_rent, fmr)
- calculate_section8_scenario(deal_inputs, fmr)
- calculate_mab_auction(arv, rehab, title_reserve)
- calculate_auction_scenario(deal_inputs, starting_bid)
- compute_all_scenarios(deal_inputs, verified_data, deal_type) # entry point
Todas las funciones devuelven dicts con valores numericos exactos (no LLM-friendly text).
build_calculated_block() formatea los resultados como bloque markdown para inyectar
en el prompt del DealAnalyzer.
"""
from __future__ import annotations
import math
from dataclasses import dataclass, field
from typing import Optional, Literal
# ════════════════════════════════════════════════════════════════════════════
# Constantes y benchmarks de Florida
# ════════════════════════════════════════════════════════════════════════════
# Reservas operativas como % de la renta (estandar de inversion)
VACANCY_PCT = 0.08 # 8% vacancia
MGMT_PCT = 0.10 # 10% property management
MAINT_PCT = 0.05 # 5% mantenimiento
CAPEX_PCT = 0.05 # 5% CapEx reserve
# Loan defaults — INVESTOR FINANCING
DSCR_LOAN_RATE = 0.0775 # 7.75% tasa DSCR loan
DSCR_LOAN_LTV = 0.75 # 75% down payment requires 25%
CONVENTIONAL_LTV = 0.75 # 75% investment property
HARD_MONEY_RATE = 0.12 # 12% interest
HARD_MONEY_POINTS = 0.02 # 2 points origination
HARD_MONEY_TERM_MONTHS = 6 # 6 meses tipico
BRRRR_REFI_LTV = 0.75 # 75% LTV post-refi
BRRRR_REFI_RATE = 0.075 # 7.5% post-refi rate
# Loan defaults — OWNER-OCCUPIED (vivir en la casa)
# Bug fix 2026-05-15: usuario reporto que system solo mostraba 12% (hard money).
# Para alguien que compra para VIVIR (primary residence) las tasas reales son:
FHA_RATE = 0.0625 # 6.25% FHA loan (~30y fixed, owner-occupied)
FHA_DOWN_PCT = 0.035 # 3.5% minimum down (FHA)
FHA_MIP_RATE = 0.0055 # 0.55% annual MIP (mortgage insurance premium)
CONVENTIONAL_OO_RATE = 0.065 # 6.5% conventional owner-occupied (~30y)
CONVENTIONAL_OO_DOWN_PCT = 0.05 # 5% min conv OO (PMI < 20% down)
CONVENTIONAL_OO_PMI_RATE = 0.005 # 0.5% annual PMI if < 20% down
VA_RATE = 0.060 # 6.00% VA loan (military, 0% down)
# DTI thresholds (debt-to-income ratios)
# Front-end = mortgage PITI / gross monthly income
# Back-end = total debt / gross monthly income
DTI_FRONT_END_CONVENTIONAL = 0.28 # 28% conventional
DTI_FRONT_END_FHA = 0.31 # 31% FHA
DTI_BACK_END_CONVENTIONAL = 0.36 # 36% conventional
DTI_BACK_END_FHA = 0.43 # 43% FHA
DTI_FRONT_END_AGGRESSIVE_MAX = 0.35 # >35% es claramente riesgoso
DTI_FRONT_END_DANGER_ZONE = 0.40 # >40% no califica typically
# Target margin para "max profitable offer"
TARGET_INVESTOR_MARGIN_PCT = 0.20 # 20% margin sobre ARV - costos
TARGET_LIVE_IN_MARGIN_PCT = 0.05 # 5% margin para owner-occupant (no es flip)
# Wholesale rule
WHOLESALE_ARV_PCT = 0.70 # MAO = ARV*0.70 - rehab (regla 70)
WHOLESALE_MIN_SPREAD = 10_000 # PASS si spread > $10K
# Section 8 multiplier (paga 100-110% de FMR tipicamente)
SECTION8_FMR_MULTIPLIER = 1.05 # asumimos 105% del FMR para calculos
# Auction (Fix #2)
AUCTION_ARV_FLOOR = 0.65 # MAB usa 65% del ARV (mas conservador que 70 de wholesale)
AUCTION_REHAB_WORST_CASE = 1.5 # factor 1.5x sin inspeccion previa
AUCTION_BUFFER_PCT = 0.10 # 10% buffer en MAB
TITLE_RESERVE_MIAMI_DADE = 15_000 # title issues mas comunes en Miami-Dade
TITLE_RESERVE_OTHER = 5_000
# Auction veredict thresholds (sobre MAB)
AUCTION_PASS_THRESHOLD = 0.70 # starting < MAB*0.70 = PASS
AUCTION_CAUTION_THRESHOLD = 0.95 # starting < MAB*0.95 = CAUTION; sobre = NO BID
# Buy & Hold pass thresholds
BUYHOLD_DSCR_MIN = 1.20
BUYHOLD_CASHFLOW_MIN = 200 # $/mo
SECTION8_DSCR_MIN = 1.25
BRRRR_TRAPPED_CAPITAL_PCT_MAX = 0.25 # 25% del invested original
# Florida insurance defaults por price tier (anual)
FL_INSURANCE_TIERS = [
(300_000, 4_000), # < $300K -> $4K/year
(500_000, 6_000), # < $500K -> $6K/year
(float("inf"), 8_000), # >= $500K -> $8K/year
]
FLOOD_INSURANCE_AE = 3_000 # ~$2-4K typical
FLOOD_INSURANCE_VE = 10_000 # ~$8-15K typical
# ════════════════════════════════════════════════════════════════════════════
# Calculo primario: PITI mensual
# ════════════════════════════════════════════════════════════════════════════
def calculate_monthly_mortgage_payment(loan_amount: float, annual_rate: float, years: int) -> float:
"""Calcula el pago mensual P+I de una hipoteca standard.
Formula: M = P * [r(1+r)^n] / [(1+r)^n - 1]
donde r = tasa mensual, n = meses totales.
"""
if loan_amount <= 0:
return 0.0
if annual_rate <= 0:
return loan_amount / (years * 12) # interest-free, divide en cuotas
r = annual_rate / 12.0
n = years * 12
if r == 0:
return loan_amount / n
numerator = r * (1 + r) ** n
denominator = (1 + r) ** n - 1
return loan_amount * numerator / denominator
def calculate_piti(
loan_amount: float,
annual_rate: float,
years: int,
tax_annual: float,
insurance_annual: float,
hoa_monthly: float = 0,
) -> dict:
"""PITI = Principal + Interest + Tax + Insurance + HOA.
Returns dict con piti_monthly (total), p_and_i_monthly, t_and_i_monthly, hoa_monthly.
"""
p_and_i = calculate_monthly_mortgage_payment(loan_amount, annual_rate, years)
t_and_i = (tax_annual + insurance_annual) / 12.0
total = p_and_i + t_and_i + hoa_monthly
return {
"piti_monthly": round(total, 2),
"piti_annual": round(total * 12, 2),
"p_and_i_monthly": round(p_and_i, 2),
"t_and_i_monthly": round(t_and_i, 2),
"hoa_monthly": round(hoa_monthly, 2),
}
# ════════════════════════════════════════════════════════════════════════════
# NOI / DSCR / Cash Flow / Cap Rate / CoC
# ════════════════════════════════════════════════════════════════════════════
def calculate_operating_expenses(
rent_monthly: float,
tax_annual: float,
insurance_annual: float,
hoa_monthly: float = 0,
vacancy_pct: float = VACANCY_PCT,
mgmt_pct: float = MGMT_PCT,
maint_pct: float = MAINT_PCT,
capex_pct: float = CAPEX_PCT,
) -> dict:
"""Total operating expenses anuales (PRE servicio de deuda).
Incluye: vacancia + mgmt + mantenimiento + capex + tax + insurance + HOA.
"""
gross_rent_annual = rent_monthly * 12
vacancy = gross_rent_annual * vacancy_pct
mgmt = gross_rent_annual * mgmt_pct
maint = gross_rent_annual * maint_pct
capex = gross_rent_annual * capex_pct
total_opex = vacancy + mgmt + maint + capex + tax_annual + insurance_annual + (hoa_monthly * 12)
return {
"gross_rent_annual": round(gross_rent_annual, 2),
"vacancy_annual": round(vacancy, 2),
"mgmt_annual": round(mgmt, 2),
"maint_annual": round(maint, 2),
"capex_annual": round(capex, 2),
"tax_annual": round(tax_annual, 2),
"insurance_annual": round(insurance_annual, 2),
"hoa_annual": round(hoa_monthly * 12, 2),
"total_opex_annual": round(total_opex, 2),
}
def calculate_noi(rent_monthly: float, opex_annual: float) -> float:
"""Net Operating Income anual (PRE servicio de deuda).
NOI = Gross Rent Anual - Operating Expenses (incluye vacancia/mgmt/etc).
NO incluye servicio de deuda.
"""
gross = rent_monthly * 12
return round(gross - opex_annual, 2)
def calculate_dscr(noi_annual: float, piti_annual: float) -> float:
"""DSCR = NOI / Annual Debt Service.
PITI annual incluye P+I+T+I+HOA. Como NOI ya descuenta T+I+HOA en el opex,
el "debt service" puro deberia ser solo P+I anual. Para alinearse con como
los DSCR lenders calculan (que usan PITI completo), usamos PITI annual aqui.
Threshold pass: 1.20 typical, 1.25 estricto.
"""
if piti_annual <= 0:
return 0.0
return round(noi_annual / piti_annual, 3)
def calculate_cash_flow(rent_monthly: float, piti_monthly: float, opex_annual: float) -> dict:
"""Cash Flow mensual y anual (POST servicio de deuda).
Cash Flow = Gross Rent - PITI - Reservas operativas (sin tax/ins/HOA porque
ya estan en PITI).
"""
# Reservas operativas mensuales (sin tax/insurance/HOA - estan en PITI)
vacancy_m = rent_monthly * VACANCY_PCT
mgmt_m = rent_monthly * MGMT_PCT
maint_m = rent_monthly * MAINT_PCT
capex_m = rent_monthly * CAPEX_PCT
reserves_m = vacancy_m + mgmt_m + maint_m + capex_m
cf_monthly = rent_monthly - piti_monthly - reserves_m
return {
"cash_flow_monthly": round(cf_monthly, 2),
"cash_flow_annual": round(cf_monthly * 12, 2),
"reserves_monthly": round(reserves_m, 2),
}
def calculate_cap_rate(noi_annual: float, property_value: float) -> float:
"""Cap Rate = NOI / Property Value (unlevered yield).
Devuelve porcentaje (0-100), no decimal.
"""
if property_value <= 0:
return 0.0
return round(noi_annual / property_value * 100, 2)
def calculate_coc_return(annual_cash_flow: float, total_cash_invested: float) -> float:
"""Cash-on-Cash return = Annual Cash Flow / Cash Invested (leveraged ROI).
Devuelve porcentaje (0-100), no decimal.
"""
if total_cash_invested <= 0:
return 0.0
return round(annual_cash_flow / total_cash_invested * 100, 2)
# ════════════════════════════════════════════════════════════════════════════
# Helpers para auto-estimar inputs faltantes
# ════════════════════════════════════════════════════════════════════════════
def estimate_florida_insurance(price: float, flood_zone: Optional[str] = None) -> float:
"""Estima insurance anual segun el tier de precio FL y zona FEMA."""
base = next(insurance for limit, insurance in FL_INSURANCE_TIERS if price < limit)
if flood_zone:
zone = flood_zone.upper().strip()
if zone in ("VE", "V"):
base += FLOOD_INSURANCE_VE
elif zone in ("A", "AE", "AH", "AO", "AR"):
base += FLOOD_INSURANCE_AE
return base
def estimate_florida_property_tax(price: float) -> float:
"""FL property tax sin homestead: ~2% del precio."""
return price * 0.02
# ════════════════════════════════════════════════════════════════════════════
# ESCENARIO 1: Buy & Hold (DSCR loan)
# ════════════════════════════════════════════════════════════════════════════
def calculate_buy_and_hold(
price: float,
rent_monthly: float,
tax_annual: float,
insurance_annual: float,
hoa_monthly: float = 0,
down_payment_pct: float = 1 - DSCR_LOAN_LTV,
loan_rate: float = DSCR_LOAN_RATE,
loan_years: int = 30,
) -> dict:
"""Buy & Hold con DSCR loan.
Returns dict con todos los numeros + verdict.
"""
down_payment = price * down_payment_pct
loan_amount = price - down_payment
closing_costs_pct = 0.03
closing_costs = price * closing_costs_pct
total_cash_invested = down_payment + closing_costs
piti = calculate_piti(loan_amount, loan_rate, loan_years, tax_annual, insurance_annual, hoa_monthly)
opex = calculate_operating_expenses(rent_monthly, tax_annual, insurance_annual, hoa_monthly)
noi = calculate_noi(rent_monthly, opex["total_opex_annual"])
# DSCR usa P+I (no PITI) porque T+I ya estan en opex. Pero la convencion de
# los lenders es PITI/12*12 = piti_annual. Para alinearse con como lo computan:
# Lenders calculan: NOI/PITI annual (donde PITI excluye reservas operativas pero
# incluye T+I). Nuestro NOI ya descuenta T+I asi que sumarlos en el denominador
# los double-counts. Mejor: DSCR = (rent_annual - vacancy - opex_reserves) / piti_annual
# donde opex_reserves son las RESERVAS solamente (vacancia/mgmt/maint/capex), no T+I/HOA.
gross_rent_annual = rent_monthly * 12
reserves_only = gross_rent_annual * (VACANCY_PCT + MGMT_PCT + MAINT_PCT + CAPEX_PCT)
noi_for_dscr = gross_rent_annual - reserves_only # antes de T+I+HOA (esos van en PITI)
dscr = calculate_dscr(noi_for_dscr, piti["piti_annual"])
cf = calculate_cash_flow(rent_monthly, piti["piti_monthly"], opex["total_opex_annual"])
cap_rate = calculate_cap_rate(noi, price)
coc = calculate_coc_return(cf["cash_flow_annual"], total_cash_invested)
# Veredicto
pass_dscr = dscr >= BUYHOLD_DSCR_MIN
pass_cf = cf["cash_flow_monthly"] >= BUYHOLD_CASHFLOW_MIN
verdict = "PASA" if (pass_dscr and pass_cf) else "NO PASA"
reasons = []
if not pass_dscr:
reasons.append(f"DSCR {dscr:.2f} < {BUYHOLD_DSCR_MIN}")
if not pass_cf:
reasons.append(f"Cash Flow ${cf['cash_flow_monthly']:.0f}/mo < ${BUYHOLD_CASHFLOW_MIN}")
return {
"strategy": "Buy & Hold (DSCR loan)",
"down_payment": round(down_payment, 2),
"down_payment_pct": round(down_payment_pct * 100, 1),
"loan_amount": round(loan_amount, 2),
"loan_rate_pct": round(loan_rate * 100, 2),
"loan_years": loan_years,
"closing_costs": round(closing_costs, 2),
"total_cash_invested": round(total_cash_invested, 2),
"piti_monthly": piti["piti_monthly"],
"piti_annual": piti["piti_annual"],
"p_and_i_monthly": piti["p_and_i_monthly"],
"noi_annual": noi,
"dscr": dscr,
"cash_flow_monthly": cf["cash_flow_monthly"],
"cash_flow_annual": cf["cash_flow_annual"],
"cap_rate_pct": cap_rate,
"coc_return_pct": coc,
"verdict": verdict,
"verdict_reasons": reasons,
}
# ════════════════════════════════════════════════════════════════════════════
# ESCENARIO 2: BRRRR
# ════════════════════════════════════════════════════════════════════════════
def calculate_brrrr_metrics(
price: float,
rehab: float,
arv: float,
rent_monthly: float,
tax_annual: float,
insurance_annual: float,
hoa_monthly: float = 0,
refi_ltv: float = BRRRR_REFI_LTV,
refi_rate: float = BRRRR_REFI_RATE,
refi_years: int = 30,
hard_money_rate: float = HARD_MONEY_RATE,
hard_money_points: float = HARD_MONEY_POINTS,
hard_money_term_months: int = HARD_MONEY_TERM_MONTHS,
) -> dict:
"""BRRRR: Buy + Rehab + Rent + Refinance + Repeat.
Asume compra con hard money (90% LTC), rehab, refi al 75% LTV del ARV.
"""
# Acquisition phase
hard_money_ltc = 0.90 # 90% LTC tipico
hard_money_loan = price * hard_money_ltc
down_payment_acq = price - hard_money_loan
points_cost = hard_money_loan * hard_money_points
holding_interest = hard_money_loan * hard_money_rate * (hard_money_term_months / 12)
# Total cash invertido durante acquisition + rehab
cash_invested_initial = down_payment_acq + rehab + points_cost + holding_interest
# Refi phase
refi_loan = arv * refi_ltv
refi_payoff = hard_money_loan # paga hard money con refi
refi_closing_costs = refi_loan * 0.03
cash_recovered_at_refi = refi_loan - refi_payoff - refi_closing_costs
# Capital atrapado post-refi
trapped_capital = cash_invested_initial - cash_recovered_at_refi
trapped_pct = (trapped_capital / cash_invested_initial * 100) if cash_invested_initial > 0 else 0
# Cash flow post-refi
piti_post_refi = calculate_piti(refi_loan, refi_rate, refi_years, tax_annual, insurance_annual, hoa_monthly)
cf_post = calculate_cash_flow(rent_monthly, piti_post_refi["piti_monthly"], 0)
# Regla 70: precio + rehab <= 70% ARV
rule_70_max = arv * 0.70
rule_70_actual = price + rehab
rule_70_pass = rule_70_actual <= rule_70_max
# Veredicto
pass_trapped = trapped_pct <= BRRRR_TRAPPED_CAPITAL_PCT_MAX * 100
pass_cf = cf_post["cash_flow_monthly"] >= BUYHOLD_CASHFLOW_MIN
verdict = "PASA" if (pass_trapped and pass_cf and rule_70_pass) else "NO PASA"
reasons = []
if not rule_70_pass:
reasons.append(f"Regla 70 fallo: precio+rehab ${rule_70_actual:,.0f} > 70% ARV ${rule_70_max:,.0f}")
if not pass_trapped:
reasons.append(f"Capital atrapado {trapped_pct:.0f}% > {BRRRR_TRAPPED_CAPITAL_PCT_MAX*100:.0f}%")
if not pass_cf:
reasons.append(f"Cash Flow post-refi ${cf_post['cash_flow_monthly']:.0f} < ${BUYHOLD_CASHFLOW_MIN}")
return {
"strategy": "BRRRR",
"hard_money_loan": round(hard_money_loan, 2),
"hard_money_down_payment": round(down_payment_acq, 2),
"rehab_budget": round(rehab, 2),
"points_cost": round(points_cost, 2),
"holding_interest": round(holding_interest, 2),
"cash_invested_initial": round(cash_invested_initial, 2),
"refi_loan": round(refi_loan, 2),
"refi_closing_costs": round(refi_closing_costs, 2),
"cash_recovered_at_refi": round(cash_recovered_at_refi, 2),
"trapped_capital": round(trapped_capital, 2),
"trapped_capital_pct": round(trapped_pct, 1),
"piti_post_refi_monthly": piti_post_refi["piti_monthly"],
"cash_flow_post_refi_monthly": cf_post["cash_flow_monthly"],
"rule_70_max_price_plus_rehab": round(rule_70_max, 2),
"rule_70_actual_price_plus_rehab": round(rule_70_actual, 2),
"rule_70_pass": rule_70_pass,
"verdict": verdict,
"verdict_reasons": reasons,
}
# ════════════════════════════════════════════════════════════════════════════
# ESCENARIO 3: Wholesale
# ════════════════════════════════════════════════════════════════════════════
def calculate_mao_wholesale(arv: float, rehab: float) -> float:
"""MAO = ARV * 0.70 - rehab. Regla 70."""
return round(arv * WHOLESALE_ARV_PCT - rehab, 2)
def calculate_wholesale(price: float, arv: float, rehab: float) -> dict:
"""Wholesale: spread entre MAO y precio listado."""
mao = calculate_mao_wholesale(arv, rehab)
spread = mao - price
verdict = "PASA" if spread >= WHOLESALE_MIN_SPREAD else "NO PASA"
reasons = []
if spread < WHOLESALE_MIN_SPREAD:
reasons.append(f"Spread ${spread:,.0f} < ${WHOLESALE_MIN_SPREAD:,} minimo")
return {
"strategy": "Wholesale",
"mao": mao,
"list_price": round(price, 2),
"spread": round(spread, 2),
"verdict": verdict,
"verdict_reasons": reasons,
}
# ════════════════════════════════════════════════════════════════════════════
# ESCENARIO 4: Section 8
# ════════════════════════════════════════════════════════════════════════════
def calculate_section8_rent(fmr: float, multiplier: float = SECTION8_FMR_MULTIPLIER) -> float:
"""Section 8 paga 100-110% del FMR tipicamente. Asumimos multiplier=1.05."""
return round(fmr * multiplier, 2)
def calculate_section8_scenario(
price: float,
market_rent_monthly: float,
fmr_3br: float,
tax_annual: float,
insurance_annual: float,
hoa_monthly: float = 0,
) -> dict:
"""Section 8: usa renta FMR-based en lugar de market rent.
Compara renta Section 8 (FMR * multiplier) vs market rent.
"""
section8_rent = calculate_section8_rent(fmr_3br)
rent_advantage = section8_rent > market_rent_monthly
# Use higher of section8 vs market
effective_rent = max(section8_rent, market_rent_monthly)
bh = calculate_buy_and_hold(
price=price,
rent_monthly=effective_rent,
tax_annual=tax_annual,
insurance_annual=insurance_annual,
hoa_monthly=hoa_monthly,
)
# Section 8 requiere DSCR >= 1.25 (mas estricto)
pass_dscr = bh["dscr"] >= SECTION8_DSCR_MIN
pass_advantage = rent_advantage
verdict = "PASA" if (pass_dscr and pass_advantage) else "NO PASA"
reasons = []
if not pass_advantage:
reasons.append(f"Section 8 rent ${section8_rent:.0f} <= market rent ${market_rent_monthly:.0f}")
if not pass_dscr:
reasons.append(f"DSCR {bh['dscr']:.2f} < {SECTION8_DSCR_MIN}")
return {
"strategy": "Section 8",
"fmr_3br_monthly": round(fmr_3br, 2),
"section8_rent_monthly": section8_rent,
"market_rent_monthly": round(market_rent_monthly, 2),
"section8_advantage_usd": round(section8_rent - market_rent_monthly, 2),
"effective_rent_used": round(effective_rent, 2),
"dscr": bh["dscr"],
"cash_flow_monthly": bh["cash_flow_monthly"],
"cap_rate_pct": bh["cap_rate_pct"],
"coc_return_pct": bh["coc_return_pct"],
"verdict": verdict,
"verdict_reasons": reasons,
}
# ════════════════════════════════════════════════════════════════════════════
# ESCENARIO 5: AUCTION ACQUISITION (Fix #2)
# ════════════════════════════════════════════════════════════════════════════
def calculate_mab_auction(
arv: float,
rehab: float,
title_reserve: Optional[float] = None,
county_name: Optional[str] = None,
surviving_debt: float = 0.0,
) -> dict:
"""Maximum Allowable Bid para auction acquisition.
Formula original:
MAB * 1.10 = ARV * 0.65 - rehab * 1.5 - title_reserve
MAB = (ARV * 0.65 - rehab * 1.5 - title_reserve) / 1.10
Wave 1.5A v1.2: surviving_debt parameter
Si court_records detecta liens que sobreviven el foreclosure (IRS,
municipal, property tax, HOA, senior mortgages), el buyer HEREDA esos
liens y debe sumarlos a su costo real. El effective_MAB = MAB_original
- surviving_debt.
title_reserve: $15K Miami-Dade, $5K otros condados.
"""
if title_reserve is None:
if county_name and "miami-dade" in county_name.lower():
title_reserve = TITLE_RESERVE_MIAMI_DADE
else:
title_reserve = TITLE_RESERVE_OTHER
numerator = arv * AUCTION_ARV_FLOOR - rehab * AUCTION_REHAB_WORST_CASE - title_reserve
mab_original = numerator / (1 + AUCTION_BUFFER_PCT)
mab_original = max(0, mab_original) # nunca negativo
# Effective MAB: el buyer puede pagar HASTA mab_original menos liens heredables
# porque esos liens deberan ser pagados post-cierre.
effective_mab = mab_original - max(0, surviving_debt)
effective_mab = max(0, effective_mab)
return {
"mab": round(effective_mab, 2), # mab "principal" para downstream consumers
"mab_original": round(mab_original, 2),
"mab_effective": round(effective_mab, 2),
"surviving_debt_deduction": round(surviving_debt, 2),
"arv": round(arv, 2),
"arv_floor_value": round(arv * AUCTION_ARV_FLOOR, 2),
"rehab_worst_case": round(rehab * AUCTION_REHAB_WORST_CASE, 2),
"title_reserve": round(title_reserve, 2),
"buffer_pct": AUCTION_BUFFER_PCT * 100,
}
def calculate_auction_scenario(
starting_bid: float,
arv: float,
rehab: float,
county_name: Optional[str] = None,
surviving_debt: float = 0.0,
) -> dict:
"""Auction acquisition con veredicto basado en MAB.
Verdicts:
PASS si starting_bid < effective_MAB * 0.70 (~30% safety margin)
CAUTION si effective_MAB*0.70 <= starting_bid < effective_MAB*0.95
NO BID si starting_bid >= effective_MAB * 0.95
Wave 1.5A v1.2: surviving_debt ajusta el MAB. Si el buyer hereda $30K de
liens, el effective_MAB baja $30K y el veredicto se vuelve mas conservador.
"""
mab_data = calculate_mab_auction(arv, rehab, county_name=county_name,
surviving_debt=surviving_debt)
mab = mab_data["mab"] # esto ya es el effective_mab
pass_threshold = mab * AUCTION_PASS_THRESHOLD
caution_threshold = mab * AUCTION_CAUTION_THRESHOLD
if starting_bid < pass_threshold:
verdict = "PASS"
verdict_reason = f"Starting bid ${starting_bid:,.0f} < MAB*0.70 (${pass_threshold:,.0f}) — gran margen de safety"
elif starting_bid < caution_threshold:
verdict = "CAUTION"
verdict_reason = f"Starting bid ${starting_bid:,.0f} entre MAB*0.70 (${pass_threshold:,.0f}) y MAB*0.95 (${caution_threshold:,.0f}) — margen apretado, requiere validacion exhaustiva"
else:
verdict = "NO BID"
verdict_reason = f"Starting bid ${starting_bid:,.0f} >= MAB*0.95 (${caution_threshold:,.0f}) — sin margen de safety, riesgo asimetrico negativo"
# Cash needed Day-1 (auctions son cash-only) + surviving debt que se paga post-cierre
cash_needed_day_one = starting_bid + mab_data["title_reserve"] # bid + reserve
cash_total_to_close = cash_needed_day_one + max(0, surviving_debt)
return {
"strategy": "Auction Acquisition",
"starting_bid": round(starting_bid, 2),
"mab": mab,
"mab_original": mab_data["mab_original"],
"mab_effective": mab_data["mab_effective"],
"surviving_debt_deduction": mab_data["surviving_debt_deduction"],
"mab_breakdown": mab_data,
"pass_threshold": round(pass_threshold, 2),
"caution_threshold": round(caution_threshold, 2),
"cash_needed_day_one": round(cash_needed_day_one, 2),
"cash_total_to_close": round(cash_total_to_close, 2),
"verdict": verdict,
"verdict_reason": verdict_reason,
"checklist_pre_bid": [
"Title search profesional ANTES del bid ($300-$500)",
"Code enforcement check con municipalidad",
"Occupancy drive-by (manejar por la calle)",
"Bankruptcy court check (auto-stay puede invalidar foreclosure)",
"PACER search del owner anterior",
],
"auction_risks": [
"Occupied status: eviction $1.5K-$5K + 30-90 dias (Florida legal process)",
"Foreclosure: posibilidad de equity stripping, junior liens sobreviven",
"Tax deed: 1-year redemption period (ex-owner puede recomprar)",
"Sin inspeccion previa: rehab puede ser 1.5x-2x del estimado",
"IRS liens NO se extinguen con foreclosure (heredas la deuda)",
"HOA pre-existing dues: posible herencia",
],
}
# ════════════════════════════════════════════════════════════════════════════
# Entry point: compute_all_scenarios
# ════════════════════════════════════════════════════════════════════════════
DealType = Literal["mls", "off_market", "auction", "foreclosure", "tax_deed", "reo"]
def compute_all_scenarios(
*,
price: float,
rent_monthly: float,
property_tax_annual: float,
insurance_annual: float,
hoa_monthly: float = 0,
arv: float = 0,
rehab: float = 0,
fmr_3br: Optional[float] = None,
flood_zone: Optional[str] = None,
county_name: Optional[str] = None,
deal_type: str = "mls",
surviving_debt: float = 0.0, # Wave 1.5A v1.2: liens heredables del court_records
) -> dict:
"""Computa los 4 escenarios estandar + escenario 5 si deal_type es auction.
Entry point principal. Devuelve dict con TODOS los numeros calculados,
listos para inyectar al prompt del DealAnalyzer.
Si fmr_3br es None, Section 8 se omite (no podemos calcular sin FMR).
"""
# Auto-estimar insurance si llega en 0 o muy bajo
if insurance_annual < 500:
insurance_annual = estimate_florida_insurance(price, flood_zone)
# Auto-estimar tax si llega en 0
if property_tax_annual < 500:
property_tax_annual = estimate_florida_property_tax(price)
deal_type_normalized = (deal_type or "mls").lower().strip()
is_auction = deal_type_normalized in ("auction", "foreclosure", "tax_deed", "reo")
scenarios: dict = {}
# 1. Buy & Hold
scenarios["buy_and_hold"] = calculate_buy_and_hold(
price=price,
rent_monthly=rent_monthly,
tax_annual=property_tax_annual,
insurance_annual=insurance_annual,
hoa_monthly=hoa_monthly,
)
# 2. BRRRR (si hay rehab y ARV)
if rehab > 0 and arv > price:
scenarios["brrrr"] = calculate_brrrr_metrics(
price=price,
rehab=rehab,
arv=arv,
rent_monthly=rent_monthly,
tax_annual=property_tax_annual,
insurance_annual=insurance_annual,
hoa_monthly=hoa_monthly,
)
else:
scenarios["brrrr"] = {
"strategy": "BRRRR",
"verdict": "N/A",
"verdict_reasons": ["Falta rehab>0 o ARV>price para evaluar BRRRR"],
}
# 3. Wholesale (si hay ARV)
if arv > 0:
scenarios["wholesale"] = calculate_wholesale(price=price, arv=arv, rehab=rehab)
else:
scenarios["wholesale"] = {
"strategy": "Wholesale",
"verdict": "N/A",
"verdict_reasons": ["Falta ARV para calcular MAO"],
}
# 4. Section 8 (si hay FMR)
if fmr_3br and fmr_3br > 0:
scenarios["section8"] = calculate_section8_scenario(
price=price,
market_rent_monthly=rent_monthly,
fmr_3br=fmr_3br,
tax_annual=property_tax_annual,
insurance_annual=insurance_annual,
hoa_monthly=hoa_monthly,
)
else:
scenarios["section8"] = {
"strategy": "Section 8",
"verdict": "N/A",
"verdict_reasons": ["Falta FMR del condado (HUD_API_KEY o fetcher fallo)"],
}
# 5. AUCTION (solo si deal_type indica auction)
if is_auction:
scenarios["auction"] = calculate_auction_scenario(
starting_bid=price, # en auctions, "price" del input es el starting bid
arv=arv,
rehab=rehab,
county_name=county_name,
surviving_debt=surviving_debt, # Wave 1.5A v1.2: ajusta effective_MAB
)
else:
scenarios["auction"] = {
"strategy": "Auction Acquisition",
"verdict": "N/A",
"verdict_reasons": [f"deal_type='{deal_type_normalized}' no es auction"],
}
# Best strategy: pick the one with PASS / PASS / PASS y mejor cash flow
candidates = []
for key in ("buy_and_hold", "brrrr", "section8", "auction"):
s = scenarios[key]
if s.get("verdict") == "PASA" or s.get("verdict") == "PASS":
cf = s.get("cash_flow_monthly", 0) or s.get("cash_flow_post_refi_monthly", 0) or 0
candidates.append((key, s["strategy"], cf))
candidates.sort(key=lambda x: x[2], reverse=True)
best = candidates[0][1] if candidates else "Ninguna estrategia pasa los thresholds"
# Bug 3: Anomaly Detection. Detecta metricas "demasiado buenas para ser
# verdad" que en USA real estate suelen indicar precio anomalo (data error,
# listing fake, hidden problem heredable, etc.).
anomalies = detect_anomalies(scenarios)
return {
"inputs_used": {
"price": price,
"rent_monthly": rent_monthly,
"property_tax_annual": property_tax_annual,
"insurance_annual": insurance_annual,
"hoa_monthly": hoa_monthly,
"arv": arv,
"rehab": rehab,
"fmr_3br": fmr_3br,
"flood_zone": flood_zone,
"county_name": county_name,
"deal_type": deal_type_normalized,
},
"scenarios": scenarios,
"best_strategy": best,
"is_auction": is_auction,
"anomalies": anomalies, # Bug 3
}
# ════════════════════════════════════════════════════════════════════════════
# Bug 3: Anomaly Detection
# ════════════════════════════════════════════════════════════════════════════
# Thresholds basados en benchmarks USA reales (Florida SFR):
# - Cap Rate tipico SFR: 4-7% (>10% ya es raro, >12% es bandera roja)
# - CoC tipico: 8-15% (>20% ya es raro, >25% es bandera roja)
# - DSCR tipico: 1.20-1.45 (>1.6 raro en FL post-insurance crisis, >1.7 bandera)
# Estos UMBRALES SUPERIORES no son "buenos" — son SOSPECHOSOS.
# Si las metricas pasan, hay 3 escenarios:
# (a) data error en los inputs (rent inflado, price bajo equivocado)
# (b) listing precio bajo por hidden problem heredable
# (c) deal REAL excepcional (raro, requiere validacion exhaustiva)
ANOMALY_THRESHOLDS = {
"cap_rate_pct_max": 12.0, # > 12% sospechoso
"coc_return_pct_max": 25.0, # > 25% sospechoso
"dscr_max": 1.70, # > 1.70 sospechoso
"cash_flow_monthly_max": 2500, # > $2,500/mo sospechoso en SFR FL <$300K
"brrrr_trapped_capital_pct_max": 5.0, # <5% trapped es raro (BRRRR perfecto = sospechoso)
"wholesale_spread_max": 80_000, # >$80K spread es muy raro en SFR
}
# Benchmarks USA tipicos para citar en explicaciones
TYPICAL_USA_BENCHMARKS = {
"cap_rate_pct_typical": "4-7% (SFR Florida tipico)",
"coc_return_pct_typical": "8-15% (Buy & Hold con leverage tipico)",
"dscr_typical": "1.20-1.45 (umbral lender DSCR loan)",
"cash_flow_monthly_typical": "$200-$600/mo (SFR Buy & Hold tipico)",
"brrrr_trapped_capital_typical": "10-25% del capital invertido (BRRRR normal)",
"wholesale_spread_typical": "$15K-$40K spread normal",
}
def detect_anomalies(scenarios: dict) -> dict:
"""Detecta metricas anomalas en los escenarios computados.
Returns dict con:
has_anomalies: bool
anomaly_count: int
flagged_metrics: list of dicts con {scenario, metric, value, threshold, severity, message}
recommendation: str
"""
flagged: list[dict] = []
bh = scenarios.get("buy_and_hold") or {}
if isinstance(bh, dict) and bh.get("verdict") in ("PASA", "PASS"):
# Cap rate anomalo
cr = bh.get("cap_rate_pct")
if cr is not None and cr > ANOMALY_THRESHOLDS["cap_rate_pct_max"]:
flagged.append({
"scenario": "Buy & Hold",
"metric": "Cap Rate",
"value": round(cr, 2),
"threshold": ANOMALY_THRESHOLDS["cap_rate_pct_max"],
"typical": TYPICAL_USA_BENCHMARKS["cap_rate_pct_typical"],
"severity": "HIGH" if cr > 15 else "MEDIUM",
"message": (
f"Cap Rate {cr:.2f}% supera el umbral de {ANOMALY_THRESHOLDS['cap_rate_pct_max']}%. "
f"Tipico USA: {TYPICAL_USA_BENCHMARKS['cap_rate_pct_typical']}. "
"Suele indicar rent inflada o price subvaluado por hidden problem."
),
})
# CoC anomalo
coc = bh.get("coc_return_pct")
if coc is not None and coc > ANOMALY_THRESHOLDS["coc_return_pct_max"]:
flagged.append({
"scenario": "Buy & Hold",
"metric": "Cash-on-Cash Return",
"value": round(coc, 2),
"threshold": ANOMALY_THRESHOLDS["coc_return_pct_max"],
"typical": TYPICAL_USA_BENCHMARKS["coc_return_pct_typical"],
"severity": "HIGH" if coc > 35 else "MEDIUM",
"message": (
f"CoC {coc:.2f}% supera el umbral de {ANOMALY_THRESHOLDS['coc_return_pct_max']}%. "
f"Tipico USA: {TYPICAL_USA_BENCHMARKS['coc_return_pct_typical']}. "
"Cuando es tan alto en SFR, validar inputs (rent? price?)."
),
})
# DSCR anomalo
dscr = bh.get("dscr")
if dscr is not None and dscr > ANOMALY_THRESHOLDS["dscr_max"]:
flagged.append({
"scenario": "Buy & Hold",
"metric": "DSCR",
"value": round(dscr, 2),
"threshold": ANOMALY_THRESHOLDS["dscr_max"],
"typical": TYPICAL_USA_BENCHMARKS["dscr_typical"],
"severity": "HIGH" if dscr > 2.0 else "MEDIUM",
"message": (
f"DSCR {dscr:.2f} supera el umbral de {ANOMALY_THRESHOLDS['dscr_max']}. "
f"Tipico USA: {TYPICAL_USA_BENCHMARKS['dscr_typical']}. "
"DSCR >1.7 en SFR FL post-insurance crisis es raro — revisar rent/PITI."
),
})
# Cash flow anomalo
cfm = bh.get("cash_flow_monthly")
if cfm is not None and cfm > ANOMALY_THRESHOLDS["cash_flow_monthly_max"]:
flagged.append({
"scenario": "Buy & Hold",
"metric": "Cash Flow mensual",
"value": round(cfm, 0),
"threshold": ANOMALY_THRESHOLDS["cash_flow_monthly_max"],
"typical": TYPICAL_USA_BENCHMARKS["cash_flow_monthly_typical"],
"severity": "HIGH" if cfm > 4000 else "MEDIUM",
"message": (
f"Cash Flow ${cfm:,.0f}/mo supera ${ANOMALY_THRESHOLDS['cash_flow_monthly_max']:,}/mo. "
f"Tipico USA: {TYPICAL_USA_BENCHMARKS['cash_flow_monthly_typical']}."
),
})
# Section 8 con metricas anomalas
s8 = scenarios.get("section8") or {}
if isinstance(s8, dict) and s8.get("verdict") in ("PASA", "PASS"):
cr = s8.get("cap_rate_pct")
if cr is not None and cr > ANOMALY_THRESHOLDS["cap_rate_pct_max"]:
flagged.append({
"scenario": "Section 8",
"metric": "Cap Rate",
"value": round(cr, 2),
"threshold": ANOMALY_THRESHOLDS["cap_rate_pct_max"],
"typical": TYPICAL_USA_BENCHMARKS["cap_rate_pct_typical"],
"severity": "MEDIUM",
"message": f"Section 8 Cap Rate {cr:.2f}% supera umbral {ANOMALY_THRESHOLDS['cap_rate_pct_max']}%.",
})
coc = s8.get("coc_return_pct")
if coc is not None and coc > ANOMALY_THRESHOLDS["coc_return_pct_max"]:
flagged.append({
"scenario": "Section 8",
"metric": "Cash-on-Cash",
"value": round(coc, 2),
"threshold": ANOMALY_THRESHOLDS["coc_return_pct_max"],
"typical": TYPICAL_USA_BENCHMARKS["coc_return_pct_typical"],
"severity": "MEDIUM",
"message": f"Section 8 CoC {coc:.2f}% supera umbral {ANOMALY_THRESHOLDS['coc_return_pct_max']}%.",
})
# BRRRR con trapped capital sospechosamente bajo (BRRRR "perfecto" suele indicar
# ARV inflado o rehab subestimado)
br = scenarios.get("brrrr") or {}
if isinstance(br, dict) and "trapped_capital_pct" in br:
tc = br.get("trapped_capital_pct")
if tc is not None and tc < ANOMALY_THRESHOLDS["brrrr_trapped_capital_pct_max"]:
flagged.append({
"scenario": "BRRRR",
"metric": "Trapped Capital",
"value": round(tc, 2),
"threshold": ANOMALY_THRESHOLDS["brrrr_trapped_capital_pct_max"],
"typical": TYPICAL_USA_BENCHMARKS["brrrr_trapped_capital_typical"],
"severity": "MEDIUM",
"message": (
f"BRRRR trapped capital {tc:.1f}% es sospechosamente bajo "
f"(typico: {TYPICAL_USA_BENCHMARKS['brrrr_trapped_capital_typical']}). "
"Suele indicar ARV inflado, rehab subestimado, o price anomalo."
),
})
# Wholesale spread excepcional (puede indicar ARV inflado o listing anomalo)
wh = scenarios.get("wholesale") or {}
if isinstance(wh, dict) and "spread" in wh:
sp = wh.get("spread")
if sp is not None and sp > ANOMALY_THRESHOLDS["wholesale_spread_max"]:
flagged.append({
"scenario": "Wholesale",
"metric": "Spread (MAO - listing)",
"value": round(sp, 0),
"threshold": ANOMALY_THRESHOLDS["wholesale_spread_max"],
"typical": TYPICAL_USA_BENCHMARKS["wholesale_spread_typical"],
"severity": "MEDIUM",
"message": (
f"Wholesale spread ${sp:,.0f} supera ${ANOMALY_THRESHOLDS['wholesale_spread_max']:,} "
f"(tipico: {TYPICAL_USA_BENCHMARKS['wholesale_spread_typical']}). "
"Validar ARV con comps reales antes de oferta."
),
})
# Recommendation segun cantidad y severidad
high_count = sum(1 for f in flagged if f["severity"] == "HIGH")
medium_count = sum(1 for f in flagged if f["severity"] == "MEDIUM")
total = len(flagged)
# 3+ MEDIUM stacked es por si solo HIGH-severity pattern (correlacionados sugieren input
# error sistematico o hidden problem que infla todos los ratios)
is_critical = (
high_count >= 2
or (high_count >= 1 and medium_count >= 2)
or medium_count >= 3
)
if total == 0:
recommendation = "Sin anomalias detectadas — metricas dentro de benchmarks USA tipicos."
elif is_critical:
recommendation = (
f"🚨 {total} metrica(s) anomala(s) detectada(s) ({high_count} HIGH, {medium_count} MEDIUM). "
"El deal es 'demasiado bueno para ser verdad' — tratar como sospechoso. "
"Validar inputs (rent? price? ARV?) Y ejecutar due diligence completa "
"(court records, code enforcement, title search) ANTES de cualquier oferta. "
"Los numeros 'espectaculares' pueden ser ilusion derivada de input incorrecto "
"o de un precio anomalo por hidden problem heredable."
)
elif total >= 1:
recommendation = (
f"⚠️ {total} metrica(s) sobre benchmark USA tipico. "
"Re-verificar inputs antes de proceder. Posibles causas: rent over-estimada, "
"rehab sub-estimada, ARV inflado, o deal realmente excepcional (raro). "
"Cross-check con comps de mercado recientes."
)
else:
recommendation = "Metricas razonables."
return {
"has_anomalies": total > 0,
"is_critical": is_critical if total > 0 else False,
"anomaly_count": total,
"high_severity_count": high_count,
"medium_severity_count": medium_count,
"flagged_metrics": flagged,
"recommendation": recommendation,
"thresholds_used": ANOMALY_THRESHOLDS,
}
# ════════════════════════════════════════════════════════════════════════════
# Formato para prompt (markdown block ready para inyectar al DealAnalyzer)
# ════════════════════════════════════════════════════════════════════════════
def _money(x: float) -> str:
if x is None:
return "—"
return f"${x:,.0f}"
def _pct(x: float) -> str:
if x is None:
return "—"
return f"{x:.2f}%"
def build_calculated_block(computed: dict) -> str:
"""Genera un bloque markdown con TODOS los numeros pre-calculados.
Este bloque se inyecta al prompt del DealAnalyzer ANTES de pedir su analisis.
El DealAnalyzer solo INTERPRETA estos numeros, NO los recalcula.
"""
s = computed["scenarios"]
inputs = computed["inputs_used"]
bh = s["buy_and_hold"]
br = s["brrrr"]
wh = s["wholesale"]
s8 = s["section8"]
au = s["auction"]
lines = []
lines.append("=== NUMEROS PRE-CALCULADOS (FUENTE: finance_calculator.py) ===")
lines.append("USA estos numeros EXACTOS. NO recalcules. NO contradigas los veredictos.")
lines.append("Tu tarea es INTERPRETAR y RECOMENDAR, no recalcular.")
lines.append("")
# Bug 3: Anomaly Detection block (al INICIO del bloque calculado para que el
# LLM lo lea antes que las metricas individuales).
anomalies = computed.get("anomalies") or {}
if anomalies.get("has_anomalies"):
lines.append("--- ⚠️ ANOMALIAS DETECTADAS (Bug 3 — auto-flag) ---")
lines.append(f"Total: {anomalies['anomaly_count']} metrica(s) sospechosa(s) "
f"({anomalies['high_severity_count']} HIGH, {anomalies['medium_severity_count']} MEDIUM).")
lines.append("")
lines.append("**Recomendacion del sistema:** " + anomalies["recommendation"])
lines.append("")
lines.append("**Metricas flageadas (umbrales USA reales):**")
lines.append("")
lines.append("| Escenario | Metrica | Valor | Umbral | Tipico USA | Severidad |")
lines.append("|---|---|---|---|---|---|")
for f in anomalies["flagged_metrics"]:
val = f["value"]
val_str = f"{val:.2f}" if isinstance(val, float) and val < 100 else f"{val:,.0f}"
lines.append(
f"| {f['scenario']} | {f['metric']} | **{val_str}** | "
f"> {f['threshold']} | {f['typical']} | **{f['severity']}** |"
)
lines.append("")
lines.append("**INSTRUCCION OBLIGATORIA AL DEALANALYZER:**")
lines.append("Si arriba hay anomalias flageadas, tu output DEBE incluir una seccion titulada exactamente '## ⚠️ Validacion de Inputs Requerida' explicando que las metricas son sospechosas, las 3 causas posibles (data error / hidden problem heredable / deal real excepcional), y 4-6 acciones concretas de validacion antes de cualquier oferta. Esto es NO-NEGOCIABLE incluso si el veredicto calculado dice PASA — un PASS con metricas anomalas requiere validacion antes de oferta.")
lines.append("")
else:
lines.append("--- Anomaly check ---")
lines.append("Sin anomalias detectadas — metricas dentro de benchmarks USA tipicos (cap rate 4-7%, CoC 8-15%, DSCR 1.20-1.45).")
lines.append("")
lines.append("--- INPUTS NORMALIZADOS ---")
lines.append(f"- Precio: {_money(inputs['price'])} | Renta: {_money(inputs['rent_monthly'])}/mo")
lines.append(f"- Property tax: {_money(inputs['property_tax_annual'])}/year | Insurance: {_money(inputs['insurance_annual'])}/year (auto-estimada si era 0)")
lines.append(f"- HOA: {_money(inputs['hoa_monthly'])}/mo | ARV: {_money(inputs['arv'])} | Rehab: {_money(inputs['rehab'])}")
lines.append(f"- Flood zone: {inputs.get('flood_zone', 'N/A')} | County: {inputs.get('county_name', 'N/A')}")
lines.append(f"- Deal type: {inputs['deal_type']}")
if inputs.get('fmr_3br'):
lines.append(f"- HUD FMR 3BR: {_money(inputs['fmr_3br'])}/mo")
lines.append("")
# Tabla comparativa
lines.append("--- TABLA COMPARATIVA DE ESCENARIOS (calculada) ---")
lines.append("")
lines.append("| Estrategia | DSCR | Cash Flow/mo | Cap Rate | CoC | Veredicto (DATO CERRADO) |")
lines.append("|---|---|---|---|---|---|")
lines.append(
f"| Buy & Hold | {bh.get('dscr', '—'):.2f} | {_money(bh.get('cash_flow_monthly'))} | "
f"{_pct(bh.get('cap_rate_pct'))} | {_pct(bh.get('coc_return_pct'))} | **{bh.get('verdict')}** |"
)
if "trapped_capital_pct" in br:
lines.append(
f"| BRRRR | — | {_money(br.get('cash_flow_post_refi_monthly'))} | — | — | "
f"**{br.get('verdict')}** (capital atrapado {br.get('trapped_capital_pct', '—')}%) |"
)
else:
lines.append(f"| BRRRR | — | — | — | — | **{br.get('verdict')}** |")
if "mao" in wh:
lines.append(f"| Wholesale | — | MAO {_money(wh.get('mao'))} (spread {_money(wh.get('spread'))}) | — | — | **{wh.get('verdict')}** |")
else:
lines.append(f"| Wholesale | — | — | — | — | **{wh.get('verdict')}** |")
if "dscr" in s8:
lines.append(
f"| Section 8 | {s8.get('dscr', '—'):.2f} | {_money(s8.get('cash_flow_monthly'))} | "
f"{_pct(s8.get('cap_rate_pct'))} | {_pct(s8.get('coc_return_pct'))} | **{s8.get('verdict')}** |"
)
else:
lines.append(f"| Section 8 | — | — | — | — | **{s8.get('verdict')}** |")
if computed["is_auction"]:
lines.append(f"| Auction | — | MAB {_money(au.get('mab'))} | — | — | **{au.get('verdict')}** |")
lines.append("")
lines.append(f"**Mejor estrategia (calculada por veredicto + cash flow):** {computed['best_strategy']}")
lines.append("")
# Detalles Buy & Hold
if "dscr" in bh:
lines.append("--- DETALLE: Buy & Hold ---")
lines.append(f"- Down payment: {_money(bh['down_payment'])} ({bh['down_payment_pct']}%)")
lines.append(f"- Loan amount: {_money(bh['loan_amount'])} @ {bh['loan_rate_pct']}% por {bh['loan_years']} anos")
lines.append(f"- PITI mensual: {_money(bh['piti_monthly'])} (P+I: {_money(bh['p_and_i_monthly'])})")
lines.append(f"- NOI anual: {_money(bh['noi_annual'])}")
lines.append(f"- DSCR: {bh['dscr']:.2f} (threshold pass: {BUYHOLD_DSCR_MIN})")
lines.append(f"- Cash flow mensual: {_money(bh['cash_flow_monthly'])} (threshold pass: ${BUYHOLD_CASHFLOW_MIN}+)")
lines.append(f"- Total cash invertido: {_money(bh['total_cash_invested'])}")
if bh.get("verdict_reasons"):
lines.append("- Razones del veredicto: " + "; ".join(bh["verdict_reasons"]))
lines.append("")
# Detalles BRRRR
if "trapped_capital_pct" in br:
lines.append("--- DETALLE: BRRRR ---")
lines.append(f"- Hard money loan: {_money(br['hard_money_loan'])} @ {HARD_MONEY_RATE*100}% por {HARD_MONEY_TERM_MONTHS} meses")
lines.append(f"- Cash inicial requerido: {_money(br['cash_invested_initial'])}")
lines.append(f"- Refi loan post-rehab: {_money(br['refi_loan'])} ({BRRRR_REFI_LTV*100:.0f}% LTV del ARV)")
lines.append(f"- Cash recuperado al refi: {_money(br['cash_recovered_at_refi'])}")
lines.append(f"- Capital atrapado final: {_money(br['trapped_capital'])} ({br['trapped_capital_pct']}% del invertido)")
lines.append(f"- Cash flow post-refi: {_money(br['cash_flow_post_refi_monthly'])}/mo")
lines.append(f"- Regla 70: precio+rehab {_money(br['rule_70_actual_price_plus_rehab'])} vs max {_money(br['rule_70_max_price_plus_rehab'])} = {'PASS' if br['rule_70_pass'] else 'FAIL'}")
if br.get("verdict_reasons"):
lines.append("- Razones del veredicto: " + "; ".join(br["verdict_reasons"]))
lines.append("")
# Detalles Wholesale
if "mao" in wh:
lines.append("--- DETALLE: Wholesale ---")
lines.append(f"- MAO: {_money(wh['mao'])} (ARV * 0.70 - rehab)")
lines.append(f"- List price: {_money(wh['list_price'])}")
lines.append(f"- Spread: {_money(wh['spread'])} (threshold pass: ${WHOLESALE_MIN_SPREAD:,}+)")
if wh.get("verdict_reasons"):
lines.append("- Razones del veredicto: " + "; ".join(wh["verdict_reasons"]))
lines.append("")
# Detalles Section 8
if "dscr" in s8:
lines.append("--- DETALLE: Section 8 ---")
lines.append(f"- HUD FMR 3BR: {_money(s8['fmr_3br_monthly'])}/mo")
lines.append(f"- Section 8 rent estimado: {_money(s8['section8_rent_monthly'])}/mo (FMR x {SECTION8_FMR_MULTIPLIER})")
lines.append(f"- Market rent: {_money(s8['market_rent_monthly'])}/mo")
lines.append(f"- Ventaja Section 8 vs market: {_money(s8['section8_advantage_usd'])}/mo")
lines.append(f"- DSCR con renta efectiva: {s8['dscr']:.2f} (threshold Section 8: {SECTION8_DSCR_MIN})")
if s8.get("verdict_reasons"):
lines.append("- Razones del veredicto: " + "; ".join(s8["verdict_reasons"]))
lines.append("")
# Detalles AUCTION (si aplica)
if computed["is_auction"] and "mab" in au:
lines.append("--- DETALLE: AUCTION ACQUISITION ---")
mb = au["mab_breakdown"]
lines.append(f"- ARV: {_money(mb['arv'])} | ARV*0.65 = {_money(mb['arv_floor_value'])}")
lines.append(f"- Rehab worst-case (x{AUCTION_REHAB_WORST_CASE}): {_money(mb['rehab_worst_case'])}")
lines.append(f"- Title reserve: {_money(mb['title_reserve'])}")
# Wave 1.5A v1.2: distinguir MAB original vs effective si hay surviving_debt
surviving = au.get("surviving_debt_deduction", 0) or 0
if surviving > 0:
lines.append(f"- **MAB original** (sin surviving debt): {_money(au['mab_original'])} (con buffer {mb['buffer_pct']:.0f}%)")
lines.append(f"- **Surviving debt heredable** (liens IRS/municipal/HOA/etc): -{_money(surviving)}")
lines.append(f"- **MAB EFFECTIVE** (lo que realmente podes pagar al auction): **{_money(au['mab_effective'])}**")
else:
lines.append(f"- MAB calculado: **{_money(au['mab'])}** (con buffer {mb['buffer_pct']:.0f}%)")
lines.append(f"- Starting bid: {_money(au['starting_bid'])}")
lines.append(f"- Threshold PASS: < {_money(au['pass_threshold'])} (effective_MAB * 0.70)")
lines.append(f"- Threshold CAUTION: < {_money(au['caution_threshold'])} (effective_MAB * 0.95)")
lines.append(f"- **VEREDICTO: {au['verdict']}** — {au['verdict_reason']}")
lines.append(f"- Cash needed Day-1 (bid + title reserve): {_money(au['cash_needed_day_one'])}")
if surviving > 0:
lines.append(f"- **Cash TOTAL to close** (Day-1 + liens heredables): {_money(au.get('cash_total_to_close', 0))}")
lines.append("- Checklist pre-bid:")
for item in au["checklist_pre_bid"]:
lines.append(f" * {item}")
lines.append("- Riesgos de auction:")
for risk in au["auction_risks"]:
lines.append(f" * {risk}")
lines.append("")
return "\n".join(lines)
# ════════════════════════════════════════════════════════════════════════════
# Owner-Occupied / Live-In Scenarios (bug fix 2026-05-15)
# ════════════════════════════════════════════════════════════════════════════
def calculate_live_in_scenario(
purchase_price: float,
down_payment: float,
*,
loan_type: str = "fha", # "fha" | "conventional_oo" | "va"
years: int = 30,
tax_annual: float = 0,
insurance_annual: float = 0,
hoa_monthly: float = 0,
rent_room_monthly: float = 0,
monthly_income: float = 0,
other_monthly_debts: float = 0,
) -> dict:
"""Live-in scenario: compras para VIVIR + opcional house-hack (rent a room).
Args:
purchase_price: precio de compra (asking o oferta)
down_payment: cuanto vas a poner cash
loan_type: 'fha' (3.5% min down) | 'conventional_oo' (5% min) | 'va' (0%)
years: typically 30
tax_annual / insurance_annual / hoa_monthly: holding costs
rent_room_monthly: si vas a alquilar un cuarto, cuanto te pagan
monthly_income: tu ingreso bruto mensual (para DTI)
other_monthly_debts: pagos otros (car, student loan, credit cards)
Returns:
{
"loan_type": str,
"rate": float, # tasa anual
"loan_amount": float,
"down_payment": float,
"down_pct": float,
"p_and_i_monthly": float,
"mip_or_pmi_monthly": float, # MIP FHA o PMI conventional si <20%
"tax_monthly": float,
"insurance_monthly": float,
"hoa_monthly": float,
"piti_total_monthly": float, # incluye PMI/MIP
"rent_room_monthly": float,
"net_payment_monthly": float, # PITI - rent_room = lo que sales del bolsillo
"dti_evaluation": { # solo si monthly_income > 0
"monthly_income": float,
"front_end_dti_pct": float,
"back_end_dti_pct": float,
"verdict": "AFFORDABLE" | "TIGHT" | "RISKY" | "WONT_QUALIFY",
"verdict_es": str, # version humana en español
"rationale": str,
} | None,
"scenarios_with_room": [ # net payment para distintos rent levels
{"rent_room": int, "net_payment": float}, ...
],
}
"""
purchase_price = max(0, purchase_price)
down_payment = max(0, min(down_payment, purchase_price))
loan_amount = purchase_price - down_payment
down_pct = (down_payment / purchase_price) if purchase_price > 0 else 0
# Pick rate + MIP/PMI by loan type
if loan_type == "fha":
rate = FHA_RATE
mip_or_pmi_annual = loan_amount * FHA_MIP_RATE
elif loan_type == "conventional_oo":
rate = CONVENTIONAL_OO_RATE
# PMI if down < 20%
mip_or_pmi_annual = loan_amount * CONVENTIONAL_OO_PMI_RATE if down_pct < 0.20 else 0
elif loan_type == "va":
rate = VA_RATE
mip_or_pmi_annual = 0 # VA no PMI
else:
rate = CONVENTIONAL_OO_RATE
mip_or_pmi_annual = loan_amount * CONVENTIONAL_OO_PMI_RATE if down_pct < 0.20 else 0
p_and_i = calculate_monthly_mortgage_payment(loan_amount, rate, years)
mip_or_pmi_monthly = mip_or_pmi_annual / 12.0
tax_monthly = tax_annual / 12.0
insurance_monthly = insurance_annual / 12.0
piti_total = p_and_i + mip_or_pmi_monthly + tax_monthly + insurance_monthly + hoa_monthly
net_payment = piti_total - rent_room_monthly
# Compute multi-room scenarios — buyer puede pensar en rentar 0, 1, 2 cuartos
scenarios_with_room = []
for r in (0, 800, 1000, 1200, 1500, 2000):
scenarios_with_room.append({
"rent_room_monthly": r,
"net_payment_monthly": round(piti_total - r, 2),
})
# DTI evaluation
dti_eval = None
if monthly_income and monthly_income > 0:
front_end = (piti_total / monthly_income) * 100
back_end = ((piti_total + other_monthly_debts) / monthly_income) * 100
# Pick thresholds based on loan type
if loan_type == "fha":
fe_max = DTI_FRONT_END_FHA * 100
be_max = DTI_BACK_END_FHA * 100
else:
fe_max = DTI_FRONT_END_CONVENTIONAL * 100
be_max = DTI_BACK_END_CONVENTIONAL * 100
if front_end <= fe_max * 0.9 and back_end <= be_max * 0.9:
verdict = "AFFORDABLE"
verdict_es = "Si, podes pagarla comodamente"
rationale = f"Front-end DTI {front_end:.1f}% <= {fe_max*0.9:.0f}% (buffer), Back-end {back_end:.1f}% <= {be_max*0.9:.0f}%. Te queda margen para imprevistos."
elif front_end <= fe_max and back_end <= be_max:
verdict = "TIGHT"
verdict_es = "Podes pagarla pero ajustado"
rationale = f"Front-end {front_end:.1f}% dentro de limite ({fe_max:.0f}%) pero sin buffer. Imprevistos te complican. Considera rentar un cuarto para aliviar."
elif front_end <= DTI_FRONT_END_AGGRESSIVE_MAX * 100 or back_end <= be_max * 1.1:
verdict = "RISKY"
verdict_es = "Riesgoso — sale del confort normal"
rationale = f"Front-end {front_end:.1f}% supera limite ({fe_max:.0f}%). Te exiges. Algunos lenders aprueban con compensating factors (reservas, credit score 720+) pero NO recomendable."
else:
verdict = "WONT_QUALIFY"
verdict_es = "No vas a calificar / muy riesgoso"
rationale = f"Front-end {front_end:.1f}% o back-end {back_end:.1f}% sobre maximo absoluto. Bancos rechazan. Bajar precio, aumentar down payment, o ingresar mas income."
dti_eval = {
"monthly_income": round(monthly_income, 2),
"other_monthly_debts": round(other_monthly_debts, 2),
"front_end_dti_pct": round(front_end, 1),
"back_end_dti_pct": round(back_end, 1),
"front_end_max_pct": fe_max,
"back_end_max_pct": be_max,
"verdict": verdict,
"verdict_es": verdict_es,
"rationale": rationale,
}
return {
"loan_type": loan_type.upper(),
"rate": rate,
"rate_pct": round(rate * 100, 3),
"loan_amount": round(loan_amount, 2),
"down_payment": round(down_payment, 2),
"down_pct": round(down_pct * 100, 1),
"p_and_i_monthly": round(p_and_i, 2),
"mip_or_pmi_monthly": round(mip_or_pmi_monthly, 2),
"mip_or_pmi_label": "MIP" if loan_type == "fha" else ("PMI" if down_pct < 0.20 else ""),
"tax_monthly": round(tax_monthly, 2),
"insurance_monthly": round(insurance_monthly, 2),
"hoa_monthly": round(hoa_monthly, 2),
"piti_total_monthly": round(piti_total, 2),
"rent_room_monthly": round(rent_room_monthly, 2),
"net_payment_monthly": round(net_payment, 2),
"scenarios_with_room": scenarios_with_room,
"dti_evaluation": dti_eval,
}
# ════════════════════════════════════════════════════════════════════════════
# Max profitable offer
# ════════════════════════════════════════════════════════════════════════════
def calculate_max_profitable_offer(
*,
arv: float,
rehab_estimate: float = 0,
target_margin_pct: float = TARGET_INVESTOR_MARGIN_PCT,
closing_costs_pct: float = 0.02, # 2% closing
holding_months: int = 4,
holding_cost_monthly: float = 600, # tax + insurance + utilities while holding
selling_costs_pct: float = 0.08, # 8% (6% commission + 2% misc)
) -> dict:
"""Maximum profitable offer para fix-n-flip investor.
Math:
Net Proceeds = ARV * (1 - selling_costs_pct)
Target Profit = ARV * target_margin_pct
Holding Costs = holding_months * holding_cost_monthly
Max Offer = Net Proceeds - Target Profit - rehab - holding - closing
= ARV * (1 - selling_pct) - ARV * target_pct - rehab - holding - ARV * closing_pct
Returns:
{
"arv": float,
"rehab_estimate": float,
"target_profit": float,
"selling_costs": float,
"closing_costs": float,
"holding_costs": float,
"max_offer": float,
"max_offer_pct_of_arv": float,
"justification": str (human-readable),
}
"""
if arv <= 0:
return {"error": "ARV must be positive"}
net_proceeds = arv * (1 - selling_costs_pct)
target_profit = arv * target_margin_pct
selling_costs = arv * selling_costs_pct
closing_costs = arv * closing_costs_pct
holding_costs = holding_months * holding_cost_monthly
max_offer = net_proceeds - target_profit - rehab_estimate - holding_costs - closing_costs
max_offer = max(0, max_offer)
max_offer_pct = (max_offer / arv * 100) if arv > 0 else 0
justification = (
f"ARV ${arv:,.0f}. "
f"Tras gastos de venta ({selling_costs_pct*100:.0f}%) = ${net_proceeds:,.0f} neto. "
f"Quitando profit target ({target_margin_pct*100:.0f}% = ${target_profit:,.0f}), "
f"rehab estimado ${rehab_estimate:,.0f}, "
f"holding {holding_months}mo (${holding_costs:,.0f}), "
f"closing ({closing_costs_pct*100:.0f}% = ${closing_costs:,.0f}). "
f"Maxima oferta rentable: ${max_offer:,.0f} ({max_offer_pct:.0f}% de ARV)."
)
return {
"arv": round(arv, 2),
"rehab_estimate": round(rehab_estimate, 2),
"target_profit": round(target_profit, 2),
"target_margin_pct": round(target_margin_pct * 100, 1),
"selling_costs": round(selling_costs, 2),
"closing_costs": round(closing_costs, 2),
"holding_costs": round(holding_costs, 2),
"max_offer": round(max_offer, 2),
"max_offer_pct_of_arv": round(max_offer_pct, 1),
"justification": justification,
}
# ════════════════════════════════════════════════════════════════════════════
# Multi-price-point payment table
# ════════════════════════════════════════════════════════════════════════════
def calculate_payment_table(
*,
asking_price: float,
max_offer: float,
down_payment: float,
annual_rate: float = CONVENTIONAL_OO_RATE,
years: int = 30,
tax_annual: float = 0,
insurance_annual: float = 0,
hoa_monthly: float = 0,
) -> list[dict]:
"""Multi-price payment table: para asking / midpoint / max_offer, calcula PITI mensual.
Returns: lista de dicts [{label, price, down, loan_amount, piti_monthly, p_and_i_only}, ...]
"""
if asking_price <= 0 or max_offer <= 0:
return []
midpoint = (asking_price + max_offer) / 2
rows = []
for label, price in [
("Maximum profitable offer", max_offer),
("Negotiation midpoint", midpoint),
("Asking price", asking_price),
]:
loan = max(0, price - down_payment)
p_and_i = calculate_monthly_mortgage_payment(loan, annual_rate, years)
tax_m = tax_annual / 12.0
ins_m = insurance_annual / 12.0
piti = p_and_i + tax_m + ins_m + hoa_monthly
rows.append({
"label": label,
"price": round(price, 2),
"down_payment": round(min(down_payment, price), 2),
"loan_amount": round(loan, 2),
"p_and_i_only_monthly": round(p_and_i, 2),
"tax_monthly": round(tax_m, 2),
"insurance_monthly": round(ins_m, 2),
"hoa_monthly": round(hoa_monthly, 2),
"piti_monthly": round(piti, 2),
})
return rows
Reference in New Issue View Git Blame Copy Permalink