A unit audit for CRE debt models: periods, percentages and balances

A clean spreadsheet can conceal an incompatible denominator. Annual NOI divided by a monthly payment, a whole percentage entered as a fraction, or committed debt substituted for funded debt can produce a precise result that answers the wrong question.

Before reviewing a model’s conclusion, audit the units. Each input should identify its currency, time period, percentage convention, balance basis and economic definition. This reference supplies a practical ledger and a set of small examples for testing those choices.

Start with a unit ledger

For the hypothetical property below, assume 10,000 rentable square feet, $36 per square foot of annual base rent, $390,000 of separately established annual underwritten income, and $300,000 of annual debt service. The income figure is not derived solely from the base-rent line; this simplified exhibit assumes other income and expenses have already been reconciled elsewhere.

Annual base rent is 10,000 × $36 = $360,000, or $30,000 per month before concessions, vacancy or other lease adjustments. Multiplying the $36 annual rate by 12 would overstate annual rent twelvefold. A rent-roll column labeled only “rent” is insufficient for automated ingestion.

Test 1: align income and payment periods

Annual coverage is $390,000/$300,000 = 1.30x. Converting both figures to a consistent monthly equivalent gives $32,500/$25,000 = the same 1.30x. Dividing annual income by the monthly payment instead gives 15.60x, an arithmetic result with mismatched periods.

The OCC handbook describes DSCR using NOI and annual debt service, while also distinguishing contractual covenant calculations from underwriting calculations. The lesson is broader than multiplying by twelve: preserve the actual definition of each numerator and denominator.

A monthly equivalent is not a forecast of actual monthly cash. Seasonal operations, irregular tax payments and interest-only transitions can create a liquidity deficit even when annual coverage looks sufficient. Use a dated cash-flow schedule for those questions.

Test 2: identify the debt balance being measured

Using the hypothetical $390,000 annual income, debt yield on $4 million of funded principal is 9.75%. Using the $5 million commitment produces 7.80%. Neither denominator can be selected solely because it yields the more attractive percentage.

Similarly, funded principal divided by the $6.5 million value is 61.54%, while commitment divided by that value is 76.92%. A covenant, underwriting screen or release formula may specify its own debt definition. Label the model output accordingly and confirm the controlling definition before calling either figure compliant.

Undrawn commitment is also not cash currently available for every purpose. Draw conditions, budget allocations and holdbacks belong in the funding analysis. The unit audit identifies a balance distinction; it does not establish that remaining availability can be accessed.

Test 3: separate percent, percentage points and basis points

Twenty-five basis points equals 0.25 percentage point, or 0.0025 as a decimal fraction. On a constant $4 million balance, that annual simple-interest increment is $10,000. Entering 0.25 as the fraction would instead model a 25-percentage-point change.

The publication’s tools label whole-percentage inputs: entering 6 means 6%. Exported results identify percentage formats explicitly. When transferring data to another model, inspect that model’s convention rather than assuming the same displayed number has the same underlying representation.

A rise from 6% to 6.25% is a 25-basis-point increase and approximately a 4.17% relative increase in the rate itself. Those descriptions answer different questions. For coupon sensitivity, the basis-point change is usually the direct arithmetic input; relative change should not replace it silently.

Test 4: distinguish a stock from a flow

A principal balance is measured at a date. Interest expense is measured over a period. Dividing one month’s expense by the closing commitment is not necessarily a meaningful annual financing-cost measure, especially when balances rise through construction draws.

Record opening principal, each dated advance or repayment and closing principal. Reconcile closing = opening + advances + capitalized items − principal repayments. Then apply the stated interest convention to the applicable balances and days. Fees and restricted deposits require their own classifications.

Test 5: preserve signs and missing values

If cash available after mandatory uses is negative, retain the negative amount and identify the funding shortfall. Replacing it with zero hides the amount of required equity. Conversely, a blank balance or unknown expense is not evidence that the amount is zero.

Undefined ratios should remain unassessed. Dividing income by zero debt service does not establish a meaningful covenant result. A user should be able to distinguish “no debt,” “missing payment input” and “calculation not applicable.”

Test 6: round only at a documented boundary

Keep sufficient precision through intermediate calculations, then apply the stated display or contractual rounding rule. Independently rounded monthly values can sum to a different annual amount than a rounded annual calculation. Show which method generated the reported total.

A completed unit audit lets a second reviewer reconstruct the calculation without guessing what each cell means. It does not validate the property evidence or select the lender’s definitions. Those remain separate review steps, supported by the operating records, valuation and financing documents.