About this Series
This five-part series explores the craft of building Excel models with AI, from foundational skills to advanced techniques for developing full financial models.
- Tutorial 0: Introduction
- Tutorial 1: Decomposition
- Tutorial 2: Specification
- Tutorial 3: Iteration
- Tutorial 4: Verification
- Tutorial 5: Context and Collaboration
You know exactly what you want. A clean acquisition model, monthly cash flows, a promote waterfall that splits at an 8% pref and 12% IRR hurdle. You've built dozens of these. So you describe it to the LLM, hit enter, and get back... something that's almost right but fundamentally wrong.
The NOI calculation includes capital expenditures. The waterfall pays the promote before the pref. The time periods are annual when you needed monthly.
What happened?
The LLM did exactly what you asked. The problem is that what you asked and what you meant were two different things.
The Specification Gap
Every time you prompt an LLM to build a model, you're translating from the rich, contextual understanding in your head to flat text on a screen. That translation is lossy. Things you know implicitly — how your firm structures waterfalls, what "NOI" means in your context, whether returns are levered or unlevered — don't make it into the prompt because they feel obvious.
They're not obvious to the LLM. The LLM has seen thousands of models across every convention and style. Without explicit guidance, it's guessing. Often it guesses well. Sometimes it guesses in ways that look right but aren't.
This is the specification gap: the distance between your mental model and what you actually communicate. Closing that gap is the core skill of working with LLMs effectively.
The insidious part is that you often don't notice the gap until you've built on top of a flawed foundation. The numbers look plausible. The structure seems reasonable. It's only when you dig into the formulas — or worse, when your MD digs into the formulas — that you discover the waterfall was paying promote on the wrong base.
The Curse of Expertise
The better you are at real estate finance, the worse this problem can be.
When you've built a hundred models, certain concepts become invisible. You say "cash flow" and your brain fills in: unlevered, before debt service, after reserves, at the property level. You don't specify these things because you don't consciously think about them. They're just what cash flow means.
But to an LLM, "cash flow" could mean any of a dozen things. Free cash flow. Operating cash flow. Cash flow to equity. Cash flow before or after capex. Without specification, the LLM picks one. Often not the one you wanted.
The same problem appears with terms like:
- NOI: Does it include management fees? Replacement reserves? Ground rent?
- Cap rate: Going-in? Exit? Stabilized? Trailing? Forward?
- Return: IRR? Equity multiple? Cash-on-cash? Levered or unlevered?
- Debt service: Interest only? Amortizing? Based on initial balance or current balance?
Each of these terms has a "standard" meaning. The problem is there are multiple standards, and they vary by firm, by asset class, by geography. The LLM knows all of them. It doesn't know which one you mean.
Structural Specification
Beyond terminology, you need to specify how the model should be organized. Structure that feels intuitive to you is arbitrary to the LLM.
Orientation is the most common source of confusion. Do time periods run across columns or down rows? For most real estate models, time goes across (columns) and line items go down (rows). But the LLM might do it differently unless you say so.
A simple statement in your prompt prevents this:
"Time periods should run across columns, with each column representing one month. Line items should run down rows."
Layout matters more than you think. Where do inputs live? Are they on a separate sheet, in a dedicated section at the top, or scattered throughout? How are sections demarcated? What's the visual hierarchy?
If you have preferences — and you probably do — state them:
"All inputs should be in a clearly labeled section at the top of the sheet, with a blue fill to distinguish them from calculations. No inputs should appear below row 20."
Naming conventions can save you hours of cleanup. Specify them upfront:
"Use descriptive row labels in column A. Column headers should show period numbers (1, 2, 3...) in row 1 and dates (Jan-25, Feb-25...) in row 2. Name key ranges using the format 'inp_' for inputs and 'calc_' for calculated values."
Financial Logic Specification
The most consequential specifications are about financial logic. This is where ambiguity creates models that look correct but calculate incorrectly.
Timing conventions are a classic trap. When does a cash flow hit — at the beginning of the period or the end? For an acquisition, is the purchase price a Year 0 cash flow or a Year 1 cash flow? For monthly models, is rent received on the first of the month or the last?
These details change your IRR. Specify them:
"Use end-of-period convention for all cash flows. The acquisition is a time-0 outflow. Operating cash flows begin in period 1. The exit sale occurs at the end of the hold period and is included in that period's cash flow."
Calculation conventions are equally important. Is the annual interest rate divided by 12 for monthly interest, or do you use actual/360? Is the cap rate applied to trailing NOI or forward NOI? Is the loan sized on stabilized NOI or Year 1 NOI?
"Calculate monthly interest as annual rate / 12 applied to beginning-of-month balance. Size the loan at 65% of stabilized value, where stabilized NOI is the Year 3 NOI."
The waterfall deserves special attention because waterfall logic is notoriously easy to get wrong. Don't just describe the splits — describe the mechanics:
"The waterfall distributes available cash in the following priority:
- Return of LP capital contributions until fully returned
- LP preferred return (8% annual, non-compounding) on unreturned capital
- GP catch-up until GP has received 20% of total distributions to that point
- Remaining cash split 80% LP / 20% GP
Calculate preferred return on a monthly accrual basis. Unpaid pref accrues but does not compound. All distributions apply first to accrued unpaid pref, then to return of capital, before any promote is paid."
This level of detail might feel excessive. It's not. Waterfalls are exactly where "almost right" causes the most damage.
Behavioral Specification
A model isn't just formulas — it's a machine that responds to inputs. Specify how you want it to behave.
Handling edge cases prevents errors from surfacing later:
"If occupancy is zero, NOI should be negative (equal to operating expenses), not zero or an error. If the loan amount is zero, all debt-related rows should show zero, not errors. The model should handle a hold period of 1 to 20 years without requiring structural changes."
Flexibility requirements determine how adaptable the model is:
"The number of units should be an input that flows through to all calculations. Changing it should automatically update the rent roll, operating expenses, and valuation. I should be able to run scenarios by changing only cells in the input section."
Circular references need explicit handling:
"This model will have a circular reference between the interest expense and the cash flow available for debt service. Set up the model to use iterative calculation. Include a manual override input that allows me to break the circularity if needed."
Constraint Specification
Sometimes what you don't want is as important as what you do want.
Technical constraints keep the model usable:
"Use only standard Excel functions — no VBA, no macros, no array formulas that require Ctrl+Shift+Enter. The model should work in Excel 2016 and later. Avoid volatile functions like INDIRECT, OFFSET, and NOW where possible."
Auditability constraints make the model reviewable:
"Each formula should be self-contained and traceable. No nested IF statements more than 3 levels deep. If complex logic is required, break it into intermediate calculation rows with labels explaining each step. No hardcoded numbers in formulas — all assumptions should reference input cells."
Structural constraints preserve your sanity:
"Keep the entire model on a single sheet. No hidden rows or columns. Group related line items but don't hide them by default."
A Specification Template
Here's a framework you can adapt for any model. Before you write your first prompt, answer these questions:
Purpose and Context
- What decision will this model support?
- Who will use it besides you?
- What level of precision is required?
Structure
- Time periods: Monthly? Quarterly? Annual? How many?
- Orientation: Time across columns, line items down rows?
- Layout: Where do inputs, calculations, and outputs live?
- Sheets: Single sheet or multiple?
Terminology (define any term that could be ambiguous)
- How is NOI defined?
- What does "cash flow" mean in this context?
- Which return metrics are needed and how are they calculated?
Timing and Conventions
- Beginning or end of period for cash flows?
- How is interest calculated?
- When is the acquisition? The exit?
Behavior
- What inputs should the user be able to change?
- How should the model handle zeros, blanks, edge cases?
- Are there circular references? How should they resolve?
Constraints
- Technical limitations (Excel version, no VBA, etc.)
- Auditability requirements
- Performance requirements
You don't need to include all of this in every prompt. But you should have answered these questions for yourself before you start prompting. The act of working through them surfaces the implicit knowledge you need to make explicit.
Putting It Into Practice
Let's see the difference specification makes. Here's a vague prompt:
"Build me a DCF model for a multifamily acquisition."
The LLM will build something. It'll probably be reasonable. But you'll spend the next hour discovering that it used annual periods when you needed monthly, calculated returns on an unlevered basis when you needed levered, and structured the exit cap rate as an input when you wanted it calculated as a spread to the going-in cap.
Here's a specified prompt:
"Build a 10-year monthly DCF for a 200-unit multifamily acquisition.
Structure: Single sheet. Time periods in columns (120 months plus a month 0 for acquisition). Line items in rows. Input section at top with blue fill. Summary metrics at bottom.
Inputs: Purchase price, going-in cap rate (calculated from purchase price and Year 1 NOI), exit cap spread over going-in, rent per unit per month, annual rent growth, vacancy rate, operating expense ratio, capex reserve per unit per year.
Debt: 65% LTV, 5.5% interest rate, 30-year amortization, interest-only for first 24 months. Monthly debt service calculation based on beginning-of-month balance.
Cash flows: End-of-period convention. Acquisition at month 0. Operating cash flows months 1-120. Exit sale at month 120, calculated as Year 10 forward NOI divided by exit cap rate, less 2% selling costs.
Returns: Calculate levered IRR, equity multiple, and average annual cash-on-cash. IRR should use XIRR with actual dates.
Constraints: No VBA. No volatile functions. All assumptions must trace to labeled input cells."
This prompt takes longer to write. It produces a model that works the first time.
The Paradox of Specification
There's a natural objection to all this: if you have to specify everything in such detail, why use an LLM at all? Isn't the point to describe what you want in plain language and have the AI figure it out?
Partly. But the value of the LLM isn't that it reads your mind. The value is that it can translate a well-specified description into hundreds of correctly-structured formulas in seconds. The specification is the thinking. The LLM does the implementation.
This division of labor is actually powerful. You focus on what matters — the logic, the structure, the financial reasoning — and outsource the mechanical translation to Excel syntax. That's time well spent.
The other benefit: writing a good specification forces you to think through the model before you build it. That clarity pays dividends whether you're working with an LLM or building manually. Many of the errors in financial models aren't Excel mistakes — they're conceptual ambiguities that never got resolved. The discipline of specification resolves them upfront.
Common Specification Failures
Even with the framework above, certain failures recur. Watch for these:
The "standard" trap: Using words like "standard," "typical," or "normal." These mean different things to different people. The LLM will pick an interpretation. It might not be yours.
The inherited assumption: Assuming the LLM remembers something from earlier in the conversation. If it matters, restate it.
The obvious detail: Omitting something because it seems too basic to mention. Whether time goes in rows or columns is basic. It still needs to be stated.
The format afterthought: Specifying logic in detail but leaving formatting vague. Then spending 30 minutes fixing formatting issues.
The one-shot fantasy: Trying to specify everything in a single prompt. Complex models need decomposition. Specify each piece as you build it.
Next in the Series
Specification gets you a solid first draft. But first drafts are never final. Part 3: Iteration covers the art of refinement — how to communicate what's wrong, how to converge efficiently, and how to know when you've pushed iteration far enough.
The goal isn't a perfect prompt that produces a perfect model. The goal is a clear specification that produces a good starting point, followed by focused iteration that gets you to the finish line.
That combination — clear specification, efficient iteration — is what separates people who struggle with LLMs from people who use them to build real models for real deals.
About Apers AI
Apers AI was founded by researchers from Harvard, Yale, and MIT with backgrounds in quantitative asset pricing and institutional real estate. Our mission is to advance the science of capital deployment by applying autonomous agents and machine reasoning to real asset markets.
