PHPOffice /
PhpSpreadsheet
| Total Complexity | 367 |
| Total Lines | 2401 |
| Duplicated Lines | 0 % |
| Coverage | 65.61% |
| Changes | 0 | ||
Complex classes like Financial often do a lot of different things. To break such a class down, we need to identify a cohesive component within that class. A common approach to find such a component is to look for fields/methods that share the same prefixes, or suffixes.
Once you have determined the fields that belong together, you can apply the Extract Class refactoring. If the component makes sense as a sub-class, Extract Subclass is also a candidate, and is often faster.
While breaking up the class, it is a good idea to analyze how other classes use Financial, and based on these observations, apply Extract Interface, too.
| 1 | <?php |
||
| 7 | class Financial |
||
| 8 | { |
||
| 9 | const FINANCIAL_MAX_ITERATIONS = 32; |
||
| 10 | |||
| 11 | const FINANCIAL_PRECISION = 1.0e-08; |
||
| 12 | |||
| 13 | /** |
||
| 14 | * isLastDayOfMonth. |
||
| 15 | * |
||
| 16 | * Returns a boolean TRUE/FALSE indicating if this date is the last date of the month |
||
| 17 | * |
||
| 18 | * @param \DateTime $testDate The date for testing |
||
| 19 | * |
||
| 20 | * @return bool |
||
| 21 | */ |
||
| 22 | 12 | private static function isLastDayOfMonth(\DateTime $testDate) |
|
| 25 | } |
||
| 26 | |||
| 27 | 12 | private static function couponFirstPeriodDate($settlement, $maturity, $frequency, $next) |
|
| 28 | { |
||
| 29 | 12 | $months = 12 / $frequency; |
|
| 30 | |||
| 31 | 12 | $result = Date::excelToDateTimeObject($maturity); |
|
| 32 | 12 | $eom = self::isLastDayOfMonth($result); |
|
| 33 | |||
| 34 | 12 | while ($settlement < Date::PHPToExcel($result)) { |
|
| 35 | 12 | $result->modify('-' . $months . ' months'); |
|
| 36 | } |
||
| 37 | 12 | if ($next) { |
|
| 38 | 8 | $result->modify('+' . $months . ' months'); |
|
| 39 | } |
||
| 40 | |||
| 41 | 12 | if ($eom) { |
|
| 42 | 1 | $result->modify('-1 day'); |
|
| 43 | } |
||
| 44 | |||
| 45 | 12 | return Date::PHPToExcel($result); |
|
| 46 | } |
||
| 47 | |||
| 48 | 22 | private static function isValidFrequency($frequency) |
|
| 49 | { |
||
| 50 | 22 | if (($frequency == 1) || ($frequency == 2) || ($frequency == 4)) { |
|
| 51 | 16 | return true; |
|
| 52 | } |
||
| 53 | 6 | if ((Functions::getCompatibilityMode() == Functions::COMPATIBILITY_GNUMERIC) && |
|
| 54 | 6 | (($frequency == 6) || ($frequency == 12))) { |
|
| 55 | return true; |
||
| 56 | } |
||
| 57 | |||
| 58 | 6 | return false; |
|
| 59 | } |
||
| 60 | |||
| 61 | /** |
||
| 62 | * daysPerYear. |
||
| 63 | * |
||
| 64 | * Returns the number of days in a specified year, as defined by the "basis" value |
||
| 65 | * |
||
| 66 | * @param int|string $year The year against which we're testing |
||
| 67 | * @param int|string $basis The type of day count: |
||
| 68 | * 0 or omitted US (NASD) 360 |
||
| 69 | * 1 Actual (365 or 366 in a leap year) |
||
| 70 | * 2 360 |
||
| 71 | * 3 365 |
||
| 72 | * 4 European 360 |
||
| 73 | * |
||
| 74 | * @return int |
||
| 75 | */ |
||
| 76 | 6 | private static function daysPerYear($year, $basis = 0) |
|
| 77 | { |
||
| 78 | 6 | switch ($basis) { |
|
| 79 | 6 | case 0: |
|
| 80 | 4 | case 2: |
|
| 81 | 4 | case 4: |
|
| 82 | 2 | $daysPerYear = 360; |
|
| 83 | |||
| 84 | 2 | break; |
|
| 85 | 4 | case 3: |
|
| 86 | $daysPerYear = 365; |
||
| 87 | |||
| 88 | break; |
||
| 89 | 4 | case 1: |
|
| 90 | 4 | $daysPerYear = (DateTime::isLeapYear($year)) ? 366 : 365; |
|
| 91 | |||
| 92 | 4 | break; |
|
| 93 | default: |
||
| 94 | return Functions::NAN(); |
||
|
Bug
Best Practice
introduced
by
|
|||
| 95 | } |
||
| 96 | |||
| 97 | 6 | return $daysPerYear; |
|
| 98 | } |
||
| 99 | |||
| 100 | 20 | private static function interestAndPrincipal($rate = 0, $per = 0, $nper = 0, $pv = 0, $fv = 0, $type = 0) |
|
| 101 | { |
||
| 102 | 20 | $pmt = self::PMT($rate, $nper, $pv, $fv, $type); |
|
| 103 | 20 | $capital = $pv; |
|
| 104 | 20 | for ($i = 1; $i <= $per; ++$i) { |
|
| 105 | 20 | $interest = ($type && $i == 1) ? 0 : -$capital * $rate; |
|
| 106 | 20 | $principal = $pmt - $interest; |
|
| 107 | 20 | $capital += $principal; |
|
| 108 | } |
||
| 109 | |||
| 110 | 20 | return [$interest, $principal]; |
|
| 111 | } |
||
| 112 | |||
| 113 | /** |
||
| 114 | * ACCRINT. |
||
| 115 | * |
||
| 116 | * Returns the accrued interest for a security that pays periodic interest. |
||
| 117 | * |
||
| 118 | * Excel Function: |
||
| 119 | * ACCRINT(issue,firstinterest,settlement,rate,par,frequency[,basis]) |
||
| 120 | * |
||
| 121 | * @category Financial Functions |
||
| 122 | * |
||
| 123 | * @param mixed $issue the security's issue date |
||
| 124 | * @param mixed $firstinterest the security's first interest date |
||
| 125 | * @param mixed $settlement The security's settlement date. |
||
| 126 | * The security settlement date is the date after the issue date |
||
| 127 | * when the security is traded to the buyer. |
||
| 128 | * @param float $rate the security's annual coupon rate |
||
| 129 | * @param float $par The security's par value. |
||
| 130 | * If you omit par, ACCRINT uses $1,000. |
||
| 131 | * @param int $frequency the number of coupon payments per year. |
||
| 132 | * Valid frequency values are: |
||
| 133 | * 1 Annual |
||
| 134 | * 2 Semi-Annual |
||
| 135 | * 4 Quarterly |
||
| 136 | * If working in Gnumeric Mode, the following frequency options are |
||
| 137 | * also available |
||
| 138 | * 6 Bimonthly |
||
| 139 | * 12 Monthly |
||
| 140 | * @param int $basis The type of day count to use. |
||
| 141 | * 0 or omitted US (NASD) 30/360 |
||
| 142 | * 1 Actual/actual |
||
| 143 | * 2 Actual/360 |
||
| 144 | * 3 Actual/365 |
||
| 145 | * 4 European 30/360 |
||
| 146 | * |
||
| 147 | * @return float|string |
||
| 148 | */ |
||
| 149 | 7 | public static function ACCRINT($issue, $firstinterest, $settlement, $rate, $par = 1000, $frequency = 1, $basis = 0) |
|
| 150 | { |
||
| 151 | 7 | $issue = Functions::flattenSingleValue($issue); |
|
| 152 | 7 | $firstinterest = Functions::flattenSingleValue($firstinterest); |
|
| 153 | 7 | $settlement = Functions::flattenSingleValue($settlement); |
|
| 154 | 7 | $rate = Functions::flattenSingleValue($rate); |
|
| 155 | 7 | $par = ($par === null) ? 1000 : Functions::flattenSingleValue($par); |
|
| 156 | 7 | $frequency = ($frequency === null) ? 1 : Functions::flattenSingleValue($frequency); |
|
| 157 | 7 | $basis = ($basis === null) ? 0 : Functions::flattenSingleValue($basis); |
|
| 158 | |||
| 159 | // Validate |
||
| 160 | 7 | if ((is_numeric($rate)) && (is_numeric($par))) { |
|
| 161 | 6 | $rate = (float) $rate; |
|
| 162 | 6 | $par = (float) $par; |
|
| 163 | 6 | if (($rate <= 0) || ($par <= 0)) { |
|
| 164 | 1 | return Functions::NAN(); |
|
| 165 | } |
||
| 166 | 5 | $daysBetweenIssueAndSettlement = DateTime::YEARFRAC($issue, $settlement, $basis); |
|
| 167 | 5 | if (!is_numeric($daysBetweenIssueAndSettlement)) { |
|
| 168 | // return date error |
||
| 169 | 2 | return $daysBetweenIssueAndSettlement; |
|
| 170 | } |
||
| 171 | |||
| 172 | 3 | return $par * $rate * $daysBetweenIssueAndSettlement; |
|
| 173 | } |
||
| 174 | |||
| 175 | 1 | return Functions::VALUE(); |
|
| 176 | } |
||
| 177 | |||
| 178 | /** |
||
| 179 | * ACCRINTM. |
||
| 180 | * |
||
| 181 | * Returns the accrued interest for a security that pays interest at maturity. |
||
| 182 | * |
||
| 183 | * Excel Function: |
||
| 184 | * ACCRINTM(issue,settlement,rate[,par[,basis]]) |
||
| 185 | * |
||
| 186 | * @category Financial Functions |
||
| 187 | * |
||
| 188 | * @param mixed $issue The security's issue date |
||
| 189 | * @param mixed $settlement The security's settlement (or maturity) date |
||
| 190 | * @param float $rate The security's annual coupon rate |
||
| 191 | * @param float $par The security's par value. |
||
| 192 | * If you omit par, ACCRINT uses $1,000. |
||
| 193 | * @param int $basis The type of day count to use. |
||
| 194 | * 0 or omitted US (NASD) 30/360 |
||
| 195 | * 1 Actual/actual |
||
| 196 | * 2 Actual/360 |
||
| 197 | * 3 Actual/365 |
||
| 198 | * 4 European 30/360 |
||
| 199 | * |
||
| 200 | * @return float|string |
||
| 201 | */ |
||
| 202 | 5 | public static function ACCRINTM($issue, $settlement, $rate, $par = 1000, $basis = 0) |
|
| 203 | { |
||
| 204 | 5 | $issue = Functions::flattenSingleValue($issue); |
|
| 205 | 5 | $settlement = Functions::flattenSingleValue($settlement); |
|
| 206 | 5 | $rate = Functions::flattenSingleValue($rate); |
|
| 207 | 5 | $par = ($par === null) ? 1000 : Functions::flattenSingleValue($par); |
|
| 208 | 5 | $basis = ($basis === null) ? 0 : Functions::flattenSingleValue($basis); |
|
| 209 | |||
| 210 | // Validate |
||
| 211 | 5 | if ((is_numeric($rate)) && (is_numeric($par))) { |
|
| 212 | 4 | $rate = (float) $rate; |
|
| 213 | 4 | $par = (float) $par; |
|
| 214 | 4 | if (($rate <= 0) || ($par <= 0)) { |
|
| 215 | 1 | return Functions::NAN(); |
|
| 216 | } |
||
| 217 | 3 | $daysBetweenIssueAndSettlement = DateTime::YEARFRAC($issue, $settlement, $basis); |
|
| 218 | 3 | if (!is_numeric($daysBetweenIssueAndSettlement)) { |
|
| 219 | // return date error |
||
| 220 | 1 | return $daysBetweenIssueAndSettlement; |
|
| 221 | } |
||
| 222 | |||
| 223 | 2 | return $par * $rate * $daysBetweenIssueAndSettlement; |
|
| 224 | } |
||
| 225 | |||
| 226 | 1 | return Functions::VALUE(); |
|
| 227 | } |
||
| 228 | |||
| 229 | /** |
||
| 230 | * AMORDEGRC. |
||
| 231 | * |
||
| 232 | * Returns the depreciation for each accounting period. |
||
| 233 | * This function is provided for the French accounting system. If an asset is purchased in |
||
| 234 | * the middle of the accounting period, the prorated depreciation is taken into account. |
||
| 235 | * The function is similar to AMORLINC, except that a depreciation coefficient is applied in |
||
| 236 | * the calculation depending on the life of the assets. |
||
| 237 | * This function will return the depreciation until the last period of the life of the assets |
||
| 238 | * or until the cumulated value of depreciation is greater than the cost of the assets minus |
||
| 239 | * the salvage value. |
||
| 240 | * |
||
| 241 | * Excel Function: |
||
| 242 | * AMORDEGRC(cost,purchased,firstPeriod,salvage,period,rate[,basis]) |
||
| 243 | * |
||
| 244 | * @category Financial Functions |
||
| 245 | * |
||
| 246 | * @param float $cost The cost of the asset |
||
| 247 | * @param mixed $purchased Date of the purchase of the asset |
||
| 248 | * @param mixed $firstPeriod Date of the end of the first period |
||
| 249 | * @param mixed $salvage The salvage value at the end of the life of the asset |
||
| 250 | * @param float $period The period |
||
| 251 | * @param float $rate Rate of depreciation |
||
| 252 | * @param int $basis The type of day count to use. |
||
| 253 | * 0 or omitted US (NASD) 30/360 |
||
| 254 | * 1 Actual/actual |
||
| 255 | * 2 Actual/360 |
||
| 256 | * 3 Actual/365 |
||
| 257 | * 4 European 30/360 |
||
| 258 | * |
||
| 259 | * @return float |
||
| 260 | */ |
||
| 261 | 2 | public static function AMORDEGRC($cost, $purchased, $firstPeriod, $salvage, $period, $rate, $basis = 0) |
|
| 262 | { |
||
| 263 | 2 | $cost = Functions::flattenSingleValue($cost); |
|
| 264 | 2 | $purchased = Functions::flattenSingleValue($purchased); |
|
| 265 | 2 | $firstPeriod = Functions::flattenSingleValue($firstPeriod); |
|
| 266 | 2 | $salvage = Functions::flattenSingleValue($salvage); |
|
| 267 | 2 | $period = floor(Functions::flattenSingleValue($period)); |
|
| 268 | 2 | $rate = Functions::flattenSingleValue($rate); |
|
| 269 | 2 | $basis = ($basis === null) ? 0 : (int) Functions::flattenSingleValue($basis); |
|
| 270 | |||
| 271 | // The depreciation coefficients are: |
||
| 272 | // Life of assets (1/rate) Depreciation coefficient |
||
| 273 | // Less than 3 years 1 |
||
| 274 | // Between 3 and 4 years 1.5 |
||
| 275 | // Between 5 and 6 years 2 |
||
| 276 | // More than 6 years 2.5 |
||
| 277 | 2 | $fUsePer = 1.0 / $rate; |
|
| 278 | 2 | if ($fUsePer < 3.0) { |
|
| 279 | $amortiseCoeff = 1.0; |
||
| 280 | 2 | } elseif ($fUsePer < 5.0) { |
|
| 281 | $amortiseCoeff = 1.5; |
||
| 282 | 2 | } elseif ($fUsePer <= 6.0) { |
|
| 283 | 1 | $amortiseCoeff = 2.0; |
|
| 284 | } else { |
||
| 285 | 1 | $amortiseCoeff = 2.5; |
|
| 286 | } |
||
| 287 | |||
| 288 | 2 | $rate *= $amortiseCoeff; |
|
| 289 | 2 | $fNRate = round(DateTime::YEARFRAC($purchased, $firstPeriod, $basis) * $rate * $cost, 0); |
|
| 290 | 2 | $cost -= $fNRate; |
|
| 291 | 2 | $fRest = $cost - $salvage; |
|
| 292 | |||
| 293 | 2 | for ($n = 0; $n < $period; ++$n) { |
|
| 294 | 2 | $fNRate = round($rate * $cost, 0); |
|
| 295 | 2 | $fRest -= $fNRate; |
|
| 296 | |||
| 297 | 2 | if ($fRest < 0.0) { |
|
| 298 | switch ($period - $n) { |
||
| 299 | case 0: |
||
| 300 | case 1: |
||
| 301 | return round($cost * 0.5, 0); |
||
| 302 | default: |
||
| 303 | return 0.0; |
||
| 304 | } |
||
| 305 | } |
||
| 306 | 2 | $cost -= $fNRate; |
|
| 307 | } |
||
| 308 | |||
| 309 | 2 | return $fNRate; |
|
| 310 | } |
||
| 311 | |||
| 312 | /** |
||
| 313 | * AMORLINC. |
||
| 314 | * |
||
| 315 | * Returns the depreciation for each accounting period. |
||
| 316 | * This function is provided for the French accounting system. If an asset is purchased in |
||
| 317 | * the middle of the accounting period, the prorated depreciation is taken into account. |
||
| 318 | * |
||
| 319 | * Excel Function: |
||
| 320 | * AMORLINC(cost,purchased,firstPeriod,salvage,period,rate[,basis]) |
||
| 321 | * |
||
| 322 | * @category Financial Functions |
||
| 323 | * |
||
| 324 | * @param float $cost The cost of the asset |
||
| 325 | * @param mixed $purchased Date of the purchase of the asset |
||
| 326 | * @param mixed $firstPeriod Date of the end of the first period |
||
| 327 | * @param mixed $salvage The salvage value at the end of the life of the asset |
||
| 328 | * @param float $period The period |
||
| 329 | * @param float $rate Rate of depreciation |
||
| 330 | * @param int $basis The type of day count to use. |
||
| 331 | * 0 or omitted US (NASD) 30/360 |
||
| 332 | * 1 Actual/actual |
||
| 333 | * 2 Actual/360 |
||
| 334 | * 3 Actual/365 |
||
| 335 | * 4 European 30/360 |
||
| 336 | * |
||
| 337 | * @return float |
||
| 338 | */ |
||
| 339 | 2 | public static function AMORLINC($cost, $purchased, $firstPeriod, $salvage, $period, $rate, $basis = 0) |
|
| 340 | { |
||
| 341 | 2 | $cost = Functions::flattenSingleValue($cost); |
|
| 342 | 2 | $purchased = Functions::flattenSingleValue($purchased); |
|
| 343 | 2 | $firstPeriod = Functions::flattenSingleValue($firstPeriod); |
|
| 344 | 2 | $salvage = Functions::flattenSingleValue($salvage); |
|
| 345 | 2 | $period = Functions::flattenSingleValue($period); |
|
| 346 | 2 | $rate = Functions::flattenSingleValue($rate); |
|
| 347 | 2 | $basis = ($basis === null) ? 0 : (int) Functions::flattenSingleValue($basis); |
|
| 348 | |||
| 349 | 2 | $fOneRate = $cost * $rate; |
|
| 350 | 2 | $fCostDelta = $cost - $salvage; |
|
| 351 | // Note, quirky variation for leap years on the YEARFRAC for this function |
||
| 352 | 2 | $purchasedYear = DateTime::YEAR($purchased); |
|
| 353 | 2 | $yearFrac = DateTime::YEARFRAC($purchased, $firstPeriod, $basis); |
|
| 354 | |||
| 355 | 2 | if (($basis == 1) && ($yearFrac < 1) && (DateTime::isLeapYear($purchasedYear))) { |
|
| 356 | 1 | $yearFrac *= 365 / 366; |
|
| 357 | } |
||
| 358 | |||
| 359 | 2 | $f0Rate = $yearFrac * $rate * $cost; |
|
| 360 | 2 | $nNumOfFullPeriods = (int) (($cost - $salvage - $f0Rate) / $fOneRate); |
|
| 361 | |||
| 362 | 2 | if ($period == 0) { |
|
| 363 | return $f0Rate; |
||
| 364 | 2 | } elseif ($period <= $nNumOfFullPeriods) { |
|
| 365 | 2 | return $fOneRate; |
|
| 366 | } elseif ($period == ($nNumOfFullPeriods + 1)) { |
||
| 367 | return $fCostDelta - $fOneRate * $nNumOfFullPeriods - $f0Rate; |
||
| 368 | } |
||
| 369 | |||
| 370 | return 0.0; |
||
| 371 | } |
||
| 372 | |||
| 373 | /** |
||
| 374 | * COUPDAYBS. |
||
| 375 | * |
||
| 376 | * Returns the number of days from the beginning of the coupon period to the settlement date. |
||
| 377 | * |
||
| 378 | * Excel Function: |
||
| 379 | * COUPDAYBS(settlement,maturity,frequency[,basis]) |
||
| 380 | * |
||
| 381 | * @category Financial Functions |
||
| 382 | * |
||
| 383 | * @param mixed $settlement The security's settlement date. |
||
| 384 | * The security settlement date is the date after the issue |
||
| 385 | * date when the security is traded to the buyer. |
||
| 386 | * @param mixed $maturity The security's maturity date. |
||
| 387 | * The maturity date is the date when the security expires. |
||
| 388 | * @param int $frequency the number of coupon payments per year. |
||
| 389 | * Valid frequency values are: |
||
| 390 | * 1 Annual |
||
| 391 | * 2 Semi-Annual |
||
| 392 | * 4 Quarterly |
||
| 393 | * If working in Gnumeric Mode, the following frequency options are |
||
| 394 | * also available |
||
| 395 | * 6 Bimonthly |
||
| 396 | * 12 Monthly |
||
| 397 | * @param int $basis The type of day count to use. |
||
| 398 | * 0 or omitted US (NASD) 30/360 |
||
| 399 | * 1 Actual/actual |
||
| 400 | * 2 Actual/360 |
||
| 401 | * 3 Actual/365 |
||
| 402 | * 4 European 30/360 |
||
| 403 | * |
||
| 404 | * @return float|string |
||
| 405 | */ |
||
| 406 | 5 | public static function COUPDAYBS($settlement, $maturity, $frequency, $basis = 0) |
|
| 407 | { |
||
| 408 | 5 | $settlement = Functions::flattenSingleValue($settlement); |
|
| 409 | 5 | $maturity = Functions::flattenSingleValue($maturity); |
|
| 410 | 5 | $frequency = (int) Functions::flattenSingleValue($frequency); |
|
| 411 | 5 | $basis = ($basis === null) ? 0 : (int) Functions::flattenSingleValue($basis); |
|
| 412 | |||
| 413 | 5 | if (is_string($settlement = DateTime::getDateValue($settlement))) { |
|
| 414 | 1 | return Functions::VALUE(); |
|
| 415 | } |
||
| 416 | 4 | if (is_string($maturity = DateTime::getDateValue($maturity))) { |
|
| 417 | 1 | return Functions::VALUE(); |
|
| 418 | } |
||
| 419 | |||
| 420 | 3 | if (($settlement > $maturity) || |
|
| 421 | 3 | (!self::isValidFrequency($frequency)) || |
|
| 422 | 3 | (($basis < 0) || ($basis > 4))) { |
|
| 423 | 1 | return Functions::NAN(); |
|
| 424 | } |
||
| 425 | |||
| 426 | 2 | $daysPerYear = self::daysPerYear(DateTime::YEAR($settlement), $basis); |
|
| 427 | 2 | $prev = self::couponFirstPeriodDate($settlement, $maturity, $frequency, false); |
|
| 428 | |||
| 429 | 2 | return DateTime::YEARFRAC($prev, $settlement, $basis) * $daysPerYear; |
|
| 430 | } |
||
| 431 | |||
| 432 | /** |
||
| 433 | * COUPDAYS. |
||
| 434 | * |
||
| 435 | * Returns the number of days in the coupon period that contains the settlement date. |
||
| 436 | * |
||
| 437 | * Excel Function: |
||
| 438 | * COUPDAYS(settlement,maturity,frequency[,basis]) |
||
| 439 | * |
||
| 440 | * @category Financial Functions |
||
| 441 | * |
||
| 442 | * @param mixed $settlement The security's settlement date. |
||
| 443 | * The security settlement date is the date after the issue |
||
| 444 | * date when the security is traded to the buyer. |
||
| 445 | * @param mixed $maturity The security's maturity date. |
||
| 446 | * The maturity date is the date when the security expires. |
||
| 447 | * @param mixed $frequency the number of coupon payments per year. |
||
| 448 | * Valid frequency values are: |
||
| 449 | * 1 Annual |
||
| 450 | * 2 Semi-Annual |
||
| 451 | * 4 Quarterly |
||
| 452 | * If working in Gnumeric Mode, the following frequency options are |
||
| 453 | * also available |
||
| 454 | * 6 Bimonthly |
||
| 455 | * 12 Monthly |
||
| 456 | * @param int $basis The type of day count to use. |
||
| 457 | * 0 or omitted US (NASD) 30/360 |
||
| 458 | * 1 Actual/actual |
||
| 459 | * 2 Actual/360 |
||
| 460 | * 3 Actual/365 |
||
| 461 | * 4 European 30/360 |
||
| 462 | * |
||
| 463 | * @return float|string |
||
| 464 | */ |
||
| 465 | 8 | public static function COUPDAYS($settlement, $maturity, $frequency, $basis = 0) |
|
| 466 | { |
||
| 467 | 8 | $settlement = Functions::flattenSingleValue($settlement); |
|
| 468 | 8 | $maturity = Functions::flattenSingleValue($maturity); |
|
| 469 | 8 | $frequency = (int) Functions::flattenSingleValue($frequency); |
|
| 470 | 8 | $basis = ($basis === null) ? 0 : (int) Functions::flattenSingleValue($basis); |
|
| 471 | |||
| 472 | 8 | if (is_string($settlement = DateTime::getDateValue($settlement))) { |
|
| 473 | 1 | return Functions::VALUE(); |
|
| 474 | } |
||
| 475 | 7 | if (is_string($maturity = DateTime::getDateValue($maturity))) { |
|
| 476 | 1 | return Functions::VALUE(); |
|
| 477 | } |
||
| 478 | |||
| 479 | 6 | if (($settlement > $maturity) || |
|
| 480 | 6 | (!self::isValidFrequency($frequency)) || |
|
| 481 | 6 | (($basis < 0) || ($basis > 4))) { |
|
| 482 | 1 | return Functions::NAN(); |
|
| 483 | } |
||
| 484 | |||
| 485 | switch ($basis) { |
||
| 486 | 5 | case 3: |
|
| 487 | // Actual/365 |
||
| 488 | 1 | return 365 / $frequency; |
|
| 489 | 4 | case 1: |
|
| 490 | // Actual/actual |
||
| 491 | 3 | if ($frequency == 1) { |
|
| 492 | 2 | $daysPerYear = self::daysPerYear(DateTime::YEAR($settlement), $basis); |
|
| 493 | |||
| 494 | 2 | return $daysPerYear / $frequency; |
|
| 495 | } |
||
| 496 | 1 | $prev = self::couponFirstPeriodDate($settlement, $maturity, $frequency, false); |
|
| 497 | 1 | $next = self::couponFirstPeriodDate($settlement, $maturity, $frequency, true); |
|
| 498 | |||
| 499 | 1 | return $next - $prev; |
|
| 500 | default: |
||
| 501 | // US (NASD) 30/360, Actual/360 or European 30/360 |
||
| 502 | 1 | return 360 / $frequency; |
|
| 503 | } |
||
| 504 | } |
||
| 505 | |||
| 506 | /** |
||
| 507 | * COUPDAYSNC. |
||
| 508 | * |
||
| 509 | * Returns the number of days from the settlement date to the next coupon date. |
||
| 510 | * |
||
| 511 | * Excel Function: |
||
| 512 | * COUPDAYSNC(settlement,maturity,frequency[,basis]) |
||
| 513 | * |
||
| 514 | * @category Financial Functions |
||
| 515 | * |
||
| 516 | * @param mixed $settlement The security's settlement date. |
||
| 517 | * The security settlement date is the date after the issue |
||
| 518 | * date when the security is traded to the buyer. |
||
| 519 | * @param mixed $maturity The security's maturity date. |
||
| 520 | * The maturity date is the date when the security expires. |
||
| 521 | * @param mixed $frequency the number of coupon payments per year. |
||
| 522 | * Valid frequency values are: |
||
| 523 | * 1 Annual |
||
| 524 | * 2 Semi-Annual |
||
| 525 | * 4 Quarterly |
||
| 526 | * If working in Gnumeric Mode, the following frequency options are |
||
| 527 | * also available |
||
| 528 | * 6 Bimonthly |
||
| 529 | * 12 Monthly |
||
| 530 | * @param int $basis The type of day count to use. |
||
| 531 | * 0 or omitted US (NASD) 30/360 |
||
| 532 | * 1 Actual/actual |
||
| 533 | * 2 Actual/360 |
||
| 534 | * 3 Actual/365 |
||
| 535 | * 4 European 30/360 |
||
| 536 | * |
||
| 537 | * @return float|string |
||
| 538 | */ |
||
| 539 | 5 | public static function COUPDAYSNC($settlement, $maturity, $frequency, $basis = 0) |
|
| 540 | { |
||
| 541 | 5 | $settlement = Functions::flattenSingleValue($settlement); |
|
| 542 | 5 | $maturity = Functions::flattenSingleValue($maturity); |
|
| 543 | 5 | $frequency = (int) Functions::flattenSingleValue($frequency); |
|
| 544 | 5 | $basis = ($basis === null) ? 0 : (int) Functions::flattenSingleValue($basis); |
|
| 545 | |||
| 546 | 5 | if (is_string($settlement = DateTime::getDateValue($settlement))) { |
|
| 547 | 1 | return Functions::VALUE(); |
|
| 548 | } |
||
| 549 | 4 | if (is_string($maturity = DateTime::getDateValue($maturity))) { |
|
| 550 | 1 | return Functions::VALUE(); |
|
| 551 | } |
||
| 552 | |||
| 553 | 3 | if (($settlement > $maturity) || |
|
| 554 | 3 | (!self::isValidFrequency($frequency)) || |
|
| 555 | 3 | (($basis < 0) || ($basis > 4))) { |
|
| 556 | 1 | return Functions::NAN(); |
|
| 557 | } |
||
| 558 | |||
| 559 | 2 | $daysPerYear = self::daysPerYear(DateTime::YEAR($settlement), $basis); |
|
| 560 | 2 | $next = self::couponFirstPeriodDate($settlement, $maturity, $frequency, true); |
|
| 561 | |||
| 562 | 2 | return DateTime::YEARFRAC($settlement, $next, $basis) * $daysPerYear; |
|
| 563 | } |
||
| 564 | |||
| 565 | /** |
||
| 566 | * COUPNCD. |
||
| 567 | * |
||
| 568 | * Returns the next coupon date after the settlement date. |
||
| 569 | * |
||
| 570 | * Excel Function: |
||
| 571 | * COUPNCD(settlement,maturity,frequency[,basis]) |
||
| 572 | * |
||
| 573 | * @category Financial Functions |
||
| 574 | * |
||
| 575 | * @param mixed $settlement The security's settlement date. |
||
| 576 | * The security settlement date is the date after the issue |
||
| 577 | * date when the security is traded to the buyer. |
||
| 578 | * @param mixed $maturity The security's maturity date. |
||
| 579 | * The maturity date is the date when the security expires. |
||
| 580 | * @param mixed $frequency the number of coupon payments per year. |
||
| 581 | * Valid frequency values are: |
||
| 582 | * 1 Annual |
||
| 583 | * 2 Semi-Annual |
||
| 584 | * 4 Quarterly |
||
| 585 | * If working in Gnumeric Mode, the following frequency options are |
||
| 586 | * also available |
||
| 587 | * 6 Bimonthly |
||
| 588 | * 12 Monthly |
||
| 589 | * @param int $basis The type of day count to use. |
||
| 590 | * 0 or omitted US (NASD) 30/360 |
||
| 591 | * 1 Actual/actual |
||
| 592 | * 2 Actual/360 |
||
| 593 | * 3 Actual/365 |
||
| 594 | * 4 European 30/360 |
||
| 595 | * |
||
| 596 | * @return mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object, |
||
| 597 | * depending on the value of the ReturnDateType flag |
||
| 598 | */ |
||
| 599 | 5 | public static function COUPNCD($settlement, $maturity, $frequency, $basis = 0) |
|
| 600 | { |
||
| 601 | 5 | $settlement = Functions::flattenSingleValue($settlement); |
|
| 602 | 5 | $maturity = Functions::flattenSingleValue($maturity); |
|
| 603 | 5 | $frequency = (int) Functions::flattenSingleValue($frequency); |
|
| 604 | 5 | $basis = ($basis === null) ? 0 : (int) Functions::flattenSingleValue($basis); |
|
| 605 | |||
| 606 | 5 | if (is_string($settlement = DateTime::getDateValue($settlement))) { |
|
| 607 | 1 | return Functions::VALUE(); |
|
| 608 | } |
||
| 609 | 4 | if (is_string($maturity = DateTime::getDateValue($maturity))) { |
|
| 610 | 1 | return Functions::VALUE(); |
|
| 611 | } |
||
| 612 | |||
| 613 | 3 | if (($settlement > $maturity) || |
|
| 614 | 3 | (!self::isValidFrequency($frequency)) || |
|
| 615 | 3 | (($basis < 0) || ($basis > 4))) { |
|
| 616 | 1 | return Functions::NAN(); |
|
| 617 | } |
||
| 618 | |||
| 619 | 2 | return self::couponFirstPeriodDate($settlement, $maturity, $frequency, true); |
|
| 620 | } |
||
| 621 | |||
| 622 | /** |
||
| 623 | * COUPNUM. |
||
| 624 | * |
||
| 625 | * Returns the number of coupons payable between the settlement date and maturity date, |
||
| 626 | * rounded up to the nearest whole coupon. |
||
| 627 | * |
||
| 628 | * Excel Function: |
||
| 629 | * COUPNUM(settlement,maturity,frequency[,basis]) |
||
| 630 | * |
||
| 631 | * @category Financial Functions |
||
| 632 | * |
||
| 633 | * @param mixed $settlement The security's settlement date. |
||
| 634 | * The security settlement date is the date after the issue |
||
| 635 | * date when the security is traded to the buyer. |
||
| 636 | * @param mixed $maturity The security's maturity date. |
||
| 637 | * The maturity date is the date when the security expires. |
||
| 638 | * @param mixed $frequency the number of coupon payments per year. |
||
| 639 | * Valid frequency values are: |
||
| 640 | * 1 Annual |
||
| 641 | * 2 Semi-Annual |
||
| 642 | * 4 Quarterly |
||
| 643 | * If working in Gnumeric Mode, the following frequency options are |
||
| 644 | * also available |
||
| 645 | * 6 Bimonthly |
||
| 646 | * 12 Monthly |
||
| 647 | * @param int $basis The type of day count to use. |
||
| 648 | * 0 or omitted US (NASD) 30/360 |
||
| 649 | * 1 Actual/actual |
||
| 650 | * 2 Actual/360 |
||
| 651 | * 3 Actual/365 |
||
| 652 | * 4 European 30/360 |
||
| 653 | * |
||
| 654 | * @return int|string |
||
| 655 | */ |
||
| 656 | 6 | public static function COUPNUM($settlement, $maturity, $frequency, $basis = 0) |
|
| 657 | { |
||
| 658 | 6 | $settlement = Functions::flattenSingleValue($settlement); |
|
| 659 | 6 | $maturity = Functions::flattenSingleValue($maturity); |
|
| 660 | 6 | $frequency = (int) Functions::flattenSingleValue($frequency); |
|
| 661 | 6 | $basis = ($basis === null) ? 0 : (int) Functions::flattenSingleValue($basis); |
|
| 662 | |||
| 663 | 6 | if (is_string($settlement = DateTime::getDateValue($settlement))) { |
|
| 664 | 1 | return Functions::VALUE(); |
|
| 665 | } |
||
| 666 | 5 | if (is_string($maturity = DateTime::getDateValue($maturity))) { |
|
| 667 | 1 | return Functions::VALUE(); |
|
| 668 | } |
||
| 669 | |||
| 670 | 4 | if (($settlement > $maturity) || |
|
| 671 | 4 | (!self::isValidFrequency($frequency)) || |
|
| 672 | 4 | (($basis < 0) || ($basis > 4))) { |
|
| 673 | 1 | return Functions::NAN(); |
|
| 674 | } |
||
| 675 | |||
| 676 | 3 | $settlement = self::couponFirstPeriodDate($settlement, $maturity, $frequency, true); |
|
| 677 | 3 | $daysBetweenSettlementAndMaturity = DateTime::YEARFRAC($settlement, $maturity, $basis) * 365; |
|
| 678 | |||
| 679 | 3 | switch ($frequency) { |
|
| 680 | 3 | case 1: // annual payments |
|
| 681 | 1 | return ceil($daysBetweenSettlementAndMaturity / 360); |
|
| 682 | 2 | case 2: // half-yearly |
|
| 683 | 1 | return ceil($daysBetweenSettlementAndMaturity / 180); |
|
| 684 | 1 | case 4: // quarterly |
|
| 685 | 1 | return ceil($daysBetweenSettlementAndMaturity / 90); |
|
| 686 | case 6: // bimonthly |
||
| 687 | return ceil($daysBetweenSettlementAndMaturity / 60); |
||
| 688 | case 12: // monthly |
||
| 689 | return ceil($daysBetweenSettlementAndMaturity / 30); |
||
| 690 | } |
||
| 691 | |||
| 692 | return Functions::VALUE(); |
||
| 693 | } |
||
| 694 | |||
| 695 | /** |
||
| 696 | * COUPPCD. |
||
| 697 | * |
||
| 698 | * Returns the previous coupon date before the settlement date. |
||
| 699 | * |
||
| 700 | * Excel Function: |
||
| 701 | * COUPPCD(settlement,maturity,frequency[,basis]) |
||
| 702 | * |
||
| 703 | * @category Financial Functions |
||
| 704 | * |
||
| 705 | * @param mixed $settlement The security's settlement date. |
||
| 706 | * The security settlement date is the date after the issue |
||
| 707 | * date when the security is traded to the buyer. |
||
| 708 | * @param mixed $maturity The security's maturity date. |
||
| 709 | * The maturity date is the date when the security expires. |
||
| 710 | * @param mixed $frequency the number of coupon payments per year. |
||
| 711 | * Valid frequency values are: |
||
| 712 | * 1 Annual |
||
| 713 | * 2 Semi-Annual |
||
| 714 | * 4 Quarterly |
||
| 715 | * If working in Gnumeric Mode, the following frequency options are |
||
| 716 | * also available |
||
| 717 | * 6 Bimonthly |
||
| 718 | * 12 Monthly |
||
| 719 | * @param int $basis The type of day count to use. |
||
| 720 | * 0 or omitted US (NASD) 30/360 |
||
| 721 | * 1 Actual/actual |
||
| 722 | * 2 Actual/360 |
||
| 723 | * 3 Actual/365 |
||
| 724 | * 4 European 30/360 |
||
| 725 | * |
||
| 726 | * @return mixed Excel date/time serial value, PHP date/time serial value or PHP date/time object, |
||
| 727 | * depending on the value of the ReturnDateType flag |
||
| 728 | */ |
||
| 729 | 5 | public static function COUPPCD($settlement, $maturity, $frequency, $basis = 0) |
|
| 730 | { |
||
| 731 | 5 | $settlement = Functions::flattenSingleValue($settlement); |
|
| 732 | 5 | $maturity = Functions::flattenSingleValue($maturity); |
|
| 733 | 5 | $frequency = (int) Functions::flattenSingleValue($frequency); |
|
| 734 | 5 | $basis = ($basis === null) ? 0 : (int) Functions::flattenSingleValue($basis); |
|
| 735 | |||
| 736 | 5 | if (is_string($settlement = DateTime::getDateValue($settlement))) { |
|
| 737 | 1 | return Functions::VALUE(); |
|
| 738 | } |
||
| 739 | 4 | if (is_string($maturity = DateTime::getDateValue($maturity))) { |
|
| 740 | 1 | return Functions::VALUE(); |
|
| 741 | } |
||
| 742 | |||
| 743 | 3 | if (($settlement > $maturity) || |
|
| 744 | 3 | (!self::isValidFrequency($frequency)) || |
|
| 745 | 3 | (($basis < 0) || ($basis > 4))) { |
|
| 746 | 1 | return Functions::NAN(); |
|
| 747 | } |
||
| 748 | |||
| 749 | 2 | return self::couponFirstPeriodDate($settlement, $maturity, $frequency, false); |
|
| 750 | } |
||
| 751 | |||
| 752 | /** |
||
| 753 | * CUMIPMT. |
||
| 754 | * |
||
| 755 | * Returns the cumulative interest paid on a loan between the start and end periods. |
||
| 756 | * |
||
| 757 | * Excel Function: |
||
| 758 | * CUMIPMT(rate,nper,pv,start,end[,type]) |
||
| 759 | * |
||
| 760 | * @category Financial Functions |
||
| 761 | * |
||
| 762 | * @param float $rate The Interest rate |
||
| 763 | * @param int $nper The total number of payment periods |
||
| 764 | * @param float $pv Present Value |
||
| 765 | * @param int $start The first period in the calculation. |
||
| 766 | * Payment periods are numbered beginning with 1. |
||
| 767 | * @param int $end the last period in the calculation |
||
| 768 | * @param int $type A number 0 or 1 and indicates when payments are due: |
||
| 769 | * 0 or omitted At the end of the period. |
||
| 770 | * 1 At the beginning of the period. |
||
| 771 | * |
||
| 772 | * @return float|string |
||
| 773 | */ |
||
| 774 | 9 | public static function CUMIPMT($rate, $nper, $pv, $start, $end, $type = 0) |
|
| 775 | { |
||
| 776 | 9 | $rate = Functions::flattenSingleValue($rate); |
|
| 777 | 9 | $nper = (int) Functions::flattenSingleValue($nper); |
|
| 778 | 9 | $pv = Functions::flattenSingleValue($pv); |
|
| 779 | 9 | $start = (int) Functions::flattenSingleValue($start); |
|
| 780 | 9 | $end = (int) Functions::flattenSingleValue($end); |
|
| 781 | 9 | $type = (int) Functions::flattenSingleValue($type); |
|
| 782 | |||
| 783 | // Validate parameters |
||
| 784 | 9 | if ($type != 0 && $type != 1) { |
|
| 785 | 1 | return Functions::NAN(); |
|
| 786 | } |
||
| 787 | 8 | if ($start < 1 || $start > $end) { |
|
| 788 | 1 | return Functions::VALUE(); |
|
| 789 | } |
||
| 790 | |||
| 791 | // Calculate |
||
| 792 | 7 | $interest = 0; |
|
| 793 | 7 | for ($per = $start; $per <= $end; ++$per) { |
|
| 794 | 7 | $interest += self::IPMT($rate, $per, $nper, $pv, 0, $type); |
|
| 795 | } |
||
| 796 | |||
| 797 | 7 | return $interest; |
|
| 798 | } |
||
| 799 | |||
| 800 | /** |
||
| 801 | * CUMPRINC. |
||
| 802 | * |
||
| 803 | * Returns the cumulative principal paid on a loan between the start and end periods. |
||
| 804 | * |
||
| 805 | * Excel Function: |
||
| 806 | * CUMPRINC(rate,nper,pv,start,end[,type]) |
||
| 807 | * |
||
| 808 | * @category Financial Functions |
||
| 809 | * |
||
| 810 | * @param float $rate The Interest rate |
||
| 811 | * @param int $nper The total number of payment periods |
||
| 812 | * @param float $pv Present Value |
||
| 813 | * @param int $start The first period in the calculation. |
||
| 814 | * Payment periods are numbered beginning with 1. |
||
| 815 | * @param int $end the last period in the calculation |
||
| 816 | * @param int $type A number 0 or 1 and indicates when payments are due: |
||
| 817 | * 0 or omitted At the end of the period. |
||
| 818 | * 1 At the beginning of the period. |
||
| 819 | * |
||
| 820 | * @return float|string |
||
| 821 | */ |
||
| 822 | 9 | public static function CUMPRINC($rate, $nper, $pv, $start, $end, $type = 0) |
|
| 823 | { |
||
| 824 | 9 | $rate = Functions::flattenSingleValue($rate); |
|
| 825 | 9 | $nper = (int) Functions::flattenSingleValue($nper); |
|
| 826 | 9 | $pv = Functions::flattenSingleValue($pv); |
|
| 827 | 9 | $start = (int) Functions::flattenSingleValue($start); |
|
| 828 | 9 | $end = (int) Functions::flattenSingleValue($end); |
|
| 829 | 9 | $type = (int) Functions::flattenSingleValue($type); |
|
| 830 | |||
| 831 | // Validate parameters |
||
| 832 | 9 | if ($type != 0 && $type != 1) { |
|
| 833 | 1 | return Functions::NAN(); |
|
| 834 | } |
||
| 835 | 8 | if ($start < 1 || $start > $end) { |
|
| 836 | 1 | return Functions::VALUE(); |
|
| 837 | } |
||
| 838 | |||
| 839 | // Calculate |
||
| 840 | 7 | $principal = 0; |
|
| 841 | 7 | for ($per = $start; $per <= $end; ++$per) { |
|
| 842 | 7 | $principal += self::PPMT($rate, $per, $nper, $pv, 0, $type); |
|
| 843 | } |
||
| 844 | |||
| 845 | 7 | return $principal; |
|
| 846 | } |
||
| 847 | |||
| 848 | /** |
||
| 849 | * DB. |
||
| 850 | * |
||
| 851 | * Returns the depreciation of an asset for a specified period using the |
||
| 852 | * fixed-declining balance method. |
||
| 853 | * This form of depreciation is used if you want to get a higher depreciation value |
||
| 854 | * at the beginning of the depreciation (as opposed to linear depreciation). The |
||
| 855 | * depreciation value is reduced with every depreciation period by the depreciation |
||
| 856 | * already deducted from the initial cost. |
||
| 857 | * |
||
| 858 | * Excel Function: |
||
| 859 | * DB(cost,salvage,life,period[,month]) |
||
| 860 | * |
||
| 861 | * @category Financial Functions |
||
| 862 | * |
||
| 863 | * @param float $cost Initial cost of the asset |
||
| 864 | * @param float $salvage Value at the end of the depreciation. |
||
| 865 | * (Sometimes called the salvage value of the asset) |
||
| 866 | * @param int $life Number of periods over which the asset is depreciated. |
||
| 867 | * (Sometimes called the useful life of the asset) |
||
| 868 | * @param int $period The period for which you want to calculate the |
||
| 869 | * depreciation. Period must use the same units as life. |
||
| 870 | * @param int $month Number of months in the first year. If month is omitted, |
||
| 871 | * it defaults to 12. |
||
| 872 | * |
||
| 873 | * @return float|string |
||
| 874 | */ |
||
| 875 | 16 | public static function DB($cost, $salvage, $life, $period, $month = 12) |
|
| 876 | { |
||
| 877 | 16 | $cost = Functions::flattenSingleValue($cost); |
|
| 878 | 16 | $salvage = Functions::flattenSingleValue($salvage); |
|
| 879 | 16 | $life = Functions::flattenSingleValue($life); |
|
| 880 | 16 | $period = Functions::flattenSingleValue($period); |
|
| 881 | 16 | $month = Functions::flattenSingleValue($month); |
|
| 882 | |||
| 883 | // Validate |
||
| 884 | 16 | if ((is_numeric($cost)) && (is_numeric($salvage)) && (is_numeric($life)) && (is_numeric($period)) && (is_numeric($month))) { |
|
| 885 | 15 | $cost = (float) $cost; |
|
| 886 | 15 | $salvage = (float) $salvage; |
|
| 887 | 15 | $life = (int) $life; |
|
| 888 | 15 | $period = (int) $period; |
|
| 889 | 15 | $month = (int) $month; |
|
| 890 | 15 | if ($cost == 0) { |
|
| 891 | 1 | return 0.0; |
|
| 892 | 14 | } elseif (($cost < 0) || (($salvage / $cost) < 0) || ($life <= 0) || ($period < 1) || ($month < 1)) { |
|
| 893 | 1 | return Functions::NAN(); |
|
| 894 | } |
||
| 895 | // Set Fixed Depreciation Rate |
||
| 896 | 13 | $fixedDepreciationRate = 1 - pow(($salvage / $cost), (1 / $life)); |
|
| 897 | 13 | $fixedDepreciationRate = round($fixedDepreciationRate, 3); |
|
| 898 | |||
| 899 | // Loop through each period calculating the depreciation |
||
| 900 | 13 | $previousDepreciation = 0; |
|
| 901 | 13 | for ($per = 1; $per <= $period; ++$per) { |
|
| 902 | 13 | if ($per == 1) { |
|
| 903 | 13 | $depreciation = $cost * $fixedDepreciationRate * $month / 12; |
|
| 904 | 11 | } elseif ($per == ($life + 1)) { |
|
| 905 | 2 | $depreciation = ($cost - $previousDepreciation) * $fixedDepreciationRate * (12 - $month) / 12; |
|
| 906 | } else { |
||
| 907 | 11 | $depreciation = ($cost - $previousDepreciation) * $fixedDepreciationRate; |
|
| 908 | } |
||
| 909 | 13 | $previousDepreciation += $depreciation; |
|
| 910 | } |
||
| 911 | 13 | if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_GNUMERIC) { |
|
| 912 | $depreciation = round($depreciation, 2); |
||
| 913 | } |
||
| 914 | |||
| 915 | 13 | return $depreciation; |
|
| 916 | } |
||
| 917 | |||
| 918 | 1 | return Functions::VALUE(); |
|
| 919 | } |
||
| 920 | |||
| 921 | /** |
||
| 922 | * DDB. |
||
| 923 | * |
||
| 924 | * Returns the depreciation of an asset for a specified period using the |
||
| 925 | * double-declining balance method or some other method you specify. |
||
| 926 | * |
||
| 927 | * Excel Function: |
||
| 928 | * DDB(cost,salvage,life,period[,factor]) |
||
| 929 | * |
||
| 930 | * @category Financial Functions |
||
| 931 | * |
||
| 932 | * @param float $cost Initial cost of the asset |
||
| 933 | * @param float $salvage Value at the end of the depreciation. |
||
| 934 | * (Sometimes called the salvage value of the asset) |
||
| 935 | * @param int $life Number of periods over which the asset is depreciated. |
||
| 936 | * (Sometimes called the useful life of the asset) |
||
| 937 | * @param int $period The period for which you want to calculate the |
||
| 938 | * depreciation. Period must use the same units as life. |
||
| 939 | * @param float $factor The rate at which the balance declines. |
||
| 940 | * If factor is omitted, it is assumed to be 2 (the |
||
| 941 | * double-declining balance method). |
||
| 942 | * |
||
| 943 | * @return float|string |
||
| 944 | */ |
||
| 945 | 15 | public static function DDB($cost, $salvage, $life, $period, $factor = 2.0) |
|
| 946 | { |
||
| 947 | 15 | $cost = Functions::flattenSingleValue($cost); |
|
| 948 | 15 | $salvage = Functions::flattenSingleValue($salvage); |
|
| 949 | 15 | $life = Functions::flattenSingleValue($life); |
|
| 950 | 15 | $period = Functions::flattenSingleValue($period); |
|
| 951 | 15 | $factor = Functions::flattenSingleValue($factor); |
|
| 952 | |||
| 953 | // Validate |
||
| 954 | 15 | if ((is_numeric($cost)) && (is_numeric($salvage)) && (is_numeric($life)) && (is_numeric($period)) && (is_numeric($factor))) { |
|
| 955 | 14 | $cost = (float) $cost; |
|
| 956 | 14 | $salvage = (float) $salvage; |
|
| 957 | 14 | $life = (int) $life; |
|
| 958 | 14 | $period = (int) $period; |
|
| 959 | 14 | $factor = (float) $factor; |
|
| 960 | 14 | if (($cost <= 0) || (($salvage / $cost) < 0) || ($life <= 0) || ($period < 1) || ($factor <= 0.0) || ($period > $life)) { |
|
| 961 | 1 | return Functions::NAN(); |
|
| 962 | } |
||
| 963 | // Set Fixed Depreciation Rate |
||
| 964 | 13 | $fixedDepreciationRate = 1 - pow(($salvage / $cost), (1 / $life)); |
|
| 965 | 13 | $fixedDepreciationRate = round($fixedDepreciationRate, 3); |
|
| 966 | |||
| 967 | // Loop through each period calculating the depreciation |
||
| 968 | 13 | $previousDepreciation = 0; |
|
| 969 | 13 | for ($per = 1; $per <= $period; ++$per) { |
|
| 970 | 13 | $depreciation = min(($cost - $previousDepreciation) * ($factor / $life), ($cost - $salvage - $previousDepreciation)); |
|
| 971 | 13 | $previousDepreciation += $depreciation; |
|
| 972 | } |
||
| 973 | 13 | if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_GNUMERIC) { |
|
| 974 | $depreciation = round($depreciation, 2); |
||
| 975 | } |
||
| 976 | |||
| 977 | 13 | return $depreciation; |
|
| 978 | } |
||
| 979 | |||
| 980 | 1 | return Functions::VALUE(); |
|
| 981 | } |
||
| 982 | |||
| 983 | /** |
||
| 984 | * DISC. |
||
| 985 | * |
||
| 986 | * Returns the discount rate for a security. |
||
| 987 | * |
||
| 988 | * Excel Function: |
||
| 989 | * DISC(settlement,maturity,price,redemption[,basis]) |
||
| 990 | * |
||
| 991 | * @category Financial Functions |
||
| 992 | * |
||
| 993 | * @param mixed $settlement The security's settlement date. |
||
| 994 | * The security settlement date is the date after the issue |
||
| 995 | * date when the security is traded to the buyer. |
||
| 996 | * @param mixed $maturity The security's maturity date. |
||
| 997 | * The maturity date is the date when the security expires. |
||
| 998 | * @param int $price The security's price per $100 face value |
||
| 999 | * @param int $redemption The security's redemption value per $100 face value |
||
| 1000 | * @param int $basis The type of day count to use. |
||
| 1001 | * 0 or omitted US (NASD) 30/360 |
||
| 1002 | * 1 Actual/actual |
||
| 1003 | * 2 Actual/360 |
||
| 1004 | * 3 Actual/365 |
||
| 1005 | * 4 European 30/360 |
||
| 1006 | * |
||
| 1007 | * @return float|string |
||
| 1008 | */ |
||
| 1009 | 5 | public static function DISC($settlement, $maturity, $price, $redemption, $basis = 0) |
|
| 1010 | { |
||
| 1011 | 5 | $settlement = Functions::flattenSingleValue($settlement); |
|
| 1012 | 5 | $maturity = Functions::flattenSingleValue($maturity); |
|
| 1013 | 5 | $price = Functions::flattenSingleValue($price); |
|
| 1014 | 5 | $redemption = Functions::flattenSingleValue($redemption); |
|
| 1015 | 5 | $basis = Functions::flattenSingleValue($basis); |
|
| 1016 | |||
| 1017 | // Validate |
||
| 1018 | 5 | if ((is_numeric($price)) && (is_numeric($redemption)) && (is_numeric($basis))) { |
|
| 1019 | 4 | $price = (float) $price; |
|
| 1020 | 4 | $redemption = (float) $redemption; |
|
| 1021 | 4 | $basis = (int) $basis; |
|
| 1022 | 4 | if (($price <= 0) || ($redemption <= 0)) { |
|
| 1023 | 1 | return Functions::NAN(); |
|
| 1024 | } |
||
| 1025 | 3 | $daysBetweenSettlementAndMaturity = DateTime::YEARFRAC($settlement, $maturity, $basis); |
|
| 1026 | 3 | if (!is_numeric($daysBetweenSettlementAndMaturity)) { |
|
| 1027 | // return date error |
||
| 1028 | 1 | return $daysBetweenSettlementAndMaturity; |
|
| 1029 | } |
||
| 1030 | |||
| 1031 | 2 | return (1 - $price / $redemption) / $daysBetweenSettlementAndMaturity; |
|
| 1032 | } |
||
| 1033 | |||
| 1034 | 1 | return Functions::VALUE(); |
|
| 1035 | } |
||
| 1036 | |||
| 1037 | /** |
||
| 1038 | * DOLLARDE. |
||
| 1039 | * |
||
| 1040 | * Converts a dollar price expressed as an integer part and a fraction |
||
| 1041 | * part into a dollar price expressed as a decimal number. |
||
| 1042 | * Fractional dollar numbers are sometimes used for security prices. |
||
| 1043 | * |
||
| 1044 | * Excel Function: |
||
| 1045 | * DOLLARDE(fractional_dollar,fraction) |
||
| 1046 | * |
||
| 1047 | * @category Financial Functions |
||
| 1048 | * |
||
| 1049 | * @param float $fractional_dollar Fractional Dollar |
||
| 1050 | * @param int $fraction Fraction |
||
| 1051 | * |
||
| 1052 | * @return float|string |
||
| 1053 | */ |
||
| 1054 | 9 | public static function DOLLARDE($fractional_dollar = null, $fraction = 0) |
|
| 1073 | } |
||
| 1074 | |||
| 1075 | /** |
||
| 1076 | * DOLLARFR. |
||
| 1077 | * |
||
| 1078 | * Converts a dollar price expressed as a decimal number into a dollar price |
||
| 1079 | * expressed as a fraction. |
||
| 1080 | * Fractional dollar numbers are sometimes used for security prices. |
||
| 1081 | * |
||
| 1082 | * Excel Function: |
||
| 1083 | * DOLLARFR(decimal_dollar,fraction) |
||
| 1084 | * |
||
| 1085 | * @category Financial Functions |
||
| 1086 | * |
||
| 1087 | * @param float $decimal_dollar Decimal Dollar |
||
| 1088 | * @param int $fraction Fraction |
||
| 1089 | * |
||
| 1090 | * @return float|string |
||
| 1091 | */ |
||
| 1092 | 9 | public static function DOLLARFR($decimal_dollar = null, $fraction = 0) |
|
| 1093 | { |
||
| 1094 | 9 | $decimal_dollar = Functions::flattenSingleValue($decimal_dollar); |
|
| 1095 | 9 | $fraction = (int) Functions::flattenSingleValue($fraction); |
|
| 1096 | |||
| 1097 | // Validate parameters |
||
| 1098 | 9 | if ($decimal_dollar === null || $fraction < 0) { |
|
| 1099 | 1 | return Functions::NAN(); |
|
| 1100 | } |
||
| 1101 | 8 | if ($fraction == 0) { |
|
| 1102 | 1 | return Functions::DIV0(); |
|
| 1103 | } |
||
| 1104 | |||
| 1105 | 7 | $dollars = floor($decimal_dollar); |
|
| 1106 | 7 | $cents = fmod($decimal_dollar, 1); |
|
| 1107 | 7 | $cents *= $fraction; |
|
| 1108 | 7 | $cents *= pow(10, -ceil(log10($fraction))); |
|
| 1109 | |||
| 1110 | 7 | return $dollars + $cents; |
|
| 1111 | } |
||
| 1112 | |||
| 1113 | /** |
||
| 1114 | * EFFECT. |
||
| 1115 | * |
||
| 1116 | * Returns the effective interest rate given the nominal rate and the number of |
||
| 1117 | * compounding payments per year. |
||
| 1118 | * |
||
| 1119 | * Excel Function: |
||
| 1120 | * EFFECT(nominal_rate,npery) |
||
| 1121 | * |
||
| 1122 | * @category Financial Functions |
||
| 1123 | * |
||
| 1124 | * @param float $nominal_rate Nominal interest rate |
||
| 1125 | * @param int $npery Number of compounding payments per year |
||
| 1126 | * |
||
| 1127 | * @return float|string |
||
| 1128 | */ |
||
| 1129 | 5 | public static function EFFECT($nominal_rate = 0, $npery = 0) |
|
| 1130 | { |
||
| 1131 | 5 | $nominal_rate = Functions::flattenSingleValue($nominal_rate); |
|
| 1132 | 5 | $npery = (int) Functions::flattenSingleValue($npery); |
|
| 1133 | |||
| 1134 | // Validate parameters |
||
| 1135 | 5 | if ($nominal_rate <= 0 || $npery < 1) { |
|
| 1136 | 1 | return Functions::NAN(); |
|
| 1137 | } |
||
| 1138 | |||
| 1139 | 4 | return pow((1 + $nominal_rate / $npery), $npery) - 1; |
|
| 1140 | } |
||
| 1141 | |||
| 1142 | /** |
||
| 1143 | * FV. |
||
| 1144 | * |
||
| 1145 | * Returns the Future Value of a cash flow with constant payments and interest rate (annuities). |
||
| 1146 | * |
||
| 1147 | * Excel Function: |
||
| 1148 | * FV(rate,nper,pmt[,pv[,type]]) |
||
| 1149 | * |
||
| 1150 | * @category Financial Functions |
||
| 1151 | * |
||
| 1152 | * @param float $rate The interest rate per period |
||
| 1153 | * @param int $nper Total number of payment periods in an annuity |
||
| 1154 | * @param float $pmt The payment made each period: it cannot change over the |
||
| 1155 | * life of the annuity. Typically, pmt contains principal |
||
| 1156 | * and interest but no other fees or taxes. |
||
| 1157 | * @param float $pv present Value, or the lump-sum amount that a series of |
||
| 1158 | * future payments is worth right now |
||
| 1159 | * @param int $type A number 0 or 1 and indicates when payments are due: |
||
| 1160 | * 0 or omitted At the end of the period. |
||
| 1161 | * 1 At the beginning of the period. |
||
| 1162 | * |
||
| 1163 | * @return float|string |
||
| 1164 | */ |
||
| 1165 | 8 | public static function FV($rate = 0, $nper = 0, $pmt = 0, $pv = 0, $type = 0) |
|
| 1166 | { |
||
| 1167 | 8 | $rate = Functions::flattenSingleValue($rate); |
|
| 1168 | 8 | $nper = Functions::flattenSingleValue($nper); |
|
| 1169 | 8 | $pmt = Functions::flattenSingleValue($pmt); |
|
| 1170 | 8 | $pv = Functions::flattenSingleValue($pv); |
|
| 1171 | 8 | $type = Functions::flattenSingleValue($type); |
|
| 1172 | |||
| 1173 | // Validate parameters |
||
| 1174 | 8 | if ($type != 0 && $type != 1) { |
|
| 1175 | 1 | return Functions::NAN(); |
|
| 1176 | } |
||
| 1177 | |||
| 1178 | // Calculate |
||
| 1179 | 7 | if ($rate !== null && $rate != 0) { |
|
| 1180 | 6 | return -$pv * pow(1 + $rate, $nper) - $pmt * (1 + $rate * $type) * (pow(1 + $rate, $nper) - 1) / $rate; |
|
| 1181 | } |
||
| 1182 | |||
| 1183 | 1 | return -$pv - $pmt * $nper; |
|
| 1184 | } |
||
| 1185 | |||
| 1186 | /** |
||
| 1187 | * FVSCHEDULE. |
||
| 1188 | * |
||
| 1189 | * Returns the future value of an initial principal after applying a series of compound interest rates. |
||
| 1190 | * Use FVSCHEDULE to calculate the future value of an investment with a variable or adjustable rate. |
||
| 1191 | * |
||
| 1192 | * Excel Function: |
||
| 1193 | * FVSCHEDULE(principal,schedule) |
||
| 1194 | * |
||
| 1195 | * @param float $principal the present value |
||
| 1196 | * @param float[] $schedule an array of interest rates to apply |
||
| 1197 | * |
||
| 1198 | * @return float |
||
| 1199 | */ |
||
| 1200 | 3 | public static function FVSCHEDULE($principal, $schedule) |
|
| 1201 | { |
||
| 1202 | 3 | $principal = Functions::flattenSingleValue($principal); |
|
| 1203 | 3 | $schedule = Functions::flattenArray($schedule); |
|
| 1204 | |||
| 1205 | 3 | foreach ($schedule as $rate) { |
|
| 1206 | 3 | $principal *= 1 + $rate; |
|
| 1207 | } |
||
| 1208 | |||
| 1209 | 3 | return $principal; |
|
| 1210 | } |
||
| 1211 | |||
| 1212 | /** |
||
| 1213 | * INTRATE. |
||
| 1214 | * |
||
| 1215 | * Returns the interest rate for a fully invested security. |
||
| 1216 | * |
||
| 1217 | * Excel Function: |
||
| 1218 | * INTRATE(settlement,maturity,investment,redemption[,basis]) |
||
| 1219 | * |
||
| 1220 | * @param mixed $settlement The security's settlement date. |
||
| 1221 | * The security settlement date is the date after the issue date when the security is traded to the buyer. |
||
| 1222 | * @param mixed $maturity The security's maturity date. |
||
| 1223 | * The maturity date is the date when the security expires. |
||
| 1224 | * @param int $investment the amount invested in the security |
||
| 1225 | * @param int $redemption the amount to be received at maturity |
||
| 1226 | * @param int $basis The type of day count to use. |
||
| 1227 | * 0 or omitted US (NASD) 30/360 |
||
| 1228 | * 1 Actual/actual |
||
| 1229 | * 2 Actual/360 |
||
| 1230 | * 3 Actual/365 |
||
| 1231 | * 4 European 30/360 |
||
| 1232 | * |
||
| 1233 | * @return float|string |
||
| 1234 | */ |
||
| 1235 | 5 | public static function INTRATE($settlement, $maturity, $investment, $redemption, $basis = 0) |
|
| 1236 | { |
||
| 1237 | 5 | $settlement = Functions::flattenSingleValue($settlement); |
|
| 1238 | 5 | $maturity = Functions::flattenSingleValue($maturity); |
|
| 1239 | 5 | $investment = Functions::flattenSingleValue($investment); |
|
| 1240 | 5 | $redemption = Functions::flattenSingleValue($redemption); |
|
| 1241 | 5 | $basis = Functions::flattenSingleValue($basis); |
|
| 1242 | |||
| 1243 | // Validate |
||
| 1244 | 5 | if ((is_numeric($investment)) && (is_numeric($redemption)) && (is_numeric($basis))) { |
|
| 1245 | 4 | $investment = (float) $investment; |
|
| 1246 | 4 | $redemption = (float) $redemption; |
|
| 1247 | 4 | $basis = (int) $basis; |
|
| 1248 | 4 | if (($investment <= 0) || ($redemption <= 0)) { |
|
| 1249 | 1 | return Functions::NAN(); |
|
| 1250 | } |
||
| 1251 | 3 | $daysBetweenSettlementAndMaturity = DateTime::YEARFRAC($settlement, $maturity, $basis); |
|
| 1252 | 3 | if (!is_numeric($daysBetweenSettlementAndMaturity)) { |
|
| 1253 | // return date error |
||
| 1254 | 1 | return $daysBetweenSettlementAndMaturity; |
|
| 1255 | } |
||
| 1256 | |||
| 1257 | 2 | return (($redemption / $investment) - 1) / ($daysBetweenSettlementAndMaturity); |
|
| 1258 | } |
||
| 1259 | |||
| 1260 | 1 | return Functions::VALUE(); |
|
| 1261 | } |
||
| 1262 | |||
| 1263 | /** |
||
| 1264 | * IPMT. |
||
| 1265 | * |
||
| 1266 | * Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate. |
||
| 1267 | * |
||
| 1268 | * Excel Function: |
||
| 1269 | * IPMT(rate,per,nper,pv[,fv][,type]) |
||
| 1270 | * |
||
| 1271 | * @param float $rate Interest rate per period |
||
| 1272 | * @param int $per Period for which we want to find the interest |
||
| 1273 | * @param int $nper Number of periods |
||
| 1274 | * @param float $pv Present Value |
||
| 1275 | * @param float $fv Future Value |
||
| 1276 | * @param int $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period |
||
| 1277 | * |
||
| 1278 | * @return float|string |
||
| 1279 | */ |
||
| 1280 | 15 | public static function IPMT($rate, $per, $nper, $pv, $fv = 0, $type = 0) |
|
| 1281 | { |
||
| 1282 | 15 | $rate = Functions::flattenSingleValue($rate); |
|
| 1283 | 15 | $per = (int) Functions::flattenSingleValue($per); |
|
| 1284 | 15 | $nper = (int) Functions::flattenSingleValue($nper); |
|
| 1285 | 15 | $pv = Functions::flattenSingleValue($pv); |
|
| 1286 | 15 | $fv = Functions::flattenSingleValue($fv); |
|
| 1287 | 15 | $type = (int) Functions::flattenSingleValue($type); |
|
| 1288 | |||
| 1289 | // Validate parameters |
||
| 1290 | 15 | if ($type != 0 && $type != 1) { |
|
| 1291 | 1 | return Functions::NAN(); |
|
| 1292 | } |
||
| 1293 | 14 | if ($per <= 0 || $per > $nper) { |
|
| 1294 | 1 | return Functions::VALUE(); |
|
| 1295 | } |
||
| 1296 | |||
| 1297 | // Calculate |
||
| 1298 | 13 | $interestAndPrincipal = self::interestAndPrincipal($rate, $per, $nper, $pv, $fv, $type); |
|
| 1299 | |||
| 1300 | 13 | return $interestAndPrincipal[0]; |
|
| 1301 | } |
||
| 1302 | |||
| 1303 | /** |
||
| 1304 | * IRR. |
||
| 1305 | * |
||
| 1306 | * Returns the internal rate of return for a series of cash flows represented by the numbers in values. |
||
| 1307 | * These cash flows do not have to be even, as they would be for an annuity. However, the cash flows must occur |
||
| 1308 | * at regular intervals, such as monthly or annually. The internal rate of return is the interest rate received |
||
| 1309 | * for an investment consisting of payments (negative values) and income (positive values) that occur at regular |
||
| 1310 | * periods. |
||
| 1311 | * |
||
| 1312 | * Excel Function: |
||
| 1313 | * IRR(values[,guess]) |
||
| 1314 | * |
||
| 1315 | * @param float[] $values An array or a reference to cells that contain numbers for which you want |
||
| 1316 | * to calculate the internal rate of return. |
||
| 1317 | * Values must contain at least one positive value and one negative value to |
||
| 1318 | * calculate the internal rate of return. |
||
| 1319 | * @param float $guess A number that you guess is close to the result of IRR |
||
| 1320 | * |
||
| 1321 | * @return float|string |
||
| 1322 | */ |
||
| 1323 | 5 | public static function IRR($values, $guess = 0.1) |
|
| 1324 | { |
||
| 1325 | 5 | if (!is_array($values)) { |
|
| 1326 | return Functions::VALUE(); |
||
| 1327 | } |
||
| 1328 | 5 | $values = Functions::flattenArray($values); |
|
| 1329 | 5 | $guess = Functions::flattenSingleValue($guess); |
|
| 1330 | |||
| 1331 | // create an initial range, with a root somewhere between 0 and guess |
||
| 1332 | 5 | $x1 = 0.0; |
|
| 1333 | 5 | $x2 = $guess; |
|
| 1334 | 5 | $f1 = self::NPV($x1, $values); |
|
| 1335 | 5 | $f2 = self::NPV($x2, $values); |
|
| 1336 | 5 | for ($i = 0; $i < self::FINANCIAL_MAX_ITERATIONS; ++$i) { |
|
| 1337 | 5 | if (($f1 * $f2) < 0.0) { |
|
| 1338 | 5 | break; |
|
| 1339 | } |
||
| 1340 | 4 | if (abs($f1) < abs($f2)) { |
|
| 1341 | 3 | $f1 = self::NPV($x1 += 1.6 * ($x1 - $x2), $values); |
|
| 1342 | } else { |
||
| 1343 | 1 | $f2 = self::NPV($x2 += 1.6 * ($x2 - $x1), $values); |
|
| 1344 | } |
||
| 1345 | } |
||
| 1346 | 5 | if (($f1 * $f2) > 0.0) { |
|
| 1347 | return Functions::VALUE(); |
||
| 1348 | } |
||
| 1349 | |||
| 1350 | 5 | $f = self::NPV($x1, $values); |
|
| 1351 | 5 | if ($f < 0.0) { |
|
| 1352 | $rtb = $x1; |
||
| 1353 | $dx = $x2 - $x1; |
||
| 1354 | } else { |
||
| 1355 | 5 | $rtb = $x2; |
|
| 1356 | 5 | $dx = $x1 - $x2; |
|
| 1357 | } |
||
| 1358 | |||
| 1359 | 5 | for ($i = 0; $i < self::FINANCIAL_MAX_ITERATIONS; ++$i) { |
|
| 1360 | 5 | $dx *= 0.5; |
|
| 1361 | 5 | $x_mid = $rtb + $dx; |
|
| 1362 | 5 | $f_mid = self::NPV($x_mid, $values); |
|
| 1363 | 5 | if ($f_mid <= 0.0) { |
|
| 1364 | 5 | $rtb = $x_mid; |
|
| 1365 | } |
||
| 1366 | 5 | if ((abs($f_mid) < self::FINANCIAL_PRECISION) || (abs($dx) < self::FINANCIAL_PRECISION)) { |
|
| 1367 | 5 | return $x_mid; |
|
| 1368 | } |
||
| 1369 | } |
||
| 1370 | |||
| 1371 | return Functions::VALUE(); |
||
| 1372 | } |
||
| 1373 | |||
| 1374 | /** |
||
| 1375 | * ISPMT. |
||
| 1376 | * |
||
| 1377 | * Returns the interest payment for an investment based on an interest rate and a constant payment schedule. |
||
| 1378 | * |
||
| 1379 | * Excel Function: |
||
| 1380 | * =ISPMT(interest_rate, period, number_payments, PV) |
||
| 1381 | * |
||
| 1382 | * interest_rate is the interest rate for the investment |
||
| 1383 | * |
||
| 1384 | * period is the period to calculate the interest rate. It must be betweeen 1 and number_payments. |
||
| 1385 | * |
||
| 1386 | * number_payments is the number of payments for the annuity |
||
| 1387 | * |
||
| 1388 | * PV is the loan amount or present value of the payments |
||
| 1389 | */ |
||
| 1390 | 4 | public static function ISPMT(...$args) |
|
| 1414 | } |
||
| 1415 | |||
| 1416 | /** |
||
| 1417 | * MIRR. |
||
| 1418 | * |
||
| 1419 | * Returns the modified internal rate of return for a series of periodic cash flows. MIRR considers both |
||
| 1420 | * the cost of the investment and the interest received on reinvestment of cash. |
||
| 1421 | * |
||
| 1422 | * Excel Function: |
||
| 1423 | * MIRR(values,finance_rate, reinvestment_rate) |
||
| 1424 | * |
||
| 1425 | * @param float[] $values An array or a reference to cells that contain a series of payments and |
||
| 1426 | * income occurring at regular intervals. |
||
| 1427 | * Payments are negative value, income is positive values. |
||
| 1428 | * @param float $finance_rate The interest rate you pay on the money used in the cash flows |
||
| 1429 | * @param float $reinvestment_rate The interest rate you receive on the cash flows as you reinvest them |
||
| 1430 | * |
||
| 1431 | * @return float|string |
||
| 1432 | */ |
||
| 1433 | 5 | public static function MIRR($values, $finance_rate, $reinvestment_rate) |
|
| 1463 | } |
||
| 1464 | |||
| 1465 | /** |
||
| 1466 | * NOMINAL. |
||
| 1467 | * |
||
| 1468 | * Returns the nominal interest rate given the effective rate and the number of compounding payments per year. |
||
| 1469 | * |
||
| 1470 | * @param float $effect_rate Effective interest rate |
||
| 1471 | * @param int $npery Number of compounding payments per year |
||
| 1472 | * |
||
| 1473 | * @return float|string |
||
| 1474 | */ |
||
| 1475 | 5 | public static function NOMINAL($effect_rate = 0, $npery = 0) |
|
| 1487 | } |
||
| 1488 | |||
| 1489 | /** |
||
| 1490 | * NPER. |
||
| 1491 | * |
||
| 1492 | * Returns the number of periods for a cash flow with constant periodic payments (annuities), and interest rate. |
||
| 1493 | * |
||
| 1494 | * @param float $rate Interest rate per period |
||
| 1495 | * @param int $pmt Periodic payment (annuity) |
||
| 1496 | * @param float $pv Present Value |
||
| 1497 | * @param float $fv Future Value |
||
| 1498 | * @param int $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period |
||
| 1499 | * |
||
| 1500 | * @return float|string |
||
| 1501 | */ |
||
| 1502 | 9 | public static function NPER($rate = 0, $pmt = 0, $pv = 0, $fv = 0, $type = 0) |
|
| 1528 | } |
||
| 1529 | |||
| 1530 | /** |
||
| 1531 | * NPV. |
||
| 1532 | * |
||
| 1533 | * Returns the Net Present Value of a cash flow series given a discount rate. |
||
| 1534 | * |
||
| 1535 | * @return float |
||
| 1536 | */ |
||
| 1537 | 9 | public static function NPV(...$args) |
|
| 1557 | } |
||
| 1558 | |||
| 1559 | /** |
||
| 1560 | * PDURATION. |
||
| 1561 | * |
||
| 1562 | * Calculates the number of periods required for an investment to reach a specified value. |
||
| 1563 | * |
||
| 1564 | * @param float $rate Interest rate per period |
||
| 1565 | * @param float $pv Present Value |
||
| 1566 | * @param float $fv Future Value |
||
| 1567 | * |
||
| 1568 | * @return float|string |
||
| 1569 | */ |
||
| 1570 | 8 | public static function PDURATION($rate = 0, $pv = 0, $fv = 0) |
|
| 1571 | { |
||
| 1572 | 8 | $rate = Functions::flattenSingleValue($rate); |
|
| 1573 | 8 | $pv = Functions::flattenSingleValue($pv); |
|
| 1574 | 8 | $fv = Functions::flattenSingleValue($fv); |
|
| 1575 | |||
| 1576 | // Validate parameters |
||
| 1577 | 8 | if (!is_numeric($rate) || !is_numeric($pv) || !is_numeric($fv)) { |
|
| 1578 | 1 | return Functions::VALUE(); |
|
| 1579 | 7 | } elseif ($rate <= 0.0 || $pv <= 0.0 || $fv <= 0.0) { |
|
| 1580 | 2 | return Functions::NAN(); |
|
| 1581 | } |
||
| 1582 | |||
| 1583 | 5 | return (log($fv) - log($pv)) / log(1 + $rate); |
|
| 1584 | } |
||
| 1585 | |||
| 1586 | /** |
||
| 1587 | * PMT. |
||
| 1588 | * |
||
| 1589 | * Returns the constant payment (annuity) for a cash flow with a constant interest rate. |
||
| 1590 | * |
||
| 1591 | * @param float $rate Interest rate per period |
||
| 1592 | * @param int $nper Number of periods |
||
| 1593 | * @param float $pv Present Value |
||
| 1594 | * @param float $fv Future Value |
||
| 1595 | * @param int $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period |
||
| 1596 | * |
||
| 1597 | * @return float |
||
| 1598 | */ |
||
| 1599 | 20 | public static function PMT($rate = 0, $nper = 0, $pv = 0, $fv = 0, $type = 0) |
|
| 1600 | { |
||
| 1601 | 20 | $rate = Functions::flattenSingleValue($rate); |
|
| 1602 | 20 | $nper = Functions::flattenSingleValue($nper); |
|
| 1603 | 20 | $pv = Functions::flattenSingleValue($pv); |
|
| 1604 | 20 | $fv = Functions::flattenSingleValue($fv); |
|
| 1605 | 20 | $type = Functions::flattenSingleValue($type); |
|
| 1606 | |||
| 1607 | // Validate parameters |
||
| 1608 | 20 | if ($type != 0 && $type != 1) { |
|
| 1609 | return Functions::NAN(); |
||
| 1610 | } |
||
| 1611 | |||
| 1612 | // Calculate |
||
| 1613 | 20 | if ($rate !== null && $rate != 0) { |
|
| 1614 | 20 | return (-$fv - $pv * pow(1 + $rate, $nper)) / (1 + $rate * $type) / ((pow(1 + $rate, $nper) - 1) / $rate); |
|
| 1615 | } |
||
| 1616 | |||
| 1617 | return (-$pv - $fv) / $nper; |
||
| 1618 | } |
||
| 1619 | |||
| 1620 | /** |
||
| 1621 | * PPMT. |
||
| 1622 | * |
||
| 1623 | * Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate. |
||
| 1624 | * |
||
| 1625 | * @param float $rate Interest rate per period |
||
| 1626 | * @param int $per Period for which we want to find the interest |
||
| 1627 | * @param int $nper Number of periods |
||
| 1628 | * @param float $pv Present Value |
||
| 1629 | * @param float $fv Future Value |
||
| 1630 | * @param int $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period |
||
| 1631 | * |
||
| 1632 | * @return float |
||
| 1633 | */ |
||
| 1634 | 7 | public static function PPMT($rate, $per, $nper, $pv, $fv = 0, $type = 0) |
|
| 1635 | { |
||
| 1636 | 7 | $rate = Functions::flattenSingleValue($rate); |
|
| 1637 | 7 | $per = (int) Functions::flattenSingleValue($per); |
|
| 1638 | 7 | $nper = (int) Functions::flattenSingleValue($nper); |
|
| 1639 | 7 | $pv = Functions::flattenSingleValue($pv); |
|
| 1640 | 7 | $fv = Functions::flattenSingleValue($fv); |
|
| 1641 | 7 | $type = (int) Functions::flattenSingleValue($type); |
|
| 1642 | |||
| 1643 | // Validate parameters |
||
| 1644 | 7 | if ($type != 0 && $type != 1) { |
|
| 1645 | return Functions::NAN(); |
||
| 1646 | } |
||
| 1647 | 7 | if ($per <= 0 || $per > $nper) { |
|
| 1648 | return Functions::VALUE(); |
||
| 1649 | } |
||
| 1650 | |||
| 1651 | // Calculate |
||
| 1652 | 7 | $interestAndPrincipal = self::interestAndPrincipal($rate, $per, $nper, $pv, $fv, $type); |
|
| 1653 | |||
| 1654 | 7 | return $interestAndPrincipal[1]; |
|
| 1655 | } |
||
| 1656 | |||
| 1657 | public static function PRICE($settlement, $maturity, $rate, $yield, $redemption, $frequency, $basis = 0) |
||
| 1696 | } |
||
| 1697 | |||
| 1698 | /** |
||
| 1699 | * PRICEDISC. |
||
| 1700 | * |
||
| 1701 | * Returns the price per $100 face value of a discounted security. |
||
| 1702 | * |
||
| 1703 | * @param mixed $settlement The security's settlement date. |
||
| 1704 | * The security settlement date is the date after the issue date when the security is traded to the buyer. |
||
| 1705 | * @param mixed $maturity The security's maturity date. |
||
| 1706 | * The maturity date is the date when the security expires. |
||
| 1707 | * @param int $discount The security's discount rate |
||
| 1708 | * @param int $redemption The security's redemption value per $100 face value |
||
| 1709 | * @param int $basis The type of day count to use. |
||
| 1710 | * 0 or omitted US (NASD) 30/360 |
||
| 1711 | * 1 Actual/actual |
||
| 1712 | * 2 Actual/360 |
||
| 1713 | * 3 Actual/365 |
||
| 1714 | * 4 European 30/360 |
||
| 1715 | * |
||
| 1716 | * @return float |
||
| 1717 | */ |
||
| 1718 | 2 | public static function PRICEDISC($settlement, $maturity, $discount, $redemption, $basis = 0) |
|
| 1719 | { |
||
| 1720 | 2 | $settlement = Functions::flattenSingleValue($settlement); |
|
| 1721 | 2 | $maturity = Functions::flattenSingleValue($maturity); |
|
| 1722 | 2 | $discount = (float) Functions::flattenSingleValue($discount); |
|
| 1723 | 2 | $redemption = (float) Functions::flattenSingleValue($redemption); |
|
| 1724 | 2 | $basis = (int) Functions::flattenSingleValue($basis); |
|
| 1725 | |||
| 1726 | // Validate |
||
| 1727 | 2 | if ((is_numeric($discount)) && (is_numeric($redemption)) && (is_numeric($basis))) { |
|
| 1728 | 2 | if (($discount <= 0) || ($redemption <= 0)) { |
|
| 1729 | return Functions::NAN(); |
||
| 1730 | } |
||
| 1731 | 2 | $daysBetweenSettlementAndMaturity = DateTime::YEARFRAC($settlement, $maturity, $basis); |
|
| 1732 | 2 | if (!is_numeric($daysBetweenSettlementAndMaturity)) { |
|
| 1733 | // return date error |
||
| 1734 | return $daysBetweenSettlementAndMaturity; |
||
| 1735 | } |
||
| 1736 | |||
| 1737 | 2 | return $redemption * (1 - $discount * $daysBetweenSettlementAndMaturity); |
|
| 1738 | } |
||
| 1739 | |||
| 1740 | return Functions::VALUE(); |
||
| 1741 | } |
||
| 1742 | |||
| 1743 | /** |
||
| 1744 | * PRICEMAT. |
||
| 1745 | * |
||
| 1746 | * Returns the price per $100 face value of a security that pays interest at maturity. |
||
| 1747 | * |
||
| 1748 | * @param mixed $settlement The security's settlement date. |
||
| 1749 | * The security's settlement date is the date after the issue date when the security is traded to the buyer. |
||
| 1750 | * @param mixed $maturity The security's maturity date. |
||
| 1751 | * The maturity date is the date when the security expires. |
||
| 1752 | * @param mixed $issue The security's issue date |
||
| 1753 | * @param int $rate The security's interest rate at date of issue |
||
| 1754 | * @param int $yield The security's annual yield |
||
| 1755 | * @param int $basis The type of day count to use. |
||
| 1756 | * 0 or omitted US (NASD) 30/360 |
||
| 1757 | * 1 Actual/actual |
||
| 1758 | * 2 Actual/360 |
||
| 1759 | * 3 Actual/365 |
||
| 1760 | * 4 European 30/360 |
||
| 1761 | * |
||
| 1762 | * @return float |
||
| 1763 | */ |
||
| 1764 | public static function PRICEMAT($settlement, $maturity, $issue, $rate, $yield, $basis = 0) |
||
| 1765 | { |
||
| 1766 | $settlement = Functions::flattenSingleValue($settlement); |
||
| 1767 | $maturity = Functions::flattenSingleValue($maturity); |
||
| 1768 | $issue = Functions::flattenSingleValue($issue); |
||
| 1769 | $rate = Functions::flattenSingleValue($rate); |
||
| 1770 | $yield = Functions::flattenSingleValue($yield); |
||
| 1771 | $basis = (int) Functions::flattenSingleValue($basis); |
||
| 1772 | |||
| 1773 | // Validate |
||
| 1774 | if (is_numeric($rate) && is_numeric($yield)) { |
||
| 1775 | if (($rate <= 0) || ($yield <= 0)) { |
||
| 1776 | return Functions::NAN(); |
||
| 1777 | } |
||
| 1778 | $daysPerYear = self::daysPerYear(DateTime::YEAR($settlement), $basis); |
||
| 1779 | if (!is_numeric($daysPerYear)) { |
||
| 1780 | return $daysPerYear; |
||
| 1781 | } |
||
| 1782 | $daysBetweenIssueAndSettlement = DateTime::YEARFRAC($issue, $settlement, $basis); |
||
| 1783 | if (!is_numeric($daysBetweenIssueAndSettlement)) { |
||
| 1784 | // return date error |
||
| 1785 | return $daysBetweenIssueAndSettlement; |
||
| 1786 | } |
||
| 1787 | $daysBetweenIssueAndSettlement *= $daysPerYear; |
||
| 1788 | $daysBetweenIssueAndMaturity = DateTime::YEARFRAC($issue, $maturity, $basis); |
||
| 1789 | if (!is_numeric($daysBetweenIssueAndMaturity)) { |
||
| 1790 | // return date error |
||
| 1791 | return $daysBetweenIssueAndMaturity; |
||
| 1792 | } |
||
| 1793 | $daysBetweenIssueAndMaturity *= $daysPerYear; |
||
| 1794 | $daysBetweenSettlementAndMaturity = DateTime::YEARFRAC($settlement, $maturity, $basis); |
||
| 1795 | if (!is_numeric($daysBetweenSettlementAndMaturity)) { |
||
| 1796 | // return date error |
||
| 1797 | return $daysBetweenSettlementAndMaturity; |
||
| 1798 | } |
||
| 1799 | $daysBetweenSettlementAndMaturity *= $daysPerYear; |
||
| 1800 | |||
| 1801 | return (100 + (($daysBetweenIssueAndMaturity / $daysPerYear) * $rate * 100)) / |
||
| 1802 | (1 + (($daysBetweenSettlementAndMaturity / $daysPerYear) * $yield)) - |
||
| 1803 | (($daysBetweenIssueAndSettlement / $daysPerYear) * $rate * 100); |
||
| 1804 | } |
||
| 1805 | |||
| 1806 | return Functions::VALUE(); |
||
| 1807 | } |
||
| 1808 | |||
| 1809 | /** |
||
| 1810 | * PV. |
||
| 1811 | * |
||
| 1812 | * Returns the Present Value of a cash flow with constant payments and interest rate (annuities). |
||
| 1813 | * |
||
| 1814 | * @param float $rate Interest rate per period |
||
| 1815 | * @param int $nper Number of periods |
||
| 1816 | * @param float $pmt Periodic payment (annuity) |
||
| 1817 | * @param float $fv Future Value |
||
| 1818 | * @param int $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period |
||
| 1819 | * |
||
| 1820 | * @return float |
||
| 1821 | */ |
||
| 1822 | 5 | public static function PV($rate = 0, $nper = 0, $pmt = 0, $fv = 0, $type = 0) |
|
| 1823 | { |
||
| 1824 | 5 | $rate = Functions::flattenSingleValue($rate); |
|
| 1825 | 5 | $nper = Functions::flattenSingleValue($nper); |
|
| 1826 | 5 | $pmt = Functions::flattenSingleValue($pmt); |
|
| 1827 | 5 | $fv = Functions::flattenSingleValue($fv); |
|
| 1828 | 5 | $type = Functions::flattenSingleValue($type); |
|
| 1829 | |||
| 1830 | // Validate parameters |
||
| 1831 | 5 | if ($type != 0 && $type != 1) { |
|
| 1832 | return Functions::NAN(); |
||
| 1833 | } |
||
| 1834 | |||
| 1835 | // Calculate |
||
| 1836 | 5 | if ($rate !== null && $rate != 0) { |
|
| 1837 | 5 | return (-$pmt * (1 + $rate * $type) * ((pow(1 + $rate, $nper) - 1) / $rate) - $fv) / pow(1 + $rate, $nper); |
|
| 1838 | } |
||
| 1839 | |||
| 1840 | return -$fv - $pmt * $nper; |
||
| 1841 | } |
||
| 1842 | |||
| 1843 | /** |
||
| 1844 | * RATE. |
||
| 1845 | * |
||
| 1846 | * Returns the interest rate per period of an annuity. |
||
| 1847 | * RATE is calculated by iteration and can have zero or more solutions. |
||
| 1848 | * If the successive results of RATE do not converge to within 0.0000001 after 20 iterations, |
||
| 1849 | * RATE returns the #NUM! error value. |
||
| 1850 | * |
||
| 1851 | * Excel Function: |
||
| 1852 | * RATE(nper,pmt,pv[,fv[,type[,guess]]]) |
||
| 1853 | * |
||
| 1854 | * @category Financial Functions |
||
| 1855 | * |
||
| 1856 | * @param float $nper The total number of payment periods in an annuity |
||
| 1857 | * @param float $pmt The payment made each period and cannot change over the life |
||
| 1858 | * of the annuity. |
||
| 1859 | * Typically, pmt includes principal and interest but no other |
||
| 1860 | * fees or taxes. |
||
| 1861 | * @param float $pv The present value - the total amount that a series of future |
||
| 1862 | * payments is worth now |
||
| 1863 | * @param float $fv The future value, or a cash balance you want to attain after |
||
| 1864 | * the last payment is made. If fv is omitted, it is assumed |
||
| 1865 | * to be 0 (the future value of a loan, for example, is 0). |
||
| 1866 | * @param int $type A number 0 or 1 and indicates when payments are due: |
||
| 1867 | * 0 or omitted At the end of the period. |
||
| 1868 | * 1 At the beginning of the period. |
||
| 1869 | * @param float $guess Your guess for what the rate will be. |
||
| 1870 | * If you omit guess, it is assumed to be 10 percent. |
||
| 1871 | * |
||
| 1872 | * @return float |
||
| 1873 | */ |
||
| 1874 | public static function RATE($nper, $pmt, $pv, $fv = 0.0, $type = 0, $guess = 0.1) |
||
| 1875 | { |
||
| 1876 | $nper = (int) Functions::flattenSingleValue($nper); |
||
| 1877 | $pmt = Functions::flattenSingleValue($pmt); |
||
| 1878 | $pv = Functions::flattenSingleValue($pv); |
||
| 1879 | $fv = ($fv === null) ? 0.0 : Functions::flattenSingleValue($fv); |
||
| 1880 | $type = ($type === null) ? 0 : (int) Functions::flattenSingleValue($type); |
||
| 1881 | $guess = ($guess === null) ? 0.1 : Functions::flattenSingleValue($guess); |
||
| 1882 | |||
| 1883 | $rate = $guess; |
||
| 1884 | if (abs($rate) < self::FINANCIAL_PRECISION) { |
||
| 1885 | $y = $pv * (1 + $nper * $rate) + $pmt * (1 + $rate * $type) * $nper + $fv; |
||
| 1886 | } else { |
||
| 1887 | $f = exp($nper * log(1 + $rate)); |
||
| 1888 | $y = $pv * $f + $pmt * (1 / $rate + $type) * ($f - 1) + $fv; |
||
| 1889 | } |
||
| 1890 | $y0 = $pv + $pmt * $nper + $fv; |
||
| 1891 | $y1 = $pv * $f + $pmt * (1 / $rate + $type) * ($f - 1) + $fv; |
||
| 1892 | |||
| 1893 | // find root by secant method |
||
| 1894 | $i = $x0 = 0.0; |
||
| 1895 | $x1 = $rate; |
||
| 1896 | while ((abs($y0 - $y1) > self::FINANCIAL_PRECISION) && ($i < self::FINANCIAL_MAX_ITERATIONS)) { |
||
| 1897 | $rate = ($y1 * $x0 - $y0 * $x1) / ($y1 - $y0); |
||
| 1898 | $x0 = $x1; |
||
| 1899 | $x1 = $rate; |
||
| 1900 | if (($nper * abs($pmt)) > ($pv - $fv)) { |
||
| 1901 | $x1 = abs($x1); |
||
| 1902 | } |
||
| 1903 | if (abs($rate) < self::FINANCIAL_PRECISION) { |
||
| 1904 | $y = $pv * (1 + $nper * $rate) + $pmt * (1 + $rate * $type) * $nper + $fv; |
||
| 1905 | } else { |
||
| 1906 | $f = exp($nper * log(1 + $rate)); |
||
| 1907 | $y = $pv * $f + $pmt * (1 / $rate + $type) * ($f - 1) + $fv; |
||
| 1908 | } |
||
| 1909 | |||
| 1910 | $y0 = $y1; |
||
| 1911 | $y1 = $y; |
||
| 1912 | ++$i; |
||
| 1913 | } |
||
| 1914 | |||
| 1915 | return $rate; |
||
| 1916 | } |
||
| 1917 | |||
| 1918 | /** |
||
| 1919 | * RECEIVED. |
||
| 1920 | * |
||
| 1921 | * Returns the price per $100 face value of a discounted security. |
||
| 1922 | * |
||
| 1923 | * @param mixed $settlement The security's settlement date. |
||
| 1924 | * The security settlement date is the date after the issue date when the security is traded to the buyer. |
||
| 1925 | * @param mixed $maturity The security's maturity date. |
||
| 1926 | * The maturity date is the date when the security expires. |
||
| 1927 | * @param int $investment The amount invested in the security |
||
| 1928 | * @param int $discount The security's discount rate |
||
| 1929 | * @param int $basis The type of day count to use. |
||
| 1930 | * 0 or omitted US (NASD) 30/360 |
||
| 1931 | * 1 Actual/actual |
||
| 1932 | * 2 Actual/360 |
||
| 1933 | * 3 Actual/365 |
||
| 1934 | * 4 European 30/360 |
||
| 1935 | * |
||
| 1936 | * @return float |
||
| 1937 | */ |
||
| 1938 | public static function RECEIVED($settlement, $maturity, $investment, $discount, $basis = 0) |
||
| 1939 | { |
||
| 1940 | $settlement = Functions::flattenSingleValue($settlement); |
||
| 1941 | $maturity = Functions::flattenSingleValue($maturity); |
||
| 1942 | $investment = (float) Functions::flattenSingleValue($investment); |
||
| 1943 | $discount = (float) Functions::flattenSingleValue($discount); |
||
| 1944 | $basis = (int) Functions::flattenSingleValue($basis); |
||
| 1945 | |||
| 1946 | // Validate |
||
| 1947 | if ((is_numeric($investment)) && (is_numeric($discount)) && (is_numeric($basis))) { |
||
| 1948 | if (($investment <= 0) || ($discount <= 0)) { |
||
| 1949 | return Functions::NAN(); |
||
| 1950 | } |
||
| 1951 | $daysBetweenSettlementAndMaturity = DateTime::YEARFRAC($settlement, $maturity, $basis); |
||
| 1952 | if (!is_numeric($daysBetweenSettlementAndMaturity)) { |
||
| 1953 | // return date error |
||
| 1954 | return $daysBetweenSettlementAndMaturity; |
||
| 1955 | } |
||
| 1956 | |||
| 1957 | return $investment / (1 - ($discount * $daysBetweenSettlementAndMaturity)); |
||
| 1958 | } |
||
| 1959 | |||
| 1960 | return Functions::VALUE(); |
||
| 1961 | } |
||
| 1962 | |||
| 1963 | /** |
||
| 1964 | * RRI. |
||
| 1965 | * |
||
| 1966 | * Calculates the interest rate required for an investment to grow to a specified future value . |
||
| 1967 | * |
||
| 1968 | * @param float $nper The number of periods over which the investment is made |
||
| 1969 | * @param float $pv Present Value |
||
| 1970 | * @param float $fv Future Value |
||
| 1971 | * |
||
| 1972 | * @return float|string |
||
| 1973 | */ |
||
| 1974 | 6 | public static function RRI($nper = 0, $pv = 0, $fv = 0) |
|
| 1988 | } |
||
| 1989 | |||
| 1990 | /** |
||
| 1991 | * SLN. |
||
| 1992 | * |
||
| 1993 | * Returns the straight-line depreciation of an asset for one period |
||
| 1994 | * |
||
| 1995 | * @param mixed $cost Initial cost of the asset |
||
| 1996 | * @param mixed $salvage Value at the end of the depreciation |
||
| 1997 | * @param mixed $life Number of periods over which the asset is depreciated |
||
| 1998 | * |
||
| 1999 | * @return float|string |
||
| 2000 | */ |
||
| 2001 | 9 | public static function SLN($cost, $salvage, $life) |
|
| 2002 | { |
||
| 2003 | 9 | $cost = Functions::flattenSingleValue($cost); |
|
| 2004 | 9 | $salvage = Functions::flattenSingleValue($salvage); |
|
| 2005 | 9 | $life = Functions::flattenSingleValue($life); |
|
| 2006 | |||
| 2007 | // Calculate |
||
| 2008 | 9 | if ((is_numeric($cost)) && (is_numeric($salvage)) && (is_numeric($life))) { |
|
| 2009 | 8 | if ($life < 0) { |
|
| 2010 | 1 | return Functions::NAN(); |
|
| 2011 | } |
||
| 2012 | |||
| 2013 | 7 | return ($cost - $salvage) / $life; |
|
| 2014 | } |
||
| 2015 | |||
| 2016 | 1 | return Functions::VALUE(); |
|
| 2017 | } |
||
| 2018 | |||
| 2019 | /** |
||
| 2020 | * SYD. |
||
| 2021 | * |
||
| 2022 | * Returns the sum-of-years' digits depreciation of an asset for a specified period. |
||
| 2023 | * |
||
| 2024 | * @param mixed $cost Initial cost of the asset |
||
| 2025 | * @param mixed $salvage Value at the end of the depreciation |
||
| 2026 | * @param mixed $life Number of periods over which the asset is depreciated |
||
| 2027 | * @param mixed $period Period |
||
| 2028 | * |
||
| 2029 | * @return float|string |
||
| 2030 | */ |
||
| 2031 | 10 | public static function SYD($cost, $salvage, $life, $period) |
|
| 2032 | { |
||
| 2033 | 10 | $cost = Functions::flattenSingleValue($cost); |
|
| 2034 | 10 | $salvage = Functions::flattenSingleValue($salvage); |
|
| 2035 | 10 | $life = Functions::flattenSingleValue($life); |
|
| 2036 | 10 | $period = Functions::flattenSingleValue($period); |
|
| 2037 | |||
| 2038 | // Calculate |
||
| 2039 | 10 | if ((is_numeric($cost)) && (is_numeric($salvage)) && (is_numeric($life)) && (is_numeric($period))) { |
|
| 2040 | 9 | if (($life < 1) || ($period > $life)) { |
|
| 2041 | 1 | return Functions::NAN(); |
|
| 2042 | } |
||
| 2043 | |||
| 2044 | 8 | return (($cost - $salvage) * ($life - $period + 1) * 2) / ($life * ($life + 1)); |
|
| 2045 | } |
||
| 2046 | |||
| 2047 | 1 | return Functions::VALUE(); |
|
| 2048 | } |
||
| 2049 | |||
| 2050 | /** |
||
| 2051 | * TBILLEQ. |
||
| 2052 | * |
||
| 2053 | * Returns the bond-equivalent yield for a Treasury bill. |
||
| 2054 | * |
||
| 2055 | * @param mixed $settlement The Treasury bill's settlement date. |
||
| 2056 | * The Treasury bill's settlement date is the date after the issue date when the Treasury bill is traded to the buyer. |
||
| 2057 | * @param mixed $maturity The Treasury bill's maturity date. |
||
| 2058 | * The maturity date is the date when the Treasury bill expires. |
||
| 2059 | * @param int $discount The Treasury bill's discount rate |
||
| 2060 | * |
||
| 2061 | * @return float |
||
| 2062 | */ |
||
| 2063 | public static function TBILLEQ($settlement, $maturity, $discount) |
||
| 2087 | } |
||
| 2088 | |||
| 2089 | /** |
||
| 2090 | * TBILLPRICE. |
||
| 2091 | * |
||
| 2092 | * Returns the yield for a Treasury bill. |
||
| 2093 | * |
||
| 2094 | * @param mixed $settlement The Treasury bill's settlement date. |
||
| 2095 | * The Treasury bill's settlement date is the date after the issue date when the Treasury bill is traded to the buyer. |
||
| 2096 | * @param mixed $maturity The Treasury bill's maturity date. |
||
| 2097 | * The maturity date is the date when the Treasury bill expires. |
||
| 2098 | * @param int $discount The Treasury bill's discount rate |
||
| 2099 | * |
||
| 2100 | * @return float |
||
| 2101 | */ |
||
| 2102 | public static function TBILLPRICE($settlement, $maturity, $discount) |
||
| 2103 | { |
||
| 2104 | $settlement = Functions::flattenSingleValue($settlement); |
||
| 2105 | $maturity = Functions::flattenSingleValue($maturity); |
||
| 2106 | $discount = Functions::flattenSingleValue($discount); |
||
| 2107 | |||
| 2108 | if (is_string($maturity = DateTime::getDateValue($maturity))) { |
||
| 2109 | return Functions::VALUE(); |
||
| 2110 | } |
||
| 2111 | |||
| 2112 | // Validate |
||
| 2113 | if (is_numeric($discount)) { |
||
| 2114 | if ($discount <= 0) { |
||
| 2115 | return Functions::NAN(); |
||
| 2116 | } |
||
| 2117 | |||
| 2118 | if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_OPENOFFICE) { |
||
| 2119 | ++$maturity; |
||
| 2120 | $daysBetweenSettlementAndMaturity = DateTime::YEARFRAC($settlement, $maturity) * 360; |
||
| 2121 | if (!is_numeric($daysBetweenSettlementAndMaturity)) { |
||
| 2122 | // return date error |
||
| 2123 | return $daysBetweenSettlementAndMaturity; |
||
| 2124 | } |
||
| 2125 | } else { |
||
| 2126 | $daysBetweenSettlementAndMaturity = (DateTime::getDateValue($maturity) - DateTime::getDateValue($settlement)); |
||
| 2127 | } |
||
| 2128 | |||
| 2129 | if ($daysBetweenSettlementAndMaturity > 360) { |
||
| 2130 | return Functions::NAN(); |
||
| 2131 | } |
||
| 2132 | |||
| 2133 | $price = 100 * (1 - (($discount * $daysBetweenSettlementAndMaturity) / 360)); |
||
| 2134 | if ($price <= 0) { |
||
| 2135 | return Functions::NAN(); |
||
| 2136 | } |
||
| 2137 | |||
| 2138 | return $price; |
||
| 2139 | } |
||
| 2140 | |||
| 2141 | return Functions::VALUE(); |
||
| 2142 | } |
||
| 2143 | |||
| 2144 | /** |
||
| 2145 | * TBILLYIELD. |
||
| 2146 | * |
||
| 2147 | * Returns the yield for a Treasury bill. |
||
| 2148 | * |
||
| 2149 | * @param mixed $settlement The Treasury bill's settlement date. |
||
| 2150 | * The Treasury bill's settlement date is the date after the issue date when the Treasury bill is traded to the buyer. |
||
| 2151 | * @param mixed $maturity The Treasury bill's maturity date. |
||
| 2152 | * The maturity date is the date when the Treasury bill expires. |
||
| 2153 | * @param int $price The Treasury bill's price per $100 face value |
||
| 2154 | * |
||
| 2155 | * @return float |
||
| 2156 | */ |
||
| 2157 | public static function TBILLYIELD($settlement, $maturity, $price) |
||
| 2158 | { |
||
| 2159 | $settlement = Functions::flattenSingleValue($settlement); |
||
| 2160 | $maturity = Functions::flattenSingleValue($maturity); |
||
| 2161 | $price = Functions::flattenSingleValue($price); |
||
| 2162 | |||
| 2163 | // Validate |
||
| 2164 | if (is_numeric($price)) { |
||
| 2165 | if ($price <= 0) { |
||
| 2166 | return Functions::NAN(); |
||
| 2167 | } |
||
| 2168 | |||
| 2169 | if (Functions::getCompatibilityMode() == Functions::COMPATIBILITY_OPENOFFICE) { |
||
| 2170 | ++$maturity; |
||
| 2171 | $daysBetweenSettlementAndMaturity = DateTime::YEARFRAC($settlement, $maturity) * 360; |
||
| 2172 | if (!is_numeric($daysBetweenSettlementAndMaturity)) { |
||
| 2173 | // return date error |
||
| 2174 | return $daysBetweenSettlementAndMaturity; |
||
| 2175 | } |
||
| 2176 | } else { |
||
| 2177 | $daysBetweenSettlementAndMaturity = (DateTime::getDateValue($maturity) - DateTime::getDateValue($settlement)); |
||
| 2178 | } |
||
| 2179 | |||
| 2180 | if ($daysBetweenSettlementAndMaturity > 360) { |
||
| 2181 | return Functions::NAN(); |
||
| 2182 | } |
||
| 2183 | |||
| 2184 | return ((100 - $price) / $price) * (360 / $daysBetweenSettlementAndMaturity); |
||
| 2185 | } |
||
| 2186 | |||
| 2187 | return Functions::VALUE(); |
||
| 2188 | } |
||
| 2189 | |||
| 2190 | public static function XIRR($values, $dates, $guess = 0.1) |
||
| 2191 | { |
||
| 2192 | if ((!is_array($values)) && (!is_array($dates))) { |
||
| 2193 | return Functions::VALUE(); |
||
| 2194 | } |
||
| 2195 | $values = Functions::flattenArray($values); |
||
| 2196 | $dates = Functions::flattenArray($dates); |
||
| 2197 | $guess = Functions::flattenSingleValue($guess); |
||
| 2198 | if (count($values) != count($dates)) { |
||
| 2199 | return Functions::NAN(); |
||
| 2200 | } |
||
| 2201 | |||
| 2202 | // create an initial range, with a root somewhere between 0 and guess |
||
| 2203 | $x1 = 0.0; |
||
| 2204 | $x2 = $guess; |
||
| 2205 | $f1 = self::XNPV($x1, $values, $dates); |
||
| 2206 | $f2 = self::XNPV($x2, $values, $dates); |
||
| 2207 | for ($i = 0; $i < self::FINANCIAL_MAX_ITERATIONS; ++$i) { |
||
| 2208 | if (($f1 * $f2) < 0.0) { |
||
| 2209 | break; |
||
| 2210 | } elseif (abs($f1) < abs($f2)) { |
||
| 2211 | $f1 = self::XNPV($x1 += 1.6 * ($x1 - $x2), $values, $dates); |
||
| 2212 | } else { |
||
| 2213 | $f2 = self::XNPV($x2 += 1.6 * ($x2 - $x1), $values, $dates); |
||
| 2214 | } |
||
| 2215 | } |
||
| 2216 | if (($f1 * $f2) > 0.0) { |
||
| 2217 | return Functions::VALUE(); |
||
| 2218 | } |
||
| 2219 | |||
| 2220 | $f = self::XNPV($x1, $values, $dates); |
||
| 2221 | if ($f < 0.0) { |
||
| 2222 | $rtb = $x1; |
||
| 2223 | $dx = $x2 - $x1; |
||
| 2224 | } else { |
||
| 2225 | $rtb = $x2; |
||
| 2226 | $dx = $x1 - $x2; |
||
| 2227 | } |
||
| 2228 | |||
| 2229 | for ($i = 0; $i < self::FINANCIAL_MAX_ITERATIONS; ++$i) { |
||
| 2230 | $dx *= 0.5; |
||
| 2231 | $x_mid = $rtb + $dx; |
||
| 2232 | $f_mid = self::XNPV($x_mid, $values, $dates); |
||
| 2233 | if ($f_mid <= 0.0) { |
||
| 2234 | $rtb = $x_mid; |
||
| 2235 | } |
||
| 2236 | if ((abs($f_mid) < self::FINANCIAL_PRECISION) || (abs($dx) < self::FINANCIAL_PRECISION)) { |
||
| 2237 | return $x_mid; |
||
| 2238 | } |
||
| 2239 | } |
||
| 2240 | |||
| 2241 | return Functions::VALUE(); |
||
| 2242 | } |
||
| 2243 | |||
| 2244 | /** |
||
| 2245 | * XNPV. |
||
| 2246 | * |
||
| 2247 | * Returns the net present value for a schedule of cash flows that is not necessarily periodic. |
||
| 2248 | * To calculate the net present value for a series of cash flows that is periodic, use the NPV function. |
||
| 2249 | * |
||
| 2250 | * Excel Function: |
||
| 2251 | * =XNPV(rate,values,dates) |
||
| 2252 | * |
||
| 2253 | * @param float $rate the discount rate to apply to the cash flows |
||
| 2254 | * @param array of float $values A series of cash flows that corresponds to a schedule of payments in dates. |
||
| 2255 | * The first payment is optional and corresponds to a cost or payment that occurs at the beginning of the investment. |
||
| 2256 | * 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. |
||
| 2257 | * The series of values must contain at least one positive value and one negative value. |
||
| 2258 | * @param array of mixed $dates A schedule of payment dates that corresponds to the cash flow payments. |
||
| 2259 | * The first payment date indicates the beginning of the schedule of payments. |
||
| 2260 | * All other dates must be later than this date, but they may occur in any order. |
||
| 2261 | * |
||
| 2262 | * @return float |
||
| 2263 | */ |
||
| 2264 | public static function XNPV($rate, $values, $dates) |
||
| 2292 | } |
||
| 2293 | |||
| 2294 | /** |
||
| 2295 | * YIELDDISC. |
||
| 2296 | * |
||
| 2297 | * Returns the annual yield of a security that pays interest at maturity. |
||
| 2298 | * |
||
| 2299 | * @param mixed $settlement The security's settlement date. |
||
| 2300 | * The security's settlement date is the date after the issue date when the security is traded to the buyer. |
||
| 2301 | * @param mixed $maturity The security's maturity date. |
||
| 2302 | * The maturity date is the date when the security expires. |
||
| 2303 | * @param int $price The security's price per $100 face value |
||
| 2304 | * @param int $redemption The security's redemption value per $100 face value |
||
| 2305 | * @param int $basis The type of day count to use. |
||
| 2306 | * 0 or omitted US (NASD) 30/360 |
||
| 2307 | * 1 Actual/actual |
||
| 2308 | * 2 Actual/360 |
||
| 2309 | * 3 Actual/365 |
||
| 2310 | * 4 European 30/360 |
||
| 2311 | * |
||
| 2312 | * @return float |
||
| 2313 | */ |
||
| 2314 | public static function YIELDDISC($settlement, $maturity, $price, $redemption, $basis = 0) |
||
| 2315 | { |
||
| 2316 | $settlement = Functions::flattenSingleValue($settlement); |
||
| 2317 | $maturity = Functions::flattenSingleValue($maturity); |
||
| 2318 | $price = Functions::flattenSingleValue($price); |
||
| 2319 | $redemption = Functions::flattenSingleValue($redemption); |
||
| 2320 | $basis = (int) Functions::flattenSingleValue($basis); |
||
| 2321 | |||
| 2322 | // Validate |
||
| 2323 | if (is_numeric($price) && is_numeric($redemption)) { |
||
| 2324 | if (($price <= 0) || ($redemption <= 0)) { |
||
| 2325 | return Functions::NAN(); |
||
| 2326 | } |
||
| 2327 | $daysPerYear = self::daysPerYear(DateTime::YEAR($settlement), $basis); |
||
| 2328 | if (!is_numeric($daysPerYear)) { |
||
| 2329 | return $daysPerYear; |
||
| 2330 | } |
||
| 2331 | $daysBetweenSettlementAndMaturity = DateTime::YEARFRAC($settlement, $maturity, $basis); |
||
| 2332 | if (!is_numeric($daysBetweenSettlementAndMaturity)) { |
||
| 2333 | // return date error |
||
| 2334 | return $daysBetweenSettlementAndMaturity; |
||
| 2335 | } |
||
| 2336 | $daysBetweenSettlementAndMaturity *= $daysPerYear; |
||
| 2337 | |||
| 2338 | return (($redemption - $price) / $price) * ($daysPerYear / $daysBetweenSettlementAndMaturity); |
||
| 2339 | } |
||
| 2340 | |||
| 2341 | return Functions::VALUE(); |
||
| 2342 | } |
||
| 2343 | |||
| 2344 | /** |
||
| 2345 | * YIELDMAT. |
||
| 2346 | * |
||
| 2347 | * Returns the annual yield of a security that pays interest at maturity. |
||
| 2348 | * |
||
| 2349 | * @param mixed $settlement The security's settlement date. |
||
| 2350 | * The security's settlement date is the date after the issue date when the security is traded to the buyer. |
||
| 2351 | * @param mixed $maturity The security's maturity date. |
||
| 2352 | * The maturity date is the date when the security expires. |
||
| 2353 | * @param mixed $issue The security's issue date |
||
| 2354 | * @param int $rate The security's interest rate at date of issue |
||
| 2355 | * @param int $price The security's price per $100 face value |
||
| 2356 | * @param int $basis The type of day count to use. |
||
| 2357 | * 0 or omitted US (NASD) 30/360 |
||
| 2358 | * 1 Actual/actual |
||
| 2359 | * 2 Actual/360 |
||
| 2360 | * 3 Actual/365 |
||
| 2361 | * 4 European 30/360 |
||
| 2362 | * |
||
| 2363 | * @return float |
||
| 2364 | */ |
||
| 2365 | public static function YIELDMAT($settlement, $maturity, $issue, $rate, $price, $basis = 0) |
||
| 2408 | } |
||
| 2409 | } |
||
| 2410 |
Loading history...
