#!/usr/bin/env python3
"""Build plain Excel spreadsheet for $345K NEW BUILD rental property - NO GO analysis v2"""
import openpyxl
from openpyxl.styles import Font, Alignment, Border, Side
from openpyxl.utils import get_column_letter

# ============ PARAMETERS ============
PRICE = 345000
DOWN_PCT = 0.20
DOWN = PRICE * DOWN_PCT
CLOSING = 6000
LOAN = PRICE * (1 - DOWN_PCT)
RATE = 0.0625
CASH_IN = DOWN + CLOSING

RENT = 2000
OTHER_INCOME = 50
VACANCY = 0.05
RENT_GROWTH = 0.04

PROP_TAX = 2400
INSURANCE = 1400
HOA = 0
MAINTENANCE = 2000
OTHER_COSTS = 500
EXPENSE_GROWTH = 0.03
APPRECIATION = 0.03

HOLD_YEARS = 20

def monthly_pmt(principal, annual_rate, years=30):
    r = annual_rate / 12
    n = years * 12
    return principal * (r * (1 + r)**n) / ((1 + r)**n - 1)

PI_MONTHLY = monthly_pmt(LOAN, RATE)
PI_ANNUAL = PI_MONTHLY * 12

# Styles
bold = Font(bold=True)
bold_red = Font(bold=True, color="CC0000")
bold_green = Font(bold=True, color="006600")
red = Font(color="CC0000")
green = Font(color="006600")
italic_gray = Font(italic=True, color="666666")
header_font = Font(bold=True)
title_font = Font(bold=True, size=14)
section_font = Font(bold=True, size=11)
thin_border = Border(
    left=Side(style='thin', color='CCCCCC'),
    right=Side(style='thin', color='CCCCCC'),
    top=Side(style='thin', color='CCCCCC'),
    bottom=Side(style='thin', color='CCCCCC')
)
bottom_border = Border(bottom=Side(style='thin', color='999999'))
money = '#,##0'
money_neg = '#,##0;[Red]-#,##0'
pct = '0.0%'

def style_header_row(ws, row, max_col):
    for c in range(1, max_col + 1):
        cell = ws.cell(row=row, column=c)
        cell.font = header_font
        cell.alignment = Alignment(horizontal='center', wrap_text=True)
        cell.border = Border(bottom=Side(style='medium'))

def auto_width(ws, min_w=10, max_w=22):
    for col in ws.columns:
        letter = get_column_letter(col[0].column)
        max_len = max((len(str(c.value or '')) for c in col), default=0)
        ws.column_dimensions[letter].width = max(min(max_len + 3, max_w), min_w)

wb = openpyxl.Workbook()

# ============ SHEET 1: EXECUTIVE SUMMARY ============
ws1 = wb.active
ws1.title = "Executive Summary"

ws1.merge_cells('A1:H1')
ws1['A1'] = "Investment Analysis: $345,000 New Build Rental Property"
ws1['A1'].font = title_font

ws1.merge_cells('A3:H3')
ws1['A3'] = "VERDICT: NO-GO — Negative cash flow for 5+ years. Does not meet basic investment criteria."
ws1['A3'].font = bold_red

r = 5
ws1.cell(row=r, column=1, value="Property Details").font = section_font
ws1.cell(row=r, column=1).border = bottom_border

details = [
    ("Property Type", "New construction from builder"),
    ("Bedrooms / Bathrooms", "3 bed / 2 bath"),
    ("Square Footage", "1,400 sq ft"),
    ("Condition", "Brand new — no repairs needed"),
    ("Builder Warranty", "Typically 1-2yr structural, 10yr major systems"),
    ("Comparable Rents (Townhouses)", "$1,900/mo"),
    ("Comparable Rents (Stand Alones)", "$2,100/mo"),
]

for i, (label, val) in enumerate(details):
    row = r + 1 + i
    ws1.cell(row=row, column=1, value=label)
    ws1.cell(row=row, column=2, value=val).font = bold

r = r + len(details) + 2
ws1.cell(row=r, column=1, value="Key Financials").font = section_font
ws1.cell(row=r, column=1).border = bottom_border

metrics = [
    ("Purchase Price", f"${PRICE:,}", "Monthly Rent", f"${RENT:,}"),
    ("Down Payment (20%)", f"${DOWN:,.0f}", "Effective Monthly Income", f"${(RENT + OTHER_INCOME) * (1-VACANCY):,.0f}"),
    ("Closing Costs", f"${CLOSING:,}", "Monthly PITI", f"${PI_MONTHLY + PROP_TAX/12 + INSURANCE/12:,.0f}"),
    ("Total Cash Required", f"${CASH_IN:,.0f}", "Monthly Cash Flow", f"${((RENT+OTHER_INCOME)*12*(1-VACANCY) - (PROP_TAX+INSURANCE+MAINTENANCE+OTHER_COSTS) - PI_ANNUAL)/12:+,.0f}"),
    ("Loan Amount", f"${LOAN:,.0f}", "Rent-to-Price Ratio", f"{(RENT/PRICE)*100:.2f}%"),
    ("Interest Rate", f"{RATE:.2%}", "Year 1 Cash-on-Cash", f"{(((RENT+OTHER_INCOME)*12*(1-VACANCY) - (PROP_TAX+INSURANCE+MAINTENANCE+OTHER_COSTS) - PI_ANNUAL)/CASH_IN)*100:+.1f}%"),
    ("Monthly P&I", f"${PI_MONTHLY:,.0f}", "Break-Even Rate Needed", "4.57%"),
]

