Model Standards — the one-page institutional house style

Apply these to every workbook. Half the job of a credit analyst is producing models a colleague can audit in five minutes.

1. Color code (font color)

• Blue = input / hardcoded assumption
• Black = formula / calculation
• Green = link to another sheet
• Red = warning / failed check

A 3-line color legend sits on every Assumptions tab.

2. Inputs entered once

Every assumption lives once, on the Assumptions tab. Everything downstream links to it. Changing an assumption changes the whole model.

3. No hardcoded numbers inside formulas

Never type a number inside a formula — reference an Assumptions cell. Anchor with $ correctly so one formula copies across a row/grid without drifting.

4. One consistent formula per row

The same formula copies cleanly across each calc row. No pattern breaks mid-row. If period 13 needs different logic, drive it with a flag, not a different formula.

5. Corkscrew / BASE for balances

Beginning + Additions − Reductions = Ending, and Ending links to the next period's Beginning. Never recompute a balance from scratch each period.

6. Timing via 1/0 flag rows

An I/O-period flag, an amort-period flag, a payoff flag — multiply the flag into the calc. One formula, correct timing.

7. Live Checks block

On the Model or Output tab, conditionally formatted green (ok) / red (fail). At minimum verify: Sources = Uses; sum of principal repaid = original balance; ending balance at maturity = balloon (or 0); DSCR > 0; debt yield > 0.

8. Avoid volatile functions

Prefer INDEX over OFFSET/INDIRECT; avoid NOW/RAND. Volatiles recalc on every change, slow large models, and break audit trails. INDEX(range, n) gives you a dynamic pick without volatility.

9. Day-count discipline

• Actual/360 for commercial-mortgage interest accrual (floating/IO/construction).
• 30/360 for level-payment amortization (PMT/IPMT/PPMT).
• Actual/365 is what XIRR/XNPV use to discount dated cash flows. Mixing these up is the most common way to be quietly wrong.

10. Formatting & layout

$#,##0 dollars, 0.00% rates, 0.00x multiples/coverage, dates mmm-yy or m/d/yyyy. Freeze panes below headers. Title every tab. Tab order: Instructions → Assumptions → Model → Output.