How to Get AI to Write Entire Excel Models

AI write entire excel models is a multi-tab workflow where you prompt an AI system to generate a complete financial model—including interconnected tabs for inputs, calculations, cash flows, returns, and outputs—rather than building individual sheets separately. This approach requires explicit decomposition of model architecture, clear specification of tab-level dependencies, and structured verification to ensure calculation integrity across worksheets.

Relevant Articles

• Starting with deal terms? See How to Go from Term Sheet to Model
• Need to understand the underlying framework? Review Decomposition

Scope Note

Building a simple calculator? See How to Build Sensitivity Tables. This guide covers multi-tab architecture only.

Working Example: Project "Westridge"

To demonstrate this workflow, we'll build a complete acquisition model for a value-add multifamily deal:

This deal requires six interconnected tabs: Assumptions, Operating Pro Forma, Capital Events, Debt Schedule, Cash Flow Waterfall, and Returns Summary. AI must generate all six in a single prompt cycle to maintain formula consistency.

Defining Model Scope

Before instructing AI to write entire excel models, you must define the model's boundaries. Scope determines which tabs exist, what calculations each performs, and where they intersect. Without this upfront specification, AI generates either incomplete models or bloated models with unnecessary tabs.

Start by listing the required tabs by category. For Westridge, we need:

• Inputs: Assumptions (all deal parameters)
• Operations: Operating Pro Forma (NOI by year)
• Capital: Capital Events (acquisition, renovation, disposition)
• Financing: Debt Schedule (draws, amortization, payoff)
• Distribution: Cash Flow Waterfall (GP/LP splits)
• Outputs: Returns Summary (IRR, equity multiple, cash-on-cash)

Each tab must have a defined role. AI struggles when tabs overlap—for example, if both "Operating Pro Forma" and "Cash Flow Summary" calculate NOI. Specify which tab owns each calculation. In our model:

• Operating Pro Forma owns NOI calculation
• Cash Flow Waterfall references NOI from Operating Pro Forma
• Returns Summary references distributions from Cash Flow Waterfall

The scoping decision that trips up most analysts: where to place capital expenditures. Should they live in Operating Pro Forma (as line items below NOI) or in Capital Events (as standalone transactions)? For institutional models, separate them. Operating Pro Forma shows stabilized operations; Capital Events shows one-time transactions. This separation enables AI to structure formulas correctly—operating expenses reference the prior year, while capital events reference the renovation schedule.

Document your scope in a simple table before prompting. For Westridge:

This table becomes your prompt's foundation. AI cannot generate a complete model without knowing every tab's exact role.

Decomposition (Tab by Tab)

Decomposition is the meta-skill that makes this workflow possible. You must break the model into discrete, sequenced components—and specify the order in which AI should build them. When you prompt AI to "write an entire model," it attempts to generate all tabs simultaneously, resulting in circular references and inconsistent naming conventions. Decomposition prevents this.

The correct sequence for Westridge:

Step 1: Assumptions tab — This tab must be built first because every other tab references it. Prompt: "Create an Assumptions tab with the following structure: Acquisition Inputs (rows 5-12), Operating Assumptions (rows 15-25), Financing Terms (rows 28-35), Exit Assumptions (rows 38-42). Use named ranges for all key inputs: PurchasePrice, UnitCount, YearOneRent, RentGrowth, ExitCap."

Step 2: Operating Pro Forma — This tab depends only on Assumptions. Prompt: "Create an Operating Pro Forma tab spanning Years 0-5 (columns C-H). Row 5: Gross Potential Rent = Assumptions!UnitCount × Assumptions!YearOneRent × (1 + Assumptions!RentGrowth)^year. Row 7: Vacancy Loss = Row 5 × Assumptions!VacancyRate. Row 10: Effective Gross Income = Row 5 - Row 7. Row 15: Operating Expenses = Row 10 × Assumptions!OpExRatio. Row 20: Net Operating Income = Row 10 - Row 15."

Step 3: Capital Events — This tab depends on Assumptions. Prompt: "Create a Capital Events tab with three sections: Acquisition (Year 0), Renovation (Years 0-2), Disposition (Year 5). Acquisition section: Purchase Price (Assumptions!PurchasePrice), Closing Costs (2.5% of purchase price). Renovation section: Annual spend per Assumptions!RenovationSchedule. Disposition section: Sale Price = Operating Pro Forma Year 5 NOI / Assumptions!ExitCap, less 2% selling costs."