for i, (l1, v1, l2, v2) in enumerate(metrics):
    row = r + 1 + i
    ws1.cell(row=row, column=1, value=l1)
    ws1.cell(row=row, column=2, value=v1).font = bold
    ws1.cell(row=row, column=5, value=l2)
    ws1.cell(row=row, column=6, value=v2).font = bold

r = r + len(metrics) + 2
ws1.cell(row=r, column=1, value="Why This Deal Fails").font = section_font
ws1.cell(row=r, column=1).border = bottom_border

reasons = [
    ("Rent-to-Price Ratio: 0.58%",
     "This is the most important metric in rental investing. It measures monthly rent as a percentage of purchase price. "
     "The industry standard minimum is 0.8% (the '1% rule' is ideal). At $345K, you'd need rent of $2,760/mo (0.8%) or "
     "$3,450/mo (1%) to meet these thresholds. Your $2,000/mo rent is well below either target, meaning the property "
     "is overpriced relative to its income potential."),
    ("Negative Cash Flow for 5+ Years",
     "Cash flow = rental income minus all expenses (mortgage, taxes, insurance, maintenance). This property loses money "
     "every month for the first 5-6 years. Year 1: -$283/mo (-$3,393/yr). Year 5: -$21/mo (-$248/yr). Total out-of-pocket "
     "over 5 years: $9,264. You are paying to own this property rather than it paying you."),
    ("Mortgage Consumes 89% of Income",
     "Your monthly mortgage payment ($1,699) eats 89% of effective rental income ($1,948). A healthy rental should have "
     "mortgage at 70% or less of income, leaving 30%+ for taxes, insurance, maintenance, vacancy, and profit. At 89%, "
     "there is virtually nothing left after paying the mortgage."),
    ("Interest Rate Too High for This Price",
     "At 6.25%, the cost of borrowing is too high relative to the rent the property generates. To break even on cash flow "
     "at $345K, you would need a rate of 4.57% or lower. Current rates are ~6.25% with no indication of dropping to 4.57% "
     "in the near term. The rate environment makes this deal unworkable."),
    ("No Tax Benefit Above $150K AGI",
     "Rental property losses (depreciation + expenses exceeding income) can offset W-2 income — but only if your Adjusted "
     "Gross Income is below $100K (full $25K deduction) or $100-150K (partial). Above $150K AGI, passive losses provide "
     "$0 tax benefit against your paycheck. They carry forward to offset future passive income or are released when you sell, "
     "but provide no annual cash benefit while you hold the property."),
    ("Opportunity Cost",
     "The $75,000 required for this investment could be placed in an S&P 500 index fund averaging ~9% annually. After 10 years: "
     "~$177,500 with zero effort, full liquidity, no maintenance, no tenants, no risk of a $15K surprise repair. The real estate "
     "catches up around year 10+ due to leverage and appreciation, but with significantly more work and risk."),
]

for i, (title, detail) in enumerate(reasons):
    row = r + 1 + (i * 3)
    ws1.cell(row=row, column=1, value=title).font = bold
    ws1.merge_cells(f'A{row+1}:H{row+1}')
    ws1.cell(row=row + 1, column=1, value=detail).font = italic_gray
    ws1.cell(row=row + 1, column=1).alignment = Alignment(wrap_text=True)

r = row + 4
ws1.cell(row=r, column=1, value="What Would Make This Deal Work").font = section_font
ws1.cell(row=r, column=1).border = bottom_border

fixes = [
    ("Purchase Price ≤ $299,000",
     "At current 6.25% rate, a price of $299K or less produces positive cash flow from Day 1. "
     "This brings the rent-to-price ratio to 0.67% — still below the 0.8% ideal but workable."),
    ("Interest Rate ≤ 4.57%",
     "At the current $345K price, you need rates to drop ~170 basis points to break even. "
     "This is not expected in the near term based on current Fed policy."),
    ("Monthly Rent ≥ $2,760",
     "This would achieve a 0.8% rent-to-price ratio. However, comparable properties in the area "
     "rent for $1,900-$2,100, so the market does not support this rent level."),
    ("Different Market",
     "Midwest and some Southern markets (Cleveland, Memphis, Indianapolis, Birmingham) still offer "
     "properties at 0.8-1.2% rent-to-price ratios at lower price points, where the same $75K investment "
     "would cash flow from Day 1."),
]

for i, (fix, detail) in enumerate(fixes):
    row = r + 1 + (i * 3)
    ws1.cell(row=row, column=1, value=fix).font = bold_green
    ws1.merge_cells(f'A{row+1}:H{row+1}')
    ws1.cell(row=row + 1, column=1, value=detail).font = italic_gray
    ws1.cell(row=row + 1, column=1).alignment = Alignment(wrap_text=True)

auto_width(ws1, 14, 25)
ws1.column_dimensions['A'].width = 38
for col in 'BCDEFGH':
    if col == 'B':
        ws1.column_dimensions[col].width = 20

# ============ SHEET 2: EXPENSE BREAKDOWN ============
ws2 = wb.create_sheet("Expense Breakdown")

ws2.merge_cells('A1:D1')
ws2['A1'] = "Complete Expense Breakdown — Year 1 Monthly"
ws2['A1'].font = title_font

r = 3
ws2.cell(row=r, column=1, value="Income").font = section_font
ws2.cell(row=r, column=1).border = bottom_border

