R005 — Volatile Functions

Rule-Reference • — • 3 min read

Audience: Technical Reviewers • Auditors • Model Developers
Last Reviewed: July 2026
Updated: Version 1.0

Executive Summary

R005 (Volatile Functions) flags cells using a volatile Excel function — one that recalculates on every workbook change regardless of whether its own inputs changed. Rather than a single fixed severity, R005 derives severity per finding from the function's likely purpose (a date stamp, a dynamic range, a randomization, or presentation-only use), how many cells downstream depend on it, and whether that downstream reach touches a critical output such as an IRR, NPV, or cash flow sheet.

Key Takeaways

✓ R005 flags cells using a volatile Excel function (TODAY, NOW, RAND, RANDBETWEEN, OFFSET, INDIRECT) — functions that recalculate on every workbook change, not only when their own inputs change.
✓ Severity is derived per finding, not fixed, from the function's likely purpose, how far downstream its impact reaches, and whether that reach touches a critical output sheet.
✓ RAND and RANDBETWEEN are always treated as critical severity, since a non-deterministic value feeding a financial model undermines reproducibility regardless of context.
✓ A volatile function used only for on-screen presentation on a cover or title sheet is treated as lower risk than the same function feeding a live calculation chain.

Purpose¶

R005 detects cells whose formula calls a volatile Excel function — TODAY, NOW, RAND, RANDBETWEEN, OFFSET, or INDIRECT — functions that Excel recalculates on every workbook recalculation, not only when their own referenced inputs change.

Detection Logic¶

Every formula cell calling one of the six volatile functions is examined. The function's likely purpose is classified (a date stamp, a dynamic range reference, a randomization, or presentation-only display), the sheet context is checked (a cover or title sheet suggests lower-risk presentation use; a sheet name associated with cash flow, valuation, or returns suggests higher-risk use), and the finding's downstream reach is traced to determine whether the cell's value could affect a critical output. Severity is derived from the combination of these signals rather than fixed per function, except for RAND and RANDBETWEEN, which are always treated as critical regardless of context.

Why It Matters¶

A volatile function reintroduces a form of non-determinism or environmental sensitivity into an otherwise deterministic financial model. RAND and RANDBETWEEN produce a genuinely different value on every recalculation — a financial model that depends on a random number without an explicit, controlled simulation design is not reproducible. TODAY and NOW mean a model's output can change simply because it was opened on a different day, not because any underlying assumption changed. OFFSET and INDIRECT build a cell reference dynamically at calculation time rather than through a fixed formula reference, which can make the model harder to trace and audit, and can silently change what a formula points to if the inputs driving the dynamic reference change.

Evidence Produced¶

A triggered R005 finding's evidence names the specific volatile function(s) found in the cell, the classified purpose, the number of direct and transitive downstream dependent cells, and whether that downstream reach touches a recognized critical-output sheet — so the finding conveys not just that a volatile function is present, but how far its effect could actually propagate.

Severity¶

High (class-level default). Weight 8.0. Per-finding severity is context-derived: RAND and RANDBETWEEN are always critical; other functions range from low to high depending on purpose, downstream reach, and whether a critical output is affected. R005 is not one of the five critical-override rules.

Example¶

Trigger. A discount-rate assumption cell on a Valuation worksheet reads =OFFSET(Rates!$A$1,RAND()*10,0) — a dynamic, randomized reference feeding directly into a valuation calculation. R005 flags this cell at critical severity: RAND is always critical, and the reference reaches a recognized critical output.

Non-trigger — presentation use. A cover sheet displays =TODAY() in a "Report Date" label with no formula elsewhere in the workbook referencing that cell. R005 still flags the cell (any use of a volatile function is detected), but at reduced severity, since the classified purpose is presentation-only and the downstream reach is zero.

Recommended Remediation¶

Replace TODAY() and NOW() used as calculation inputs with a static, named input cell for the relevant date, updated deliberately rather than automatically. Remove RAND() and RANDBETWEEN() from deterministic calculations and replace with explicit, documented scenario inputs. Replace OFFSET() with INDEX(), which achieves the same dynamic-reference behavior without volatility. Replace INDIRECT() with a direct cell reference or a structured table reference where the target is known at design time.

R005 shares the Structural taxonomy category with the other Structural-category rules published in the Rule Reference — see the Rule Taxonomy page for the full category membership.

No existing Knowledge Centre technical guide addresses volatile-function risk specifically as of this writing; this section will be updated with a link in the same edit that publishes one.

Version History¶

No version-specific changes to R005's severity, weight, or category are recorded as of engine version 2.1.0. This section will be updated in the same edit as any future change to the rule's declared attributes.

How OXXON tests thisRun a free structural check with FMAE

Frequently Asked Questions

What does FMAE rule R005 check for?

Cells containing a volatile Excel function — TODAY, NOW, RAND, RANDBETWEEN, OFFSET, or INDIRECT — functions that recalculate every time the workbook recalculates at all, regardless of whether their own inputs actually changed.

Are all volatile functions treated the same way?

No. R005 derives severity per finding from the function's likely purpose (date stamp, dynamic range, randomization, or presentation), how many cells downstream depend on it, and whether that reach touches a critical output. RAND and RANDBETWEEN are always critical severity; other functions vary.

Why is a volatile function on a cover sheet treated differently from one in a calculation chain?

A volatile function used only for on-screen display (a date stamp on a title page, for example) carries materially less structural risk than the same function embedded in a chain feeding a valuation or cash flow output, so R005 reduces severity for presentation-context use and escalates it when the function's impact reaches a critical output.

FMAE Rule Taxonomy

Every rule in the FMAE structural rule pack declares a category attribute at the point it is defined in source — this is not a classification imposed on the rules afterward for documentation purposes, it is the classification the engine itself uses. Six categories cover all 26 rules — Structural (18 rules), Assumptions Governance (1), Integrity Controls (2), Structural Hygiene (1), Aggregation Logic (1), and Model Governance (3). This page publishes that taxonomy as the FMAE equivalent of a control catalog's classification scheme, cross-linked to the Rule Reference page for each member rule.

Finding Data Model & Confidence

Every FMAE finding carries the same fixed structure — which rule triggered it and that rule's identity at the time it ran, its evidence, a narrative explanation, and a confidence label of high, medium, or low. Confidence is a deterministic categorical label derived from how strongly a row's cells agree on a pattern, not a machine-learning probability estimate. Stating this plainly matters, since describing FMAE's confidence as AI-derived or probabilistic would overstate what the engine actually does.