Natural language excel pro forma creation is the process of instructing an AI to build financial models using plain English descriptions rather than manual formula construction. This approach requires precise prompt engineering—defining deal parameters, numerical assumptions, and structural logic—to generate accurate institutional-grade models without writing a single formula yourself.
Relevant Articles
- Mastering this skill requires understanding the Specification framework
- See our complete workflow: How to Get AI to Build Excel Models
- Understand the limitations: Why ChatGPT Can't Build Pro Formas
Meta Description: Natural language excel pro forma: Precise prompt engineering techniques to generate institutional-grade financial models. Includes syntax examples and iteration strategies.
Working Example: Project "Ironwood"
To demonstrate effective natural language prompting, we'll reference a specific deal throughout this article:
This specific scenario will anchor every prompt example and syntax pattern in this guide.
Writing Clear Deal Descriptions
The first 200 words of your prompt determine model quality. Most analysts treat AI like a search engine—they ask vague questions. You need to provide crisp, unambiguous deal parameters that remove interpretative latitude.
Start every prompt with three anchor statements:
Asset identification: "I am modeling a [quantity] [asset type] located in [market]." Not "some properties" or "a portfolio"—the AI needs to understand scale and asset class from sentence one.
Transaction economics: "Purchase price is [amount]. Total equity raised is [amount] at [LTV]%. The equity stack is [LP%] LP and [GP%] GP." These four numbers—price, equity, loan-to-value, and split—are the foundation of every calculation that follows. If any of these are ambiguous, the model will compound errors.
Temporal boundaries: "The hold period is [X] years. [Describe exit strategy or refinance timing]." The AI must know when cash flows terminate and when major liquidity events occur.
For Project Ironwood, the opening would be:
"I am modeling a 3-property industrial warehouse portfolio located in Dallas-Fort Worth, TX. Purchase price is $18,500,000. Total equity raised is $5,550,000 at 70% LTV. The equity stack is 90% LP and 10% GP. The hold period is 5 years with full asset sale at exit."
This 56-word opening eliminates 90% of potential misinterpretations. You've defined what, where, how much, and when.
Specification (The Ambiguity Gap)
AI models don't infer—they guess. Every undefined term in your prompt creates a branching path where the AI picks an interpretation. In our experience reviewing hundreds of AI-generated models, 80% of errors stem from specification gaps, not computational mistakes.
Specification is the practice of explicitly defining every variable, constraint, and calculation method before asking the AI to build anything. It's the meta-skill that separates functional models from broken ones.
The ambiguity gap manifests in three forms:
Undefined terms: If you say "build a waterfall," the AI doesn't know if you mean lookback IRR, deal-by-deal IRR, whole-dollar allocation, or fractional allocation. It will pick one. You won't know which until you review the formulas.
Missing constraints: If you say "the GP gets a promote above 15% IRR," the AI doesn't know if that's a hurdle rate (no catch-up) or a split threshold (with catch-up). The difference changes the formula structure entirely.
Implicit assumptions: If you say "model a refinance in Year 3," the AI doesn't know if you're pulling out equity, paying down principal, or both. It doesn't know if refinance proceeds go through the waterfall or only to the LP.
The solution is to adopt a specification checklist before writing any prompt. For every financial concept in your model, answer: What does this term mean in this deal? How is it calculated? What happens in edge cases?
When building institutional-grade models with AI, you cannot skip this step. Specification is not optional—it's the foundation.
For Project Ironwood, specifying the preferred return requires three statements:
"The preferred return is an 8% annual compounding hurdle. It accrues on the LP's unreturned capital balance. If Year 2 operating cash flow is insufficient to pay the full accrued pref, the unpaid amount carries forward to Year 3."
Without these three sentences, the AI will invent a treatment. Specification means leaving nothing to interpretation.
Specifying Numerical Assumptions
Numbers anchor the model. Every formula the AI writes will reference the assumptions you define. If your assumption block is vague or incomplete, the entire downstream calculation layer breaks.
You must specify six categories of assumptions for any real estate natural language excel pro forma:
Acquisition assumptions: Purchase price, closing costs (as percentage or dollar amount), equity structure, debt terms (rate, term, amortization, IO period if applicable).
Operating assumptions: Year 1 NOI, annual NOI growth rate, property management fee percentage, capex reserve (annual dollar amount or percentage of revenue), and any step changes (e.g., "NOI increases 15% in Year 3 due to lease-up").
Exit assumptions: Sale price (multiple of Year 5 NOI or explicit dollar amount), sale closing costs (percentage), debt payoff amount (if not full term).
Waterfall structure: Return of capital sequence, preferred return rate and accrual method, IRR hurdle thresholds, promote percentages at each tier, catch-up provisions (if any).
Debt service: Monthly or annual payment frequency, interest rate, any rate step-ups, prepayment penalties (if relevant to refinance scenario).
Reserves and fees: Acquisition fee (percentage), asset management fee (annual percentage of equity or cost basis), disposition fee (percentage of sale price).
For Project Ironwood, the complete assumption specification is 180 words:
"Acquisition: Purchase price $18,500,000. Closing costs 2.5% of price. Total equity $5,550,000 (90% LP = $4,995,000, 10% GP = $555,000). Debt $12,950,000 at 70% LTV, 5.5% fixed rate, 25-year amortization, full-term loan.
Operating: Year 1 NOI $1,240,000. NOI grows 3% annually. Property management 4% of revenue. Capex reserve $55,000 per year.
Exit: Sale at end of Year 5 for 15x Year 5 NOI. Sale closing costs 2%. Debt pays off at sale.
Waterfall: Tier 1 returns capital to LP and GP pro rata. Tier 2 pays 8% annual compounding preferred return to LP only, on unreturned LP capital. Tier 3 splits remaining cash 90/10 LP/GP with no catch-up.
Debt Service: Monthly payments of principal and interest.
Fees: Acquisition fee 1% of equity to GP. No asset management fee. Disposition fee 1% of sale price to GP."
This block removes all guesswork. Every variable the AI needs is defined.
Describing Model Structure
Once assumptions are specified, you must describe how the model organizes and sequences calculations. Structure is not the same as logic—structure is the architecture of tabs, blocks, and data flow.
In institutional modeling, we use a three-layer structure: Inputs, Calculations, Outputs. Your natural language prompt must tell the AI to build this way, or it will create a single-tab spaghetti sheet where formulas reference cells 40 rows away.
Inputs tab structure: "Create an Inputs tab with four sections: Acquisition Assumptions, Operating Assumptions, Exit Assumptions, Waterfall Structure. Use named ranges for all key inputs. Place each section in a separate block with clear headers."
Calculations tab structure: "Create a Calculations tab with annual columns for Years 0-5. Build these blocks in sequence: Sources & Uses, Debt Schedule, Operating Cash Flow, Equity Distributions. Ensure each block references only Inputs tab or prior blocks—no circular references."
Outputs tab structure: "Create an Outputs tab showing LP and GP cash flows by year, cumulative preferred return accrual, total distributions, and IRR for each party. Calculate IRRs using Excel XIRR function with transaction dates."
For Project Ironwood, the structural prompt is:
"Build a three-tab model. Inputs tab: Four assumption blocks as described above, with named ranges for Purchase_Price, Equity_LP, Equity_GP, NOI_Y1, NOI_Growth, Pref_Rate, Exit_Multiple. Calculations tab: Annual columns Years 0-5. Block 1 is Sources & Uses at acquisition. Block 2 is Debt Schedule showing monthly payments aggregated to annual rows. Block 3 is Operating Cash Flow: NOI minus debt service minus capex. Block 4 is Equity Distributions: operating cash flow and sale proceeds through the two-tier waterfall. Outputs tab: LP and GP annual cash flows, IRR calculations."
This 120-word structural map ensures the AI builds a model you can audit. Without it, you'll receive a chaotic single-sheet model where changing one input breaks 15 formulas.
When building AI-generated Excel models, structure dictates maintainability. A well-structured model can be verified in 10 minutes. A poorly structured model takes an hour to unravel.
Iterating with Follow-Up Prompts
No first-draft prompt is perfect. You will always need to refine the model after reviewing the AI's initial output. The key is knowing what to fix and how to phrase corrections without restarting from scratch.
After the AI generates a model, open it and check three things before writing a follow-up prompt:
Formula consistency: Do the waterfall formulas correctly reference the LP and GP equity balances? Does the preferred return accrue only on LP capital? Spot-check five formulas. If they're wrong, your follow-up prompt must specify the exact formula logic.
Edge case handling: What happens in Year 2 if operating cash flow is negative? Does the model correctly show zero distribution, or does it show an error? If the model breaks under stress tests, your follow-up prompt must address boundary conditions.
Formatting and clarity: Are columns labeled clearly? Are negative numbers formatted as negatives (not positive with a minus sign in the formula)? Is currency formatted as currency? Cosmetic issues matter for usability.
For Project Ironwood, after the AI's first draft, we tested Year 3 by manually changing NOI to zero. The model returned a #DIV/0 error in the waterfall. The corrective follow-up prompt was:
"In the Equity Distributions block, add a check: if Operating Cash Flow in a given year is less than or equal to zero, set distributions to zero for that year and carry forward unpaid preferred return to the next year. Do not divide by zero. Ensure the preferred return accrual tracker shows cumulative unpaid pref."
This 60-word correction fixed the edge case without rebuilding the model. Effective iteration is surgical—you target the specific broken logic, not the entire structure.
The iteration pattern is: Generate → Test → Identify the failure point → Specify the fix → Regenerate only the affected section. If you're regenerating the entire model on every iteration, your prompts lack precision. Tighten your specifications.
Examples of Effective Prompts
Seeing complete, functional prompts is the fastest way to learn syntax patterns. Below are three full prompts for common scenarios. Note the level of detail—every number, every sequence, every constraint is explicit.
Example 1: Ground-Up Development Model
"I am modeling a ground-up multifamily development in Austin, TX. Total development cost is $42,000,000. Equity raised is $16,800,000 (40% of cost) split 85% LP and 15% GP. Debt is $25,200,000 construction loan at 60% LTC, interest-only at 7.5%, converts to permanent debt at stabilization.
Construction: 18-month construction period. Draw equity and debt pro rata each month. No NOI during construction—only interest expense on drawn debt.
Stabilization: Month 19, property stabilizes. Year 1 stabilized NOI is $2,940,000. NOI grows 3% annually thereafter.
Hold: 5 years post-stabilization. Exit at 18x Year 5 NOI.
Waterfall: Tier 1 returns all capital to LP and GP pro rata. Tier 2 pays 12% annual compounding preferred return to LP on unreturned LP capital. Tier 3 splits remaining cash 70/30 LP/GP.
Build a model with monthly periods during construction, annual periods post-stabilization. Show cash flow, IRR, and equity multiple for LP and GP."
Example 2: Refinance and Hold
"I am modeling a 200-unit value-add multifamily property in Phoenix, AZ. Purchase price is $38,000,000. Initial equity is $11,400,000 at 70% LTV, split 92% LP and 8% GP. Initial debt is $26,600,000 at 5.25%, 30-year amortization.
Operating: Year 1 NOI $2,280,000. NOI grows 4% annually due to unit renovations.
Refinance: End of Year 3, refinance at 65% LTV based on Year 3 NOI at a 5.5% cap rate. New loan is 4.75% rate, 30-year amortization. Refinance proceeds net of old loan payoff go through waterfall as if they were a partial sale.
Exit: Sell end of Year 7 at 5.25% cap on Year 7 NOI.
Waterfall: Tier 1 returns capital. Tier 2 pays 9% non-compounding preferred return annually to LP. Tier 3 splits remaining 80/20 LP/GP.
Show annual cash flows, cumulative pref paid, and IRRs pre- and post-refinance."
Example 3: Portfolio Rollup
"I am modeling a 5-property industrial portfolio. Each property has distinct NOI and separate debt. Model each property on its own tab, then roll up to a consolidated Equity Distribution tab.
Property A: $12M purchase, $1.08M NOI Y1, 70% LTV at 5.5%.Property B: $8M purchase, $680K NOI Y1, 65% LTV at 5.75%.Property C: $15M purchase, $1.275M NOI Y1, 75% LTV at 5.25%.Property D: $10M purchase, $850K NOI Y1, 70% LTV at 5.5%.Property E: $6M purchase, $510K NOI Y1, 60% LTV at 6.0%.
All NOI grows 2.5% annually. All properties sell end of Year 6 at 6.5% exit cap. Total equity raised is $15,300,000, split 88% LP and 12% GP.
Waterfall: Applied at portfolio level on consolidated cash flow. Tier 1 returns capital. Tier 2 pays 8% compounding pref to LP. Tier 3 splits 85/15 LP/GP above 15% IRR, 70/30 LP/GP above 20% IRR.
Create six tabs: one per property with its own cash flow, one consolidated Equity Distribution tab. Show LP and GP IRR at portfolio level."
These three examples demonstrate the syntax density required for AI to succeed. Each prompt is 150-200 words and specifies every material variable. If your prompts are shorter than this, you're underspecifying.
Understanding why general-purpose AI tools fail at pro formas clarifies why this level of detail is necessary. Generic models assume generic structures. Institutional models require institutional specificity.
Testing Your AI-Generated Model
After the AI delivers a model, you must verify it before using it in any real context. AI does not self-check. It will confidently generate incorrect formulas if your prompt was ambiguous.
Run three verification tests on every AI-generated natural language excel pro forma:
Zero Test: Set all operating cash flows to zero. The waterfall should return zero distributions (not errors). The preferred return tracker should show accrued but unpaid pref. If the model crashes, the formulas lack error handling.
Double Test: Double the equity amount in Inputs. Every equity-dependent formula should reflect the new amount. If LP distributions stay the same, the formulas are hardcoded, not dynamic.
Exit Multiple Test: Change the exit cap rate or multiple by 50%. The sale proceeds and distributions should update proportionally. If IRRs don't change, the XIRR formula is broken.
For Project Ironwood, we ran the Zero Test by setting Year 2 and Year 3 NOI to $0. The model correctly showed zero distributions in those years, and the preferred return tracker accumulated $799,600 in unpaid pref by Year 4. When Year 4 NOI returned to positive, the model paid the accumulated pref before any other distributions. This confirmed the accrual logic was correct.
If your model fails any of these three tests, return to the AI with a corrective prompt specifying the exact failure. Do not manually fix formulas—your goal is to teach the AI to generate correct logic from the start.
Next Steps: From Prompt to Production Model
You now have the syntax framework to generate institutional-grade models using natural language. The skill is not in asking the AI to "build a pro forma"—it's in specifying the deal with enough precision that the AI has no interpretative latitude.
The three non-negotiables:
- Define all numerical assumptions explicitly (no implied values).
- Specify structural logic (tab architecture, calculation sequence, formula relationships).
- Test the output with edge cases before trusting it.
If you follow these rules, natural language excel pro forma creation becomes a reliable workflow. If you skip any step, you'll generate a model that looks correct but calculates incorrectly.
For deals with complex promote structures or multi-stage waterfalls, you'll need to combine this prompt engineering skill with deeper understanding of how AI builds Excel models and when to override AI-generated logic with manual adjustments.
The future of financial modeling is not "AI does everything automatically." It's "you specify precisely, AI executes quickly, you verify rigorously." Master specification, and you control the output.