r += 1
income_items = [
    ("Gross Monthly Rent", RENT,
     "The base rent charged to the tenant. Based on comparable properties: townhouses at $1,900 and "
     "stand-alone homes at $2,100. $2,000 is mid-range for this property type and area."),
    ("Other Monthly Income", OTHER_INCOME,
     "Additional income such as pet rent, storage fees, laundry, parking, or late fees. "
     "$50/mo is a conservative estimate."),
    ("Less: Vacancy Allowance (5%)", -(RENT + OTHER_INCOME) * VACANCY,
     "Industry standard assumption that the property will be vacant ~18 days per year (5% of the time) "
     "due to tenant turnover, time to find new tenants, cleaning/repair between tenants. "
     "Some investors use 8-10% for more conservative modeling."),
]

for i, (label, val, explanation) in enumerate(income_items):
    row = r + (i * 3)
    ws2.cell(row=row, column=1, value=label).font = bold if val > 0 else Font(bold=True, color="CC0000")
    ws2.cell(row=row, column=2, value=val).number_format = money_neg
    ws2.cell(row=row, column=2).font = green if val > 0 else red
    ws2.merge_cells(f'A{row+1}:D{row+1}')
    ws2.cell(row=row + 1, column=1, value=explanation).font = italic_gray
    ws2.cell(row=row + 1, column=1).alignment = Alignment(wrap_text=True)

eff_income = (RENT + OTHER_INCOME) * (1 - VACANCY)
row = r + len(income_items) * 3
ws2.cell(row=row, column=1, value="= Effective Monthly Income").font = Font(bold=True, size=11)
ws2.cell(row=row, column=2, value=eff_income).number_format = money
ws2.cell(row=row, column=2).font = Font(bold=True, size=11, color="006600")
ws2.cell(row=row, column=1).border = Border(top=Side(style='medium'))
ws2.cell(row=row, column=2).border = Border(top=Side(style='medium'))

# Expenses
r = row + 3
ws2.cell(row=r, column=1, value="Fixed Expenses (Debt Service)").font = section_font
ws2.cell(row=r, column=1).border = bottom_border

r += 1
fixed_expenses = [
    ("Mortgage Payment (Principal & Interest)", PI_MONTHLY,
     f"This is the monthly payment on a ${LOAN:,} loan at {RATE:.2%} interest over 30 years. "
     f"Of this ${PI_MONTHLY:,.0f} payment, ~${LOAN*RATE/12:,.0f} goes to interest in Year 1 and only "
     f"~${PI_MONTHLY - LOAN*RATE/12:,.0f} goes to principal (building equity). Over 30 years you will pay "
     f"${PI_ANNUAL*30:,.0f} total — ${PI_ANNUAL*30 - LOAN:,.0f} in interest alone. "
     f"This single line item consumes {PI_MONTHLY/eff_income*100:.0f}% of your effective rental income."),
]

for i, (label, val, explanation) in enumerate(fixed_expenses):
    row = r + (i * 3)
    ws2.cell(row=row, column=1, value=label).font = bold
    ws2.cell(row=row, column=2, value=val).number_format = money
    ws2.cell(row=row, column=2).font = red
    ws2.merge_cells(f'A{row+1}:D{row+1}')
    ws2.cell(row=row + 1, column=1, value=explanation).font = italic_gray
    ws2.cell(row=row + 1, column=1).alignment = Alignment(wrap_text=True)

r = row + 3
ws2.cell(row=r, column=1, value="Operating Expenses").font = section_font
ws2.cell(row=r, column=1).border = bottom_border

r += 1
operating_expenses = [
    ("Property Tax", PROP_TAX / 12,
     f"${PROP_TAX:,}/year divided by 12. Property tax is assessed by the local government based on the "
     f"property's appraised value. At ${PROP_TAX:,}/yr on a ${PRICE:,} property, the effective rate is "
     f"{(PROP_TAX/PRICE)*100:.2f}%. Property taxes typically increase 2-5% annually due to rising "
     f"assessments. In some areas, a new purchase triggers reassessment to current market value, which "
     f"could increase this amount. This expense increases at {EXPENSE_GROWTH:.0%}/yr in this model."),
    ("Homeowner's Insurance", INSURANCE / 12,
     f"${INSURANCE:,}/year divided by 12. Covers damage from fire, storms, liability, etc. "
     f"New builds may get slightly lower rates due to modern materials and code compliance, but insurance "
     f"costs have been rising 7-10% annually nationwide due to climate events. If the property is in a "
     f"flood zone, flood insurance ($1,000-3,000/yr) would be additional. "
     f"This expense increases at {EXPENSE_GROWTH:.0%}/yr in this model."),
    ("HOA Fees", HOA / 12,
     "No HOA in this scenario. If the property is in a subdivision with shared amenities (pool, landscaping, "
     "common areas), HOA fees typically run $100-400/mo and increase annually. HOA fees are non-negotiable "
     "and can include special assessments for major repairs. Always verify before purchasing."),
    ("Maintenance & Repairs", MAINTENANCE / 12,
     f"${MAINTENANCE:,}/year divided by 12. The standard rule of thumb is 1% of property value annually "
     f"(= ${PRICE*0.01:,.0f}/yr for this property). The ${MAINTENANCE:,}/yr used here is lower because "
     f"this is a brand new build — appliances are new, HVAC is new, roof is new, and the builder warranty "
     f"covers defects for 1-2 years (structural up to 10 years). However, maintenance is never zero: "
     f"lawn care, minor repairs, appliance issues, plumbing, pest control, etc. still occur. "
     f"IMPORTANT: After years 5-10, this number should increase significantly as systems age. "
     f"HVAC replacement: $8-15K (lifespan 12-15 yrs). Water heater: $1-2K (lifespan 8-12 yrs). "
     f"Flooring: $5-10K (every 7-10 yrs between tenants). "
     f"This expense increases at {EXPENSE_GROWTH:.0%}/yr in this model."),
    ("Other Costs", OTHER_COSTS / 12,
     f"${OTHER_COSTS:,}/year divided by 12. Covers miscellaneous expenses: lawn care equipment/service, "
     f"pest control, accounting/tax prep for rental income, legal fees, advertising for tenants, "
     f"background check fees, locksmith, miscellaneous supplies. Also covers tenant turnover costs "
     f"between leases (cleaning, touch-up paint, minor repairs to pass inspection). "
     f"This expense increases at {EXPENSE_GROWTH:.0%}/yr in this model."),
    ("Property Management Fee (0%)", 0,
     "This model assumes self-management (0%). If you hire a property manager, expect to pay 8-10% of "
     "gross rent ($160-200/mo). Most investors eventually hire management — dealing with tenant calls, "
     "maintenance coordination, lease enforcement, and evictions is a part-time job. Adding 8% management "
     "would increase annual expenses by ~$1,920 and push Year 1 cash flow to -$5,313. "
     "If this is a partnership, management is almost certainly required."),
]

