NonPeriodic::presentValue() - Code Metrics - Inspection of "Copy Styles after insertNewColumnBefore" - PHPOffice/PhpSpreadsheet - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Pull Request — master (#4468)

by Owen

created 2025-05-15 09:14 UTC

NonPeriodic::presentValue() A

↳ Parent: NonPeriodic

Complexity

Conditions	1
Paths	1

Size

Total Lines	3
Code Lines	1

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	2
CRAP Score	1

Importance

Changes

Metric	Value
cc	1
eloc	1
nc	1
nop	3
dl	0
loc	3
ccs	2
cts	2
cp	1
crap	1
rs	10
c	0
b	0
f	0

<?php

namespace PhpOffice\PhpSpreadsheet\Calculation\Financial\CashFlow\Variable;

use PhpOffice\PhpSpreadsheet\Calculation\DateTimeExcel;
use PhpOffice\PhpSpreadsheet\Calculation\Exception;
use PhpOffice\PhpSpreadsheet\Calculation\Functions;
use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
use PhpOffice\PhpSpreadsheet\Shared\StringHelper;

class NonPeriodic
{
    const FINANCIAL_MAX_ITERATIONS = 128;

    const FINANCIAL_PRECISION = 1.0e-08;

    const DEFAULT_GUESS = 0.1;

    /**
     * XIRR.
     *
     * Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic.
     *
     * Excel Function:
     *        =XIRR(values,dates,guess)
     *
     * @param array<int, float|int|numeric-string> $values     A series of cash flow payments, expecting float[]
     *                                The series of values must contain at least one positive value & one negative value
     * @param mixed[] $dates      A series of payment dates
     *                                The first payment date indicates the beginning of the schedule of payments
     *                                All other dates must be later than this date, but they may occur in any order
     * @param mixed $guess        An optional guess at the expected answer
     */
    public static function rate(mixed $values, $dates, mixed $guess = self::DEFAULT_GUESS): float|string
    {
        $rslt = self::xirrPart1($values, $dates);
        /** @var array $dates */
        if ($rslt !== '') {
            return $rslt;
        }

        // create an initial range, with a root somewhere between 0 and guess
        $guess = Functions::flattenSingleValue($guess) ?? self::DEFAULT_GUESS;
        if (!is_numeric($guess)) {
            return ExcelError::VALUE();
        }
        $guess = ($guess + 0.0) ?: self::DEFAULT_GUESS;
        $x1 = 0.0;
        $x2 = $guess + 0.0;
        $f1 = self::xnpvOrdered($x1, $values, $dates, false);
        $f2 = self::xnpvOrdered($x2, $values, $dates, false);
        $found = false;
        for ($i = 0; $i < self::FINANCIAL_MAX_ITERATIONS; ++$i) {
            if (!is_numeric($f1)) {
                return $f1;
            }
            if (!is_numeric($f2)) {
                return $f2;
            }
            $f1 = (float) $f1;
            $f2 = (float) $f2;
            if (($f1 * $f2) < 0.0) {
                $found = true;

                break;
            } elseif (abs($f1) < abs($f2)) {
                $x1 += 1.6 * ($x1 - $x2);
                $f1 = self::xnpvOrdered($x1, $values, $dates, false);
            } else {
                $x2 += 1.6 * ($x2 - $x1);
                $f2 = self::xnpvOrdered($x2, $values, $dates, false);
            }
        }
        if ($found) {
            return self::xirrPart3($values, $dates, $x1, $x2);
        }

        // Newton-Raphson didn't work - try bisection
        $x1 = $guess - 0.5;
        $x2 = $guess + 0.5;
        for ($i = 0; $i < self::FINANCIAL_MAX_ITERATIONS; ++$i) {
            $f1 = self::xnpvOrdered($x1, $values, $dates, false, true);
            $f2 = self::xnpvOrdered($x2, $values, $dates, false, true);
            if (!is_numeric($f1) || !is_numeric($f2)) {
                break;
            }
            if ($f1 * $f2 <= 0) {
                $found = true;

                break;
            }
            $x1 -= 0.5;
            $x2 += 0.5;
        }
        if ($found) {
            return self::xirrBisection($values, $dates, $x1, $x2);
        }

        return ExcelError::NAN();
    }

    /**
     * XNPV.
     *
     * Returns the net present value for a schedule of cash flows that is not necessarily periodic.
     * To calculate the net present value for a series of cash flows that is periodic, use the NPV function.
     *
     * Excel Function:
     *        =XNPV(rate,values,dates)
     *
     * @param mixed $rate the discount rate to apply to the cash flows, expect array|float
     * @param array<int,float|int|numeric-string> $values A series of cash flows that corresponds to a schedule of payments in dates, expecting float[].
     *                          The first payment is optional and corresponds to a cost or payment that occurs
     *                              at the beginning of the investment.
     *                          If the first value is a cost or payment, it must be a negative value.
     *                             All succeeding payments are discounted based on a 365-day year.
     *                          The series of values must contain at least one positive value and one negative value.
     * @param mixed $dates A schedule of payment dates that corresponds to the cash flow payments, expecting mixed[].
     *                         The first payment date indicates the beginning of the schedule of payments.
     *                         All other dates must be later than this date, but they may occur in any order.
     */
    public static function presentValue(mixed $rate, mixed $values, mixed $dates): float|string
    {
        return self::xnpvOrdered($rate, $values, $dates, true);
    }

    private static function bothNegAndPos(bool $neg, bool $pos): bool
    {
        return $neg && $pos;
    }

    /** @param array<int, float|int|numeric-string> $values */
    private static function xirrPart1(mixed &$values, mixed &$dates): string
    {
        $values = Functions::flattenArray($values); //* @phpstan-ignore-line
        $dates = Functions::flattenArray($dates);
        $valuesIsArray = count($values) > 1;
        $datesIsArray = count($dates) > 1;
        if (!$valuesIsArray && !$datesIsArray) {
            return ExcelError::NA();
        }
        if (count($values) != count($dates)) {
            return ExcelError::NAN();
        }

        $datesCount = count($dates);
        for ($i = 0; $i < $datesCount; ++$i) {
            try {
                $dates[$i] = DateTimeExcel\Helpers::getDateValue($dates[$i]);
            } catch (Exception $e) {
                return $e->getMessage();
            }
        }

        return self::xirrPart2($values);
    }

    /** @param array<int, float|int|numeric-string> $values */
    private static function xirrPart2(array &$values): string
    {
        $valCount = count($values);
        $foundpos = false;
        $foundneg = false;
        for ($i = 0; $i < $valCount; ++$i) {
            $fld = $values[$i];
            if (!is_numeric($fld)) { //* @phpstan-ignore-line
                return ExcelError::VALUE();
            } elseif ($fld > 0) {
                $foundpos = true;
            } elseif ($fld < 0) {
                $foundneg = true;
            }
        }
        if (!self::bothNegAndPos($foundneg, $foundpos)) {
            return ExcelError::NAN();
        }

        return '';
    }

    private static function xirrPart3(array $values, array $dates, float $x1, float $x2): float|string
    {
        $f = self::xnpvOrdered($x1, $values, $dates, false);
        if ($f < 0.0) {
            $rtb = $x1;
            $dx = $x2 - $x1;
        } else {
            $rtb = $x2;
            $dx = $x1 - $x2;
        }

        $rslt = ExcelError::VALUE();
        for ($i = 0; $i < self::FINANCIAL_MAX_ITERATIONS; ++$i) {
            $dx *= 0.5;
            $x_mid = $rtb + $dx;
            $f_mid = (float) self::xnpvOrdered($x_mid, $values, $dates, false);
            if ($f_mid <= 0.0) {
                $rtb = $x_mid;
            }
            if ((abs($f_mid) < self::FINANCIAL_PRECISION) || (abs($dx) < self::FINANCIAL_PRECISION)) {
                $rslt = $x_mid;

                break;
            }
        }

        return $rslt;
    }

    private static function xirrBisection(array $values, array $dates, float $x1, float $x2): string|float
    {
        $rslt = ExcelError::NAN();
        for ($i = 0; $i < self::FINANCIAL_MAX_ITERATIONS; ++$i) {
            $rslt = ExcelError::NAN();
            $f1 = self::xnpvOrdered($x1, $values, $dates, false, true);
            $f2 = self::xnpvOrdered($x2, $values, $dates, false, true);
            if (!is_numeric($f1) || !is_numeric($f2)) {
                break;
            }
            $f1 = (float) $f1;
            $f2 = (float) $f2;
            if (abs($f1) < self::FINANCIAL_PRECISION && abs($f2) < self::FINANCIAL_PRECISION) {
                break;
            }
            if ($f1 * $f2 > 0) {
                break;
            }
            $rslt = ($x1 + $x2) / 2;
            $f3 = self::xnpvOrdered($rslt, $values, $dates, false, true);
            if (!is_float($f3)) {
                break;
            }
            if ($f3 * $f1 < 0) {
                $x2 = $rslt;
            } else {
                $x1 = $rslt;
            }
            if (abs($f3) < self::FINANCIAL_PRECISION) {
                break;
            }
        }

        return $rslt;
    }

    /** @param array<int,float|int|numeric-string> $values> */
    private static function xnpvOrdered(mixed $rate, mixed $values, mixed $dates, bool $ordered = true, bool $capAtNegative1 = false): float|string
    {
        $rate = Functions::flattenSingleValue($rate);
        if (!is_numeric($rate)) {
            return ExcelError::VALUE();
        }
        $values = Functions::flattenArray($values);
        $dates = Functions::flattenArray($dates);
        $valCount = count($values);

        try {
            self::validateXnpv($rate, $values, $dates);
            if ($capAtNegative1 && $rate <= -1) {
                $rate = -1.0 + 1.0E-10;
            }
            $date0 = DateTimeExcel\Helpers::getDateValue($dates[0]);
        } catch (Exception $e) {
            return $e->getMessage();
        }

        $xnpv = 0.0;
        for ($i = 0; $i < $valCount; ++$i) {
            if (!is_numeric($values[$i])) {
                return ExcelError::VALUE();
            }

            try {
                $datei = DateTimeExcel\Helpers::getDateValue($dates[$i]);
            } catch (Exception $e) {
                return $e->getMessage();
            }
            if ($date0 > $datei) {
                $dif = $ordered ? ExcelError::NAN() : -((int) DateTimeExcel\Difference::interval($datei, $date0, 'd'));
            } else {
                $dif = Functions::scalar(DateTimeExcel\Difference::interval($date0, $datei, 'd'));
            }
            if (!is_numeric($dif)) {
                return StringHelper::convertToString($dif);
            }
            if ($rate <= -1.0) {
                $xnpv += -abs($values[$i] + 0) / (-1 - $rate) ** ($dif / 365);
            } else {
                $xnpv += $values[$i] / (1 + $rate) ** ($dif / 365);
            }
        }

        return is_finite($xnpv) ? $xnpv : ExcelError::VALUE();
    }

    private static function validateXnpv(mixed $rate, array $values, array $dates): void
    {
        if (!is_numeric($rate)) {
            throw new Exception(ExcelError::VALUE());
        }
        $valCount = count($values);
        if ($valCount != count($dates)) {
            throw new Exception(ExcelError::NAN());
        }
        if (count($values) > 1 && ((min($values) > 0) || (max($values) < 0))) {
            throw new Exception(ExcelError::NAN());
        }
    }
}


1		<?php
2
3		namespace PhpOffice\PhpSpreadsheet\Calculation\Financial\CashFlow\Variable;
4
5		use PhpOffice\PhpSpreadsheet\Calculation\DateTimeExcel;
6		use PhpOffice\PhpSpreadsheet\Calculation\Exception;
7		use PhpOffice\PhpSpreadsheet\Calculation\Functions;
8		use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
9		use PhpOffice\PhpSpreadsheet\Shared\StringHelper;
10
11		class NonPeriodic
12		{
13		const FINANCIAL_MAX_ITERATIONS = 128;
14
15		const FINANCIAL_PRECISION = 1.0e-08;
16
17		const DEFAULT_GUESS = 0.1;
18
19		/**
20		* XIRR.
21		*
22		* Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic.
23		*
24		* Excel Function:
25		* =XIRR(values,dates,guess)
26		*
27		* @param array<int, float\|int\|numeric-string> $values A series of cash flow payments, expecting float[]
28		* The series of values must contain at least one positive value & one negative value
29		* @param mixed[] $dates A series of payment dates
30		* The first payment date indicates the beginning of the schedule of payments
31		* All other dates must be later than this date, but they may occur in any order
32		* @param mixed $guess An optional guess at the expected answer
33		*/
34	40	public static function rate(mixed $values, $dates, mixed $guess = self::DEFAULT_GUESS): float\|string
35		{
36	40	$rslt = self::xirrPart1($values, $dates);
37		/** @var array $dates */
38	40	if ($rslt !== '') {
39	10	return $rslt;
40		}
41
42		// create an initial range, with a root somewhere between 0 and guess
43	30	$guess = Functions::flattenSingleValue($guess) ?? self::DEFAULT_GUESS;
44	30	if (!is_numeric($guess)) {
45	1	return ExcelError::VALUE();
46		}
47	29	$guess = ($guess + 0.0) ?: self::DEFAULT_GUESS;
48	29	$x1 = 0.0;
49	29	$x2 = $guess + 0.0;
50	29	$f1 = self::xnpvOrdered($x1, $values, $dates, false);
51	29	$f2 = self::xnpvOrdered($x2, $values, $dates, false);
52	29	$found = false;
53	29	for ($i = 0; $i < self::FINANCIAL_MAX_ITERATIONS; ++$i) {
54	29	if (!is_numeric($f1)) {
55		return $f1;
56		}
57	29	if (!is_numeric($f2)) {
58		return $f2;
59		}
60	29	$f1 = (float) $f1;
61	29	$f2 = (float) $f2;
62	29	if (($f1 * $f2) < 0.0) {
63	23	$found = true;
64
65	23	break;
66	26	} elseif (abs($f1) < abs($f2)) {
67	10	$x1 += 1.6 * ($x1 - $x2);
68	10	$f1 = self::xnpvOrdered($x1, $values, $dates, false);
69		} else {
70	21	$x2 += 1.6 * ($x2 - $x1);
71	21	$f2 = self::xnpvOrdered($x2, $values, $dates, false);
72		}
73		}
74	29	if ($found) {
75	23	return self::xirrPart3($values, $dates, $x1, $x2);
76		}
77
78		// Newton-Raphson didn't work - try bisection
79	6	$x1 = $guess - 0.5;
80	6	$x2 = $guess + 0.5;
81	6	for ($i = 0; $i < self::FINANCIAL_MAX_ITERATIONS; ++$i) {
82	6	$f1 = self::xnpvOrdered($x1, $values, $dates, false, true);
83	6	$f2 = self::xnpvOrdered($x2, $values, $dates, false, true);
84	6	if (!is_numeric($f1) \|\| !is_numeric($f2)) {
85		break;
86		}
87	6	if ($f1 * $f2 <= 0) {
88	5	$found = true;
89
90	5	break;
91		}
92	6	$x1 -= 0.5;
93	6	$x2 += 0.5;
94		}
95	6	if ($found) {
96	5	return self::xirrBisection($values, $dates, $x1, $x2);
97		}
98
99	1	return ExcelError::NAN();
100		}
101
102		/**
103		* XNPV.
104		*
105		* Returns the net present value for a schedule of cash flows that is not necessarily periodic.
106		* To calculate the net present value for a series of cash flows that is periodic, use the NPV function.
107		*
108		* Excel Function:
109		* =XNPV(rate,values,dates)
110		*
111		* @param mixed $rate the discount rate to apply to the cash flows, expect array\|float
112		* @param array<int,float\|int\|numeric-string> $values A series of cash flows that corresponds to a schedule of payments in dates, expecting float[].
113		* The first payment is optional and corresponds to a cost or payment that occurs
114		* at the beginning of the investment.
115		* If the first value is a cost or payment, it must be a negative value.
116		* All succeeding payments are discounted based on a 365-day year.
117		* The series of values must contain at least one positive value and one negative value.
118		* @param mixed $dates A schedule of payment dates that corresponds to the cash flow payments, expecting mixed[].
119		* The first payment date indicates the beginning of the schedule of payments.
120		* All other dates must be later than this date, but they may occur in any order.
121		*/
122	14	public static function presentValue(mixed $rate, mixed $values, mixed $dates): float\|string
123		{
124	14	return self::xnpvOrdered($rate, $values, $dates, true);
125		}
126
127	34	private static function bothNegAndPos(bool $neg, bool $pos): bool
128		{
129	34	return $neg && $pos;
130		}
131
132		/** @param array<int, float\|int\|numeric-string> $values */
133	40	private static function xirrPart1(mixed &$values, mixed &$dates): string
134		{
135	40	$values = Functions::flattenArray($values); //* @phpstan-ignore-line
136	40	$dates = Functions::flattenArray($dates);
137	40	$valuesIsArray = count($values) > 1;
138	40	$datesIsArray = count($dates) > 1;
139	40	if (!$valuesIsArray && !$datesIsArray) {
140	1	return ExcelError::NA();
141		}
142	39	if (count($values) != count($dates)) {
143	3	return ExcelError::NAN();
144		}
145
146	36	$datesCount = count($dates);
147	36	for ($i = 0; $i < $datesCount; ++$i) {
148		try {
149	36	$dates[$i] = DateTimeExcel\Helpers::getDateValue($dates[$i]);
150	1	} catch (Exception $e) {
151	1	return $e->getMessage();
152		}
153		}
154
155	35	return self::xirrPart2($values);
156		}
157
158		/** @param array<int, float\|int\|numeric-string> $values */
159	35	private static function xirrPart2(array &$values): string
160		{
161	35	$valCount = count($values);
162	35	$foundpos = false;
163	35	$foundneg = false;
164	35	for ($i = 0; $i < $valCount; ++$i) {
165	35	$fld = $values[$i];
166	35	if (!is_numeric($fld)) { //* @phpstan-ignore-line
167	1	return ExcelError::VALUE();
168	34	} elseif ($fld > 0) {
169	31	$foundpos = true;
170	33	} elseif ($fld < 0) {
171	33	$foundneg = true;
172		}
173		}
174	34	if (!self::bothNegAndPos($foundneg, $foundpos)) {
175	4	return ExcelError::NAN();
176		}
177
178	30	return '';
179		}
180
181	23	private static function xirrPart3(array $values, array $dates, float $x1, float $x2): float\|string
182		{
183	23	$f = self::xnpvOrdered($x1, $values, $dates, false);
184	23	if ($f < 0.0) {
185	9	$rtb = $x1;
186	9	$dx = $x2 - $x1;
187		} else {
188	14	$rtb = $x2;
189	14	$dx = $x1 - $x2;
190		}
191
192	23	$rslt = ExcelError::VALUE();
193	23	for ($i = 0; $i < self::FINANCIAL_MAX_ITERATIONS; ++$i) {
194	23	$dx *= 0.5;
195	23	$x_mid = $rtb + $dx;
196	23	$f_mid = (float) self::xnpvOrdered($x_mid, $values, $dates, false);
197	23	if ($f_mid <= 0.0) {
198	21	$rtb = $x_mid;
199		}
200	23	if ((abs($f_mid) < self::FINANCIAL_PRECISION) \|\| (abs($dx) < self::FINANCIAL_PRECISION)) {
201	23	$rslt = $x_mid;
202
203	23	break;
204		}
205		}
206
207	23	return $rslt;
208		}
209
210	5	private static function xirrBisection(array $values, array $dates, float $x1, float $x2): string\|float
211		{
212	5	$rslt = ExcelError::NAN();
213	5	for ($i = 0; $i < self::FINANCIAL_MAX_ITERATIONS; ++$i) {
214	5	$rslt = ExcelError::NAN();
215	5	$f1 = self::xnpvOrdered($x1, $values, $dates, false, true);
216	5	$f2 = self::xnpvOrdered($x2, $values, $dates, false, true);
217	5	if (!is_numeric($f1) \|\| !is_numeric($f2)) {
218		break;
219		}
220	5	$f1 = (float) $f1;
221	5	$f2 = (float) $f2;
222	5	if (abs($f1) < self::FINANCIAL_PRECISION && abs($f2) < self::FINANCIAL_PRECISION) {
223		break;
224		}
225	5	if ($f1 * $f2 > 0) {
226		break;
227		}
228	5	$rslt = ($x1 + $x2) / 2;
229	5	$f3 = self::xnpvOrdered($rslt, $values, $dates, false, true);
230	5	if (!is_float($f3)) {
231		break;
232		}
233	5	if ($f3 * $f1 < 0) {
234	5	$x2 = $rslt;
235		} else {
236	5	$x1 = $rslt;
237		}
238	5	if (abs($f3) < self::FINANCIAL_PRECISION) {
239	4	break;
240		}
241		}
242
243	5	return $rslt;
244		}
245
246		/** @param array<int,float\|int\|numeric-string> $values> */
247	43	private static function xnpvOrdered(mixed $rate, mixed $values, mixed $dates, bool $ordered = true, bool $capAtNegative1 = false): float\|string
248		{
249	43	$rate = Functions::flattenSingleValue($rate);
250	43	if (!is_numeric($rate)) {
251	1	return ExcelError::VALUE();
252		}
253	42	$values = Functions::flattenArray($values);
254	42	$dates = Functions::flattenArray($dates);
255	42	$valCount = count($values);
256
257		try {
258	42	self::validateXnpv($rate, $values, $dates);
259	39	if ($capAtNegative1 && $rate <= -1) {
260	3	$rate = -1.0 + 1.0E-10;
261		}
262	39	$date0 = DateTimeExcel\Helpers::getDateValue($dates[0]);
263	4	} catch (Exception $e) {
264	4	return $e->getMessage();
265		}
266
267	38	$xnpv = 0.0;
268	38	for ($i = 0; $i < $valCount; ++$i) {
269	38	if (!is_numeric($values[$i])) {
270	1	return ExcelError::VALUE();
271		}
272
273		try {
274	38	$datei = DateTimeExcel\Helpers::getDateValue($dates[$i]);
275	1	} catch (Exception $e) {
276	1	return $e->getMessage();
277		}
278	38	if ($date0 > $datei) {
279	4	$dif = $ordered ? ExcelError::NAN() : -((int) DateTimeExcel\Difference::interval($datei, $date0, 'd'));
280		} else {
281	38	$dif = Functions::scalar(DateTimeExcel\Difference::interval($date0, $datei, 'd'));
282		}
283	38	if (!is_numeric($dif)) {
284	1	return StringHelper::convertToString($dif);
285		}
286	38	if ($rate <= -1.0) {
287	6	$xnpv += -abs($values[$i] + 0) / (-1 - $rate) ** ($dif / 365);
288		} else {
289	38	$xnpv += $values[$i] / (1 + $rate) ** ($dif / 365);
290		}
291		}
292
293	35	return is_finite($xnpv) ? $xnpv : ExcelError::VALUE();
294		}
295
296	42	private static function validateXnpv(mixed $rate, array $values, array $dates): void
297		{
298	42	if (!is_numeric($rate)) {
299		throw new Exception(ExcelError::VALUE());
300		}
301	42	$valCount = count($values);
302	42	if ($valCount != count($dates)) {
303	1	throw new Exception(ExcelError::NAN());
304		}
305	41	if (count($values) > 1 && ((min($values) > 0) \|\| (max($values) < 0))) {
306	2	throw new Exception(ExcelError::NAN());
307		}
308		}
309		}
310

PHPOffice / PhpSpreadsheet

Pull Request — master (#4468)

NonPeriodic::presentValue() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like