Passed
Push — master ( e6aacf...0e570a )
by
unknown
25:35 queued 18:08
created

NonPeriodic::bothNegAndPos()   A

Complexity

Conditions 2
Paths 2

Size

Total Lines 3
Code Lines 1

Duplication

Lines 0
Ratio 0 %

Code Coverage

Tests 2
CRAP Score 2

Importance

Changes 0
Metric Value
cc 2
eloc 1
nc 2
nop 2
dl 0
loc 3
rs 10
c 0
b 0
f 0
ccs 2
cts 2
cp 1
crap 2
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 array<int, float|int|numeric-string> $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<int, float|int|numeric-string> $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
            /** @var array<int, float|int|numeric-string> $dates */
97 5
            return self::xirrBisection($values, $dates, $x1, $x2);
98
        }
99
100 1
        return ExcelError::NAN();
101
    }
102
103
    /**
104
     * XNPV.
105
     *
106
     * Returns the net present value for a schedule of cash flows that is not necessarily periodic.
107
     * To calculate the net present value for a series of cash flows that is periodic, use the NPV function.
108
     *
109
     * Excel Function:
110
     *        =XNPV(rate,values,dates)
111
     *
112
     * @param mixed $rate the discount rate to apply to the cash flows, expect array|float
113
     * @param array<int,float|int|numeric-string> $values A series of cash flows that corresponds to a schedule of payments in dates, expecting float[].
114
     *                          The first payment is optional and corresponds to a cost or payment that occurs
115
     *                              at the beginning of the investment.
116
     *                          If the first value is a cost or payment, it must be a negative value.
117
     *                             All succeeding payments are discounted based on a 365-day year.
118
     *                          The series of values must contain at least one positive value and one negative value.
119
     * @param mixed $dates A schedule of payment dates that corresponds to the cash flow payments, expecting mixed[].
120
     *                         The first payment date indicates the beginning of the schedule of payments.
121
     *                         All other dates must be later than this date, but they may occur in any order.
122
     */
123 14
    public static function presentValue(mixed $rate, mixed $values, mixed $dates): float|string
124
    {
125 14
        return self::xnpvOrdered($rate, $values, $dates, true);
126
    }
127
128 34
    private static function bothNegAndPos(bool $neg, bool $pos): bool
129
    {
130 34
        return $neg && $pos;
131
    }
132
133
    /** @param array<int, float|int|numeric-string> $values */
134 40
    private static function xirrPart1(mixed &$values, mixed &$dates): string
135
    {
136
        /** @var array<int, float|int|numeric-string> */
137 40
        $temp = Functions::flattenArray($values);
138 40
        $values = $temp;
139 40
        $dates = Functions::flattenArray($dates);
140 40
        $valuesIsArray = count($values) > 1;
141 40
        $datesIsArray = count($dates) > 1;
142 40
        if (!$valuesIsArray && !$datesIsArray) {
143 1
            return ExcelError::NA();
144
        }
145 39
        if (count($values) != count($dates)) {
146 3
            return ExcelError::NAN();
147
        }
148
149 36
        $datesCount = count($dates);
150 36
        for ($i = 0; $i < $datesCount; ++$i) {
151
            try {
152 36
                $dates[$i] = DateTimeExcel\Helpers::getDateValue($dates[$i]);
153 1
            } catch (Exception $e) {
154 1
                return $e->getMessage();
155
            }
156
        }
157
158 35
        return self::xirrPart2($values);
159
    }
160
161
    /** @param array<int, float|int|numeric-string> $values */
162 35
    private static function xirrPart2(array &$values): string
163
    {
164 35
        $valCount = count($values);
165 35
        $foundpos = false;
166 35
        $foundneg = false;
167 35
        for ($i = 0; $i < $valCount; ++$i) {
168 35
            $fld = $values[$i];
169 35
            if (!is_numeric($fld)) { //* @phpstan-ignore-line
170 1
                return ExcelError::VALUE();
171 34
            } elseif ($fld > 0) {
172 31
                $foundpos = true;
173 33
            } elseif ($fld < 0) {
174 33
                $foundneg = true;
175
            }
176
        }
177 34
        if (!self::bothNegAndPos($foundneg, $foundpos)) {
178 4
            return ExcelError::NAN();
179
        }
180
181 30
        return '';
182
    }
183
184
    /**
185
     * @param array<int, float|int|numeric-string> $values
186
     * @param array<int, float|int|numeric-string> $dates
187
     */
188 23
    private static function xirrPart3(array $values, array $dates, float $x1, float $x2): float|string
189
    {
190 23
        $f = self::xnpvOrdered($x1, $values, $dates, false);
191 23
        if ($f < 0.0) {
192 9
            $rtb = $x1;
193 9
            $dx = $x2 - $x1;
194
        } else {
195 14
            $rtb = $x2;
196 14
            $dx = $x1 - $x2;
197
        }
198
199 23
        $rslt = ExcelError::VALUE();
200 23
        for ($i = 0; $i < self::FINANCIAL_MAX_ITERATIONS; ++$i) {
201 23
            $dx *= 0.5;
202 23
            $x_mid = $rtb + $dx;
203 23
            $f_mid = (float) self::xnpvOrdered($x_mid, $values, $dates, false);
204 23
            if ($f_mid <= 0.0) {
205 21
                $rtb = $x_mid;
206
            }
207 23
            if ((abs($f_mid) < self::FINANCIAL_PRECISION) || (abs($dx) < self::FINANCIAL_PRECISION)) {
208 23
                $rslt = $x_mid;
209
210 23
                break;
211
            }
212
        }
213
214 23
        return $rslt;
215
    }