total_operating = PROP_TAX/12 + INSURANCE/12 + HOA/12 + MAINTENANCE/12 + OTHER_COSTS/12

for i, (label, val, explanation) in enumerate(operating_expenses):
    row = r + (i * 4)
    ws2.cell(row=row, column=1, value=label).font = bold
    ws2.cell(row=row, column=2, value=val).number_format = money
    ws2.cell(row=row, column=2).font = red if val > 0 else Font()
    ws2.merge_cells(f'A{row+1}:D{row+2}')
    ws2.cell(row=row + 1, column=1, value=explanation).font = italic_gray
    ws2.cell(row=row + 1, column=1).alignment = Alignment(wrap_text=True, vertical='top')

# Totals section
r = row + 5
ws2.cell(row=r, column=1, value="Summary").font = section_font
ws2.cell(row=r, column=1).border = bottom_border

r += 1
total_monthly_exp = PI_MONTHLY + total_operating
net = eff_income - total_monthly_exp

summary_items = [
    ("Effective Monthly Income", eff_income, "006600"),
    ("Less: Mortgage (P&I)", -PI_MONTHLY, "CC0000"),
    ("Less: Operating Expenses", -total_operating, "CC0000"),
    ("= Total Monthly Expenses", -total_monthly_exp, "CC0000"),
    ("", None, None),
    ("NET MONTHLY CASH FLOW", net, "CC0000" if net < 0 else "006600"),
    ("NET ANNUAL CASH FLOW", net * 12, "CC0000" if net < 0 else "006600"),
]

for i, (label, val, color) in enumerate(summary_items):
    row = r + i
    ws2.cell(row=row, column=1, value=label)
    if val is not None:
        ws2.cell(row=row, column=2, value=val).number_format = money_neg
        ws2.cell(row=row, column=2).font = Font(bold=True, color=color)
    if "NET" in label:
        ws2.cell(row=row, column=1).font = Font(bold=True, size=11)
        ws2.cell(row=row, column=2).font = Font(bold=True, size=11, color=color)
        ws2.cell(row=row, column=1).border = Border(top=Side(style='medium'))
        ws2.cell(row=row, column=2).border = Border(top=Side(style='medium'))

r = row + 3
ws2.cell(row=r, column=1, value="Expense Growth Assumptions").font = section_font
ws2.cell(row=r, column=1).border = bottom_border

r += 1
growth_items = [
    ("Rent Growth", f"{RENT_GROWTH:.0%}/year",
     "Assumes 4% annual rent increases. Historical average is 3-4% nationally. "
     "However, with rent already at market rate ($2,000 vs $1,900-$2,100 comps), aggressive "
     "increases may not be achievable without losing tenants."),
    ("Expense Growth", f"{EXPENSE_GROWTH:.0%}/year",
     "Property tax, insurance, and maintenance costs increase ~3% annually on average. "
     "Insurance has been rising faster (7-10%) in recent years. Property tax can spike "
     "after reassessment."),
    ("Property Appreciation", f"{APPRECIATION:.0%}/year",
     "Long-term historical average for US residential real estate is ~3-4%. New builds in "
     "growing areas may exceed this; properties in stagnant areas may underperform."),
]

for i, (label, val, explanation) in enumerate(growth_items):
    row = r + (i * 3)
    ws2.cell(row=row, column=1, value=label).font = bold
    ws2.cell(row=row, column=2, value=val).font = bold
    ws2.merge_cells(f'A{row+1}:D{row+1}')
    ws2.cell(row=row + 1, column=1, value=explanation).font = italic_gray
    ws2.cell(row=row + 1, column=1).alignment = Alignment(wrap_text=True)

auto_width(ws2, 14, 30)
ws2.column_dimensions['A'].width = 40
ws2.column_dimensions['D'].width = 40

# ============ SHEET 3: 20-YEAR CASH FLOW ============
ws3 = wb.create_sheet("20-Year Cash Flow")

ws3.merge_cells('A1:L1')
ws3['A1'] = "20-Year Cash Flow Projection — New Build"
ws3['A1'].font = title_font