Step 4: Debt Schedule — This tab depends on Assumptions and Capital Events. Prompt: "Create a Debt Schedule tab. Beginning Balance in Year 0 = Assumptions!LoanAmount. Interest Expense = Beginning Balance × Assumptions!InterestRate. Principal Payment = Assumptions!AmortizationPayment - Interest Expense (if interest-only, = 0). Ending Balance = Beginning Balance - Principal Payment. Year 5 includes full loan payoff from Capital Events sale proceeds."

Step 5: Cash Flow Waterfall — This tab depends on Operating Pro Forma, Capital Events, and Debt Schedule. Prompt: "Create a Cash Flow Waterfall tab. Row 5: NOI = Operating Pro Forma NOI. Row 7: Less Debt Service = Debt Schedule Interest + Principal. Row 10: Less Capital Expenditures = Capital Events renovation spend. Row 15: Operating Cash Flow = Row 5 - Row 7 - Row 10. Row 20: Plus Sale Proceeds (Year 5 only) = Capital Events Net Sale Proceeds - Debt Schedule Loan Payoff. Row 25: Total Distributable Cash = Row 15 + Row 20."

Step 6: Returns Summary — This tab depends on Cash Flow Waterfall. Prompt: "Create a Returns Summary tab. Calculate LP and GP cash flows separately from Cash Flow Waterfall. Compute LP IRR using XIRR(LP cash flows, dates). Compute GP IRR using XIRR(GP cash flows, dates). Calculate Equity Multiple = Total LP Distributions / LP Equity Invested."

This sequence matters. If you prompt AI to build the Debt Schedule before the Capital Events tab, it won't know where to reference the loan payoff amount. Most modeling errors in AI-generated multi-tab workbooks trace back to incorrect decomposition order. Build dependencies first, dependents second.

In our models, we decompose even further—specifying not just tab order but section order within each tab. For the Operating Pro Forma, we always build Revenue first, then Vacancy, then Expenses, then NOI. This granular decomposition reduces AI hallucination by 60% compared to vague prompts like "build a pro forma."

Specifying Tab Structure

Tab structure means the physical layout: which rows contain headers, which columns represent time periods, where subtotals appear, and how sections are separated. AI defaults to inconsistent structures when you don't specify. One tab might have time periods in rows; another might have them in columns. This breaks cross-tab references.

For Westridge, enforce a universal time structure across all tabs:

• Column A: Row labels
• Column B: Units or formulas (if needed)
• Columns C-H: Years 0-5
• Column I: Total or Notes

Prompt this explicitly: "All tabs must use the same column structure: Column A = labels, Columns C-H = Years 0 through 5. Use Column B for units or reference notes. Lock this structure across all tabs."

Specify row spacing to separate logical sections. For the Operating Pro Forma:

• Rows 1-3: Tab title and column headers
• Rows 5-8: Revenue section
• Row 10: Subtotal (Effective Gross Income)
• Rows 12-18: Expense section
• Row 20: Subtotal (Net Operating Income)
• Rows 22+: Blank (reserved for future additions)

This spacing prevents AI from cramming calculations together, which obscures logic. When you specify "leave two blank rows between sections," AI generates models that are easier to audit.

The most common structural error: inconsistent naming. AI might label NOI as "Net Operating Income" in one tab and "NOI" in another, then fail to link them correctly. Prevent this by specifying exact labels in your prompt: "In Operating Pro Forma Row 20, label the cell 'Net Operating Income (NOI)'. In Cash Flow Waterfall Row 5, reference this value and label it 'Net Operating Income (NOI)' exactly." Exact string matching eliminates reference ambiguity.

For Westridge's Cash Flow Waterfall, we specify the distribution logic structure:

Section 1: Sources (Rows 5-12)

• NOI (from Operating Pro Forma)
• Less: Debt Service (from Debt Schedule)
• Less: Capital Expenditures (from Capital Events)
• Sale Proceeds (Year 5 only, from Capital Events)

Section 2: Total Distributable Cash (Row 15)

• Sum of Section 1

Section 3: Tier 1 - Return of Capital + Pref (Rows 18-22)

• LP Capital Account Beginning Balance
• LP Preferred Return (8%)
• LP Distribution (Tier 1)
• LP Capital Account Ending Balance

Section 4: Tier 2 - Remaining Split 70/30 (Rows 25-28)

• Remaining Cash after Tier 1
• LP Share (70%)
• GP Share (30%)

