PHPOffice/PhpSpreadsheet · error · PhpOffice\PhpSpreadsheet\Calculation\Exception

#NUM!

#NUM!

Error message

#NUM!

What it means

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.

Source

Thrown at src/PhpSpreadsheet/Calculation/Financial/Securities/SecurityValidations.php:19

<?php

namespace PhpOffice\PhpSpreadsheet\Calculation\Financial\Securities;

use PhpOffice\PhpSpreadsheet\Calculation\Exception;
use PhpOffice\PhpSpreadsheet\Calculation\Financial\FinancialValidations;
use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;

class SecurityValidations extends FinancialValidations
{
    public static function validateIssueDate(mixed $issue): float
    {
        return self::validateDate($issue);
    }

    public static function validateSecurityPeriod(mixed $settlement, mixed $maturity): void
    {
        if ($settlement >= $maturity) {
            throw new Exception(ExcelError::NAN());
        }
    }

    public static function validateRedemption(mixed $redemption): float
    {
        $redemption = self::validateFloat($redemption);
        if ($redemption <= 0.0) {
            throw new Exception(ExcelError::NAN());
        }

        return $redemption;
    }
}

View on GitHub (pinned to 65b080eef4)

Solutions

  1. Check settlement < maturity before evaluation, comparing them as dates, not strings
  2. Fix swapped arguments in the formula or the direct function call
  3. Use unambiguous date inputs: DateTime objects, Excel serials, or 'YYYY-MM-DD' strings
  4. If reached via $cell->getCalculatedValue(), catch Calculation\Exception or set suppressFormulaErrors to receive '#NUM!' as a value

Example fix

// before
$price = Price::price('2030-01-01', '2024-01-01', 0.06, 0.05, 100, 2, 0); // settlement AFTER maturity -> '#NUM!'

// after: enforce the period invariant before pricing
if (strtotime($settlement) >= strtotime($maturity)) {
    throw new InvalidArgumentException('settlement date must precede maturity date');
}
$price = Price::price($settlement, $maturity, 0.06, 0.05, 100, 2, 0);
Defensive patterns

Strategy: try-catch

Validate before calling

if (strtotime($settlement) >= strtotime($maturity)) {
    throw new InvalidArgumentException('settlement date must be strictly before maturity date');
}

Type guard

function isValidSecurityPeriod(mixed $settlement, mixed $maturity): bool
{
    $s = is_numeric($settlement) ? (float) $settlement : (float) strtotime((string) $settlement);
    $m = is_numeric($maturity) ? (float) $maturity : (float) strtotime((string) $maturity);

    return $s < $m;
}

Try / catch

use PhpOffice\PhpSpreadsheet\Calculation\Exception as CalcException;

try {
    $value = $cell->getCalculatedValue(); // cell formula -> '#NUM!' surfaces as an exception
} catch (CalcException $e) {
    // re-inspect the formula's settlement/maturity arguments
}

Prevention

When it happens

Trigger: 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.

Common situations: 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.

Related errors

AI-assisted analysis of PHPOffice/PhpSpreadsheet@65b080eef4 (2026-08-17). Data as JSON: /api/errors/6598e0f963c65fc0. Report an issue: GitHub.