Spaces:
Sleeping
Sleeping
| import gradio as gr | |
| import tempfile | |
| import shutil | |
| from pathlib import Path | |
| import pandas as pd | |
| from openpyxl import load_workbook | |
| """ | |
| Real Estate Financial Model Pipeline | |
| Extracts data from PDFs, solves formulas with Gemini API, generates Excel | |
| """ | |
| import re | |
| import json | |
| from pathlib import Path | |
| from typing import Dict, Any, List, Optional | |
| import openpyxl | |
| from openpyxl.styles import Font, PatternFill, Alignment, Border, Side | |
| from openpyxl.utils import get_column_letter | |
| from pdfminer.high_level import extract_text | |
| import google.generativeai as genai | |
| class RealEstateModelPipeline: | |
| def __init__(self, gemini_api_key: str): | |
| """Initialize pipeline with Gemini API key""" | |
| genai.configure(api_key=gemini_api_key) | |
| self.model = genai.GenerativeModel('gemini-2.0-flash') | |
| self.extracted_data = {} | |
| self.formula_results = {} | |
| self.structured_data = {} | |
| def safe_divide(self, numerator: float, denominator: float, default: float = 0) -> float: | |
| """Safe division that returns default instead of error""" | |
| try: | |
| if denominator == 0 or denominator is None: | |
| return default | |
| return numerator / denominator | |
| except: | |
| return default | |
| def extract_pdf_text(self, pdf_path: str) -> str: | |
| """Extract text from PDF using pdfminer""" | |
| try: | |
| text = extract_text(pdf_path) | |
| return text.strip() | |
| except Exception as e: | |
| print(f"Error extracting {pdf_path}: {e}") | |
| return "" | |
| def extract_xlsx_text(self, xlsx_path: str) -> str: | |
| """Extract text from XLSX using pandas and openpyxl""" | |
| try: | |
| extracted_content = [] | |
| # Try pandas first for data extraction | |
| try: | |
| xlsx = pd.ExcelFile(xlsx_path) | |
| for sheet_name in xlsx.sheet_names: | |
| df = pd.read_excel(xlsx, sheet_name=sheet_name) | |
| extracted_content.append(f"=== Sheet: {sheet_name} ===") | |
| extracted_content.append(df.to_string(index=False)) | |
| extracted_content.append("\n") | |
| except: | |
| pass | |
| # Also try openpyxl for cell-level data | |
| try: | |
| wb = load_workbook(xlsx_path, data_only=True) | |
| for sheet in wb.worksheets: | |
| extracted_content.append(f"\n=== Sheet: {sheet.title} (Raw) ===") | |
| for row in sheet.iter_rows(values_only=True): | |
| row_text = " | ".join([str(cell) if cell is not None else "" for cell in row]) | |
| if row_text.strip(): | |
| extracted_content.append(row_text) | |
| except: | |
| pass | |
| return "\n".join(extracted_content) | |
| except Exception as e: | |
| print(f"Error extracting {xlsx_path}: {e}") | |
| return "" | |
| def extract_all_pdfs(self, pdf_directory: str) -> Dict[str, str]: | |
| """Extract text from all PDFs and XLSX files in directory""" | |
| pdf_dir = Path(pdf_directory) | |
| extracted_texts = {} | |
| with open('output_file_3.txt', "w", encoding="utf-8") as f: | |
| # Process PDFs | |
| for pdf_file in pdf_dir.glob("*.pdf"): | |
| print(f"Extracting PDF: {pdf_file.name}") | |
| text = self.extract_pdf_text(str(pdf_file)) | |
| extracted_texts[pdf_file.stem] = text | |
| f.write(f"=== {pdf_file.name} ===\n") | |
| f.write(text) | |
| f.write("\n\n" + "="*80 + "\n\n") | |
| # Process XLSX files | |
| for xlsx_file in pdf_dir.glob("*.xlsx"): | |
| print(f"Extracting XLSX: {xlsx_file.name}") | |
| text = self.extract_xlsx_text(str(xlsx_file)) | |
| extracted_texts[xlsx_file.stem] = text | |
| f.write(f"=== {xlsx_file.name} ===\n") | |
| f.write(text) | |
| f.write("\n\n" + "="*80 + "\n\n") | |
| self.extracted_data = extracted_texts | |
| return extracted_texts | |
| def extract_address_fallback(self, pdf_texts: Dict[str, str]) -> Optional[str]: | |
| """Extract address using simple pattern matching as fallback""" | |
| for name, text in pdf_texts.items(): | |
| if 'Offering_Memorandum' in name or 'offering' in name.lower(): | |
| # Pattern: "Address: <address text>" | |
| match = re.search(r'Address:\s*(.+?)(?:\n|Property Type:)', text, re.IGNORECASE) | |
| if match: | |
| address = match.group(1).strip() | |
| print(f" ✓ Extracted address via fallback: {address}") | |
| return address | |
| return None | |
| def create_gemini_prompt(self, pdf_texts: Dict[str, str]) -> str: | |
| """Create comprehensive prompt for Gemini to extract structured data""" | |
| # Build a clear summary of what's in each PDF | |
| pdf_summary = "\n".join([f"- {name}: {len(text)} characters" for name, text in pdf_texts.items()]) | |
| prompt = f"""You are a real estate financial analyst. Extract ALL numerical data from the following PDF texts and return it as a JSON object. | |
| CRITICAL INSTRUCTIONS: | |
| 1. ONLY extract data that is EXPLICITLY stated in the PDFs - DO NOT estimate or make up values | |
| 2. For missing values, use null (not 0) | |
| 3. Pay close attention to the specific document names - each contains different information | |
| 4. Extract exact numbers as they appear in the documents | |
| AVAILABLE DOCUMENTS: | |
| {pdf_summary} | |
| PDF CONTENTS: | |
| """ | |
| for name, text in pdf_texts.items(): | |
| prompt += f"\n{'='*60}\n=== {name} ===\n{'='*60}\n{text}\n" | |
| prompt += """ | |
| EXTRACTION INSTRUCTIONS BY DOCUMENT: | |
| FROM "Offering_Memorandum.pdf": | |
| - Extract: Address (full address after "Address:") | |
| - Extract: Property Type (after "Property Type:") | |
| - Extract: Units (number after "Units:") | |
| FROM "Operating_Expenses_Summary.pdf" (if present): | |
| - Extract EXACT annual amounts for: | |
| * Real Estate Taxes | |
| * Insurance | |
| * Utilities | |
| * Repairs & Maint. (or Repairs & Maintenance) | |
| * Management Fee | |
| * Payroll | |
| * Administrative (if listed) | |
| * Professional Fees (if listed) | |
| FROM "Sales_Comps.pdf": | |
| - Extract all Price/SF values | |
| - Calculate average_price_per_sf = average of all Price/SF values | |
| - Count total number of comps | |
| FROM "Rent_Comps.pdf" (if present): | |
| - Extract all rent values (numbers before @ symbol) | |
| - Calculate average_rent = average of all rent values | |
| - Count total number of rent comps | |
| FROM "Market_Report.pdf": | |
| - Extract: Vacancy Rate (percentage) | |
| - Extract: Rent Growth (YoY) (percentage) | |
| FROM "Demographics_Overview.pdf": | |
| - Extract: Population (3-mi) - the number | |
| - Extract: Median HH Income - the dollar amount | |
| - Extract: Transit Score - the number | |
| REQUIRED JSON OUTPUT STRUCTURE: | |
| { | |
| "property_info": { | |
| "address": "EXTRACT FROM Offering_Memorandum.pdf", | |
| "property_type": "EXTRACT FROM Offering_Memorandum.pdf", | |
| "units": EXTRACT_NUMBER_FROM_Offering_Memorandum.pdf, | |
| "gross_sf": null, | |
| "rentable_sf": null, | |
| "retail_sf": null | |
| }, | |
| "acquisition": { | |
| "land_value": null, | |
| "price": null, | |
| "closing_costs": null | |
| }, | |
| "construction": { | |
| "construction_cost_per_gsf": null, | |
| "construction_months": null | |
| }, | |
| "soft_costs": { | |
| "architecture_and_interior_cost": null, | |
| "structural_engineering_cost": null, | |
| "mep_engineering_cost": null, | |
| "civil_engineering_cost": null, | |
| "controlled_inspections_cost": null, | |
| "surveying_cost": null, | |
| "utilities_connection_cost": null, | |
| "advertising_and_marketing_cost": null, | |
| "accounting_cost": null, | |
| "monitoring_cost": null, | |
| "ff_and_e_cost": null, | |
| "environmental_consultant_fee": null, | |
| "miscellaneous_consultants_fee": null, | |
| "general_legal_cost": null, | |
| "real_estate_taxes_during_construction": null, | |
| "miscellaneous_admin_cost": null, | |
| "ibr_cost": null, | |
| "project_team_cost": null, | |
| "pem_fees": null, | |
| "bank_fees": null | |
| }, | |
| "financing": { | |
| "ltc_ratio": null, | |
| "financing_percentage": null, | |
| "interest_rate_basis_points": null, | |
| "financing_cost": null, | |
| "interest_reserve": null | |
| }, | |
| "operating_expenses": { | |
| "payroll": EXTRACT_FROM_Operating_Expenses_Summary.pdf, | |
| "repairs_and_maintenance": EXTRACT_FROM_Operating_Expenses_Summary.pdf, | |
| "utilities": EXTRACT_FROM_Operating_Expenses_Summary.pdf, | |
| "administrative": EXTRACT_FROM_Operating_Expenses_Summary.pdf_OR_null, | |
| "professional_fees": EXTRACT_FROM_Operating_Expenses_Summary.pdf_OR_null, | |
| "insurance": EXTRACT_FROM_Operating_Expenses_Summary.pdf, | |
| "property_taxes": EXTRACT_FROM_Operating_Expenses_Summary.pdf, | |
| "management_fee_percentage": null | |
| }, | |
| "revenue": { | |
| "free_market_rent_psf": null, | |
| "affordable_rent_psf": null, | |
| "other_income_per_unit": null, | |
| "vacancy_rate": null, | |
| "retail_rent_psf": null, | |
| "parking_income": null | |
| }, | |
| "sales_comps": { | |
| "average_price_per_sf": CALCULATE_AVERAGE_FROM_Sales_Comps.pdf, | |
| "comp_count": COUNT_FROM_Sales_Comps.pdf | |
| }, | |
| "rent_comps": { | |
| "average_rent": CALCULATE_AVERAGE_FROM_Rent_Comps.pdf_IF_EXISTS, | |
| "comp_count": COUNT_FROM_Rent_Comps.pdf_IF_EXISTS | |
| }, | |
| "market_data": { | |
| "vacancy_rate": EXTRACT_FROM_Market_Report.pdf, | |
| "rent_growth_yoy": EXTRACT_FROM_Market_Report.pdf, | |
| "median_hh_income": EXTRACT_FROM_Demographics_Overview.pdf, | |
| "population_3mi": EXTRACT_FROM_Demographics_Overview.pdf, | |
| "transit_score": EXTRACT_FROM_Demographics_Overview.pdf | |
| }, | |
| "projections": { | |
| "lease_up_months": null, | |
| "stabilization_months": null, | |
| "revenue_inflation_rate": null, | |
| "expense_inflation_rate": null, | |
| "hold_period_months": null, | |
| "exit_cap_rate_decimal": null, | |
| "sale_cost_percentage": null | |
| }, | |
| "equity_structure": { | |
| "gp_pref_rate": null, | |
| "lp_pref_rate": null, | |
| "promote_percentage": null | |
| } | |
| } | |
| EXAMPLES OF CORRECT EXTRACTION: | |
| Example 1 - From your Offering_Memorandum.pdf: | |
| "Address: 455 Atlantic Ave, Brooklyn, NY" | |
| → "address": "455 Atlantic Ave, Brooklyn, NY" | |
| "Property Type: Retail" | |
| → "property_type": "Retail" | |
| "Units: 7" | |
| → "units": 7 | |
| Example 2 - From your Operating_Expenses_Summary.pdf: | |
| "Real Estate Taxes $91940.2" | |
| → "property_taxes": 91940.2 | |
| "Insurance $16778.94" | |
| → "insurance": 16778.94 | |
| "Payroll $44948.21" | |
| → "payroll": 44948.21 | |
| Example 3 - From your Sales_Comps.pdf: | |
| "Price/SF" column shows: $880, $919, $673, $894 | |
| → "average_price_per_sf": 841.5 (average of these 4 values) | |
| → "comp_count": 4 | |
| Example 4 - From your Market_Report.pdf: | |
| "Vacancy Rate: 5.71%" | |
| → "vacancy_rate": 0.0571 | |
| "Rent Growth (YoY): 4.18%" | |
| → "rent_growth_yoy": 0.0418 | |
| CRITICAL RULES: | |
| 1. Use EXACT numbers from the PDFs - don't round or modify | |
| 2. Convert percentages to decimals (5.71% → 0.0571) | |
| 3. Remove dollar signs and commas from numbers ($91,940.2 → 91940.2) | |
| 4. If a field is not in ANY PDF, use null | |
| 5. Double-check the document name before extracting - make sure you're looking at the right PDF | |
| Return ONLY valid JSON with no explanations, comments, or markdown formatting.""" | |
| prompt += """ | |
| NOTE: Documents may be in PDF or XLSX format. For XLSX files, data is extracted sheet-by-sheet. | |
| Look for numerical data in tables, columns, and labeled cells. | |
| PDF AND XLSX CONTENTS: | |
| """ | |
| return prompt | |
| def extract_structured_data(self) -> Dict[str, Any]: | |
| """Use Gemini to extract structured data from PDFs""" | |
| print("\nProcessing with Gemini API...") | |
| # NEW: Try simple extraction first | |
| fallback_address = self.extract_address_fallback(self.extracted_data) | |
| prompt = self.create_gemini_prompt(self.extracted_data) | |
| try: | |
| response = self.model.generate_content(prompt) | |
| response_text = response.text.strip() | |
| # Clean JSON if wrapped in markdown | |
| if "```json" in response_text: | |
| response_text = response_text.split("```json")[1].split("```")[0].strip() | |
| elif "```" in response_text: | |
| response_text = response_text.split("```")[1].split("```")[0].strip() | |
| data = json.loads(response_text) | |
| # NEW: Override with fallback if Gemini failed | |
| if fallback_address and (not data.get('property_info', {}).get('address') or | |
| data['property_info']['address'] == 'adress'): | |
| data['property_info']['address'] = fallback_address | |
| print(f" ✓ Used fallback address: {fallback_address}") | |
| print("✓ Successfully extracted structured data") | |
| return data | |
| except Exception as e: | |
| print(f"Error with Gemini API: {e}") | |
| data = self.get_default_data_structure() | |
| # Use fallback even in error case | |
| if fallback_address: | |
| data['property_info']['address'] = fallback_address | |
| return data | |
| def post_process_extracted_data(self, data: Dict[str, Any]) -> Dict[str, Any]: | |
| """Fill in missing values with intelligent estimates""" | |
| # Get units | |
| units = data.get('property_info', {}).get('units', 32) | |
| # Estimate SF if missing | |
| if not data['property_info'].get('gross_sf'): | |
| data['property_info']['gross_sf'] = units * 1000 | |
| if not data['property_info'].get('rentable_sf'): | |
| data['property_info']['rentable_sf'] = int(data['property_info']['gross_sf'] * 0.85) | |
| # Set retail_sf to 0 if None (most residential projects don't have retail) | |
| if data['property_info'].get('retail_sf') is None: | |
| data['property_info']['retail_sf'] = 0 | |
| # Get gross_sf for calculations | |
| gross_sf = data['property_info']['gross_sf'] | |
| # Set default construction cost if missing | |
| if not data['construction'].get('construction_cost_per_gsf'): | |
| data['construction']['construction_cost_per_gsf'] = 338 | |
| if not data['construction'].get('construction_months'): | |
| data['construction']['construction_months'] = 18 | |
| # Estimate land value from sales comps if available | |
| if not data['acquisition'].get('land_value'): | |
| sales_comps = data.get('sales_comps', {}) | |
| avg_psf = sales_comps.get('average_price_per_sf') | |
| if avg_psf: | |
| data['acquisition']['land_value'] = avg_psf * gross_sf | |
| else: | |
| # Use default based on typical Manhattan pricing | |
| data['acquisition']['land_value'] = 6000000 | |
| if not data['acquisition'].get('price'): | |
| data['acquisition']['price'] = data['acquisition']['land_value'] | |
| if not data['acquisition'].get('closing_costs'): | |
| data['acquisition']['closing_costs'] = 150000 | |
| # Estimate soft costs as percentages if null | |
| total_hard_cost = data['construction']['construction_cost_per_gsf'] * gross_sf | |
| soft_cost_estimate = total_hard_cost * 0.15 # 15% of hard costs | |
| soft_costs = data.get('soft_costs', {}) | |
| default_soft_cost_values = { | |
| 'architecture_and_interior_cost': soft_cost_estimate * 0.15, | |
| 'structural_engineering_cost': soft_cost_estimate * 0.08, | |
| 'mep_engineering_cost': soft_cost_estimate * 0.10, | |
| 'civil_engineering_cost': soft_cost_estimate * 0.05, | |
| 'controlled_inspections_cost': soft_cost_estimate * 0.03, | |
| 'surveying_cost': soft_cost_estimate * 0.02, | |
| 'utilities_connection_cost': soft_cost_estimate * 0.05, | |
| 'advertising_and_marketing_cost': soft_cost_estimate * 0.06, | |
| 'accounting_cost': soft_cost_estimate * 0.03, | |
| 'monitoring_cost': soft_cost_estimate * 0.02, | |
| 'ff_and_e_cost': soft_cost_estimate * 0.10, | |
| 'environmental_consultant_fee': soft_cost_estimate * 0.02, | |
| 'miscellaneous_consultants_fee': soft_cost_estimate * 0.03, | |
| 'general_legal_cost': soft_cost_estimate * 0.06, | |
| 'real_estate_taxes_during_construction': soft_cost_estimate * 0.10, | |
| 'miscellaneous_admin_cost': soft_cost_estimate * 0.04, | |
| 'ibr_cost': soft_cost_estimate * 0.03, | |
| 'project_team_cost': soft_cost_estimate * 0.15, | |
| 'pem_fees': soft_cost_estimate * 0.08, | |
| 'bank_fees': soft_cost_estimate * 0.05 | |
| } | |
| for key, default_value in default_soft_cost_values.items(): | |
| if soft_costs.get(key) is None: | |
| soft_costs[key] = default_value | |
| # Set financing defaults if missing | |
| financing = data.get('financing', {}) | |
| if not financing.get('ltc_ratio'): | |
| financing['ltc_ratio'] = 0.75 | |
| if not financing.get('financing_percentage'): | |
| financing['financing_percentage'] = 0.03 | |
| if not financing.get('interest_rate_basis_points'): | |
| financing['interest_rate_basis_points'] = 350 | |
| if not financing.get('financing_cost'): | |
| financing['financing_cost'] = 200000 | |
| if not financing.get('interest_reserve'): | |
| financing['interest_reserve'] = 500000 | |
| # Set revenue defaults if missing | |
| revenue = data.get('revenue', {}) | |
| if not revenue.get('free_market_rent_psf'): | |
| revenue['free_market_rent_psf'] = 60 | |
| if not revenue.get('affordable_rent_psf'): | |
| revenue['affordable_rent_psf'] = 35 | |
| if not revenue.get('other_income_per_unit'): | |
| revenue['other_income_per_unit'] = 100 | |
| if not revenue.get('vacancy_rate'): | |
| revenue['vacancy_rate'] = 0.05 | |
| if not revenue.get('retail_rent_psf'): | |
| revenue['retail_rent_psf'] = 45 | |
| if not revenue.get('parking_income'): | |
| revenue['parking_income'] = 50000 | |
| # Ensure operating expenses have defaults | |
| op_expenses = data.get('operating_expenses', {}) | |
| if not op_expenses.get('payroll'): | |
| op_expenses['payroll'] = 31136.07 | |
| if not op_expenses.get('repairs_and_maintenance'): | |
| op_expenses['repairs_and_maintenance'] = 44418.61 | |
| if not op_expenses.get('utilities'): | |
| op_expenses['utilities'] = 12535.90 | |
| if not op_expenses.get('administrative'): | |
| op_expenses['administrative'] = 0 | |
| if not op_expenses.get('professional_fees'): | |
| op_expenses['professional_fees'] = 18789.84 | |
| if not op_expenses.get('insurance'): | |
| op_expenses['insurance'] = 9341.33 | |
| if not op_expenses.get('property_taxes'): | |
| op_expenses['property_taxes'] = 118832.22 | |
| if not op_expenses.get('management_fee_percentage'): | |
| op_expenses['management_fee_percentage'] = 0.03 | |
| # Ensure projections have defaults | |
| projections = data.get('projections', {}) | |
| if not projections.get('lease_up_months'): | |
| projections['lease_up_months'] = 12 | |
| if not projections.get('stabilization_months'): | |
| projections['stabilization_months'] = 6 | |
| if not projections.get('revenue_inflation_rate'): | |
| projections['revenue_inflation_rate'] = 0.03 | |
| if not projections.get('expense_inflation_rate'): | |
| projections['expense_inflation_rate'] = 0.025 | |
| if not projections.get('hold_period_months'): | |
| projections['hold_period_months'] = 60 | |
| if not projections.get('exit_cap_rate_decimal'): | |
| projections['exit_cap_rate_decimal'] = 0.045 | |
| if not projections.get('sale_cost_percentage'): | |
| projections['sale_cost_percentage'] = 0.02 | |
| # Ensure equity structure has defaults | |
| equity = data.get('equity_structure', {}) | |
| if not equity.get('gp_pref_rate'): | |
| equity['gp_pref_rate'] = 0.08 | |
| if not equity.get('lp_pref_rate'): | |
| equity['lp_pref_rate'] = 0.08 | |
| if not equity.get('promote_percentage'): | |
| equity['promote_percentage'] = 0.20 | |
| return data | |
| def get_default_data_structure(self) -> Dict[str, Any]: | |
| """Return default data structure with known values from PDFs""" | |
| # Try to get basic info from extracted text | |
| units = 32 # Default from your PDFs | |
| # Smart estimation | |
| gross_sf = units * 1000 # Typical 1000 SF per unit | |
| rentable_sf = int(gross_sf * 0.85) # 85% efficiency | |
| return { | |
| "property_info": { | |
| "address": "adress", | |
| "units": units, | |
| "gross_sf": gross_sf, | |
| "rentable_sf": rentable_sf, | |
| "retail_sf": 0 # No retail in this project | |
| }, | |
| "acquisition": { | |
| "land_value": None, # Will be estimated from comps | |
| "price": None, | |
| "closing_costs": 150000 | |
| }, | |
| "construction": { | |
| "construction_cost_per_gsf": 338, | |
| "construction_months": 18 | |
| }, | |
| "soft_costs": { | |
| "architecture_and_interior_cost": None, | |
| "structural_engineering_cost": None, | |
| "mep_engineering_cost": None, | |
| "civil_engineering_cost": None, | |
| "controlled_inspections_cost": None, | |
| "surveying_cost": None, | |
| "utilities_connection_cost": None, | |
| "advertising_and_marketing_cost": None, | |
| "accounting_cost": None, | |
| "monitoring_cost": None, | |
| "ff_and_e_cost": None, | |
| "environmental_consultant_fee": None, | |
| "miscellaneous_consultants_fee": None, | |
| "general_legal_cost": None, | |
| "real_estate_taxes_during_construction": None, | |
| "miscellaneous_admin_cost": None, | |
| "ibr_cost": None, | |
| "project_team_cost": None, | |
| "pem_fees": None, | |
| "bank_fees": None | |
| }, | |
| "financing": { | |
| "ltc_ratio": 0.75, | |
| "financing_percentage": 0.03, | |
| "interest_rate_basis_points": 350, | |
| "financing_cost": None, | |
| "interest_reserve": None | |
| }, | |
| "operating_expenses": { | |
| "payroll": 31136.07, # From PDF | |
| "repairs_and_maintenance": 44418.61, | |
| "utilities": 12535.90, | |
| "administrative": 0, | |
| "professional_fees": 18789.84, | |
| "insurance": 9341.33, | |
| "property_taxes": 118832.22, | |
| "management_fee_percentage": 0.03 | |
| }, | |
| "revenue": { | |
| "free_market_rent_psf": 60, | |
| "affordable_rent_psf": 35, | |
| "other_income_per_unit": 100, | |
| "vacancy_rate": 0.05, | |
| "retail_rent_psf": 45, | |
| "parking_income": 50000 | |
| }, | |
| "projections": { | |
| "lease_up_months": 12, | |
| "stabilization_months": 6, | |
| "revenue_inflation_rate": 0.03, | |
| "expense_inflation_rate": 0.025, | |
| "hold_period_months": 60, | |
| "exit_cap_rate_decimal": 0.045, | |
| "sale_cost_percentage": 0.02 | |
| }, | |
| "equity_structure": { | |
| "gp_pref_rate": 0.08, | |
| "lp_pref_rate": 0.08, | |
| "promote_percentage": 0.20 | |
| } | |
| } | |
| def calculate_all_formulas(self, data: Dict[str, Any]) -> Dict[str, float]: | |
| """Calculate all formulas in correct dependency order""" | |
| results = {} | |
| self.structured_data = data | |
| # Flatten data for easier access | |
| d = self.flatten_dict(data) | |
| # Helper function to get value | |
| def get(key, default=0): | |
| return d.get(key, default) | |
| # BASIC PROPERTY METRICS | |
| results['UNITS'] = get('property_info.units') | |
| results['GROSS_SF'] = get('property_info.gross_sf') | |
| results['RENTABLE_SF'] = get('property_info.rentable_sf') | |
| results['RETAIL_SF'] = get('property_info.retail_sf') | |
| results['BUILDING_EFFICIENCY'] = self.safe_divide(results['RENTABLE_SF'], results['GROSS_SF']) | |
| # ACQUISITION COSTS | |
| results['LAND_VALUE'] = get('acquisition.land_value') | |
| results['PRICE'] = get('acquisition.price') | |
| results['CLOSING_COSTS'] = get('acquisition.closing_costs') | |
| results['ACQUISITION_FEE'] = results['LAND_VALUE'] * 0.02 | |
| results['TOTAL_ACQUISITION_COST'] = results['LAND_VALUE'] + results['CLOSING_COSTS'] + results['ACQUISITION_FEE'] | |
| # Per unit/SF metrics for acquisition | |
| results['LAND_VALUE_PER_GSF'] = self.safe_divide(results['LAND_VALUE'], results['GROSS_SF']) | |
| results['LAND_VALUE_PER_RSF'] = self.safe_divide(results['LAND_VALUE'], results['RENTABLE_SF']) | |
| results['LAND_VALUE_PER_UNIT'] = self.safe_divide(results['LAND_VALUE'], results['UNITS']) | |
| results['TOTAL_ACQUISITION_COST_PER_GSF'] = self.safe_divide(results['TOTAL_ACQUISITION_COST'], results['GROSS_SF']) | |
| results['TOTAL_ACQUISITION_COST_PER_RSF'] = self.safe_divide(results['TOTAL_ACQUISITION_COST'], results['RENTABLE_SF']) | |
| results['TOTAL_ACQUISITION_COST_PER_UNIT'] = self.safe_divide(results['TOTAL_ACQUISITION_COST'], results['UNITS']) | |
| # CONSTRUCTION COSTS | |
| results['CONSTRUCTION_COST_PER_GSF'] = get('construction.construction_cost_per_gsf') | |
| results['CONSTRUCTION_MONTHS'] = get('construction.construction_months') | |
| results['TOTAL_CONSTRUCTION_GMP'] = results['CONSTRUCTION_COST_PER_GSF'] * results['GROSS_SF'] | |
| results['CONSTRUCTION_GMP_PER_GSF'] = self.safe_divide(results['TOTAL_CONSTRUCTION_GMP'], results['GROSS_SF']) | |
| results['CONSTRUCTION_GMP_PER_RSF'] = self.safe_divide(results['TOTAL_CONSTRUCTION_GMP'], results['RENTABLE_SF']) | |
| results['CONSTRUCTION_GMP_PER_UNIT'] = self.safe_divide(results['TOTAL_CONSTRUCTION_GMP'], results['UNITS']) | |
| # SOFT COSTS (individual items) | |
| soft_cost_items = [ | |
| 'architecture_and_interior_cost', 'structural_engineering_cost', 'mep_engineering_cost', | |
| 'civil_engineering_cost', 'controlled_inspections_cost', 'surveying_cost', | |
| 'utilities_connection_cost', 'advertising_and_marketing_cost', 'accounting_cost', | |
| 'monitoring_cost', 'ff_and_e_cost', 'environmental_consultant_fee', | |
| 'miscellaneous_consultants_fee', 'general_legal_cost', 'real_estate_taxes_during_construction', | |
| 'miscellaneous_admin_cost', 'ibr_cost', 'project_team_cost', 'pem_fees', 'bank_fees' | |
| ] | |
| for item in soft_cost_items: | |
| key = item.upper() | |
| results[key] = get(f'soft_costs.{item}') | |
| # REVENUE SETUP (needed for some soft costs) | |
| results['FREE_MARKET_RENT_PSF'] = get('revenue.free_market_rent_psf') | |
| results['AFFORDABLE_RENT_PSF'] = get('revenue.affordable_rent_psf') | |
| results['OTHER_INCOME_PER_UNIT'] = get('revenue.other_income_per_unit') | |
| results['VACANCY_RATE'] = get('revenue.vacancy_rate') | |
| results['RETAIL_RENT_PSF'] = get('revenue.retail_rent_psf') | |
| results['PARKING_INCOME'] = get('revenue.parking_income') | |
| # Calculate retail revenue (needed for soft costs) | |
| results['RETAIL_REVENUE'] = results['RETAIL_RENT_PSF'] * results['RETAIL_SF'] | |
| # HPD & IH COST | |
| results['HPD_AND_IH_COST'] = (3500 * results['UNITS'] * 0.75) + (5000 * results['UNITS'] * 0.25) | |
| # RETAIL TI & LC COST | |
| results['RETAIL_TI_AND_LC_COST'] = (results['RETAIL_REVENUE'] * 0.3) + (50 * results['RETAIL_SF']) | |
| # TOTAL SOFT COSTS | |
| soft_cost_sum = sum([results[item.upper()] for item in soft_cost_items]) | |
| results['TOTAL_SOFT_COST'] = soft_cost_sum + results['HPD_AND_IH_COST'] + results['RETAIL_TI_AND_LC_COST'] | |
| results['TOTAL_SOFT_COST_PER_GSF'] = self.safe_divide(results['TOTAL_SOFT_COST'],results['GROSS_SF']) | |
| # OPERATING EXPENSES (for reserves calculation) | |
| results['PAYROLL'] = get('operating_expenses.payroll') | |
| results['REPAIRS_AND_MAINTENANCE'] = get('operating_expenses.repairs_and_maintenance') | |
| results['UTILITIES'] = get('operating_expenses.utilities') | |
| results['ADMINISTRATIVE'] = get('operating_expenses.administrative') | |
| results['PROFESSIONAL_FEES'] = get('operating_expenses.professional_fees') | |
| results['INSURANCE'] = get('operating_expenses.insurance') | |
| results['PROPERTY_TAXES'] = get('operating_expenses.property_taxes') | |
| results['MANAGEMENT_FEE_PERCENTAGE'] = get('operating_expenses.management_fee_percentage') | |
| results['TOTAL_OPERATING_EXPENSES'] = (results['PAYROLL'] + results['REPAIRS_AND_MAINTENANCE'] + | |
| results['UTILITIES'] + results['ADMINISTRATIVE'] + | |
| results['PROFESSIONAL_FEES'] + results['INSURANCE'] + | |
| results['PROPERTY_TAXES']) | |
| # CONTINGENCY & RESERVES | |
| results['CONTINGENCY_COST'] = (results['TOTAL_CONSTRUCTION_GMP'] + results['TOTAL_SOFT_COST']) * 0.05 | |
| results['DEVELOPMENT_FEE'] = (results['TOTAL_CONSTRUCTION_GMP'] + results['TOTAL_SOFT_COST']) * 0.04 | |
| results['OPERATING_RESERVE'] = results['TOTAL_OPERATING_EXPENSES'] * 0.2 | |
| results['FINANCING_COST'] = get('financing.financing_cost') | |
| results['INTEREST_RESERVE'] = get('financing.interest_reserve') | |
| # TOTAL PROJECT COST (before financing) | |
| results['TOTAL_FINANCING_CONTINGENCY_AND_RESERVES'] = ( | |
| results['TOTAL_SOFT_COST'] + | |
| results['TOTAL_CONSTRUCTION_GMP'] + | |
| results['TOTAL_ACQUISITION_COST'] + | |
| results['CONTINGENCY_COST'] + | |
| results['DEVELOPMENT_FEE'] + | |
| results['FINANCING_COST'] + | |
| results['INTEREST_RESERVE'] + | |
| results['OPERATING_RESERVE'] | |
| ) | |
| results['TOTAL_PROJECT_COST_PER_GSF'] = self.safe_divide(results['TOTAL_FINANCING_CONTINGENCY_AND_RESERVES'], results['GROSS_SF']) | |
| results['TOTAL_PROJECT_COST_PER_RSF'] = self.safe_divide(results['TOTAL_FINANCING_CONTINGENCY_AND_RESERVES'], results['RENTABLE_SF']) | |
| results['TOTAL_PROJECT_COST_PER_UNIT'] = self.safe_divide(results['TOTAL_FINANCING_CONTINGENCY_AND_RESERVES'], results['UNITS']) | |
| # FINANCING CALCULATIONS | |
| results['LTC_RATIO'] = get('financing.ltc_ratio') | |
| results['FINANCING_PERCENTAGE'] = get('financing.financing_percentage') | |
| results['INTEREST_RATE_BASIS_POINTS'] = get('financing.interest_rate_basis_points') | |
| results['PRE_LTC_BUDGET'] = (results['TOTAL_SOFT_COST'] + results['CONTINGENCY_COST'] + | |
| results['DEVELOPMENT_FEE'] + results['OPERATING_RESERVE'] + | |
| results['TOTAL_CONSTRUCTION_GMP'] + results['TOTAL_ACQUISITION_COST']) | |
| results['LOAN_AMOUNT'] = results['LTC_RATIO'] * results['PRE_LTC_BUDGET'] | |
| results['FINANCING_AMOUNT'] = results['FINANCING_PERCENTAGE'] * results['LOAN_AMOUNT'] | |
| results['INTEREST_RATE_DECIMAL'] = (results['INTEREST_RATE_BASIS_POINTS'] + 430) / 10000 | |
| results['CONSTRUCTION_INTEREST'] = results['LOAN_AMOUNT'] * 0.7 * (results['INTEREST_RATE_DECIMAL'] / 12) * results['CONSTRUCTION_MONTHS'] | |
| # DEBT & EQUITY | |
| results['TOTAL_DEBT'] = results['CONSTRUCTION_INTEREST'] + results['LOAN_AMOUNT'] + results['FINANCING_AMOUNT'] | |
| results['TOTAL_EQUITY'] = results['TOTAL_FINANCING_CONTINGENCY_AND_RESERVES'] - results['TOTAL_DEBT'] | |
| results['DEBT_PERCENTAGE'] = results['TOTAL_DEBT'] / results['TOTAL_FINANCING_CONTINGENCY_AND_RESERVES'] if results['TOTAL_FINANCING_CONTINGENCY_AND_RESERVES'] > 0 else 0 | |
| results['EQUITY_PERCENTAGE'] = results['TOTAL_EQUITY'] / results['TOTAL_FINANCING_CONTINGENCY_AND_RESERVES'] if results['TOTAL_FINANCING_CONTINGENCY_AND_RESERVES'] > 0 else 0 | |
| results['TOTAL_CAPITAL_STACK'] = results['TOTAL_DEBT'] + results['TOTAL_EQUITY'] | |
| results['DEBT_PER_GSF'] = self.safe_divide(results['TOTAL_DEBT'],results['GROSS_SF']) | |
| results['EQUITY_PER_GSF'] = self.safe_divide(results['TOTAL_EQUITY'],results['GROSS_SF']) | |
| results['DEBT_PER_UNIT'] = self.safe_divide(results['TOTAL_DEBT'], results['UNITS']) | |
| results['EQUITY_PER_UNIT'] = self.safe_divide(results['TOTAL_EQUITY'], results['UNITS']) | |
| # OPERATING EXPENSE METRICS | |
| results['PAYROLL_PER_UNIT'] = self.safe_divide(results['PAYROLL'], results['UNITS']) | |
| results['REPAIRS_AND_MAINTENANCE_PER_UNIT'] = self.safe_divide(results['REPAIRS_AND_MAINTENANCE'], results['UNITS']) | |
| results['UTILITIES_PER_UNIT'] = self.safe_divide(results['UTILITIES'], results['UNITS']) | |
| results['ADMIN_AND_PROFESSIONAL_PER_UNIT'] = self.safe_divide((results['ADMINISTRATIVE'] + results['PROFESSIONAL_FEES']), results['UNITS']) | |
| results['INSURANCE_PER_UNIT'] = self.safe_divide(results['INSURANCE'], results['UNITS']) | |
| results['OPERATING_EXPENSES_PER_UNIT'] = self.safe_divide(results['TOTAL_OPERATING_EXPENSES'], results['UNITS']) | |
| results['OPERATING_EXPENSES_PER_GSF'] = self.safe_divide(results['TOTAL_OPERATING_EXPENSES'],results['GROSS_SF']) | |
| # REVENUE CALCULATIONS | |
| results['LEASE_UP_MONTHS'] = get('projections.lease_up_months') | |
| results['STABILIZATION_MONTHS'] = get('projections.stabilization_months') | |
| results['REVENUE_INFLATION_RATE'] = get('projections.revenue_inflation_rate') | |
| results['EXPENSE_INFLATION_RATE'] = get('projections.expense_inflation_rate') | |
| results['TRENDING_TERM'] = results['LEASE_UP_MONTHS'] + results['STABILIZATION_MONTHS'] | |
| results['TERM_REVENUE_INFLATION'] = (1 + results['REVENUE_INFLATION_RATE']) ** (results['TRENDING_TERM'] / 12) | |
| results['TERM_EXPENSE_INFLATION'] = (1 + results['EXPENSE_INFLATION_RATE']) ** (results['TRENDING_TERM'] / 12) | |
| results['GROSS_POTENTIAL_FREE_MARKET_RENT'] = results['FREE_MARKET_RENT_PSF'] * 0.75 * results['RENTABLE_SF'] | |
| results['GROSS_POTENTIAL_AFFORDABLE_RENT'] = results['AFFORDABLE_RENT_PSF'] * 0.25 * results['RENTABLE_SF'] | |
| results['OTHER_INCOME'] = results['OTHER_INCOME_PER_UNIT'] * results['UNITS'] * 12 * 0.75 | |
| results['VACANCY_LOSS'] = results['VACANCY_RATE'] * (results['OTHER_INCOME'] + results['GROSS_POTENTIAL_FREE_MARKET_RENT'] + results['GROSS_POTENTIAL_AFFORDABLE_RENT']) | |
| results['EFFECTIVE_GROSS_INCOME'] = results['GROSS_POTENTIAL_FREE_MARKET_RENT'] - results['VACANCY_LOSS'] + results['OTHER_INCOME'] + results['GROSS_POTENTIAL_AFFORDABLE_RENT'] | |
| results['MANAGEMENT_FEE'] = results['MANAGEMENT_FEE_PERCENTAGE'] * results['EFFECTIVE_GROSS_INCOME'] | |
| results['REAL_ESTATE_TAXES'] = results['GROSS_SF'] * 30 * 0.1 | |
| results['TOTAL_EXPENSES'] = results['PAYROLL'] + results['REPAIRS_AND_MAINTENANCE'] + results['UTILITIES'] + results['REAL_ESTATE_TAXES'] + results['MANAGEMENT_FEE'] | |
| # NOI & RETURNS | |
| results['NET_OPERATING_INCOME'] = results['EFFECTIVE_GROSS_INCOME'] - results['TOTAL_EXPENSES'] + results['PARKING_INCOME'] + results['RETAIL_REVENUE'] | |
| results['NOI_PER_UNIT'] = self.safe_divide(results['NET_OPERATING_INCOME'], results['UNITS']) | |
| results['NOI_PER_GSF'] = self.safe_divide(results['NET_OPERATING_INCOME'],results['GROSS_SF']) | |
| results['CAP_RATE'] = (results['NET_OPERATING_INCOME'] / results['PRICE']) * 100 if results['PRICE'] > 0 else 0 | |
| results['STABILIZED_YIELD_ON_COST'] = (((results['EFFECTIVE_GROSS_INCOME'] + results['RETAIL_REVENUE'] - results['GROSS_POTENTIAL_AFFORDABLE_RENT']) * results['TERM_REVENUE_INFLATION']) - (results['TOTAL_EXPENSES'] * results['TERM_EXPENSE_INFLATION'])) + results['GROSS_POTENTIAL_AFFORDABLE_RENT'] | |
| results['YIELD_ON_COST_PERCENTAGE'] = results['NET_OPERATING_INCOME'] / results['TOTAL_FINANCING_CONTINGENCY_AND_RESERVES'] if results['TOTAL_FINANCING_CONTINGENCY_AND_RESERVES'] > 0 else 0 | |
| results['STABILIZED_YIELD_ON_COST_PERCENTAGE'] = results['STABILIZED_YIELD_ON_COST'] / results['TOTAL_FINANCING_CONTINGENCY_AND_RESERVES'] if results['TOTAL_FINANCING_CONTINGENCY_AND_RESERVES'] > 0 else 0 | |
| results['ANNUAL_DEBT_SERVICE'] = results['LOAN_AMOUNT'] * results['INTEREST_RATE_DECIMAL'] | |
| results['CASH_ON_CASH_RETURN'] = ((results['NET_OPERATING_INCOME'] - results['ANNUAL_DEBT_SERVICE']) / results['TOTAL_EQUITY']) * 100 if results['TOTAL_EQUITY'] > 0 else 0 | |
| results['DEBT_SERVICE_COVERAGE_RATIO'] = results['NET_OPERATING_INCOME'] / results['ANNUAL_DEBT_SERVICE'] if results['ANNUAL_DEBT_SERVICE'] > 0 else 0 | |
| # EXIT & EQUITY WATERFALL | |
| results['EXIT_CAP_RATE_DECIMAL'] = get('projections.exit_cap_rate_decimal') | |
| results['SALE_COST_PERCENTAGE'] = get('projections.sale_cost_percentage') | |
| results['HOLD_PERIOD_MONTHS'] = get('projections.hold_period_months') | |
| results['PROPERTY_VALUE_ON_SALE'] = (results['STABILIZED_YIELD_ON_COST'] / results['EXIT_CAP_RATE_DECIMAL']) + (results['STABILIZED_YIELD_ON_COST'] * 0.25) if results['EXIT_CAP_RATE_DECIMAL'] > 0 else 0 | |
| results['SALE_COST'] = results['SALE_COST_PERCENTAGE'] * results['PROPERTY_VALUE_ON_SALE'] | |
| results['NET_SALE_PROCEEDS'] = results['PROPERTY_VALUE_ON_SALE'] - results['SALE_COST'] | |
| results['CASH_REMAINING_AFTER_LOAN_PAYBACK'] = results['NET_SALE_PROCEEDS'] - results['TOTAL_DEBT'] | |
| results['GP_PREF_RATE'] = get('equity_structure.gp_pref_rate') | |
| results['LP_PREF_RATE'] = get('equity_structure.lp_pref_rate') | |
| results['PROMOTE_PERCENTAGE'] = get('equity_structure.promote_percentage') | |
| results['GP_INVESTMENT'] = results['TOTAL_EQUITY'] * 0.2 | |
| results['LP_INVESTMENT'] = results['TOTAL_EQUITY'] - results['GP_INVESTMENT'] | |
| results['GP_PREFERRED_RETURN_WITH_PRINCIPAL'] = (1 + results['GP_PREF_RATE'] / 12) ** results['HOLD_PERIOD_MONTHS'] * results['GP_INVESTMENT'] | |
| results['LP_PREFERRED_RETURN_WITH_PRINCIPAL'] = (1 + results['LP_PREF_RATE'] / 12) ** results['HOLD_PERIOD_MONTHS'] * results['LP_INVESTMENT'] | |
| results['CASH_REMAINING_AFTER_PREFERRED'] = results['CASH_REMAINING_AFTER_LOAN_PAYBACK'] - results['LP_PREFERRED_RETURN_WITH_PRINCIPAL'] - results['GP_PREFERRED_RETURN_WITH_PRINCIPAL'] | |
| results['PROMOTE_ON_JOINT_VENTURE'] = results['PROMOTE_PERCENTAGE'] * results['CASH_REMAINING_AFTER_PREFERRED'] | |
| results['CASH_TO_LP'] = (results['CASH_REMAINING_AFTER_PREFERRED'] - results['PROMOTE_ON_JOINT_VENTURE']) * (results['LP_INVESTMENT'] / (results['LP_INVESTMENT'] + results['GP_INVESTMENT'])) if (results['LP_INVESTMENT'] + results['GP_INVESTMENT']) > 0 else 0 | |
| results['NET_TO_LP_INVESTOR'] = results['CASH_TO_LP'] + results['LP_PREFERRED_RETURN_WITH_PRINCIPAL'] | |
| results['LP_MULTIPLE'] = results['NET_TO_LP_INVESTOR'] / results['LP_INVESTMENT'] if results['LP_INVESTMENT'] > 0 else 0 | |
| # results['IRR_TO_LP'] = ((results['NET_TO_LP_INVESTOR'] / results['LP_INVESTMENT']) ** (12 / results['HOLD_PERIOD_MONTHS']) - 1) * 100 if results['LP_INVESTMENT'] > 0 and results['HOLD_PERIOD_MONTHS'] > 0 else 0 | |
| # IRR calculation with complex number handling | |
| if results['LP_INVESTMENT'] > 0 and results['HOLD_PERIOD_MONTHS'] > 0: | |
| irr_base = results['NET_TO_LP_INVESTOR'] / results['LP_INVESTMENT'] | |
| if irr_base > 0: | |
| results['IRR_TO_LP'] = ((irr_base) ** (12 / results['HOLD_PERIOD_MONTHS']) - 1) * 100 | |
| else: | |
| results['IRR_TO_LP'] = -100 # Total loss | |
| else: | |
| results['IRR_TO_LP'] = 0 | |
| # BLENDED RENT CALCULATIONS | |
| results['BLENDED_RENT_PER_RSF'] = (results['FREE_MARKET_RENT_PSF'] * 0.75) + (results['AFFORDABLE_RENT_PSF'] * 0.25) | |
| results['TOTAL_FREE_MARKET_RENT'] = results['FREE_MARKET_RENT_PSF'] * 425 / 12 | |
| results['TOTAL_BLENDED_RENT'] = results['BLENDED_RENT_PER_RSF'] * 750 / 12 | |
| results['FREE_MARKET_RENT_PER_SF'] = results['TOTAL_FREE_MARKET_RENT'] * 110 / 12 | |
| results['AFFORDABLE_RENT_PER_SF'] = results['AFFORDABLE_RENT_PSF'] * 110 / 12 | |
| results['BLENDED_RENT_PER_SF'] = results['TOTAL_BLENDED_RENT'] * 110 / 12 | |
| results['AVERAGE_RENT_PER_UNIT'] = self.safe_divide((results['GROSS_POTENTIAL_FREE_MARKET_RENT']+results['GROSS_POTENTIAL_AFFORDABLE_RENT']), results['UNITS']) | |
| results['RENT_PER_UNIT_PER_MONTH'] = results['AVERAGE_RENT_PER_UNIT'] / 12 | |
| # EGI PERCENTAGES | |
| if results['EFFECTIVE_GROSS_INCOME'] > 0: | |
| results['PAYROLL_PERCENTAGE_OF_EGI'] = results['PAYROLL'] / results['EFFECTIVE_GROSS_INCOME'] | |
| results['REPAIRS_AND_MAINTENANCE_PERCENTAGE_OF_EGI'] = results['REPAIRS_AND_MAINTENANCE'] / results['EFFECTIVE_GROSS_INCOME'] | |
| results['UTILITIES_PERCENTAGE_OF_EGI'] = results['UTILITIES'] / results['EFFECTIVE_GROSS_INCOME'] | |
| results['ADMIN_AND_PROFESSIONAL_PERCENTAGE_OF_EGI'] = (results['ADMINISTRATIVE'] + results['PROFESSIONAL_FEES']) / results['EFFECTIVE_GROSS_INCOME'] | |
| results['INSURANCE_PERCENTAGE_OF_EGI'] = results['INSURANCE'] / results['EFFECTIVE_GROSS_INCOME'] | |
| results['PROFESSIONAL_FEES_PERCENTAGE_OF_EGI'] = results['PROFESSIONAL_FEES'] / results['EFFECTIVE_GROSS_INCOME'] | |
| results['TOTAL_OPERATING_EXPENSES_PERCENTAGE_OF_EGI'] = results['TOTAL_OPERATING_EXPENSES'] / results['EFFECTIVE_GROSS_INCOME'] | |
| else: | |
| results['PAYROLL_PERCENTAGE_OF_EGI'] = 0 | |
| results['REPAIRS_AND_MAINTENANCE_PERCENTAGE_OF_EGI'] = 0 | |
| results['UTILITIES_PERCENTAGE_OF_EGI'] = 0 | |
| results['ADMIN_AND_PROFESSIONAL_PERCENTAGE_OF_EGI'] = 0 | |
| results['INSURANCE_PERCENTAGE_OF_EGI'] = 0 | |
| results['PROFESSIONAL_FEES_PERCENTAGE_OF_EGI'] = 0 | |
| results['TOTAL_OPERATING_EXPENSES_PERCENTAGE_OF_EGI'] = 0 | |
| self.formula_results = results | |
| return results | |
| def flatten_dict(self, d: Dict[str, Any], parent_key: str = '', sep: str = '.') -> Dict[str, Any]: | |
| """Flatten nested dictionary""" | |
| items = [] | |
| for k, v in d.items(): | |
| new_key = f"{parent_key}{sep}{k}" if parent_key else k | |
| if isinstance(v, dict): | |
| items.extend(self.flatten_dict(v, new_key, sep=sep).items()) | |
| else: | |
| items.append((new_key, v)) | |
| return dict(items) | |
| def generate_excel(self, output_path: str = "Real_Estate_Financial_Model.xlsx"): | |
| """Generate professional Excel file with all calculations""" | |
| try: | |
| # Validate critical values before Excel generation | |
| r = self.formula_results | |
| print(" Validating calculations...") | |
| critical_values = { | |
| 'UNITS': r.get('UNITS', 0), | |
| 'GROSS_SF': r.get('GROSS_SF', 0), | |
| 'RENTABLE_SF': r.get('RENTABLE_SF', 0), | |
| 'EFFECTIVE_GROSS_INCOME': r.get('EFFECTIVE_GROSS_INCOME', 0), | |
| 'TOTAL_PROJECT_COST': r.get('TOTAL_FINANCING_CONTINGENCY_AND_RESERVES', 0) | |
| } | |
| warnings = [] | |
| for key, value in critical_values.items(): | |
| if value == 0: | |
| warnings.append(f" WARNING: {key} is zero or missing") | |
| if warnings: | |
| print("\n".join(warnings)) | |
| print(" Continuing with available data...\n") | |
| wb = openpyxl.Workbook() | |
| # Remove default sheet | |
| if 'Sheet' in wb.sheetnames: | |
| wb.remove(wb['Sheet']) | |
| # Create sheets with error handling | |
| print(" Creating Executive Summary...") | |
| self.create_summary_sheet(wb) | |
| print(" Creating Acquisition sheet...") | |
| self.create_acquisition_sheet(wb) | |
| print(" Creating Construction sheet...") | |
| self.create_construction_sheet(wb) | |
| print(" Creating Soft Costs sheet...") | |
| self.create_soft_costs_sheet(wb) | |
| print(" Creating Financing sheet...") | |
| self.create_financing_sheet(wb) | |
| print(" Creating Operations sheet...") | |
| self.create_operations_sheet(wb) | |
| print(" Creating Returns sheet...") | |
| self.create_returns_sheet(wb) | |
| # Save workbook | |
| wb.save(output_path) | |
| print(f"✓ Excel file generated: {output_path}") | |
| return output_path | |
| except Exception as e: | |
| print(f"ERROR generating Excel: {e}") | |
| import traceback | |
| traceback.print_exc() | |
| raise | |
| def create_summary_sheet(self, wb): | |
| """Create executive summary sheet""" | |
| ws = wb.create_sheet("Executive Summary", 0) | |
| # Styles | |
| header_fill = PatternFill(start_color="1F4E78", end_color="1F4E78", fill_type="solid") | |
| header_font = Font(color="FFFFFF", bold=True, size=12) | |
| subheader_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid") | |
| subheader_font = Font(color="FFFFFF", bold=True, size=11) | |
| r = self.formula_results | |
| # Title | |
| ws['A1'] = "REAL ESTATE DEVELOPMENT FINANCIAL MODEL" | |
| ws['A1'].font = Font(bold=True, size=16) | |
| ws.merge_cells('A1:D1') | |
| # Property Information | |
| row = 3 | |
| ws[f'A{row}'] = "PROPERTY INFORMATION" | |
| ws[f'A{row}'].fill = header_fill | |
| ws[f'A{row}'].font = header_font | |
| ws.merge_cells(f'A{row}:D{row}') | |
| address = self.structured_data.get('property_info', {}).get('address', 'N/A') | |
| row += 1 | |
| data = [ | |
| ("Address:", address), | |
| ("Units:", r.get('UNITS', 0)), | |
| ("Gross Square Feet:", f"{r.get('GROSS_SF', 0):,.0f}"), | |
| ("Rentable Square Feet:", f"{r.get('RENTABLE_SF', 0):,.0f}"), | |
| ("Building Efficiency:", f"{r.get('BUILDING_EFFICIENCY', 0):.2%}"), | |
| ] | |
| for label, value in data: | |
| ws[f'A{row}'] = label | |
| ws[f'A{row}'].font = Font(bold=True) | |
| ws[f'B{row}'] = value | |
| row += 1 | |
| # Project Costs Summary | |
| row += 1 | |
| ws[f'A{row}'] = "PROJECT COSTS SUMMARY" | |
| ws[f'A{row}'].fill = header_fill | |
| ws[f'A{row}'].font = header_font | |
| ws.merge_cells(f'A{row}:D{row}') | |
| row += 1 | |
| ws[f'A{row}'] = "Category" | |
| ws[f'B{row}'] = "Total Cost" | |
| ws[f'C{row}'] = "Per GSF" | |
| ws[f'D{row}'] = "Per Unit" | |
| for col in ['A', 'B', 'C', 'D']: | |
| ws[f'{col}{row}'].fill = subheader_fill | |
| ws[f'{col}{row}'].font = subheader_font | |
| row += 1 | |
| cost_summary = [ | |
| ("Acquisition", r.get('TOTAL_ACQUISITION_COST', 0), r.get('TOTAL_ACQUISITION_COST_PER_GSF', 0), r.get('TOTAL_ACQUISITION_COST_PER_UNIT', 0)), | |
| ("Construction", r.get('TOTAL_CONSTRUCTION_GMP', 0), r.get('CONSTRUCTION_GMP_PER_GSF', 0), r.get('CONSTRUCTION_GMP_PER_UNIT', 0)), | |
| ("Soft Costs", r.get('TOTAL_SOFT_COST', 0), r.get('TOTAL_SOFT_COST_PER_GSF', 0), r.get('TOTAL_SOFT_COST_PER_GSF', 0) * r.get('GROSS_SF', 0) / r.get('UNITS', 1)), | |
| ("Contingency", r.get('CONTINGENCY_COST', 0), r.get('CONTINGENCY_COST', 0) / r.get('GROSS_SF', 1), r.get('CONTINGENCY_COST', 0) / r.get('UNITS', 1)), | |
| ("Development Fee", r.get('DEVELOPMENT_FEE', 0), r.get('DEVELOPMENT_FEE', 0) / r.get('GROSS_SF', 1), r.get('DEVELOPMENT_FEE', 0) / r.get('UNITS', 1)), | |
| ("Financing & Reserves", r.get('FINANCING_COST', 0) + r.get('INTEREST_RESERVE', 0) + r.get('OPERATING_RESERVE', 0), 0, 0), | |
| ] | |
| for label, total, per_gsf, per_unit in cost_summary: | |
| ws[f'A{row}'] = label | |
| ws[f'B{row}'] = total | |
| ws[f'B{row}'].number_format = '$#,##0' | |
| ws[f'C{row}'] = per_gsf | |
| ws[f'C{row}'].number_format = '$#,##0.00' | |
| ws[f'D{row}'] = per_unit | |
| ws[f'D{row}'].number_format = '$#,##0' | |
| row += 1 | |
| # Total | |
| ws[f'A{row}'] = "TOTAL PROJECT COST" | |
| ws[f'A{row}'].font = Font(bold=True) | |
| ws[f'B{row}'] = r.get('TOTAL_FINANCING_CONTINGENCY_AND_RESERVES', 0) | |
| ws[f'B{row}'].number_format = '$#,##0' | |
| ws[f'B{row}'].font = Font(bold=True) | |
| ws[f'C{row}'] = r.get('TOTAL_PROJECT_COST_PER_GSF', 0) | |
| ws[f'C{row}'].number_format = '$#,##0.00' | |
| ws[f'C{row}'].font = Font(bold=True) | |
| ws[f'D{row}'] = r.get('TOTAL_PROJECT_COST_PER_UNIT', 0) | |
| ws[f'D{row}'].number_format = '$#,##0' | |
| ws[f'D{row}'].font = Font(bold=True) | |
| # Capital Stack | |
| row += 2 | |
| ws[f'A{row}'] = "CAPITAL STACK" | |
| ws[f'A{row}'].fill = header_fill | |
| ws[f'A{row}'].font = header_font | |
| ws.merge_cells(f'A{row}:D{row}') | |
| row += 1 | |
| ws[f'A{row}'] = "Source" | |
| ws[f'B{row}'] = "Amount" | |
| ws[f'C{row}'] = "Percentage" | |
| ws[f'D{row}'] = "Per Unit" | |
| for col in ['A', 'B', 'C', 'D']: | |
| ws[f'{col}{row}'].fill = subheader_fill | |
| ws[f'{col}{row}'].font = subheader_font | |
| row += 1 | |
| ws[f'A{row}'] = "Total Debt" | |
| ws[f'B{row}'] = r.get('TOTAL_DEBT', 0) | |
| ws[f'B{row}'].number_format = '$#,##0' | |
| ws[f'C{row}'] = r.get('DEBT_PERCENTAGE', 0) | |
| ws[f'C{row}'].number_format = '0.00%' | |
| ws[f'D{row}'] = r.get('DEBT_PER_UNIT', 0) | |
| ws[f'D{row}'].number_format = '$#,##0' | |
| row += 1 | |
| ws[f'A{row}'] = "Total Equity" | |
| ws[f'B{row}'] = r.get('TOTAL_EQUITY', 0) | |
| ws[f'B{row}'].number_format = '$#,##0' | |
| ws[f'C{row}'] = r.get('EQUITY_PERCENTAGE', 0) | |
| ws[f'C{row}'].number_format = '0.00%' | |
| ws[f'D{row}'] = r.get('EQUITY_PER_UNIT', 0) | |
| ws[f'D{row}'].number_format = '$#,##0' | |
| # Returns Summary | |
| row += 2 | |
| ws[f'A{row}'] = "INVESTMENT RETURNS" | |
| ws[f'A{row}'].fill = header_fill | |
| ws[f'A{row}'].font = header_font | |
| ws.merge_cells(f'A{row}:D{row}') | |
| row += 1 | |
| returns_data = [ | |
| ("Stabilized NOI:", f"${r.get('NET_OPERATING_INCOME', 0):,.0f}"), | |
| ("Yield on Cost:", f"{r.get('YIELD_ON_COST_PERCENTAGE', 0):.2%}"), | |
| ("Stabilized Yield on Cost:", f"{r.get('STABILIZED_YIELD_ON_COST_PERCENTAGE', 0):.2%}"), | |
| ("Cash-on-Cash Return:", f"{r.get('CASH_ON_CASH_RETURN', 0):.2f}%"), | |
| ("DSCR:", f"{r.get('DEBT_SERVICE_COVERAGE_RATIO', 0):.2f}x"), | |
| ("LP IRR:", f"{float(r.get('IRR_TO_LP', 0).real if isinstance(r.get('IRR_TO_LP', 0), complex) else r.get('IRR_TO_LP', 0)):.2f}%"), | |
| ("LP Multiple:", f"{r.get('LP_MULTIPLE', 0):.2f}x"), | |
| ] | |
| for label, value in returns_data: | |
| ws[f'A{row}'] = label | |
| ws[f'A{row}'].font = Font(bold=True) | |
| ws[f'B{row}'] = value | |
| row += 1 | |
| # Adjust column widths | |
| ws.column_dimensions['A'].width = 25 | |
| ws.column_dimensions['B'].width = 18 | |
| ws.column_dimensions['C'].width = 15 | |
| ws.column_dimensions['D'].width = 15 | |
| def create_acquisition_sheet(self, wb): | |
| """Create acquisition costs detail sheet""" | |
| ws = wb.create_sheet("Acquisition") | |
| r = self.formula_results | |
| # Header | |
| ws['A1'] = "ACQUISITION COSTS" | |
| ws['A1'].font = Font(bold=True, size=14) | |
| ws.merge_cells('A1:E1') | |
| # Column headers | |
| row = 3 | |
| headers = ["Item", "Total Cost", "Per GSF", "Per RSF", "Per Unit"] | |
| for col_idx, header in enumerate(headers, start=1): | |
| cell = ws.cell(row=row, column=col_idx, value=header) | |
| cell.fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid") | |
| cell.font = Font(color="FFFFFF", bold=True) | |
| # Data | |
| row += 1 | |
| data = [ | |
| ("Land Value", r.get('LAND_VALUE', 0), r.get('LAND_VALUE_PER_GSF', 0), r.get('LAND_VALUE_PER_RSF', 0), r.get('LAND_VALUE_PER_UNIT', 0)), | |
| ("Closing Costs", r.get('CLOSING_COSTS', 0), r.get('CLOSING_COSTS', 0) / r.get('GROSS_SF', 1), r.get('CLOSING_COSTS', 0) / r.get('RENTABLE_SF', 1), r.get('CLOSING_COSTS', 0) / r.get('UNITS', 1)), | |
| ("Acquisition Fee (2%)", r.get('ACQUISITION_FEE', 0), r.get('ACQUISITION_FEE', 0) / r.get('GROSS_SF', 1), r.get('ACQUISITION_FEE', 0) / r.get('RENTABLE_SF', 1), r.get('ACQUISITION_FEE', 0) / r.get('UNITS', 1)), | |
| ] | |
| for item, total, per_gsf, per_rsf, per_unit in data: | |
| ws.cell(row=row, column=1, value=item) | |
| ws.cell(row=row, column=2, value=total).number_format = '$#,##0' | |
| ws.cell(row=row, column=3, value=per_gsf).number_format = '$#,##0.00' | |
| ws.cell(row=row, column=4, value=per_rsf).number_format = '$#,##0.00' | |
| ws.cell(row=row, column=5, value=per_unit).number_format = '$#,##0' | |
| row += 1 | |
| # Total | |
| ws.cell(row=row, column=1, value="TOTAL ACQUISITION COST").font = Font(bold=True) | |
| ws.cell(row=row, column=2, value=r.get('TOTAL_ACQUISITION_COST', 0)).number_format = '$#,##0' | |
| ws.cell(row=row, column=2).font = Font(bold=True) | |
| ws.cell(row=row, column=3, value=r.get('TOTAL_ACQUISITION_COST_PER_GSF', 0)).number_format = '$#,##0.00' | |
| ws.cell(row=row, column=3).font = Font(bold=True) | |
| ws.cell(row=row, column=4, value=r.get('TOTAL_ACQUISITION_COST_PER_RSF', 0)).number_format = '$#,##0.00' | |
| ws.cell(row=row, column=4).font = Font(bold=True) | |
| ws.cell(row=row, column=5, value=r.get('TOTAL_ACQUISITION_COST_PER_UNIT', 0)).number_format = '$#,##0' | |
| ws.cell(row=row, column=5).font = Font(bold=True) | |
| # Adjust widths | |
| for col in range(1, 6): | |
| ws.column_dimensions[get_column_letter(col)].width = 20 | |
| def create_construction_sheet(self, wb): | |
| """Create construction costs sheet""" | |
| ws = wb.create_sheet("Construction") | |
| r = self.formula_results | |
| ws['A1'] = "CONSTRUCTION COSTS" | |
| ws['A1'].font = Font(bold=True, size=14) | |
| row = 3 | |
| ws[f'A{row}'] = "Construction Cost per GSF:" | |
| ws[f'B{row}'] = r.get('CONSTRUCTION_COST_PER_GSF', 0) | |
| ws[f'B{row}'].number_format = '$#,##0.00' | |
| row += 1 | |
| ws[f'A{row}'] = "Gross Square Feet:" | |
| ws[f'B{row}'] = r.get('GROSS_SF', 0) | |
| ws[f'B{row}'].number_format = '#,##0' | |
| row += 2 | |
| ws[f'A{row}'] = "Total Construction GMP:" | |
| ws[f'A{row}'].font = Font(bold=True) | |
| ws[f'B{row}'] = r.get('TOTAL_CONSTRUCTION_GMP', 0) | |
| ws[f'B{row}'].number_format = '$#,##0' | |
| ws[f'B{row}'].font = Font(bold=True) | |
| row += 2 | |
| ws[f'A{row}'] = "Construction Duration:" | |
| ws[f'B{row}'] = f"{r.get('CONSTRUCTION_MONTHS', 0)} months" | |
| ws.column_dimensions['A'].width = 30 | |
| ws.column_dimensions['B'].width = 20 | |
| def create_soft_costs_sheet(self, wb): | |
| """Create soft costs detail sheet""" | |
| ws = wb.create_sheet("Soft Costs") | |
| r = self.formula_results | |
| ws['A1'] = "SOFT COSTS BUDGET" | |
| ws['A1'].font = Font(bold=True, size=14) | |
| row = 3 | |
| headers = ["Category", "Total Cost", "Per GSF"] | |
| for col_idx, header in enumerate(headers, start=1): | |
| cell = ws.cell(row=row, column=col_idx, value=header) | |
| cell.fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid") | |
| cell.font = Font(color="FFFFFF", bold=True) | |
| row += 1 | |
| soft_cost_items = [ | |
| ("Architecture & Interior Design", 'ARCHITECTURE_AND_INTERIOR_COST'), | |
| ("Structural Engineering", 'STRUCTURAL_ENGINEERING_COST'), | |
| ("MEP Engineering", 'MEP_ENGINEERING_COST'), | |
| ("Civil Engineering", 'CIVIL_ENGINEERING_COST'), | |
| ("Controlled Inspections", 'CONTROLLED_INSPECTIONS_COST'), | |
| ("Surveying", 'SURVEYING_COST'), | |
| ("Utilities Connection", 'UTILITIES_CONNECTION_COST'), | |
| ("Advertising & Marketing", 'ADVERTISING_AND_MARKETING_COST'), | |
| ("Accounting", 'ACCOUNTING_COST'), | |
| ("Monitoring", 'MONITORING_COST'), | |
| ("FF&E", 'FF_AND_E_COST'), | |
| ("Environmental Consultant", 'ENVIRONMENTAL_CONSULTANT_FEE'), | |
| ("Miscellaneous Consultants", 'MISCELLANEOUS_CONSULTANTS_FEE'), | |
| ("General Legal", 'GENERAL_LEGAL_COST'), | |
| ("RE Taxes During Construction", 'REAL_ESTATE_TAXES_DURING_CONSTRUCTION'), | |
| ("Miscellaneous Admin", 'MISCELLANEOUS_ADMIN_COST'), | |
| ("IBR Cost", 'IBR_COST'), | |
| ("Project Team", 'PROJECT_TEAM_COST'), | |
| ("PEM Fees", 'PEM_FEES'), | |
| ("Bank Fees", 'BANK_FEES'), | |
| ("HPD & IH Costs", 'HPD_AND_IH_COST'), | |
| ("Retail TI & LC", 'RETAIL_TI_AND_LC_COST'), | |
| ] | |
| for label, key in soft_cost_items: | |
| cost = r.get(key, 0) | |
| per_gsf = cost / r.get('GROSS_SF', 1) if r.get('GROSS_SF', 0) > 0 else 0 | |
| ws.cell(row=row, column=1, value=label) | |
| ws.cell(row=row, column=2, value=cost).number_format = '$#,##0' | |
| ws.cell(row=row, column=3, value=per_gsf).number_format = '$#,##0.00' | |
| row += 1 | |
| # Total | |
| ws.cell(row=row, column=1, value="TOTAL SOFT COSTS").font = Font(bold=True) | |
| ws.cell(row=row, column=2, value=r.get('TOTAL_SOFT_COST', 0)).number_format = '$#,##0' | |
| ws.cell(row=row, column=2).font = Font(bold=True) | |
| ws.cell(row=row, column=3, value=r.get('TOTAL_SOFT_COST_PER_GSF', 0)).number_format = '$#,##0.00' | |
| ws.cell(row=row, column=3).font = Font(bold=True) | |
| ws.column_dimensions['A'].width = 35 | |
| ws.column_dimensions['B'].width = 18 | |
| ws.column_dimensions['C'].width = 15 | |
| def create_financing_sheet(self, wb): | |
| """Create financing structure sheet""" | |
| ws = wb.create_sheet("Financing") | |
| r = self.formula_results | |
| ws['A1'] = "FINANCING STRUCTURE" | |
| ws['A1'].font = Font(bold=True, size=14) | |
| row = 3 | |
| ws[f'A{row}'] = "Pre-LTC Budget:" | |
| ws[f'B{row}'] = r.get('PRE_LTC_BUDGET', 0) | |
| ws[f'B{row}'].number_format = '$#,##0' | |
| row += 1 | |
| ws[f'A{row}'] = "LTC Ratio:" | |
| ws[f'B{row}'] = r.get('LTC_RATIO', 0) | |
| ws[f'B{row}'].number_format = '0.00%' | |
| row += 1 | |
| ws[f'A{row}'] = "Loan Amount:" | |
| ws[f'A{row}'].font = Font(bold=True) | |
| ws[f'B{row}'] = r.get('LOAN_AMOUNT', 0) | |
| ws[f'B{row}'].number_format = '$#,##0' | |
| ws[f'B{row}'].font = Font(bold=True) | |
| row += 2 | |
| ws[f'A{row}'] = "Financing Percentage:" | |
| ws[f'B{row}'] = r.get('FINANCING_PERCENTAGE', 0) | |
| ws[f'B{row}'].number_format = '0.00%' | |
| row += 1 | |
| ws[f'A{row}'] = "Financing Amount:" | |
| ws[f'B{row}'] = r.get('FINANCING_AMOUNT', 0) | |
| ws[f'B{row}'].number_format = '$#,##0' | |
| row += 2 | |
| ws[f'A{row}'] = "Interest Rate (bps + spread):" | |
| ws[f'B{row}'] = r.get('INTEREST_RATE_DECIMAL', 0) | |
| ws[f'B{row}'].number_format = '0.00%' | |
| row += 1 | |
| ws[f'A{row}'] = "Construction Interest:" | |
| ws[f'B{row}'] = r.get('CONSTRUCTION_INTEREST', 0) | |
| ws[f'B{row}'].number_format = '$#,##0' | |
| row += 2 | |
| ws[f'A{row}'] = "TOTAL DEBT" | |
| ws[f'A{row}'].font = Font(bold=True, size=12) | |
| ws[f'B{row}'] = r.get('TOTAL_DEBT', 0) | |
| ws[f'B{row}'].number_format = '$#,##0' | |
| ws[f'B{row}'].font = Font(bold=True, size=12) | |
| row += 1 | |
| ws[f'A{row}'] = "TOTAL EQUITY" | |
| ws[f'A{row}'].font = Font(bold=True, size=12) | |
| ws[f'B{row}'] = r.get('TOTAL_EQUITY', 0) | |
| ws[f'B{row}'].number_format = '$#,##0' | |
| ws[f'B{row}'].font = Font(bold=True, size=12) | |
| row += 2 | |
| ws[f'A{row}'] = "Debt Percentage:" | |
| ws[f'B{row}'] = r.get('DEBT_PERCENTAGE', 0) | |
| ws[f'B{row}'].number_format = '0.00%' | |
| row += 1 | |
| ws[f'A{row}'] = "Equity Percentage:" | |
| ws[f'B{row}'] = r.get('EQUITY_PERCENTAGE', 0) | |
| ws[f'B{row}'].number_format = '0.00%' | |
| ws.column_dimensions['A'].width = 35 | |
| ws.column_dimensions['B'].width = 20 | |
| def create_operations_sheet(self, wb): | |
| """Create operations and revenue sheet""" | |
| ws = wb.create_sheet("Operations") | |
| r = self.formula_results | |
| ws['A1'] = "OPERATIONS & REVENUE" | |
| ws['A1'].font = Font(bold=True, size=14) | |
| # Revenue Section | |
| row = 3 | |
| ws[f'A{row}'] = "REVENUE" | |
| ws[f'A{row}'].fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid") | |
| ws[f'A{row}'].font = Font(color="FFFFFF", bold=True) | |
| ws.merge_cells(f'A{row}:B{row}') | |
| row += 1 | |
| revenue_items = [ | |
| ("Gross Potential Free Market Rent", r.get('GROSS_POTENTIAL_FREE_MARKET_RENT', 0)), | |
| ("Gross Potential Affordable Rent", r.get('GROSS_POTENTIAL_AFFORDABLE_RENT', 0)), | |
| ("Other Income", r.get('OTHER_INCOME', 0)), | |
| ("Less: Vacancy Loss", -r.get('VACANCY_LOSS', 0)), | |
| ("Effective Gross Income", r.get('EFFECTIVE_GROSS_INCOME', 0)), | |
| ("Parking Income", r.get('PARKING_INCOME', 0)), | |
| ("Retail Revenue", r.get('RETAIL_REVENUE', 0)), | |
| ] | |
| for label, value in revenue_items: | |
| ws[f'A{row}'] = label | |
| if "Effective Gross" in label: | |
| ws[f'A{row}'].font = Font(bold=True) | |
| ws[f'B{row}'] = value | |
| ws[f'B{row}'].number_format = '$#,##0' | |
| row += 1 | |
| # Expense Section | |
| row += 1 | |
| ws[f'A{row}'] = "OPERATING EXPENSES" | |
| ws[f'A{row}'].fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid") | |
| ws[f'A{row}'].font = Font(color="FFFFFF", bold=True) | |
| ws.merge_cells(f'A{row}:C{row}') | |
| row += 1 | |
| ws[f'A{row}'] = "Expense Category" | |
| ws[f'B{row}'] = "Annual Amount" | |
| ws[f'C{row}'] = "% of EGI" | |
| for col in ['A', 'B', 'C']: | |
| ws[f'{col}{row}'].font = Font(bold=True) | |
| row += 1 | |
| # Safe division helper | |
| egi = r.get('EFFECTIVE_GROSS_INCOME', 0) | |
| def safe_pct(value): | |
| return value / egi if egi > 0 else 0 | |
| expense_items = [ | |
| ("Payroll", r.get('PAYROLL', 0), r.get('PAYROLL_PERCENTAGE_OF_EGI', 0)), | |
| ("Repairs & Maintenance", r.get('REPAIRS_AND_MAINTENANCE', 0), r.get('REPAIRS_AND_MAINTENANCE_PERCENTAGE_OF_EGI', 0)), | |
| ("Utilities", r.get('UTILITIES', 0), r.get('UTILITIES_PERCENTAGE_OF_EGI', 0)), | |
| ("Insurance", r.get('INSURANCE', 0), r.get('INSURANCE_PERCENTAGE_OF_EGI', 0)), | |
| ("Management Fee", r.get('MANAGEMENT_FEE', 0), safe_pct(r.get('MANAGEMENT_FEE', 0))), | |
| ("Real Estate Taxes", r.get('REAL_ESTATE_TAXES', 0), safe_pct(r.get('REAL_ESTATE_TAXES', 0))), | |
| ] | |
| for label, amount, pct in expense_items: | |
| ws[f'A{row}'] = label | |
| ws[f'B{row}'] = amount | |
| ws[f'B{row}'].number_format = '$#,##0' | |
| ws[f'C{row}'] = pct | |
| ws[f'C{row}'].number_format = '0.00%' | |
| row += 1 | |
| ws[f'A{row}'] = "TOTAL EXPENSES" | |
| ws[f'A{row}'].font = Font(bold=True) | |
| ws[f'B{row}'] = r.get('TOTAL_EXPENSES', 0) | |
| ws[f'B{row}'].number_format = '$#,##0' | |
| ws[f'B{row}'].font = Font(bold=True) | |
| total_exp_pct = safe_pct(r.get('TOTAL_EXPENSES', 0)) | |
| ws[f'C{row}'] = total_exp_pct | |
| ws[f'C{row}'].number_format = '0.00%' | |
| ws[f'C{row}'].font = Font(bold=True) | |
| row += 2 | |
| ws[f'A{row}'] = "NET OPERATING INCOME" | |
| ws[f'A{row}'].font = Font(bold=True, size=12) | |
| ws[f'B{row}'] = r.get('NET_OPERATING_INCOME', 0) | |
| ws[f'B{row}'].number_format = '$#,##0' | |
| ws[f'B{row}'].font = Font(bold=True, size=12) | |
| row += 2 | |
| ws[f'A{row}'] = "NOI per Unit:" | |
| ws[f'B{row}'] = r.get('NOI_PER_UNIT', 0) | |
| ws[f'B{row}'].number_format = '$#,##0' | |
| row += 1 | |
| ws[f'A{row}'] = "NOI per GSF:" | |
| ws[f'B{row}'] = r.get('NOI_PER_GSF', 0) | |
| ws[f'B{row}'].number_format = '$#,##0.00' | |
| ws.column_dimensions['A'].width = 35 | |
| ws.column_dimensions['B'].width = 20 | |
| ws.column_dimensions['C'].width = 15 | |
| def create_returns_sheet(self, wb): | |
| """Create investment returns and waterfall sheet""" | |
| ws = wb.create_sheet("Returns") | |
| r = self.formula_results | |
| ws['A1'] = "INVESTMENT RETURNS & EXIT ANALYSIS" | |
| ws['A1'].font = Font(bold=True, size=14) | |
| # Current Returns | |
| row = 3 | |
| ws[f'A{row}'] = "STABILIZED RETURNS" | |
| ws[f'A{row}'].fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid") | |
| ws[f'A{row}'].font = Font(color="FFFFFF", bold=True) | |
| ws.merge_cells(f'A{row}:B{row}') | |
| row += 1 | |
| returns_data = [ | |
| ("Net Operating Income", f"${r.get('NET_OPERATING_INCOME', 0):,.0f}"), | |
| ("Stabilized Yield on Cost", f"${r.get('STABILIZED_YIELD_ON_COST', 0):,.0f}"), | |
| ("Yield on Cost %", f"{r.get('YIELD_ON_COST_PERCENTAGE', 0):.2%}"), | |
| ("Stabilized Yield on Cost %", f"{r.get('STABILIZED_YIELD_ON_COST_PERCENTAGE', 0):.2%}"), | |
| ("Annual Debt Service", f"${r.get('ANNUAL_DEBT_SERVICE', 0):,.0f}"), | |
| ("Cash-on-Cash Return", f"{r.get('CASH_ON_CASH_RETURN', 0):.2f}%"), | |
| ("Debt Service Coverage Ratio", f"{r.get('DEBT_SERVICE_COVERAGE_RATIO', 0):.2f}x"), | |
| ] | |
| for label, value in returns_data: | |
| ws[f'A{row}'] = label | |
| ws[f'A{row}'].font = Font(bold=True) | |
| ws[f'B{row}'] = value | |
| row += 1 | |
| # Exit Analysis | |
| row += 2 | |
| ws[f'A{row}'] = "EXIT ANALYSIS" | |
| ws[f'A{row}'].fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid") | |
| ws[f'A{row}'].font = Font(color="FFFFFF", bold=True) | |
| ws.merge_cells(f'A{row}:B{row}') | |
| row += 1 | |
| ws[f'A{row}'] = "Hold Period (months):" | |
| ws[f'B{row}'] = r.get('HOLD_PERIOD_MONTHS', 0) | |
| row += 1 | |
| ws[f'A{row}'] = "Exit Cap Rate:" | |
| ws[f'B{row}'] = r.get('EXIT_CAP_RATE_DECIMAL', 0) | |
| ws[f'B{row}'].number_format = '0.00%' | |
| row += 1 | |
| ws[f'A{row}'] = "Property Value on Sale:" | |
| ws[f'B{row}'] = r.get('PROPERTY_VALUE_ON_SALE', 0) | |
| ws[f'B{row}'].number_format = '$#,##0' | |
| row += 1 | |
| ws[f'A{row}'] = "Less: Sale Costs (2%):" | |
| ws[f'B{row}'] = -r.get('SALE_COST', 0) | |
| ws[f'B{row}'].number_format = '$#,##0' | |
| row += 1 | |
| ws[f'A{row}'] = "Net Sale Proceeds:" | |
| ws[f'A{row}'].font = Font(bold=True) | |
| ws[f'B{row}'] = r.get('NET_SALE_PROCEEDS', 0) | |
| ws[f'B{row}'].number_format = '$#,##0' | |
| ws[f'B{row}'].font = Font(bold=True) | |
| row += 1 | |
| ws[f'A{row}'] = "Less: Loan Payoff:" | |
| ws[f'B{row}'] = -r.get('TOTAL_DEBT', 0) | |
| ws[f'B{row}'].number_format = '$#,##0' | |
| row += 1 | |
| ws[f'A{row}'] = "Cash After Loan Payback:" | |
| ws[f'A{row}'].font = Font(bold=True) | |
| ws[f'B{row}'] = r.get('CASH_REMAINING_AFTER_LOAN_PAYBACK', 0) | |
| ws[f'B{row}'].number_format = '$#,##0' | |
| ws[f'B{row}'].font = Font(bold=True) | |
| # Equity Waterfall | |
| row += 2 | |
| ws[f'A{row}'] = "EQUITY WATERFALL" | |
| ws[f'A{row}'].fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid") | |
| ws[f'A{row}'].font = Font(color="FFFFFF", bold=True) | |
| ws.merge_cells(f'A{row}:B{row}') | |
| row += 1 | |
| ws[f'A{row}'] = "GP Investment (20%):" | |
| ws[f'B{row}'] = r.get('GP_INVESTMENT', 0) | |
| ws[f'B{row}'].number_format = '$#,##0' | |
| row += 1 | |
| ws[f'A{row}'] = "LP Investment (80%):" | |
| ws[f'B{row}'] = r.get('LP_INVESTMENT', 0) | |
| ws[f'B{row}'].number_format = '$#,##0' | |
| row += 2 | |
| ws[f'A{row}'] = "GP Preferred Return + Principal:" | |
| ws[f'B{row}'] = r.get('GP_PREFERRED_RETURN_WITH_PRINCIPAL', 0) | |
| ws[f'B{row}'].number_format = '$#,##0' | |
| row += 1 | |
| ws[f'A{row}'] = "LP Preferred Return + Principal:" | |
| ws[f'B{row}'] = r.get('LP_PREFERRED_RETURN_WITH_PRINCIPAL', 0) | |
| ws[f'B{row}'].number_format = '$#,##0' | |
| row += 1 | |
| ws[f'A{row}'] = "Cash After Preferred:" | |
| ws[f'B{row}'] = r.get('CASH_REMAINING_AFTER_PREFERRED', 0) | |
| ws[f'B{row}'].number_format = '$#,##0' | |
| row += 2 | |
| ws[f'A{row}'] = "GP Promote (20%):" | |
| ws[f'B{row}'] = r.get('PROMOTE_ON_JOINT_VENTURE', 0) | |
| ws[f'B{row}'].number_format = '$#,##0' | |
| row += 1 | |
| ws[f'A{row}'] = "Cash to LP:" | |
| ws[f'B{row}'] = r.get('CASH_TO_LP', 0) | |
| ws[f'B{row}'].number_format = '$#,##0' | |
| row += 2 | |
| ws[f'A{row}'] = "NET TO LP INVESTOR" | |
| ws[f'A{row}'].font = Font(bold=True, size=12) | |
| ws[f'B{row}'] = r.get('NET_TO_LP_INVESTOR', 0) | |
| ws[f'B{row}'].number_format = '$#,##0' | |
| ws[f'B{row}'].font = Font(bold=True, size=12) | |
| row += 2 | |
| ws[f'A{row}'] = "LP Multiple:" | |
| ws[f'A{row}'].font = Font(bold=True) | |
| ws[f'B{row}'] = r.get('LP_MULTIPLE', 0) | |
| ws[f'B{row}'].number_format = '0.00x' | |
| ws[f'B{row}'].font = Font(bold=True) | |
| row += 1 | |
| ws[f'A{row}'] = "LP IRR:" | |
| ws[f'A{row}'].font = Font(bold=True) | |
| irr_value = r.get('IRR_TO_LP', 0) | |
| # Handle complex numbers or invalid values | |
| if isinstance(irr_value, complex): | |
| irr_value = 0 # or use irr_value.real if you want the real component | |
| ws[f'B{row}'] = irr_value / 100 | |
| ws[f'B{row}'].number_format = '0.00%' | |
| ws[f'B{row}'].font = Font(bold=True) | |
| ws.column_dimensions['A'].width = 35 | |
| ws.column_dimensions['B'].width = 20 | |
| def run_full_pipeline(self, pdf_directory: str, output_excel: str = "Real_Estate_Financial_Model.xlsx"): | |
| """Execute complete pipeline""" | |
| print("=" * 60) | |
| print("REAL ESTATE FINANCIAL MODEL PIPELINE") | |
| print("=" * 60) | |
| # Step 1: Extract PDFs | |
| print("\n[Step 1/4] Extracting text from PDFs...") | |
| self.extract_all_pdfs(pdf_directory) | |
| print(f"✓ Extracted {len(self.extracted_data)} PDF files") | |
| # Step 2: Process with Gemini | |
| print("\n[Step 2/4] Extracting structured data with Gemini API...") | |
| structured_data = self.extract_structured_data() | |
| # NEW: Post-process to fill gaps | |
| print("\n[Step 2.5/4] Post-processing and filling estimates...") | |
| structured_data = self.post_process_extracted_data(structured_data) | |
| # Step 3: Calculate formulas | |
| print("\n[Step 3/4] Calculating all formulas...") | |
| self.calculate_all_formulas(structured_data) | |
| print(f"✓ Calculated {len(self.formula_results)} formula values") | |
| # Step 4: Generate Excel | |
| print("\n[Step 4/4] Generating Excel file...") | |
| self.generate_excel(output_excel) | |
| print("\n" + "=" * 60) | |
| print("PIPELINE COMPLETE!") | |
| print("=" * 60) | |
| print(f"\nKey Metrics:") | |
| print(f" Total Project Cost: ${self.formula_results.get('TOTAL_FINANCING_CONTINGENCY_AND_RESERVES', 0):,.0f}") | |
| print(f" Total Debt: ${self.formula_results.get('TOTAL_DEBT', 0):,.0f}") | |
| print(f" Total Equity: ${self.formula_results.get('TOTAL_EQUITY', 0):,.0f}") | |
| print(f" NOI: ${self.formula_results.get('NET_OPERATING_INCOME', 0):,.0f}") | |
| print(f" Yield on Cost: {self.formula_results.get('YIELD_ON_COST_PERCENTAGE', 0):.2%}") | |
| irr_val = self.formula_results.get('IRR_TO_LP', 0) | |
| if isinstance(irr_val, complex): | |
| irr_val = irr_val.real | |
| print(f" LP IRR: {irr_val:.2f}%") | |
| print(f"\nExcel file: {output_excel}") | |
| return output_excel | |
| if __name__ == "__main__": | |
| # Hardcoded API Key | |
| GEMINI_API_KEY = "AIzaSyCy6GoBR724Hj9VyuW3hKM4N0P6liBOlDo" | |
| def process_pdfs(pdf_files): | |
| """Process uploaded PDFs and return Excel file""" | |
| if not pdf_files: | |
| return None, "Please upload at least one PDF file" | |
| try: | |
| # Create temporary directory for PDFs | |
| temp_dir = tempfile.mkdtemp() | |
| # Save uploaded PDFs to temp directory | |
| for pdf_file in pdf_files: | |
| shutil.copy(pdf_file.name, temp_dir) | |
| # Initialize pipeline with hardcoded API key | |
| pipeline = RealEstateModelPipeline(GEMINI_API_KEY) | |
| # Create output file in temp directory | |
| output_file = Path(temp_dir) / "Real_Estate_Financial_Model.xlsx" | |
| # Run pipeline | |
| result = pipeline.run_full_pipeline(temp_dir, str(output_file)) | |
| # Generate summary text | |
| summary = f""" | |
| ✅ Processing Complete! | |
| Key Metrics: | |
| • Total Project Cost: ${pipeline.formula_results.get('TOTAL_FINANCING_CONTINGENCY_AND_RESERVES', 0):,.0f} | |
| • Total Debt: ${pipeline.formula_results.get('TOTAL_DEBT', 0):,.0f} | |
| • Total Equity: ${pipeline.formula_results.get('TOTAL_EQUITY', 0):,.0f} | |
| • NOI: ${pipeline.formula_results.get('NET_OPERATING_INCOME', 0):,.0f} | |
| • Yield on Cost: {pipeline.formula_results.get('YIELD_ON_COST_PERCENTAGE', 0):.2%} | |
| • LP IRR: {float(pipeline.formula_results.get('IRR_TO_LP', 0).real if isinstance(pipeline.formula_results.get('IRR_TO_LP', 0), complex) else pipeline.formula_results.get('IRR_TO_LP', 0)):.2f}% | |
| Download your Excel file below ⬇️ | |
| """ | |
| return str(output_file), summary | |
| except Exception as e: | |
| return None, f"❌ Error: {str(e)}" | |
| # Create Gradio interface | |
| with gr.Blocks(title="Real Estate Financial Model Generator", theme=gr.themes.Soft()) as demo: | |
| gr.Markdown(""" | |
| # Real Estate Financial Model Generator | |
| Upload your PDF documents and generate a comprehensive financial model in Excel format. | |
| """) | |
| with gr.Row(): | |
| with gr.Column(scale=2): | |
| pdf_input = gr.File( | |
| label="Upload PDF/XLSX Files", | |
| file_count="multiple", | |
| file_types=[".pdf", ".xlsx", ".xls"], # Added .xlsx and .xls | |
| type="filepath" | |
| ) | |
| process_btn = gr.Button("Generate Financial Model", variant="primary", size="lg") | |
| with gr.Column(scale=1): | |
| gr.Markdown(""" | |
| ### 📋 Supported Formats | |
| - **PDF**: Offering Memorandum, Reports | |
| - **XLSX/XLS**: Financial statements, data tables | |
| ### 📋 Required Documents | |
| - Offering Memorandum (PDF/XLSX) | |
| - Operating Expenses Summary (PDF/XLSX) | |
| - Sales Comps (PDF/XLSX) | |
| - Rent Comps (PDF/XLSX) | |
| - Market Report (PDF/XLSX) | |
| - Demographics Overview (PDF/XLSX) | |
| ### ⚡ Features | |
| - Automated data extraction | |
| - Formula calculations | |
| - Professional Excel output | |
| - Multiple analysis sheets | |
| """) | |
| with gr.Row(): | |
| output_text = gr.Textbox( | |
| label="Processing Results", | |
| lines=12, | |
| interactive=False | |
| ) | |
| with gr.Row(): | |
| excel_output = gr.File( | |
| label="📊 Download Excel File" | |
| ) | |
| process_btn.click( | |
| fn=process_pdfs, | |
| inputs=[pdf_input], | |
| outputs=[excel_output, output_text] | |
| ) | |
| gr.Markdown(""" | |
| --- | |
| ### 💡 Tips | |
| - Ensure PDF files are readable and not scanned images | |
| - Use descriptive filenames (e.g., "Offering_Memorandum.pdf") | |
| - Processing may take 30-60 seconds depending on file sizes | |
| """) | |
| # Launch the app | |
| demo.launch(share=False) |