Calculation and Formula Standard¶
Calculations must be auditable and documented¶
Every product with non-trivial calculations must have a calculation specification
(within products/<slug>/) that documents what each significant calculation does, in
plain language, alongside the actual formula. A customer or reviewer should be able to
read the specification and understand the calculation without reverse-engineering
nested formulas from the workbook alone.
Avoid deeply nested, opaque formulas without documentation¶
A formula that nests many functions and conditions into a single cell is harder to audit, harder to debug, and harder for a customer to trust. Prefer breaking complex logic into labeled intermediate steps (helper columns or a dedicated Calculations area) over a single dense formula, unless the calculation specification documents the nested formula clearly enough that its logic is traceable step by step.
Explicit rounding, null, zero, and error handling¶
For every calculation where these situations are possible, the calculation specification and the formula itself must handle them explicitly and deliberately, not by accident of Excel's default behavior:
- Rounding: state the rounding rule (round half up, round to nearest cent, etc.) and where in the calculation chain rounding is applied, since rounding at different stages can produce different results.
- Null/blank inputs: state what happens when a required input is left blank (e.g., the calculation should show a clear "awaiting input" state rather than a raw error or a misleading zero).
- Zero values: state whether zero is a valid, meaningful input (e.g., zero debt) versus an unset/placeholder value, and ensure the formula treats it correctly.
- Errors: anticipate foreseeable error conditions (division by zero, invalid date math, lookup misses) and handle them with a readable fallback rather than letting a raw Excel error propagate to the dashboard.
Worked examples required for complex calculations¶
Any calculation complex enough to warrant its own section in the calculation specification must include at least one worked numeric example: sample inputs, the intermediate steps, and the final output, so a reviewer can verify the formula produces the expected result by hand or by re-deriving it independently.
Relationship to financial guardrails¶
For finance-related products, this standard operates alongside, and does not replace, financial-product-guardrails.md, which adds requirements around assumption disclosure, sensitivity analysis, and edge-case testing specific to financial calculations.