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