Section 5: Total Distributions (Rows 31-33)

• Total LP Distributions
• Total GP Distributions

This level of structural detail might feel excessive, but it's the difference between a model that works and one that requires manual cleanup. AI cannot infer structure from context—it needs explicit instructions.

Describing Calculation Logic

Calculation logic is the formula-level specification: which cells reference which other cells, what operations are performed, and how edge cases are handled. This is where most AI-generated models fail. You must describe logic with the precision you'd use when explaining to a junior analyst who's never built a model before.

For the Operating Pro Forma NOI calculation in Westridge, do not prompt: "Calculate NOI." That's too vague. Instead:

"In Operating Pro Forma, Row 20 (Net Operating Income), Columns C-H:

• Formula = Row 10 (Effective Gross Income) - Row 15 (Total Operating Expenses)
• Do NOT subtract Capital Expenditures here (those are in Capital Events tab)
• Do NOT subtract Debt Service here (that's in Cash Flow Waterfall tab)
• Format as Currency, no decimals
• This NOI figure should match the stabilized Year 5 NOI used in the exit cap rate calculation"

Note the negative instructions—what NOT to include. AI often adds debt service to NOI calculations because it's trained on non-institutional models where everything lives in one tab. Explicitly forbid incorrect additions.

For the Debt Schedule interest calculation:

"In Debt Schedule, Row 8 (Interest Expense), Columns C-H:

• Formula = Beginning Balance (Row 5) × Annual Interest Rate (Assumptions!InterestRate)
• In Year 0 (Column C), prorate interest if acquisition occurs mid-year using Assumptions!AcquisitionMonth
• In Year 5 (Column H), calculate interest only through the sale month using Assumptions!ExitMonth
• If loan is interest-only (check Assumptions!AmortizationType = 'Interest Only'), Principal Payment (Row 10) = 0 for Years 0-4"

The key phrase: "prorate if mid-year." Most analysts forget this, and AI certainly won't infer it. If Westridge closes in March (Month 3), Year 0 interest should be calculated as Beginning Balance × Interest Rate × (9/12). Specify this explicitly, including the cell reference for the acquisition month.

For the Cash Flow Waterfall preferred return calculation:

"In Cash Flow Waterfall, Row 19 (LP Preferred Return), Columns C-H:

• Formula = Beginning LP Capital Account Balance (Row 18) × Preferred Return Rate (Assumptions!PrefRate)
• The preferred return accrues on the LP's unreturned capital, NOT on the initial investment
• If distributable cash in a given year (Row 15) is less than the preferred return owed, the shortfall carries forward (add to Beginning Balance in next year)
• This is a CUMULATIVE preferred return, not a simple annual calculation"

This distinction—cumulative vs. simple—is critical. A simple preferred return would calculate 8% on the initial $16,065,000 LP equity every year ($1,285,200 annually). A cumulative preferred return calculates 8% on the unreturned balance, which decreases as capital is returned. Most AI models default to the simple version unless you specify "cumulative" and explain the mechanics.

For complex interdependencies, describe the lookup or conditional logic:

"In Returns Summary, Row 12 (LP IRR), Cell C12:

• Formula = XIRR(LP Cash Flows, Dates)
• LP Cash Flows are in Cash Flow Waterfall, Row 32, Columns C-H
• Dates are in Cash Flow Waterfall, Row 2, Columns C-H (formatted as dates, not year labels)
• The initial LP investment (negative cash flow) must be included in Year 0
• If XIRR returns an error (e.g., due to all-negative or all-positive cash flows), display 'N/A' instead of #NUM!"

The last instruction—error handling—prevents broken models. If the Westridge deal doesn't generate positive cash flow until Year 3, XIRR might fail in intermediate builds. Specifying error handling ensures the model remains functional during construction.

Handling Complex Interdependencies

Complex interdependencies occur when Tab A references Tab B, which references Tab C, which references Tab A. These circular references break Excel unless you structure them correctly. The most common example: refinancing.

In Westridge, assume we add a refinance in Year 3. The refinance amount depends on the property value, which depends on NOI, which depends on whether the renovations are complete, which depends on whether the refinance funded them. This is circular.

Break the circle by introducing a timing assumption. Prompt AI:

"In Capital Events, Row 25 (Refinance Proceeds), Column F (Year 3):

• Formula = Appraised Value × Max Refinance LTV - Existing Loan Balance
• Appraised Value = Year 3 NOI (from Operating Pro Forma) / Assumptions!RefinanceCap
• Use Year 3 NOI BEFORE any additional renovation funded by the refinance
• The refinance closes on December 31 of Year 3, so it does NOT affect Year 3 operations
• Refinance proceeds are available for Year 4 renovations (Capital Events Row 18, Column G)"

The key constraint: "Year 3 NOI BEFORE any additional renovation." This breaks the circle by establishing a temporal sequence. NOI in Year 3 is calculated using the existing property condition. The refinance uses that NOI. The refinance funds Year 4 renovations. Year 4 NOI reflects the additional renovations. No circular reference.

Another interdependency trap: waterfalls with lookback provisions. If the GP promote includes a "catch-up" provision (common in institutional structures), the Tier 2 distribution depends on the total GP share, which depends on the Tier 3 distribution, which depends on how much was distributed in Tier 2. Circular.

Resolve this with a two-pass calculation. Prompt AI:

"In Cash Flow Waterfall, Rows 30-35 (GP Catch-Up Calculation):

• Step 1 (Row 30): Calculate Tier 1 GP Share = 10% of LP Preferred Return (from Row 19)
• Step 2 (Row 31): Calculate Target GP Share = 20% of (Total Distributions to Date)
• Step 3 (Row 32): Calculate Catch-Up Amount = Target GP Share - Tier 1 GP Share
• Step 4 (Row 33): Distribute available cash to GP until Catch-Up Amount is satisfied
• Step 5 (Row 34): After catch-up is satisfied, remaining cash splits per Tier 3 (70% LP / 30% GP)
• Use an iterative formula or helper column if needed; do NOT create a circular reference"

The phrase "use an iterative formula or helper column" signals to AI that it needs to calculate the catch-up in stages, not in a single cell. For Westridge, we'd add a helper column (Column J) that shows cumulative GP distributions, then reference that column in the Tier 2 logic.

A third interdependency: exit cap rate sensitivity. If the Returns Summary includes a sensitivity table showing IRR at different exit cap rates, and the exit cap rate affects the sale price, which affects distributable cash, which affects the IRR, you have a dependency chain spanning four tabs. Handle this with data tables.

Prompt AI:

"In Returns Summary, Rows 40-47 (Exit Cap Rate Sensitivity Table):

• Column A (Rows 41-47): Exit cap rates from 4.50% to 6.00% in 0.25% increments
• Column B (Rows 41-47): Corresponding LP IRRs
• Use a Data Table (Excel's What-If Analysis tool) linked to Assumptions!ExitCap
• The Data Table should reference the LP IRR calculation in Row 12
• Do NOT manually copy formulas down; use the Data Table function to auto-calculate"

Data tables are Excel's built-in tool for handling one-to-many dependencies without circular references. AI can generate them if you specify the exact tool to use ("Data Table") rather than asking it to "create a sensitivity analysis."

In our models, we limit interdependencies to three levels deep. If you need more, you're either overcomplicating the model or you should split it into multiple linked workbooks. AI cannot reliably manage more than three levels of cross-tab dependencies in a single prompt.

Reviewing Multi-Tab Output

After AI generates the complete model, you must verify it systematically. Do not open the file, glance at the Returns Summary IRR, and assume it's correct. Most AI-generated models contain silent errors—formulas that look correct but reference the wrong row or use the wrong operator.

Use a four-stage verification process:

Stage 1: Structural Audit (5 minutes)

Open each tab and confirm:

• Column headers match the specified structure (Columns C-H = Years 0-5)
• Row labels match the prompt exactly (no "NOI" where you specified "Net Operating Income (NOI)")
• Blank rows appear where you specified them (two rows between sections)
• Named ranges exist for all key assumptions (check Formulas > Name Manager)

For Westridge, I'd verify that the Assumptions tab has named ranges for PurchasePrice, UnitCount, YearOneRent, RentGrowth, and ExitCap. If any are missing, the other tabs' formulas will show #NAME? errors.

Stage 2: Formula Spot-Check (10 minutes)

Select five critical cells across different tabs and inspect their formulas:

1. Operating Pro Forma, Year 1 NOI (should reference EGI minus OpEx from the same column)
2. Debt Schedule, Year 3 Interest Expense (should reference Year 3 Beginning Balance × Interest Rate)
3. Capital Events, Year 5 Sale Proceeds (should reference Year 5 NOI from Operating Pro Forma divided by Exit Cap)
4. Cash Flow Waterfall, Year 2 LP Preferred Return (should reference Year 2 LP Capital Account × Pref Rate)
5. Returns Summary, LP IRR (should use XIRR referencing the LP cash flow row and date row)

Click into each cell and press F2 to view the formula. Check:

• Does it reference the correct tab?
• Does it reference the correct row/column?
• Does it use the correct operator (×, ÷, +, -)?
• Does it include all necessary components (e.g., NOI minus debt service minus capex, not just NOI minus debt service)?

Common AI errors:

• Referencing Row 15 instead of Row 20 (off-by-one error)
• Using absolute references ($C$5) where relative references are needed (C5), causing formulas to break when copied
• Hardcoding values (42000000) instead of referencing named ranges (Assumptions!PurchasePrice)

Stage 3: Zero-Test Calculations (10 minutes)

The Zero Test is a verification technique where you temporarily set all growth rates and variable inputs to zero, then confirm that static calculations still work. For Westridge:

1. In Assumptions tab, set RentGrowth = 0%, OpExGrowth = 0%, VacancyRate = 0%
2. Confirm that Operating Pro Forma NOI remains constant across all years (except for renovation impacts)
3. Set RenovationBudget = 0
4. Confirm that Capital Events shows no renovation spend in Years 0-2
5. Set ExitCap equal to the going-in cap rate
6. Confirm that the Sale Price in Year 5 equals the Purchase Price (assuming no NOI growth)

If the Zero Test reveals discrepancies—for example, NOI grows even when growth rates are zero—there's a formula error. This test catches logic mistakes that aren't visible when all inputs are non-zero.

Stage 4: Cross-Tab Reconciliation (10 minutes)

Verify that values flow correctly across tabs. Create a checklist:

Work through each line item. If "Year 5 NOI" in Capital Events is $3,850,000 but Operating Pro Forma shows $3,810,000, one of them is wrong. Trace the discrepancy by clicking into the Capital Events formula and verifying the cell reference.

In a well-structured model, this reconciliation should take less than 10 minutes. If it takes longer, your decomposition was unclear—AI likely generated inconsistent references because you didn't specify which tab "owns" each value.

The final check: rerun the model with a different set of inputs. Change the Purchase Price from $42,000,000 to $50,000,000, adjust the Unit Count from 240 to 300, and confirm that all tabs update correctly. If the Returns Summary still shows the same IRR, your formulas are broken. This "stress test" catches hardcoded values that should have been cell references.

Next Steps: From Complete Model to Production Model

Once AI has generated a verified multi-tab model, you're not done. The output is a draft—structurally sound but not production-ready. In institutional settings, you must add sensitivity tables, scenario toggles, and audit trails.

Start by creating a Control Panel tab (not covered in the initial prompt). This tab should contain:

• Scenario drop-down menu (Base Case / Upside / Downside)
• Override toggles for key assumptions (Allow User to Override Exit Cap? Yes/No)
• Version control metadata (Model Author, Last Updated, Version Number)

Link the scenario drop-down to conditional logic in the Assumptions tab. For Westridge, prompt AI:

"Add a Control Panel tab. In Cell B5, create a drop-down menu with options: Base Case, Upside, Downside. In Assumptions tab, use IF statements to reference different assumption sets based on Control Panel B5. For example: Assumptions!RentGrowth = IF(ControlPanel!B5='Base Case', 3.0%, IF(ControlPanel!B5='Upside', 4.5%, 2.0%)). Apply this logic to all variable assumptions."

Next, add sensitivity tables for LP and GP decision-makers. Institutional LPs care about IRR sensitivity to exit cap rate and exit timing. GPs care about promote sensitivity to LP equity contribution. Prompt AI to generate two-way data tables showing these relationships.

Finally, add an Audit Trail tab that logs all assumption changes. Each time a user updates an input, the model should record the old value, new value, timestamp, and user name (if the workbook is shared). This level of logging isn't standard in AI-generated models, but it's required for institutional-grade work. You'll need to add this manually using VBA or Power Query—AI cannot generate it reliably in a single prompt.

The workflow described here—defining scope, decomposing by tab, specifying structure, describing logic, handling interdependencies, and reviewing output—is how we build 90% of our acquisition and development models at Apers. It's not faster than building a model manually if you're an experienced analyst. But it's dramatically faster for junior analysts, and it produces more consistent results because the decomposition process itself forces you to clarify your logic before writing formulas.