216
217
    /**
218
     * @param array<int, float|int|numeric-string> $values
219
     * @param array<int, float|int|numeric-string> $dates
220
     */
221 5
    private static function xirrBisection(array $values, array $dates, float $x1, float $x2): string|float
222
    {
223 5
        $rslt = ExcelError::NAN();
224 5
        for ($i = 0; $i < self::FINANCIAL_MAX_ITERATIONS; ++$i) {
225 5
            $rslt = ExcelError::NAN();
226 5
            $f1 = self::xnpvOrdered($x1, $values, $dates, false, true);
227 5
            $f2 = self::xnpvOrdered($x2, $values, $dates, false, true);
228 5
            if (!is_numeric($f1) || !is_numeric($f2)) {
229
                break;
230
            }
231 5
            $f1 = (float) $f1;
232 5
            $f2 = (float) $f2;
233 5
            if (abs($f1) < self::FINANCIAL_PRECISION && abs($f2) < self::FINANCIAL_PRECISION) {
234
                break;
235
            }
236 5
            if ($f1 * $f2 > 0) {
237
                break;
238
            }
239 5
            $rslt = ($x1 + $x2) / 2;
240 5
            $f3 = self::xnpvOrdered($rslt, $values, $dates, false, true);
241 5
            if (!is_float($f3)) {
242
                break;
243
            }
244 5
            if ($f3 * $f1 < 0) {
245 5
                $x2 = $rslt;
246
            } else {
247 5
                $x1 = $rslt;
248
            }
249 5
            if (abs($f3) < self::FINANCIAL_PRECISION) {
250 4
                break;
251
            }
252
        }
253
254 5
        return $rslt;
255
    }
256
257
    /** @param array<int,float|int|numeric-string> $values> */
258 43
    private static function xnpvOrdered(mixed $rate, mixed $values, mixed $dates, bool $ordered = true, bool $capAtNegative1 = false): float|string
259
    {
260 43
        $rate = Functions::flattenSingleValue($rate);
261 43
        if (!is_numeric($rate)) {
262 1
            return ExcelError::VALUE();
263
        }
264 42
        $values = Functions::flattenArray($values);
265 42
        $dates = Functions::flattenArray($dates);
266 42
        $valCount = count($values);
267
268
        try {
269 42
            self::validateXnpv($rate, $values, $dates);
270 39
            if ($capAtNegative1 && $rate <= -1) {
271 3
                $rate = -1.0 + 1.0E-10;
272
            }
273 39
            $date0 = DateTimeExcel\Helpers::getDateValue($dates[0]);
274 4
        } catch (Exception $e) {
275 4
            return $e->getMessage();
276
        }
277
278 38
        $xnpv = 0.0;
279 38
        for ($i = 0; $i < $valCount; ++$i) {
280 38
            if (!is_numeric($values[$i])) {
281 1
                return ExcelError::VALUE();
282
            }
283
284
            try {
285 38
                $datei = DateTimeExcel\Helpers::getDateValue($dates[$i]);
286 1
            } catch (Exception $e) {
287 1
                return $e->getMessage();
288
            }
289 38
            if ($date0 > $datei) {
290 4
                $dif = $ordered ? ExcelError::NAN() : -((int) DateTimeExcel\Difference::interval($datei, $date0, 'd'));
291
            } else {
292 38
                $dif = Functions::scalar(DateTimeExcel\Difference::interval($date0, $datei, 'd'));
293
            }
294 38
            if (!is_numeric($dif)) {
295 1
                return StringHelper::convertToString($dif);
296
            }
297 38
            if ($rate <= -1.0) {
298 6
                $xnpv += -abs($values[$i] + 0) / (-1 - $rate) ** ($dif / 365);
299
            } else {
300 38
                $xnpv += $values[$i] / (1 + $rate) ** ($dif / 365);
301
            }
302
        }
303
304 35
        return is_finite($xnpv) ? $xnpv : ExcelError::VALUE();
305
    }
306
307
    /**
308
     * @param mixed[] $values
309
     * @param mixed[] $dates
310
     */
311 42
    private static function validateXnpv(mixed $rate, array $values, array $dates): void
312
    {
313 42
        if (!is_numeric($rate)) {
314
            throw new Exception(ExcelError::VALUE());
315
        }
316 42
        $valCount = count($values);
317 42
        if ($valCount != count($dates)) {
318 1
            throw new Exception(ExcelError::NAN());
319
        }
320 41
        if (count($values) > 1 && ((min($values) > 0) || (max($values) < 0))) {
321 2
            throw new Exception(ExcelError::NAN());
322
        }
323
    }
324
}
325