Financial Calculation Correctness and Parity

Overview

This skill defines patterns for validating financial calculations against Excel models, maintaining truth-case accuracy, and ensuring numerical correctness for IRR, waterfalls, Monte Carlo, and related computations.

Core principle: Excel is the source of truth for business logic validation. Code may differ in implementation but must produce equivalent results within defined tolerances.

Invariants to Enforce

1. Mass Conservation

Money in must equal money out plus final balance. This applies to:

2. Monotonicity

Cumulative values must not decrease (unless explicitly modeling reversals):

3. Boundary Conditions

Edge cases must produce sensible results:

4. Day Count Convention Consistency

Different conventions produce different results. Document and enforce:

Precision and Tolerance Norms

When to Use Different Tolerances

Tolerance Documentation Pattern

Every tolerance in code must be documented:

// GOOD: Documented tolerance with rationale
const IRR_TOLERANCE = 1e-6; // 0.0001% - matches Excel XIRR solver precision

// BAD: Magic number
if (Math.abs(a - b) < 0.0001) { ... }

Excel Parity Workflow

Step 1: Define Source of Truth

Document the Excel model location, owner, last validation date, and known deviations from code.

Step 2: Extract Test Vectors

Create test vectors programmatically from Excel, not manually.

Step 3: Run Comparison

Compare code results with Excel expected values using explicit tolerances.

Step 4: Document Intentional Deviations

When code intentionally differs from Excel, create an ADR.

Truth Case Validation Rules

Required Fields

Every truth case must include:

• name: Descriptive unique name
• description: What this case tests
• source: excel_model_v3.xlsx | manual_calculation | regression_capture
• created: Date
• inputs: Input data
• expected: Expected outputs with tolerances and rationale

Validation Checklist

Before accepting a truth case change:

• Tolerance is explicitly specified (not default)
• Tolerance rationale is documented
• Source is identified (Excel, manual calc, regression)
• If from Excel: Excel file is committed or referenced
• If tolerance increased: ADR documents why
• Mass conservation holds in expected outputs
• Monotonicity constraints satisfied

Solver-Specific Guidance

IRR/XIRR Solvers

// Solver configuration that matches Excel behavior
const XIRR_CONFIG = {
  maxIterations: 100,      // Excel default
  tolerance: 1e-6,         // Convergence threshold
  initialGuess: 0.1,       // 10% starting point
  bounds: [-0.99, 10.0],   // -99% to 1000% annual return
};

Newton-Raphson vs Bisection

Integration with Phoenix Workflows

The parity-auditor agent uses this skill when:

• PRs touch calculation code
• Truth-case tests fail
• Excel parity impact assessment is needed

Related Skills

• phoenix-precision-guard: Numeric drift detection
• phoenix-waterfall-ledger-semantics: Waterfall calculation rules
• phoenix-xirr-fees-validator: XIRR and fee validation