778 lines
37 KiB
Python
778 lines
37 KiB
Python
#!/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}")
|