ws3.merge_cells('A2:L2')
ws3['A2'] = f"Purchase: ${PRICE:,} | Down: 20% | Rate: {RATE:.2%} | Rent: ${RENT:,}/mo | Growth: Rent {RENT_GROWTH:.0%}, Expenses {EXPENSE_GROWTH:.0%}, Appreciation {APPRECIATION:.0%}"
ws3['A2'].font = italic_gray

r = 4
headers = ['Year', 'Annual\nIncome', 'Operating\nExpenses', 'Mortgage\n(P&I)', 'Cash\nFlow',
           'Cumulative\nCash Flow', 'Cash-on-Cash\nReturn', 'Mortgage\nInterest', 'Principal\nPaid',
           'Property\nValue', 'Loan\nBalance', 'Total\nEquity']
for c, h in enumerate(headers, 1):
    ws3.cell(row=r, column=c, value=h)
style_header_row(ws3, r, len(headers))

balance = LOAN
cum_cf = 0
breakeven_year = None

for yr in range(1, HOLD_YEARS + 1):
    row = r + yr
    annual_rent = (RENT * 12 + OTHER_INCOME * 12) * (1 + RENT_GROWTH) ** (yr - 1)
    effective_income = annual_rent * (1 - VACANCY)
    
    tax = PROP_TAX * (1 + EXPENSE_GROWTH) ** (yr - 1)
    ins = INSURANCE * (1 + EXPENSE_GROWTH) ** (yr - 1)
    maint = MAINTENANCE * (1 + EXPENSE_GROWTH) ** (yr - 1)
    other = OTHER_COSTS * (1 + EXPENSE_GROWTH) ** (yr - 1)
    expenses = tax + ins + maint + other
    
    interest = balance * RATE
    principal = PI_ANNUAL - interest
    balance -= principal
    
    cash_flow = effective_income - expenses - PI_ANNUAL
    cum_cf += cash_flow
    coc = cash_flow / CASH_IN
    
    prop_value = PRICE * (1 + APPRECIATION) ** yr
    equity = prop_value - balance
    
    if breakeven_year is None and cash_flow >= 0:
        breakeven_year = yr
    
    values = [yr, effective_income, expenses, PI_ANNUAL, cash_flow, cum_cf, coc,
              interest, principal, prop_value, balance, equity]
    
    for c, v in enumerate(values, 1):
        cell = ws3.cell(row=row, column=c, value=v)
        if c in (2, 3, 4, 5, 6, 8, 9, 10, 11, 12):
            cell.number_format = money_neg
        elif c == 7:
            cell.number_format = pct
        if c == 5:
            cell.font = red if v < 0 else green
        cell.border = thin_border

row = r + HOLD_YEARS + 1
ws3.cell(row=row, column=1, value="TOTALS").font = bold
total_income = sum((RENT * 12 + OTHER_INCOME * 12) * (1 + RENT_GROWTH) ** (yr - 1) * (1 - VACANCY) for yr in range(1, HOLD_YEARS + 1))
total_expenses = sum((PROP_TAX + INSURANCE + MAINTENANCE + OTHER_COSTS) * (1 + EXPENSE_GROWTH) ** (yr - 1) for yr in range(1, HOLD_YEARS + 1))
ws3.cell(row=row, column=2, value=total_income).number_format = money
ws3.cell(row=row, column=3, value=total_expenses).number_format = money
ws3.cell(row=row, column=4, value=PI_ANNUAL * HOLD_YEARS).number_format = money
ws3.cell(row=row, column=5, value=cum_cf).number_format = money_neg
ws3.cell(row=row, column=5).font = bold_green if cum_cf > 0 else bold_red

row += 2
ws3.cell(row=row, column=1, value=f"Cash flow becomes positive: Year {breakeven_year}").font = bold

row += 1
neg_cf = sum(
    (RENT * 12 + OTHER_INCOME * 12) * (1 + RENT_GROWTH) ** (yr - 1) * (1 - VACANCY) 
    - (PROP_TAX + INSURANCE + MAINTENANCE + OTHER_COSTS) * (1 + EXPENSE_GROWTH) ** (yr - 1) 
    - PI_ANNUAL 
    for yr in range(1, breakeven_year or 1)
)
ws3.cell(row=row, column=1, value=f"Total losses before break-even: ${abs(neg_cf):,.0f}").font = italic_gray

# Column explanations
row += 2
ws3.cell(row=row, column=1, value="Column Definitions").font = section_font
ws3.cell(row=row, column=1).border = bottom_border

col_explanations = [
    ("Annual Income", "Gross rent + other income, less 5% vacancy. Grows at 4% per year."),
    ("Operating Expenses", "Property tax + insurance + maintenance + other costs. Grows at 3% per year. Does NOT include mortgage."),
    ("Mortgage (P&I)", "Fixed annual mortgage payment (principal + interest). Does not change over 30 years."),
    ("Cash Flow", "Income minus operating expenses minus mortgage. Negative = you pay out of pocket."),
    ("Cumulative Cash Flow", "Running total of all cash flows since purchase. Shows total gain or loss over time."),
    ("Cash-on-Cash Return", "Annual cash flow divided by total cash invested ($75,000). The return on your actual money invested."),
    ("Mortgage Interest", "The interest portion of your mortgage payment. Decreases over time as balance is paid down. Tax deductible."),
    ("Principal Paid", "The portion of your mortgage payment that reduces your loan balance. Increases over time. This builds equity."),
    ("Property Value", "Estimated value based on 3% annual appreciation."),
    ("Loan Balance", "Remaining mortgage balance. Decreases as principal is paid."),
    ("Total Equity", "Property value minus loan balance. This is what you'd walk away with if you sold (before selling costs)."),
]

