Getting AI to build Excel models requires clear specification of model requirements through structured prompts that define deal parameters, calculation logic, and output formats. The workflow involves: describing what you need built (financial structure, timeframes, assumptions), providing numerical inputs, generating the model, reviewing formulas for accuracy, verifying calculations match your expectations, and iterating until the output meets professional standards.
Building a specific modeling? See our [How to Build RE Pro Forma Guide] or [What Is AI-Generated Excel Modeling?] to learn the basics.
What AI Can and Cannot Build
AI builds standard financial structures reliably. Income statements, cash flow projections, balance sheets, DCF models, budget templates, debt schedules, basic waterfalls—these have abundant training examples. The AI generates them with 80-90% formula accuracy on first attempt. An analyst requesting "Build a 5-year P&L with revenue, COGS, operating expenses, and net income" receives a functional model in seconds.
Multi-tab models with linked calculations work well. The AI handles models with 5-10 tabs where earlier tabs feed later tabs: Assumptions → Revenue Model → Expense Model → P&L → Cash Flow → Balance Sheet → Returns. Cross-sheet references generate correctly. Changes to Assumptions propagate through dependent tabs.
Standard financial functions execute properly. SUM, AVERAGE, IF statements, VLOOKUP, IRR, NPV, PMT—the AI applies these correctly in appropriate contexts. A debt schedule uses PMT for payment calculation. An IRR calculation references the correct cash flow range and uses XIRR for irregular timing. These functions appear in millions of training examples, so the AI replicates them accurately.
Time-series projections apply growth formulas consistently. If the prompt specifies "3% annual revenue growth," the AI generates =PriorYear*(1+0.03) across all projection years. The formula structure remains consistent. The growth rate references the correct assumption cell using absolute references ($B$5), preventing errors when formulas copy.
AI struggles with highly customized logic. A waterfall with 7 tiers, variable splits based on multiple IRR thresholds, lookback provisions, and GP catch-up calculated differently for different equity classes—this level of complexity exceeds most AI capabilities. The model will generate something, but formula errors are likely. Manual review and correction required.
Circular references cause problems. Models where Calculation A depends on Calculation B, and Calculation B depends on Calculation A, create circularity. Excel handles this through iteration, but AI-generated models often fail to implement circular logic correctly. A debt schedule where interest depends on ending balance and ending balance depends on interest paid may generate incorrectly unless the AI explicitly recognizes and solves the circularity.
Non-financial calculations rarely work. If you need engineering calculations (structural load analysis), statistical models (regression analysis with hypothesis testing), or scientific formulas (chemical reaction kinetics), AI trained on financial data will not generate correctly. The AI lacks training examples for these domains. Stick to financial models where the AI has deep pattern knowledge.
Industry-specific nuances require explicit specification. If real estate development models in your firm always calculate construction interest using average outstanding balance (not ending balance), tell the AI: "Calculate construction interest on average outstanding balance each period." If your private equity models always calculate management fees on committed capital (not invested capital), specify that. The AI cannot infer firm-specific conventions without instruction.
The Specification Framework (Prompting 2.0)
Specification is not a casual description. A vague prompt—"Build me a financial model"—produces a generic, likely unusable output. Effective specification treats the AI as a junior analyst who needs detailed instructions. You would not tell a junior analyst "build a model" and expect a correct result. You provide specifics: deal structure, calculations required, output format. Do the same with AI.
Define the model type and purpose. Start with: "Build a [model type] for [purpose]." Examples: "Build a DCF valuation model for a SaaS acquisition," "Build a real estate pro forma for a multifamily value-add investment," "Build a project finance model for a solar farm development." This primes the AI to apply relevant templates.
Specify the time horizon. "5-year projection," "10-year hold period," "monthly granularity for Year 1 then annual thereafter," "construction period of 18 months followed by 15-year operations." Time structure affects how the AI sets up columns and formula logic.
Provide all numerical inputs upfront. List: purchase price, equity amount or percentage, debt amount or LTV, interest rate, amortization period, revenue assumptions, expense assumptions, growth rates, exit assumptions, return hurdles. The more numbers you provide, the less the AI must assume or request later.
Describe calculation logic for non-standard items. If operating expenses escalate at different rates by category (property taxes at 2.5%, insurance at 4%, repairs at CPI), state that. If debt has an interest-only period followed by amortization, specify: "Interest-only for 3 years, then 25-year amortization." If the waterfall has a GP catch-up provision, explain it: "After LP receives pref, GP catches up to achieve 20% overall split, then remaining cash splits 80/20."
Specify output requirements. "Include a sensitivity table varying exit cap rate and rent growth," "Create a summary tab showing IRR, equity multiple, and cash-on-cash return," "Add a sources and uses table," "Format all currency as thousands ($1,250K not $1,250,000)," "Use tab names: Inputs, Calcs, Outputs."
Reference examples if available. "Build a model similar to the [example file] but for a different property type," or "Use the same waterfall structure as our last deal but with 9% pref instead of 8%." If you can upload a reference file or provide a link, the AI learns your conventions faster.
Iterate with follow-up prompts. If the first generation is 80% correct, refine with: "Change the debt amortization to 20 years instead of 25," "Add a renovation budget tab showing $15K per unit for 60% of units," "Move the assumptions section to column B." Each refinement improves the model incrementally.
Preparing Your Inputs
Organize inputs in a structured format before prompting. Create a simple list:
Deal Parameters:
- Property: Industrial warehouse portfolio, 4 buildings, 380,000 SF total
- Location: Phoenix, AZ
- Purchase Price: $48,000,000
- Acquisition Costs: 3% of purchase price
Financing:
- LTV: 65%
- Interest Rate: 6.25%
- Amortization: Interest-only for 2 years, then 25-year amortization
- Loan Fees: 1.5% of loan amount
Operating Assumptions:
- Current Rent: $8.50/SF triple-net
- Occupancy: 92%
- Rent Growth: 2% annually
- Operating Expenses: $1.25/SF (triple-net, tenant pays most expenses)
- Capex Reserve: $0.40/SF annually
Exit:
- Hold Period: 7 years
- Exit Cap Rate: 6.5%
- Sale Costs: 2.5% of sale price
Returns:
- Equity Split: 90% LP / 10% GP
- Preferred Return: 8% annually to LP, compounding
- Promote: Above 15% IRR, split shifts to 70% LP / 30% GP
This structure makes it easy to copy-paste into a prompt. The AI extracts each parameter and assigns it to the correct model component.
Convert qualitative descriptions to quantitative inputs. "Moderate rent growth" is ambiguous. Does moderate mean 2%, 3%, 5%? Specify: "2.5% annual rent growth." "High leverage" could mean 70%, 75%, 80% LTV. Specify: "75% LTV." Remove ambiguity.
Gather data in advance. If you need market rent comps, pull them before prompting. If you need historical financials (trailing 12-month NOI), have that ready. If you need tenant lease schedules, compile them. Providing complete data upfront prevents back-and-forth clarifications.
Use consistent units. If some inputs are monthly (monthly rent: $15,000) and others annual (annual NOI: $2.5M), note this: "Monthly rent per unit: $1,500. Annual NOI: $2.5M." The AI converts units appropriately if told which inputs use which timeframe.
Specify defaults for missing data. "If I do not provide a value, assume: 7-year hold period, 3% expense growth, 25-year debt amortization, 8% LP preferred return." The AI fills gaps with your defaults instead of its generic assumptions.
Reviewing AI-Generated Output
Download and open the file. Do not trust the AI blindly. Even well-specified prompts occasionally produce errors. The first step is visual inspection: do the tabs make sense? Are row labels correct? Are there obvious formula errors (#REF!, #DIV/0!)?
Check the Assumptions tab first. Verify all inputs match your specifications. Purchase price correct? Interest rate correct? Hold period correct? If the AI misinterpreted a parameter (e.g., entered 6.25 as 6.25% when you meant 6.25% interest rate → 0.0625), correct it. Check that assumption cells use values, not formulas (unless you specified calculated assumptions).
Trace key formulas. Pick 5-10 critical cells and inspect their formulas:
- Year 2 revenue: Does it reference Year 1 and apply the growth rate correctly?
- Year 1 NOI: Does it calculate as Revenue minus Operating Expenses?
- Year 3 debt balance: Does it reduce by principal payments from prior years?
- IRR calculation: Does it reference the correct cash flow range (initial equity outflow through final year sale proceeds)?
Test for consistency. Year 2 through Year 7 revenue formulas should be structurally identical (only cell references change). If Year 2 uses =B10*(1+$B$5) but Year 4 uses =D10+$B$5 (addition instead of multiplication), an error exists.
Verify cross-sheet references. If the Cash Flow tab references NOI from the Pro Forma tab, check: is it referencing the correct cell? Does ='Pro Forma'!B50 point to NOI, or did the AI mistakenly reference Gross Income or some other row?
Check aggregation ranges. A formula =SUM(B10:B16) should sum exactly the intended rows. If the data extends to B17, the sum misses a row. If B17 is blank, no harm, but if B17 contains a value, the sum is wrong. Verify SUM, AVERAGE, IRR, NPV ranges include all relevant data.
Test edge cases. Change an assumption to an extreme value. Set rent growth to 50%. Does the model recalculate without errors? Set LTV to 0% (all-equity deal). Does the debt schedule correctly show zero debt? These stress tests reveal formula fragility.
Compare outputs to expectations. If you know the property's trailing NOI is $3.2M and you assumed 0% growth for Year 1, does the model show ~$3.2M NOI in Year 1? If not, something is wrong upstream (revenue, expenses, or vacancy assumption). Sanity-check outputs against what you know about the deal.
Verification (Auditing the Model)
Run a zero test. Set all variable inputs to zero and verify outputs zero out (or reach expected baseline values). Example: Set rent growth to 0%, expense growth to 0%, exit cap equal to entry cap. The model should show flat NOI, stable property value, and returns driven purely by debt paydown and cash flow. If IRR shows 25% under these assumptions, something is double-counting or miscalculating.
Test return calculations manually. Take the cash flow series from the model. Copy it into a fresh Excel sheet. Calculate IRR independently using XIRR. Compare to the model's IRR output. They should match exactly. If they differ, the model's IRR formula references the wrong range or includes incorrect dates.
Verify balance sheet balances. If the model includes a balance sheet, check: Assets = Liabilities + Equity for every period. This identity must hold. If Year 3 shows Assets = $50M and Liabilities + Equity = $49.5M, the balance sheet has an error (likely missing an accrual or incorrect retained earnings calculation).
Check debt schedule logic. Beginning balance + draws - principal payments = ending balance for each period. If this equality fails, the debt schedule is wrong. Verify interest calculations: Interest = Beginning Balance × Interest Rate (or Average Balance × Interest Rate, depending on convention).
Trace waterfall distributions. Manually calculate distributions for one period. If total distributable cash is $2.5M, LP pref is $400K, and the remaining $2.1M splits 90/10, verify the model shows: LP receives $400K (pref) + $1.89M (split) = $2.29M. GP receives $210K (split). Total = $2.5M. Confirm the model's formulas produce these values.
Validate cash flow statement ties. If the model has a full 3-statement structure, verify: Cash Flow from Operations + Cash Flow from Investing + Cash Flow from Financing = Change in Cash Balance. The ending cash balance on the cash flow statement should equal the cash balance on the balance sheet.
Sensitivity analysis check. If the model includes a sensitivity table, verify it works. Change the input variables manually (e.g., set exit cap to 6.0% instead of 6.5%) and confirm the output updates correctly. Then confirm the sensitivity table shows the same output at the 6.0% entry.
Best Practices for AI Model Generation
Start simple, add complexity iteratively. Generate a basic structure first: "Build a 7-year pro forma with revenue, expenses, NOI, debt service, and cash flow." Review it. Once the foundation is correct, add: "Now add an LP/GP waterfall with 8% pref." Review. Then: "Add a sensitivity table for IRR varying exit cap and rent growth." This incremental approach isolates errors. If something breaks, you know it was introduced in the last step.
Save iterations. After each refinement, download and save the file with a version number: Model_v1.xlsx, Model_v2.xlsx. If a later change breaks something, you can revert to a working version without starting over.
Document your prompts. Keep a log of what you asked for and what the AI generated. If you return to the model weeks later and need to modify it, you will remember what instructions produced the current structure. You can prompt: "Use the same structure as before but for a different property."
Use named ranges for key inputs. After generation, define names for critical assumption cells: "Purchase_Price" = Assumptions!B5, "Interest_Rate" = Assumptions!B8. This makes formulas more readable and resilient to row/column insertions. You can manually add named ranges post-generation, or prompt the AI: "Use named ranges for all assumption cells."
Standardize firm conventions through templates. If your firm has specific conventions (assumption cells always in column B, tabs always named Inputs/Calcs/Outputs, currency formatted with no decimals), document these in a "style guide" prompt. Include it with every model request: "Follow our firm style guide: [paste conventions]." The AI applies your standards consistently.
Test with known-good benchmarks. Occasionally, generate a model for a deal you have already underwritten manually. Compare the AI model to your manual model. Do they produce the same IRR, equity multiple, cash flows? If yes, you have high confidence in the AI's formulas. If no, investigate discrepancies and identify any systematic errors the AI makes.
Review, do not blindly accept. Even if the AI generates correctly 90% of the time, that 10% error rate is unacceptable for investment decisions. Treat AI output as a first draft that requires professional review, not a final deliverable.
