{"record":{"id":"6598e0f963c65fc0","repo":"PHPOffice/PhpSpreadsheet","slug":"num-6598e0","errorCode":"#NUM!","errorMessage":"#NUM!","messagePattern":"#NUM!","errorType":"exception","errorClass":"PhpOffice\\PhpSpreadsheet\\Calculation\\Exception","httpStatus":null,"severity":"error","filePath":"src/PhpSpreadsheet/Calculation/Financial/Securities/SecurityValidations.php","lineNumber":19,"sourceCode":"<?php\n\nnamespace PhpOffice\\PhpSpreadsheet\\Calculation\\Financial\\Securities;\n\nuse PhpOffice\\PhpSpreadsheet\\Calculation\\Exception;\nuse PhpOffice\\PhpSpreadsheet\\Calculation\\Financial\\FinancialValidations;\nuse PhpOffice\\PhpSpreadsheet\\Calculation\\Information\\ExcelError;\n\nclass SecurityValidations extends FinancialValidations\n{\n    public static function validateIssueDate(mixed $issue): float\n    {\n        return self::validateDate($issue);\n    }\n\n    public static function validateSecurityPeriod(mixed $settlement, mixed $maturity): void\n    {\n        if ($settlement >= $maturity) {\n            throw new Exception(ExcelError::NAN());\n        }\n    }\n\n    public static function validateRedemption(mixed $redemption): float\n    {\n        $redemption = self::validateFloat($redemption);\n        if ($redemption <= 0.0) {\n            throw new Exception(ExcelError::NAN());\n        }\n\n        return $redemption;\n    }\n}\n","sourceCodeStart":1,"sourceCodeEnd":33,"githubUrl":"https://github.com/PHPOffice/PhpSpreadsheet/blob/65b080eef4d9fd11a5796135ab145883e5c3d6a6/src/PhpSpreadsheet/Calculation/Financial/Securities/SecurityValidations.php#L1-L33","documentation":"Thrown by SecurityValidations::validateSecurityPeriod() (Securities/SecurityValidations.php:16-21) when settlement >= maturity on the Excel date serial numbers produced by validateSettlementDate()/validateMaturityDate(). Nearly every securities function calls it: PRICE, PRICEDISC, PRICEMAT, RECEIVED, YIELD, YIELDDISC, DISC, INTRATE, ACCRINT, ACCRINTM. The comparison happens after date coercion, so string dates, DateTime objects and serials are all fine - only the ordering matters; equality is also rejected.","triggerScenarios":"Swapped settlement/maturity arguments (the classic case); same-day settlement and maturity; a maturity parsed from 'dd/mm/yy' vs 'mm/dd/yy' so it lands before settlement for days <= 12.","commonSituations":"Locale-ambiguous dates imported from CSV; template columns users fill in the wrong order; boundary tests with settlement == maturity; midnight-trimmed DateTime values making the dates equal.","solutions":["Check settlement < maturity before evaluation, comparing them as dates, not strings","Fix swapped arguments in the formula or the direct function call","Use unambiguous date inputs: DateTime objects, Excel serials, or 'YYYY-MM-DD' strings","If reached via $cell->getCalculatedValue(), catch Calculation\\Exception or set suppressFormulaErrors to receive '#NUM!' as a value"],"exampleFix":"// before\n$price = Price::price('2030-01-01', '2024-01-01', 0.06, 0.05, 100, 2, 0); // settlement AFTER maturity -> '#NUM!'\n\n// after: enforce the period invariant before pricing\nif (strtotime($settlement) >= strtotime($maturity)) {\n    throw new InvalidArgumentException('settlement date must precede maturity date');\n}\n$price = Price::price($settlement, $maturity, 0.06, 0.05, 100, 2, 0);","handlingStrategy":"try-catch","validationCode":"if (strtotime($settlement) >= strtotime($maturity)) {\n    throw new InvalidArgumentException('settlement date must be strictly before maturity date');\n}","typeGuard":"function isValidSecurityPeriod(mixed $settlement, mixed $maturity): bool\n{\n    $s = is_numeric($settlement) ? (float) $settlement : (float) strtotime((string) $settlement);\n    $m = is_numeric($maturity) ? (float) $maturity : (float) strtotime((string) $maturity);\n\n    return $s < $m;\n}","tryCatchPattern":"use PhpOffice\\PhpSpreadsheet\\Calculation\\Exception as CalcException;\n\ntry {\n    $value = $cell->getCalculatedValue(); // cell formula -> '#NUM!' surfaces as an exception\n} catch (CalcException $e) {\n    // re-inspect the formula's settlement/maturity arguments\n}","preventionTips":["Canonicalize all dates to DateTime or ISO strings before building formulas","Reject ambiguous 'd/m/y' vs 'm/d/y' input at the boundary","Remember equality is invalid too - settlement must be strictly before maturity"],"tags":["phpspreadsheet","php","excel","financial","securities","dates","num-error","argument-validation"],"backgroundTag":"excel-num-error","analyzedSha":"65b080eef4d9fd11a5796135ab145883e5c3d6a6","analyzedAt":"2026-08-17T05:40:41.646Z","schemaVersion":2},"datasetVersion":"2026-08-17T09:17:11.063Z"}