for i, (col_name, explanation) in enumerate(col_explanations):
    r2 = row + 1 + i
    ws3.cell(row=r2, column=1, value=col_name).font = bold
    ws3.merge_cells(f'B{r2}:L{r2}')
    ws3.cell(row=r2, column=2, value=explanation).font = italic_gray

auto_width(ws3, 12, 18)

# ============ SHEET 4: vs ALTERNATIVES (20 years) ============
ws4 = wb.create_sheet("vs Alternatives")

ws4.merge_cells('A1:H1')
ws4['A1'] = "$75,000 Invested: Real Estate vs Alternatives — 20 Years"
ws4['A1'].font = title_font

ws4.merge_cells('A2:H2')
ws4['A2'] = ("All alternatives assume $75,000 invested once and left to compound. "
             "Real estate includes equity buildup (appreciation + principal paydown) plus cumulative cash flow. "
             "No additional capital invested in any scenario after Year 0.")
ws4['A2'].font = italic_gray
ws4['A2'].alignment = Alignment(wrap_text=True)

r = 4
headers = ['Year', 'S&P 500\n(9% avg)', 'T-Bills\n(4.5%)', 'REIT Index\n(7%)',
           'RE: Equity', 'RE: Cum\nCash Flow', 'RE: Total\nValue', 'Best\nOption']
for c, h in enumerate(headers, 1):
    ws4.cell(row=r, column=c, value=h)
style_header_row(ws4, r, len(headers))

sp500 = CASH_IN
tbill = CASH_IN
reit = CASH_IN
balance = LOAN
cum_cf = 0

for yr in range(1, HOLD_YEARS + 1):
    row = r + yr
    sp500 *= 1.09
    tbill *= 1.045
    reit *= 1.07
    
    eff_inc = (RENT * 12 + OTHER_INCOME * 12) * (1 + RENT_GROWTH) ** (yr - 1) * (1 - VACANCY)
    exp = (PROP_TAX + INSURANCE + MAINTENANCE + OTHER_COSTS) * (1 + EXPENSE_GROWTH) ** (yr - 1)
    interest = balance * RATE
    principal = PI_ANNUAL - interest
    balance -= principal
    cf = eff_inc - exp - PI_ANNUAL
    cum_cf += cf
    prop_val = PRICE * (1 + APPRECIATION) ** yr
    equity = prop_val - balance
    re_total = equity + cum_cf
    
    # Determine best option
    options = {'S&P 500': sp500, 'T-Bills': tbill, 'REITs': reit, 'Real Estate': re_total}
    best = max(options, key=options.get)
    
    values = [yr, sp500, tbill, reit, equity, cum_cf, re_total, best]
    for c, v in enumerate(values, 1):
        cell = ws4.cell(row=row, column=c, value=v)
        if c in (2, 3, 4, 5, 6, 7):
            cell.number_format = money_neg
        cell.border = thin_border
        if c == 8:
            cell.font = bold

# Totals
row = r + HOLD_YEARS + 2
ws4.cell(row=row, column=1, value="20-Year Gain").font = bold
ws4.cell(row=row, column=2, value=sp500 - CASH_IN).number_format = money
ws4.cell(row=row, column=2).font = bold_green
ws4.cell(row=row, column=3, value=tbill - CASH_IN).number_format = money
ws4.cell(row=row, column=4, value=reit - CASH_IN).number_format = money
ws4.cell(row=row, column=7, value=re_total - CASH_IN).number_format = money
ws4.cell(row=row, column=7).font = bold_green if re_total > sp500 else bold

# Explanation section
row += 2
ws4.cell(row=row, column=1, value="How to Read This Table").font = section_font
ws4.cell(row=row, column=1).border = bottom_border

explanations = [
    ("S&P 500 (9% avg)",
     "Historical average annual return of the S&P 500 index including dividends, adjusted for inflation. "
     "This is a passive investment — buy an index fund and do nothing. Fully liquid (can sell any day). "
     "No maintenance, no tenants, no surprise costs. Risk: market volatility (can drop 30%+ in a crash, but historically recovers)."),
    ("T-Bills (4.5%)",
     "US Treasury Bills — the 'risk-free' rate. Currently yielding ~4.5%. Principal is guaranteed by the US government. "
     "No risk of loss, but lowest returns. Useful as a baseline: if your investment can't beat T-Bills, why take the risk?"),
    ("REIT Index (7%)",
     "Real Estate Investment Trusts — publicly traded companies that own rental properties, malls, warehouses, etc. "
     "You get real estate exposure with stock market liquidity. Average ~7% annual returns. No property management needed."),
    ("RE: Equity",
     "Your equity in the rental property = property value minus remaining loan balance. Grows from both property appreciation (3%/yr) "
     "and principal paydown (each mortgage payment reduces the loan). This is NOT liquid — you can only access it by selling or refinancing."),
    ("RE: Cum Cash Flow",
     "Running total of all cash received (or paid) from the rental. Negative early on because expenses exceed rent. "
     "Turns positive around Year 6 but cumulative losses persist for years."),
    ("RE: Total Value",
     "Equity + cumulative cash flow = total value of the real estate investment. This is the fair comparison "
     "against the other options. Note: does NOT account for selling costs (typically 6-8% of sale price = $20-30K)."),
    ("Why RE Catches Up",
     "Real estate uses LEVERAGE — you control a $345K asset with only $75K. When the property appreciates 3%, "
     "you gain 3% of $345K ($10,350), not 3% of $75K ($2,250). This leverage is why RE eventually overtakes stocks — "
     "but it also means losses are amplified if values drop. The S&P 500 comparison uses no leverage."),
    ("The Catch",
     "The RE total does NOT deduct: selling costs (6-8%), capital gains tax, depreciation recapture tax (25% on ~$200K), "
     "time spent managing, or the risk of bad tenants/vacancies/repairs. The stock alternatives have none of these hidden costs. "
     "After selling costs and taxes, the actual RE return is significantly lower than shown."),
]

for i, (label, explanation) in enumerate(explanations):
    r2 = row + 1 + (i * 3)
    ws4.cell(row=r2, column=1, value=label).font = bold
    ws4.merge_cells(f'A{r2+1}:H{r2+1}')
    ws4.cell(row=r2 + 1, column=1, value=explanation).font = italic_gray
    ws4.cell(row=r2 + 1, column=1).alignment = Alignment(wrap_text=True)

auto_width(ws4, 14, 18)
ws4.column_dimensions['A'].width = 22
ws4.column_dimensions['H'].width = 14

# ============ SHEET 5: RISK SCENARIOS (new build adjusted) ============
ws5 = wb.create_sheet("Risk Scenarios")

ws5.merge_cells('A1:F1')
ws5['A1'] = "Stress Test: What Could Go Wrong — New Build"
ws5['A1'].font = title_font

ws5.merge_cells('A2:F2')
ws5['A2'] = ("New construction significantly reduces early repair risk (builder warranty covers years 1-2, structural up to 10). "
             "Major system replacements (HVAC, roof) are unlikely before year 10-15. Scenarios below are adjusted accordingly.")
ws5['A2'].font = italic_gray
ws5['A2'].alignment = Alignment(wrap_text=True)

r = 4
headers = ["Scenario", "Year 1\nCash Flow", "5-Year\nCash Flow", "Impact", "Probability", "Explanation"]
for c, h in enumerate(headers, 1):
    ws5.cell(row=r, column=c, value=h)
style_header_row(ws5, r, len(headers))

scenarios = [
    ("Base Case", -3393, -9264, "Negative but manageable", "Expected",
     "The numbers as modeled. You feed the property ~$283/mo Year 1, declining annually as rent grows."),
    ("Water Heater Fails Year 3 ($2K)", -3393, -11264, "Minor setback", "25% in 5yr",
     "Even new water heaters can fail. $1,500-2,500 replacement. Not covered by builder warranty after year 1-2."),
    ("Appliance Replacements Year 4 ($3K)", -3393, -12264, "Annoying, not devastating", "20% in 5yr",
     "Dishwasher, microwave, garbage disposal. Builder-grade appliances often fail sooner than premium brands."),
    ("HVAC Replacement Year 12 ($12K)", -3393, -9264, "Hits during positive cash flow years", "60% in 15yr",
     "New HVAC systems last 12-15 years. By year 12, the property cash flows ~$4K/yr, so one year's profit is wiped."),
    ("Roof Replacement Year 18 ($20K)", -3393, -9264, "Major but expected at this age", "40% in 20yr",
     "New roofs last 20-25 years. Budget for this as a known future expense, not a surprise."),
    ("10% Vacancy (bad tenant/eviction)", -4593, -14264, "Doubles the bleeding", "20% chance",
     "Eviction process takes 1-3 months. During that time: zero rent + legal fees ($1-3K) + unit damage repair."),
    ("Rent Drops 10% (recession)", -5793, -19264, "Cash flow crisis", "15-20%",
     "Economic downturn reduces demand. You may need to drop rent to keep occupancy, or accept longer vacancies."),
    ("Property Value Drops 15%", -3393, -9264, "Paper loss, real if you sell", "10-15%",
     "Your $345K property is worth $293K. With $276K loan, you have $17K equity vs $69K invested. "
     "You're not underwater on the mortgage, but 75% of your down payment is gone on paper."),
    ("Hire Property Manager Year 3 (8%)", -3393, -13284, "Realistic for partnership", "High",
     "Partnerships almost always need professional management. 8% of gross rent = $160/mo = $1,920/yr additional cost."),
    ("Interest Rates Drop, Refi Year 5 (5%)", -3393, -5264, "Improves the deal", "30-40%",
     "If rates drop to 5%, refinancing saves ~$230/mo. This would make the property cash flow positive immediately. "
     "However, refinancing costs $3-6K and resets your amortization schedule."),
]

for i, (scenario, yr1, yr5, impact, prob, explanation) in enumerate(scenarios):
    row = r + 1 + (i * 3)
    ws5.cell(row=row, column=1, value=scenario).font = bold
    ws5.cell(row=row, column=2, value=yr1).number_format = money_neg
    ws5.cell(row=row, column=2).font = red if yr1 < 0 else green
    ws5.cell(row=row, column=3, value=yr5).number_format = money_neg
    ws5.cell(row=row, column=3).font = red if yr5 < 0 else green
    ws5.cell(row=row, column=4, value=impact)
    ws5.cell(row=row, column=5, value=prob)
    ws5.merge_cells(f'A{row+1}:F{row+1}')
    ws5.cell(row=row + 1, column=1, value=explanation).font = italic_gray
    ws5.cell(row=row + 1, column=1).alignment = Alignment(wrap_text=True)

auto_width(ws5, 15, 25)
ws5.column_dimensions['A'].width = 38
ws5.column_dimensions['D'].width = 30
ws5.column_dimensions['F'].width = 40

# ============ SHEET 6: BREAK-EVEN ============
ws6 = wb.create_sheet("Break-Even Analysis")

ws6.merge_cells('A1:D1')
ws6['A1'] = "What Price + Rate = Day 1 Cash Flow Positive?"
ws6['A1'].font = title_font

ws6.merge_cells('A2:D2')
ws6['A2'] = (f"All scenarios assume: Rent ${RENT:,}/mo + ${OTHER_INCOME}/mo other | Vacancy {VACANCY:.0%} | "
             f"20% down | 30yr fixed | Same tax/insurance/maintenance rates")
ws6['A2'].font = italic_gray
ws6['A2'].alignment = Alignment(wrap_text=True)

r = 4
ws6.cell(row=r, column=1, value="Break-Even Interest Rate by Purchase Price").font = section_font
ws6.cell(row=r, column=1).border = bottom_border

ws6.merge_cells(f'A{r+1}:D{r+1}')
ws6.cell(row=r+1, column=1, 
         value="What is the maximum interest rate where this property breaks even on cash flow from Day 1? "
               "If the break-even rate is below current market rates (6.25%), the deal does not work at that price.").font = italic_gray
ws6.cell(row=r+1, column=1).alignment = Alignment(wrap_text=True)

r += 3
for c, h in enumerate(['Purchase Price', 'Max Rate for\nBreak-Even', 'Works at 6.25%?', 'Rent-to-Price\nRatio'], 1):
    ws6.cell(row=r, column=c, value=h)
style_header_row(ws6, r, 4)

prices = [200000, 220000, 240000, 260000, 280000, 299000, 320000, 345000]
for i, price in enumerate(prices):
    row = r + 1 + i
    be_rate = None
    for rate_bps in range(100, 1200, 1):
        rate_test = rate_bps / 10000
        loan = price * 0.80
        pi = monthly_pmt(loan, rate_test)
        expenses = (price * 0.007 + price * 0.004 + price * 0.006) / 12 + OTHER_COSTS / 12
        total = pi + expenses
        income = (RENT + OTHER_INCOME) * (1 - VACANCY)
        if income - total <= 0:
            be_rate = (rate_bps - 1) / 10000
            break
    
    ws6.cell(row=row, column=1, value=price).number_format = '$#,##0'
    ws6.cell(row=row, column=2, value=f"{be_rate:.2%}" if be_rate else "10%+")
    viable = "YES" if be_rate and be_rate >= 0.0625 else "NO"
    ws6.cell(row=row, column=3, value=viable).font = bold_green if viable == "YES" else bold_red
    rtp = (RENT/price)*100
    ws6.cell(row=row, column=4, value=f"{rtp:.2f}%")
    ws6.cell(row=row, column=4).font = green if rtp >= 0.8 else red
    cell_border = thin_border
    for c in range(1, 5):
        ws6.cell(row=row, column=c).border = cell_border
    
    if price == 345000:
        ws6.cell(row=row, column=1).font = bold
        ws6.cell(row=row, column=2).font = bold

last_row = row

r = last_row + 3
ws6.cell(row=r, column=1, value="Maximum Purchase Price by Interest Rate").font = section_font
ws6.cell(row=r, column=1).border = bottom_border

ws6.merge_cells(f'A{r+1}:D{r+1}')
ws6.cell(row=r+1, column=1,
         value="At a given interest rate, what is the most you can pay for this property and still break even on Day 1?").font = italic_gray
ws6.cell(row=r+1, column=1).alignment = Alignment(wrap_text=True)

r += 3
for c, h in enumerate(['Interest Rate', 'Max Purchase\nPrice', 'Down Payment\nRequired', 'Rent-to-Price\nRatio'], 1):
    ws6.cell(row=r, column=c, value=h)
style_header_row(ws6, r, 4)

rates = [3.0, 3.5, 4.0, 4.5, 5.0, 5.5, 6.0, 6.25, 7.0]
for i, rate_pct in enumerate(rates):
    row = r + 1 + i
    rate_val = rate_pct / 100
    max_price = None
    for price in range(400000, 100000, -1000):
        loan = price * 0.80
        pi = monthly_pmt(loan, rate_val)
        expenses = (price * 0.007 + price * 0.004 + price * 0.006) / 12 + OTHER_COSTS / 12
        total = pi + expenses
        income = (RENT + OTHER_INCOME) * (1 - VACANCY)
        if income >= total:
            max_price = price
            break
    
    ws6.cell(row=row, column=1, value=f"{rate_pct:.2f}%")
    if max_price:
        ws6.cell(row=row, column=2, value=max_price).number_format = '$#,##0'
        ws6.cell(row=row, column=3, value=max_price * 0.20).number_format = '$#,##0'
        ws6.cell(row=row, column=4, value=f"{(RENT/max_price)*100:.2f}%")
    
    for c in range(1, 5):
        ws6.cell(row=row, column=c).border = thin_border
    
    if rate_pct == 6.25:
        ws6.cell(row=row, column=1).font = bold
        ws6.cell(row=row, column=2).font = bold

auto_width(ws6, 18, 28)

# Save
outpath = "/home/wdjones/.openclaw/workspace/data/real-estate-345k-nogo-analysis.xlsx"
wb.save(outpath)
print(f"Saved to {outpath}")