PageRenderTime 50ms CodeModel.GetById 7ms RepoModel.GetById 0ms app.codeStats 1ms

/application/protected/extensions/vendors/phpexcel/Classes/PHPExcel/Calculation/Financial.php

https://bitbucket.org/yiqing/yiicorecms
PHP | 1818 lines | 1035 code | 234 blank | 549 comment | 320 complexity | b34848903a36ae229c65216fbffea68c MD5 | raw file
Possible License(s): GPL-2.0, LGPL-2.1, LGPL-3.0, GPL-3.0, BSD-3-Clause, CC0-1.0

Large files files are truncated, but you can click here to view the full file

  1. <?php
  2. /**
  3. * PHPExcel
  4. *
  5. * Copyright (c) 2006 - 2011 PHPExcel
  6. *
  7. * This library is free software; you can redistribute it and/or
  8. * modify it under the terms of the GNU Lesser General Public
  9. * License as published by the Free Software Foundation; either
  10. * version 2.1 of the License, or (at your option) any later version.
  11. *
  12. * This library is distributed in the hope that it will be useful,
  13. * but WITHOUT ANY WARRANTY; without even the implied warranty of
  14. * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
  15. * Lesser General Public License for more details.
  16. *
  17. * You should have received a copy of the GNU Lesser General Public
  18. * License along with this library; if not, write to the Free Software
  19. * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
  20. *
  21. * @category PHPExcel
  22. * @package PHPExcel_Calculation
  23. * @copyright Copyright (c) 2006 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
  24. * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
  25. * @version 1.7.6, 2011-02-27
  26. */
  27. /** PHPExcel root directory */
  28. if (!defined('PHPEXCEL_ROOT')) {
  29. /**
  30. * @ignore
  31. */
  32. define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
  33. require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
  34. }
  35. /** FINANCIAL_MAX_ITERATIONS */
  36. define('FINANCIAL_MAX_ITERATIONS', 128);
  37. /** FINANCIAL_PRECISION */
  38. define('FINANCIAL_PRECISION', 1.0e-08);
  39. /**
  40. * PHPExcel_Calculation_Financial
  41. *
  42. * @category PHPExcel
  43. * @package PHPExcel_Calculation
  44. * @copyright Copyright (c) 2006 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
  45. */
  46. class PHPExcel_Calculation_Financial {
  47. private static function _lastDayOfMonth($testDate) {
  48. $date = clone $testDate;
  49. $date->modify('+1 day');
  50. return ($date->format('d') == 1);
  51. } // function _lastDayOfMonth()
  52. private static function _firstDayOfMonth($testDate) {
  53. $date = clone $testDate;
  54. return ($date->format('d') == 1);
  55. } // function _lastDayOfMonth()
  56. private static function _coupFirstPeriodDate($settlement, $maturity, $frequency, $next) {
  57. $months = 12 / $frequency;
  58. $result = PHPExcel_Shared_Date::ExcelToPHPObject($maturity);
  59. $eom = self::_lastDayOfMonth($result);
  60. while ($settlement < PHPExcel_Shared_Date::PHPToExcel($result)) {
  61. $result->modify('-'.$months.' months');
  62. }
  63. if ($next) {
  64. $result->modify('+'.$months.' months');
  65. }
  66. if ($eom) {
  67. $result->modify('-1 day');
  68. }
  69. return PHPExcel_Shared_Date::PHPToExcel($result);
  70. } // function _coupFirstPeriodDate()
  71. private static function _validFrequency($frequency) {
  72. if (($frequency == 1) || ($frequency == 2) || ($frequency == 4)) {
  73. return true;
  74. }
  75. if ((PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_GNUMERIC) &&
  76. (($frequency == 6) || ($frequency == 12))) {
  77. return true;
  78. }
  79. return false;
  80. } // function _validFrequency()
  81. private static function _daysPerYear($year,$basis) {
  82. switch ($basis) {
  83. case 0 :
  84. case 2 :
  85. case 4 :
  86. $daysPerYear = 360;
  87. break;
  88. case 3 :
  89. $daysPerYear = 365;
  90. break;
  91. case 1 :
  92. if (PHPExcel_Calculation_DateTime::_isLeapYear($year)) {
  93. $daysPerYear = 366;
  94. } else {
  95. $daysPerYear = 365;
  96. }
  97. break;
  98. default :
  99. return PHPExcel_Calculation_Functions::NaN();
  100. }
  101. return $daysPerYear;
  102. } // function _daysPerYear()
  103. private static function _interestAndPrincipal($rate=0, $per=0, $nper=0, $pv=0, $fv=0, $type=0) {
  104. $pmt = self::PMT($rate, $nper, $pv, $fv, $type);
  105. $capital = $pv;
  106. for ($i = 1; $i<= $per; ++$i) {
  107. $interest = ($type && $i == 1) ? 0 : -$capital * $rate;
  108. $principal = $pmt - $interest;
  109. $capital += $principal;
  110. }
  111. return array($interest, $principal);
  112. } // function _interestAndPrincipal()
  113. /**
  114. * ACCRINT
  115. *
  116. * Returns the discount rate for a security.
  117. *
  118. * @param mixed issue The security's issue date.
  119. * @param mixed firstinter The security's first interest date.
  120. * @param mixed settlement The security's settlement date.
  121. * @param float rate The security's annual coupon rate.
  122. * @param float par The security's par value.
  123. * @param int basis The type of day count to use.
  124. * 0 or omitted US (NASD) 30/360
  125. * 1 Actual/actual
  126. * 2 Actual/360
  127. * 3 Actual/365
  128. * 4 European 30/360
  129. * @return float
  130. */
  131. public static function ACCRINT($issue, $firstinter, $settlement, $rate, $par=1000, $frequency=1, $basis=0) {
  132. $issue = PHPExcel_Calculation_Functions::flattenSingleValue($issue);
  133. $firstinter = PHPExcel_Calculation_Functions::flattenSingleValue($firstinter);
  134. $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
  135. $rate = (float) PHPExcel_Calculation_Functions::flattenSingleValue($rate);
  136. $par = (is_null($par)) ? 1000 : (float) PHPExcel_Calculation_Functions::flattenSingleValue($par);
  137. $frequency = (is_null($frequency)) ? 1 : (int) PHPExcel_Calculation_Functions::flattenSingleValue($frequency);
  138. $basis = (is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
  139. // Validate
  140. if ((is_numeric($rate)) && (is_numeric($par))) {
  141. if (($rate <= 0) || ($par <= 0)) {
  142. return PHPExcel_Calculation_Functions::NaN();
  143. }
  144. $daysBetweenIssueAndSettlement = PHPExcel_Calculation_DateTime::YEARFRAC($issue, $settlement, $basis);
  145. if (!is_numeric($daysBetweenIssueAndSettlement)) {
  146. // return date error
  147. return $daysBetweenIssueAndSettlement;
  148. }
  149. return $par * $rate * $daysBetweenIssueAndSettlement;
  150. }
  151. return PHPExcel_Calculation_Functions::VALUE();
  152. } // function ACCRINT()
  153. /**
  154. * ACCRINTM
  155. *
  156. * Returns the discount rate for a security.
  157. *
  158. * @param mixed issue The security's issue date.
  159. * @param mixed settlement The security's settlement date.
  160. * @param float rate The security's annual coupon rate.
  161. * @param float par The security's par value.
  162. * @param int basis The type of day count to use.
  163. * 0 or omitted US (NASD) 30/360
  164. * 1 Actual/actual
  165. * 2 Actual/360
  166. * 3 Actual/365
  167. * 4 European 30/360
  168. * @return float
  169. */
  170. public static function ACCRINTM($issue, $settlement, $rate, $par=1000, $basis=0) {
  171. $issue = PHPExcel_Calculation_Functions::flattenSingleValue($issue);
  172. $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
  173. $rate = (float) PHPExcel_Calculation_Functions::flattenSingleValue($rate);
  174. $par = (is_null($par)) ? 1000 : (float) PHPExcel_Calculation_Functions::flattenSingleValue($par);
  175. $basis = (is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
  176. // Validate
  177. if ((is_numeric($rate)) && (is_numeric($par))) {
  178. if (($rate <= 0) || ($par <= 0)) {
  179. return PHPExcel_Calculation_Functions::NaN();
  180. }
  181. $daysBetweenIssueAndSettlement = PHPExcel_Calculation_DateTime::YEARFRAC($issue, $settlement, $basis);
  182. if (!is_numeric($daysBetweenIssueAndSettlement)) {
  183. // return date error
  184. return $daysBetweenIssueAndSettlement;
  185. }
  186. return $par * $rate * $daysBetweenIssueAndSettlement;
  187. }
  188. return PHPExcel_Calculation_Functions::VALUE();
  189. } // function ACCRINTM()
  190. public static function AMORDEGRC($cost, $purchased, $firstPeriod, $salvage, $period, $rate, $basis=0) {
  191. $cost = PHPExcel_Calculation_Functions::flattenSingleValue($cost);
  192. $purchased = PHPExcel_Calculation_Functions::flattenSingleValue($purchased);
  193. $firstPeriod = PHPExcel_Calculation_Functions::flattenSingleValue($firstPeriod);
  194. $salvage = PHPExcel_Calculation_Functions::flattenSingleValue($salvage);
  195. $period = floor(PHPExcel_Calculation_Functions::flattenSingleValue($period));
  196. $rate = PHPExcel_Calculation_Functions::flattenSingleValue($rate);
  197. $basis = (is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
  198. $fUsePer = 1.0 / $rate;
  199. if ($fUsePer < 3.0) {
  200. $amortiseCoeff = 1.0;
  201. } elseif ($fUsePer < 5.0) {
  202. $amortiseCoeff = 1.5;
  203. } elseif ($fUsePer <= 6.0) {
  204. $amortiseCoeff = 2.0;
  205. } else {
  206. $amortiseCoeff = 2.5;
  207. }
  208. $rate *= $amortiseCoeff;
  209. $fNRate = round(PHPExcel_Calculation_DateTime::YEARFRAC($purchased, $firstPeriod, $basis) * $rate * $cost,0);
  210. $cost -= $fNRate;
  211. $fRest = $cost - $salvage;
  212. for ($n = 0; $n < $period; ++$n) {
  213. $fNRate = round($rate * $cost,0);
  214. $fRest -= $fNRate;
  215. if ($fRest < 0.0) {
  216. switch ($period - $n) {
  217. case 0 :
  218. case 1 : return round($cost * 0.5,0);
  219. break;
  220. default : return 0.0;
  221. break;
  222. }
  223. }
  224. $cost -= $fNRate;
  225. }
  226. return $fNRate;
  227. } // function AMORDEGRC()
  228. public static function AMORLINC($cost, $purchased, $firstPeriod, $salvage, $period, $rate, $basis=0) {
  229. $cost = PHPExcel_Calculation_Functions::flattenSingleValue($cost);
  230. $purchased = PHPExcel_Calculation_Functions::flattenSingleValue($purchased);
  231. $firstPeriod = PHPExcel_Calculation_Functions::flattenSingleValue($firstPeriod);
  232. $salvage = PHPExcel_Calculation_Functions::flattenSingleValue($salvage);
  233. $period = PHPExcel_Calculation_Functions::flattenSingleValue($period);
  234. $rate = PHPExcel_Calculation_Functions::flattenSingleValue($rate);
  235. $basis = (is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
  236. $fOneRate = $cost * $rate;
  237. $fCostDelta = $cost - $salvage;
  238. // Note, quirky variation for leap years on the YEARFRAC for this function
  239. $purchasedYear = PHPExcel_Calculation_DateTime::YEAR($purchased);
  240. $yearFrac = PHPExcel_Calculation_DateTime::YEARFRAC($purchased, $firstPeriod, $basis);
  241. if (($basis == 1) && ($yearFrac < 1) && (PHPExcel_Calculation_DateTime::_isLeapYear($purchasedYear))) {
  242. $yearFrac *= 365 / 366;
  243. }
  244. $f0Rate = $yearFrac * $rate * $cost;
  245. $nNumOfFullPeriods = intval(($cost - $salvage - $f0Rate) / $fOneRate);
  246. if ($period == 0) {
  247. return $f0Rate;
  248. } elseif ($period <= $nNumOfFullPeriods) {
  249. return $fOneRate;
  250. } elseif ($period == ($nNumOfFullPeriods + 1)) {
  251. return ($fCostDelta - $fOneRate * $nNumOfFullPeriods - $f0Rate);
  252. } else {
  253. return 0.0;
  254. }
  255. } // function AMORLINC()
  256. public static function COUPDAYBS($settlement, $maturity, $frequency, $basis=0) {
  257. $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
  258. $maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
  259. $frequency = (int) PHPExcel_Calculation_Functions::flattenSingleValue($frequency);
  260. $basis = (is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
  261. if (is_string($settlement = PHPExcel_Calculation_DateTime::_getDateValue($settlement))) {
  262. return PHPExcel_Calculation_Functions::VALUE();
  263. }
  264. if (is_string($maturity = PHPExcel_Calculation_DateTime::_getDateValue($maturity))) {
  265. return PHPExcel_Calculation_Functions::VALUE();
  266. }
  267. if (($settlement > $maturity) ||
  268. (!self::_validFrequency($frequency)) ||
  269. (($basis < 0) || ($basis > 4))) {
  270. return PHPExcel_Calculation_Functions::NaN();
  271. }
  272. $daysPerYear = self::_daysPerYear(PHPExcel_Calculation_DateTime::YEAR($settlement),$basis);
  273. $prev = self::_coupFirstPeriodDate($settlement, $maturity, $frequency, False);
  274. return PHPExcel_Calculation_DateTime::YEARFRAC($prev, $settlement, $basis) * $daysPerYear;
  275. } // function COUPDAYBS()
  276. public static function COUPDAYS($settlement, $maturity, $frequency, $basis=0) {
  277. $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
  278. $maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
  279. $frequency = (int) PHPExcel_Calculation_Functions::flattenSingleValue($frequency);
  280. $basis = (is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
  281. if (is_string($settlement = PHPExcel_Calculation_DateTime::_getDateValue($settlement))) {
  282. return PHPExcel_Calculation_Functions::VALUE();
  283. }
  284. if (is_string($maturity = PHPExcel_Calculation_DateTime::_getDateValue($maturity))) {
  285. return PHPExcel_Calculation_Functions::VALUE();
  286. }
  287. if (($settlement > $maturity) ||
  288. (!self::_validFrequency($frequency)) ||
  289. (($basis < 0) || ($basis > 4))) {
  290. return PHPExcel_Calculation_Functions::NaN();
  291. }
  292. switch ($basis) {
  293. case 3: // Actual/365
  294. return 365 / $frequency;
  295. case 1: // Actual/actual
  296. if ($frequency == 1) {
  297. $daysPerYear = self::_daysPerYear(PHPExcel_Calculation_DateTime::YEAR($maturity),$basis);
  298. return ($daysPerYear / $frequency);
  299. } else {
  300. $prev = self::_coupFirstPeriodDate($settlement, $maturity, $frequency, False);
  301. $next = self::_coupFirstPeriodDate($settlement, $maturity, $frequency, True);
  302. return ($next - $prev);
  303. }
  304. default: // US (NASD) 30/360, Actual/360 or European 30/360
  305. return 360 / $frequency;
  306. }
  307. return PHPExcel_Calculation_Functions::VALUE();
  308. } // function COUPDAYS()
  309. public static function COUPDAYSNC($settlement, $maturity, $frequency, $basis=0) {
  310. $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
  311. $maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
  312. $frequency = (int) PHPExcel_Calculation_Functions::flattenSingleValue($frequency);
  313. $basis = (is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
  314. if (is_string($settlement = PHPExcel_Calculation_DateTime::_getDateValue($settlement))) {
  315. return PHPExcel_Calculation_Functions::VALUE();
  316. }
  317. if (is_string($maturity = PHPExcel_Calculation_DateTime::_getDateValue($maturity))) {
  318. return PHPExcel_Calculation_Functions::VALUE();
  319. }
  320. if (($settlement > $maturity) ||
  321. (!self::_validFrequency($frequency)) ||
  322. (($basis < 0) || ($basis > 4))) {
  323. return PHPExcel_Calculation_Functions::NaN();
  324. }
  325. $daysPerYear = self::_daysPerYear(PHPExcel_Calculation_DateTime::YEAR($settlement),$basis);
  326. $next = self::_coupFirstPeriodDate($settlement, $maturity, $frequency, True);
  327. return PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $next, $basis) * $daysPerYear;
  328. } // function COUPDAYSNC()
  329. public static function COUPNCD($settlement, $maturity, $frequency, $basis=0) {
  330. $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
  331. $maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
  332. $frequency = (int) PHPExcel_Calculation_Functions::flattenSingleValue($frequency);
  333. $basis = (is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
  334. if (is_string($settlement = PHPExcel_Calculation_DateTime::_getDateValue($settlement))) {
  335. return PHPExcel_Calculation_Functions::VALUE();
  336. }
  337. if (is_string($maturity = PHPExcel_Calculation_DateTime::_getDateValue($maturity))) {
  338. return PHPExcel_Calculation_Functions::VALUE();
  339. }
  340. if (($settlement > $maturity) ||
  341. (!self::_validFrequency($frequency)) ||
  342. (($basis < 0) || ($basis > 4))) {
  343. return PHPExcel_Calculation_Functions::NaN();
  344. }
  345. return self::_coupFirstPeriodDate($settlement, $maturity, $frequency, True);
  346. } // function COUPNCD()
  347. public static function COUPNUM($settlement, $maturity, $frequency, $basis=0) {
  348. $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
  349. $maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
  350. $frequency = (int) PHPExcel_Calculation_Functions::flattenSingleValue($frequency);
  351. $basis = (is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
  352. if (is_string($settlement = PHPExcel_Calculation_DateTime::_getDateValue($settlement))) {
  353. return PHPExcel_Calculation_Functions::VALUE();
  354. }
  355. if (is_string($maturity = PHPExcel_Calculation_DateTime::_getDateValue($maturity))) {
  356. return PHPExcel_Calculation_Functions::VALUE();
  357. }
  358. if (($settlement > $maturity) ||
  359. (!self::_validFrequency($frequency)) ||
  360. (($basis < 0) || ($basis > 4))) {
  361. return PHPExcel_Calculation_Functions::NaN();
  362. }
  363. $settlement = self::_coupFirstPeriodDate($settlement, $maturity, $frequency, True);
  364. $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $maturity, $basis) * 365;
  365. switch ($frequency) {
  366. case 1: // annual payments
  367. return ceil($daysBetweenSettlementAndMaturity / 360);
  368. case 2: // half-yearly
  369. return ceil($daysBetweenSettlementAndMaturity / 180);
  370. case 4: // quarterly
  371. return ceil($daysBetweenSettlementAndMaturity / 90);
  372. case 6: // bimonthly
  373. return ceil($daysBetweenSettlementAndMaturity / 60);
  374. case 12: // monthly
  375. return ceil($daysBetweenSettlementAndMaturity / 30);
  376. }
  377. return PHPExcel_Calculation_Functions::VALUE();
  378. } // function COUPNUM()
  379. public static function COUPPCD($settlement, $maturity, $frequency, $basis=0) {
  380. $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
  381. $maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
  382. $frequency = (int) PHPExcel_Calculation_Functions::flattenSingleValue($frequency);
  383. $basis = (is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
  384. if (is_string($settlement = PHPExcel_Calculation_DateTime::_getDateValue($settlement))) {
  385. return PHPExcel_Calculation_Functions::VALUE();
  386. }
  387. if (is_string($maturity = PHPExcel_Calculation_DateTime::_getDateValue($maturity))) {
  388. return PHPExcel_Calculation_Functions::VALUE();
  389. }
  390. if (($settlement > $maturity) ||
  391. (!self::_validFrequency($frequency)) ||
  392. (($basis < 0) || ($basis > 4))) {
  393. return PHPExcel_Calculation_Functions::NaN();
  394. }
  395. return self::_coupFirstPeriodDate($settlement, $maturity, $frequency, False);
  396. } // function COUPPCD()
  397. /**
  398. * CUMIPMT
  399. *
  400. * Returns the cumulative interest paid on a loan between start_period and end_period.
  401. *
  402. * @param float $rate Interest rate per period
  403. * @param int $nper Number of periods
  404. * @param float $pv Present Value
  405. * @param int start The first period in the calculation.
  406. * Payment periods are numbered beginning with 1.
  407. * @param int end The last period in the calculation.
  408. * @param int $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period
  409. * @return float
  410. */
  411. public static function CUMIPMT($rate, $nper, $pv, $start, $end, $type = 0) {
  412. $rate = PHPExcel_Calculation_Functions::flattenSingleValue($rate);
  413. $nper = (int) PHPExcel_Calculation_Functions::flattenSingleValue($nper);
  414. $pv = PHPExcel_Calculation_Functions::flattenSingleValue($pv);
  415. $start = (int) PHPExcel_Calculation_Functions::flattenSingleValue($start);
  416. $end = (int) PHPExcel_Calculation_Functions::flattenSingleValue($end);
  417. $type = (int) PHPExcel_Calculation_Functions::flattenSingleValue($type);
  418. // Validate parameters
  419. if ($type != 0 && $type != 1) {
  420. return PHPExcel_Calculation_Functions::NaN();
  421. }
  422. if ($start < 1 || $start > $end) {
  423. return PHPExcel_Calculation_Functions::VALUE();
  424. }
  425. // Calculate
  426. $interest = 0;
  427. for ($per = $start; $per <= $end; ++$per) {
  428. $interest += self::IPMT($rate, $per, $nper, $pv, 0, $type);
  429. }
  430. return $interest;
  431. } // function CUMIPMT()
  432. /**
  433. * CUMPRINC
  434. *
  435. * Returns the cumulative principal paid on a loan between start_period and end_period.
  436. *
  437. * @param float $rate Interest rate per period
  438. * @param int $nper Number of periods
  439. * @param float $pv Present Value
  440. * @param int start The first period in the calculation.
  441. * Payment periods are numbered beginning with 1.
  442. * @param int end The last period in the calculation.
  443. * @param int $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period
  444. * @return float
  445. */
  446. public static function CUMPRINC($rate, $nper, $pv, $start, $end, $type = 0) {
  447. $rate = PHPExcel_Calculation_Functions::flattenSingleValue($rate);
  448. $nper = (int) PHPExcel_Calculation_Functions::flattenSingleValue($nper);
  449. $pv = PHPExcel_Calculation_Functions::flattenSingleValue($pv);
  450. $start = (int) PHPExcel_Calculation_Functions::flattenSingleValue($start);
  451. $end = (int) PHPExcel_Calculation_Functions::flattenSingleValue($end);
  452. $type = (int) PHPExcel_Calculation_Functions::flattenSingleValue($type);
  453. // Validate parameters
  454. if ($type != 0 && $type != 1) {
  455. return PHPExcel_Calculation_Functions::NaN();
  456. }
  457. if ($start < 1 || $start > $end) {
  458. return PHPExcel_Calculation_Functions::VALUE();
  459. }
  460. // Calculate
  461. $principal = 0;
  462. for ($per = $start; $per <= $end; ++$per) {
  463. $principal += self::PPMT($rate, $per, $nper, $pv, 0, $type);
  464. }
  465. return $principal;
  466. } // function CUMPRINC()
  467. /**
  468. * DB
  469. *
  470. * Returns the depreciation of an asset for a specified period using the fixed-declining balance method.
  471. * This form of depreciation is used if you want to get a higher depreciation value at the beginning of the depreciation
  472. * (as opposed to linear depreciation). The depreciation value is reduced with every depreciation period by the
  473. * depreciation already deducted from the initial cost.
  474. *
  475. * @param float cost Initial cost of the asset.
  476. * @param float salvage Value at the end of the depreciation. (Sometimes called the salvage value of the asset)
  477. * @param int life Number of periods over which the asset is depreciated. (Sometimes called the useful life of the asset)
  478. * @param int period The period for which you want to calculate the depreciation. Period must use the same units as life.
  479. * @param float month Number of months in the first year. If month is omitted, it defaults to 12.
  480. * @return float
  481. */
  482. public static function DB($cost, $salvage, $life, $period, $month=12) {
  483. $cost = (float) PHPExcel_Calculation_Functions::flattenSingleValue($cost);
  484. $salvage = (float) PHPExcel_Calculation_Functions::flattenSingleValue($salvage);
  485. $life = (int) PHPExcel_Calculation_Functions::flattenSingleValue($life);
  486. $period = (int) PHPExcel_Calculation_Functions::flattenSingleValue($period);
  487. $month = (int) PHPExcel_Calculation_Functions::flattenSingleValue($month);
  488. // Validate
  489. if ((is_numeric($cost)) && (is_numeric($salvage)) && (is_numeric($life)) && (is_numeric($period)) && (is_numeric($month))) {
  490. if ($cost == 0) {
  491. return 0.0;
  492. } elseif (($cost < 0) || (($salvage / $cost) < 0) || ($life <= 0) || ($period < 1) || ($month < 1)) {
  493. return PHPExcel_Calculation_Functions::NaN();
  494. }
  495. // Set Fixed Depreciation Rate
  496. $fixedDepreciationRate = 1 - pow(($salvage / $cost), (1 / $life));
  497. $fixedDepreciationRate = round($fixedDepreciationRate, 3);
  498. // Loop through each period calculating the depreciation
  499. $previousDepreciation = 0;
  500. for ($per = 1; $per <= $period; ++$per) {
  501. if ($per == 1) {
  502. $depreciation = $cost * $fixedDepreciationRate * $month / 12;
  503. } elseif ($per == ($life + 1)) {
  504. $depreciation = ($cost - $previousDepreciation) * $fixedDepreciationRate * (12 - $month) / 12;
  505. } else {
  506. $depreciation = ($cost - $previousDepreciation) * $fixedDepreciationRate;
  507. }
  508. $previousDepreciation += $depreciation;
  509. }
  510. if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_GNUMERIC) {
  511. $depreciation = round($depreciation,2);
  512. }
  513. return $depreciation;
  514. }
  515. return PHPExcel_Calculation_Functions::VALUE();
  516. } // function DB()
  517. /**
  518. * DDB
  519. *
  520. * Returns the depreciation of an asset for a specified period using the double-declining balance method or some other method you specify.
  521. *
  522. * @param float cost Initial cost of the asset.
  523. * @param float salvage Value at the end of the depreciation. (Sometimes called the salvage value of the asset)
  524. * @param int life Number of periods over which the asset is depreciated. (Sometimes called the useful life of the asset)
  525. * @param int period The period for which you want to calculate the depreciation. Period must use the same units as life.
  526. * @param float factor The rate at which the balance declines.
  527. * If factor is omitted, it is assumed to be 2 (the double-declining balance method).
  528. * @return float
  529. */
  530. public static function DDB($cost, $salvage, $life, $period, $factor=2.0) {
  531. $cost = (float) PHPExcel_Calculation_Functions::flattenSingleValue($cost);
  532. $salvage = (float) PHPExcel_Calculation_Functions::flattenSingleValue($salvage);
  533. $life = (int) PHPExcel_Calculation_Functions::flattenSingleValue($life);
  534. $period = (int) PHPExcel_Calculation_Functions::flattenSingleValue($period);
  535. $factor = (float) PHPExcel_Calculation_Functions::flattenSingleValue($factor);
  536. // Validate
  537. if ((is_numeric($cost)) && (is_numeric($salvage)) && (is_numeric($life)) && (is_numeric($period)) && (is_numeric($factor))) {
  538. if (($cost <= 0) || (($salvage / $cost) < 0) || ($life <= 0) || ($period < 1) || ($factor <= 0.0) || ($period > $life)) {
  539. return PHPExcel_Calculation_Functions::NaN();
  540. }
  541. // Set Fixed Depreciation Rate
  542. $fixedDepreciationRate = 1 - pow(($salvage / $cost), (1 / $life));
  543. $fixedDepreciationRate = round($fixedDepreciationRate, 3);
  544. // Loop through each period calculating the depreciation
  545. $previousDepreciation = 0;
  546. for ($per = 1; $per <= $period; ++$per) {
  547. $depreciation = min( ($cost - $previousDepreciation) * ($factor / $life), ($cost - $salvage - $previousDepreciation) );
  548. $previousDepreciation += $depreciation;
  549. }
  550. if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_GNUMERIC) {
  551. $depreciation = round($depreciation,2);
  552. }
  553. return $depreciation;
  554. }
  555. return PHPExcel_Calculation_Functions::VALUE();
  556. } // function DDB()
  557. /**
  558. * DISC
  559. *
  560. * Returns the discount rate for a security.
  561. *
  562. * @param mixed settlement The security's settlement date.
  563. * The security settlement date is the date after the issue date when the security is traded to the buyer.
  564. * @param mixed maturity The security's maturity date.
  565. * The maturity date is the date when the security expires.
  566. * @param int price The security's price per $100 face value.
  567. * @param int redemption the security's redemption value per $100 face value.
  568. * @param int basis The type of day count to use.
  569. * 0 or omitted US (NASD) 30/360
  570. * 1 Actual/actual
  571. * 2 Actual/360
  572. * 3 Actual/365
  573. * 4 European 30/360
  574. * @return float
  575. */
  576. public static function DISC($settlement, $maturity, $price, $redemption, $basis=0) {
  577. $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
  578. $maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
  579. $price = (float) PHPExcel_Calculation_Functions::flattenSingleValue($price);
  580. $redemption = (float) PHPExcel_Calculation_Functions::flattenSingleValue($redemption);
  581. $basis = (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
  582. // Validate
  583. if ((is_numeric($price)) && (is_numeric($redemption)) && (is_numeric($basis))) {
  584. if (($price <= 0) || ($redemption <= 0)) {
  585. return PHPExcel_Calculation_Functions::NaN();
  586. }
  587. $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $maturity, $basis);
  588. if (!is_numeric($daysBetweenSettlementAndMaturity)) {
  589. // return date error
  590. return $daysBetweenSettlementAndMaturity;
  591. }
  592. return ((1 - $price / $redemption) / $daysBetweenSettlementAndMaturity);
  593. }
  594. return PHPExcel_Calculation_Functions::VALUE();
  595. } // function DISC()
  596. /**
  597. * DOLLARDE
  598. *
  599. * Converts a dollar price expressed as an integer part and a fraction part into a dollar price expressed as a decimal number.
  600. * Fractional dollar numbers are sometimes used for security prices.
  601. *
  602. * @param float $fractional_dollar Fractional Dollar
  603. * @param int $fraction Fraction
  604. * @return float
  605. */
  606. public static function DOLLARDE($fractional_dollar = Null, $fraction = 0) {
  607. $fractional_dollar = PHPExcel_Calculation_Functions::flattenSingleValue($fractional_dollar);
  608. $fraction = (int)PHPExcel_Calculation_Functions::flattenSingleValue($fraction);
  609. // Validate parameters
  610. if (is_null($fractional_dollar) || $fraction < 0) {
  611. return PHPExcel_Calculation_Functions::NaN();
  612. }
  613. if ($fraction == 0) {
  614. return PHPExcel_Calculation_Functions::DIV0();
  615. }
  616. $dollars = floor($fractional_dollar);
  617. $cents = fmod($fractional_dollar,1);
  618. $cents /= $fraction;
  619. $cents *= pow(10,ceil(log10($fraction)));
  620. return $dollars + $cents;
  621. } // function DOLLARDE()
  622. /**
  623. * DOLLARFR
  624. *
  625. * Converts a dollar price expressed as a decimal number into a dollar price expressed as a fraction.
  626. * Fractional dollar numbers are sometimes used for security prices.
  627. *
  628. * @param float $decimal_dollar Decimal Dollar
  629. * @param int $fraction Fraction
  630. * @return float
  631. */
  632. public static function DOLLARFR($decimal_dollar = Null, $fraction = 0) {
  633. $decimal_dollar = PHPExcel_Calculation_Functions::flattenSingleValue($decimal_dollar);
  634. $fraction = (int)PHPExcel_Calculation_Functions::flattenSingleValue($fraction);
  635. // Validate parameters
  636. if (is_null($decimal_dollar) || $fraction < 0) {
  637. return PHPExcel_Calculation_Functions::NaN();
  638. }
  639. if ($fraction == 0) {
  640. return PHPExcel_Calculation_Functions::DIV0();
  641. }
  642. $dollars = floor($decimal_dollar);
  643. $cents = fmod($decimal_dollar,1);
  644. $cents *= $fraction;
  645. $cents *= pow(10,-ceil(log10($fraction)));
  646. return $dollars + $cents;
  647. } // function DOLLARFR()
  648. /**
  649. * EFFECT
  650. *
  651. * Returns the effective interest rate given the nominal rate and the number of compounding payments per year.
  652. *
  653. * @param float $nominal_rate Nominal interest rate
  654. * @param int $npery Number of compounding payments per year
  655. * @return float
  656. */
  657. public static function EFFECT($nominal_rate = 0, $npery = 0) {
  658. $nominal_rate = PHPExcel_Calculation_Functions::flattenSingleValue($nominal_rate);
  659. $npery = (int)PHPExcel_Calculation_Functions::flattenSingleValue($npery);
  660. // Validate parameters
  661. if ($nominal_rate <= 0 || $npery < 1) {
  662. return PHPExcel_Calculation_Functions::NaN();
  663. }
  664. return pow((1 + $nominal_rate / $npery), $npery) - 1;
  665. } // function EFFECT()
  666. /**
  667. * FV
  668. *
  669. * Returns the Future Value of a cash flow with constant payments and interest rate (annuities).
  670. *
  671. * @param float $rate Interest rate per period
  672. * @param int $nper Number of periods
  673. * @param float $pmt Periodic payment (annuity)
  674. * @param float $pv Present Value
  675. * @param int $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period
  676. * @return float
  677. */
  678. public static function FV($rate = 0, $nper = 0, $pmt = 0, $pv = 0, $type = 0) {
  679. $rate = PHPExcel_Calculation_Functions::flattenSingleValue($rate);
  680. $nper = PHPExcel_Calculation_Functions::flattenSingleValue($nper);
  681. $pmt = PHPExcel_Calculation_Functions::flattenSingleValue($pmt);
  682. $pv = PHPExcel_Calculation_Functions::flattenSingleValue($pv);
  683. $type = PHPExcel_Calculation_Functions::flattenSingleValue($type);
  684. // Validate parameters
  685. if ($type != 0 && $type != 1) {
  686. return PHPExcel_Calculation_Functions::NaN();
  687. }
  688. // Calculate
  689. if (!is_null($rate) && $rate != 0) {
  690. return -$pv * pow(1 + $rate, $nper) - $pmt * (1 + $rate * $type) * (pow(1 + $rate, $nper) - 1) / $rate;
  691. } else {
  692. return -$pv - $pmt * $nper;
  693. }
  694. } // function FV()
  695. /**
  696. * FVSCHEDULE
  697. *
  698. */
  699. public static function FVSCHEDULE($principal, $schedule) {
  700. $principal = PHPExcel_Calculation_Functions::flattenSingleValue($principal);
  701. $schedule = PHPExcel_Calculation_Functions::flattenArray($schedule);
  702. foreach($schedule as $n) {
  703. $principal *= 1 + $n;
  704. }
  705. return $principal;
  706. } // function FVSCHEDULE()
  707. /**
  708. * INTRATE
  709. *
  710. * Returns the interest rate for a fully invested security.
  711. *
  712. * @param mixed settlement The security's settlement date.
  713. * The security settlement date is the date after the issue date when the security is traded to the buyer.
  714. * @param mixed maturity The security's maturity date.
  715. * The maturity date is the date when the security expires.
  716. * @param int investment The amount invested in the security.
  717. * @param int redemption The amount to be received at maturity.
  718. * @param int basis The type of day count to use.
  719. * 0 or omitted US (NASD) 30/360
  720. * 1 Actual/actual
  721. * 2 Actual/360
  722. * 3 Actual/365
  723. * 4 European 30/360
  724. * @return float
  725. */
  726. public static function INTRATE($settlement, $maturity, $investment, $redemption, $basis=0) {
  727. $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
  728. $maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
  729. $investment = (float) PHPExcel_Calculation_Functions::flattenSingleValue($investment);
  730. $redemption = (float) PHPExcel_Calculation_Functions::flattenSingleValue($redemption);
  731. $basis = (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
  732. // Validate
  733. if ((is_numeric($investment)) && (is_numeric($redemption)) && (is_numeric($basis))) {
  734. if (($investment <= 0) || ($redemption <= 0)) {
  735. return PHPExcel_Calculation_Functions::NaN();
  736. }
  737. $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $maturity, $basis);
  738. if (!is_numeric($daysBetweenSettlementAndMaturity)) {
  739. // return date error
  740. return $daysBetweenSettlementAndMaturity;
  741. }
  742. return (($redemption / $investment) - 1) / ($daysBetweenSettlementAndMaturity);
  743. }
  744. return PHPExcel_Calculation_Functions::VALUE();
  745. } // function INTRATE()
  746. /**
  747. * IPMT
  748. *
  749. * Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate.
  750. *
  751. * @param float $rate Interest rate per period
  752. * @param int $per Period for which we want to find the interest
  753. * @param int $nper Number of periods
  754. * @param float $pv Present Value
  755. * @param float $fv Future Value
  756. * @param int $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period
  757. * @return float
  758. */
  759. public static function IPMT($rate, $per, $nper, $pv, $fv = 0, $type = 0) {
  760. $rate = PHPExcel_Calculation_Functions::flattenSingleValue($rate);
  761. $per = (int) PHPExcel_Calculation_Functions::flattenSingleValue($per);
  762. $nper = (int) PHPExcel_Calculation_Functions::flattenSingleValue($nper);
  763. $pv = PHPExcel_Calculation_Functions::flattenSingleValue($pv);
  764. $fv = PHPExcel_Calculation_Functions::flattenSingleValue($fv);
  765. $type = (int) PHPExcel_Calculation_Functions::flattenSingleValue($type);
  766. // Validate parameters
  767. if ($type != 0 && $type != 1) {
  768. return PHPExcel_Calculation_Functions::NaN();
  769. }
  770. if ($per <= 0 || $per > $nper) {
  771. return PHPExcel_Calculation_Functions::VALUE();
  772. }
  773. // Calculate
  774. $interestAndPrincipal = self::_interestAndPrincipal($rate, $per, $nper, $pv, $fv, $type);
  775. return $interestAndPrincipal[0];
  776. } // function IPMT()
  777. public static function IRR($values, $guess = 0.1) {
  778. if (!is_array($values)) return PHPExcel_Calculation_Functions::VALUE();
  779. $values = PHPExcel_Calculation_Functions::flattenArray($values);
  780. $guess = PHPExcel_Calculation_Functions::flattenSingleValue($guess);
  781. // create an initial range, with a root somewhere between 0 and guess
  782. $x1 = 0.0;
  783. $x2 = $guess;
  784. $f1 = self::NPV($x1, $values);
  785. $f2 = self::NPV($x2, $values);
  786. for ($i = 0; $i < FINANCIAL_MAX_ITERATIONS; ++$i) {
  787. if (($f1 * $f2) < 0.0) break;
  788. if (abs($f1) < abs($f2)) {
  789. $f1 = self::NPV($x1 += 1.6 * ($x1 - $x2), $values);
  790. } else {
  791. $f2 = self::NPV($x2 += 1.6 * ($x2 - $x1), $values);
  792. }
  793. }
  794. if (($f1 * $f2) > 0.0) return PHPExcel_Calculation_Functions::VALUE();
  795. $f = self::NPV($x1, $values);
  796. if ($f < 0.0) {
  797. $rtb = $x1;
  798. $dx = $x2 - $x1;
  799. } else {
  800. $rtb = $x2;
  801. $dx = $x1 - $x2;
  802. }
  803. for ($i = 0; $i < FINANCIAL_MAX_ITERATIONS; ++$i) {
  804. $dx *= 0.5;
  805. $x_mid = $rtb + $dx;
  806. $f_mid = self::NPV($x_mid, $values);
  807. if ($f_mid <= 0.0) $rtb = $x_mid;
  808. if ((abs($f_mid) < FINANCIAL_PRECISION) || (abs($dx) < FINANCIAL_PRECISION)) return $x_mid;
  809. }
  810. return PHPExcel_Calculation_Functions::VALUE();
  811. } // function IRR()
  812. /**
  813. * ISPMT
  814. *
  815. * Returns the interest payment for an investment based on an interest rate and a constant payment schedule.
  816. *
  817. * Excel Function:
  818. * =ISPMT(interest_rate, period, number_payments, PV)
  819. *
  820. * interest_rate is the interest rate for the investment
  821. *
  822. * period is the period to calculate the interest rate. It must be betweeen 1 and number_payments.
  823. *
  824. * number_payments is the number of payments for the annuity
  825. *
  826. * PV is the loan amount or present value of the payments
  827. */
  828. public static function ISPMT() {
  829. // Return value
  830. $returnValue = 0;
  831. // Get the parameters
  832. $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
  833. $interestRate = array_shift($aArgs);
  834. $period = array_shift($aArgs);
  835. $numberPeriods = array_shift($aArgs);
  836. $principleRemaining = array_shift($aArgs);
  837. // Calculate
  838. $principlePayment = ($principleRemaining * 1.0) / ($numberPeriods * 1.0);
  839. for($i=0; $i <= $period; ++$i) {
  840. $returnValue = $interestRate * $principleRemaining * -1;
  841. $principleRemaining -= $principlePayment;
  842. // principle needs to be 0 after the last payment, don't let floating point screw it up
  843. if($i == $numberPeriods) {
  844. $returnValue = 0;
  845. }
  846. }
  847. return($returnValue);
  848. } // function ISPMT()
  849. public static function MIRR($values, $finance_rate, $reinvestment_rate) {
  850. if (!is_array($values)) return PHPExcel_Calculation_Functions::VALUE();
  851. $values = PHPExcel_Calculation_Functions::flattenArray($values);
  852. $finance_rate = PHPExcel_Calculation_Functions::flattenSingleValue($finance_rate);
  853. $reinvestment_rate = PHPExcel_Calculation_Functions::flattenSingleValue($reinvestment_rate);
  854. $n = count($values);
  855. $rr = 1.0 + $reinvestment_rate;
  856. $fr = 1.0 + $finance_rate;
  857. $npv_pos = $npv_neg = 0.0;
  858. foreach($values as $i => $v) {
  859. if ($v >= 0) {
  860. $npv_pos += $v / pow($rr, $i);
  861. } else {
  862. $npv_neg += $v / pow($fr, $i);
  863. }
  864. }
  865. if (($npv_neg == 0) || ($npv_pos == 0) || ($reinvestment_rate <= -1)) {
  866. return PHPExcel_Calculation_Functions::VALUE();
  867. }
  868. $mirr = pow((-$npv_pos * pow($rr, $n))
  869. / ($npv_neg * ($rr)), (1.0 / ($n - 1))) - 1.0;
  870. return (is_finite($mirr) ? $mirr : PHPExcel_Calculation_Functions::VALUE());
  871. } // function MIRR()
  872. /**
  873. * NOMINAL
  874. *
  875. * Returns the nominal interest rate given the effective rate and the number of compounding payments per year.
  876. *
  877. * @param float $effect_rate Effective interest rate
  878. * @param int $npery Number of compounding payments per year
  879. * @return float
  880. */
  881. public static function NOMINAL($effect_rate = 0, $npery = 0) {
  882. $effect_rate = PHPExcel_Calculation_Functions::flattenSingleValue($effect_rate);
  883. $npery = (int)PHPExcel_Calculation_Functions::flattenSingleValue($npery);
  884. // Validate parameters
  885. if ($effect_rate <= 0 || $npery < 1) {
  886. return PHPExcel_Calculation_Functions::NaN();
  887. }
  888. // Calculate
  889. return $npery * (pow($effect_rate + 1, 1 / $npery) - 1);
  890. } // function NOMINAL()
  891. /**
  892. * NPER
  893. *
  894. * Returns the number of periods for a cash flow with constant periodic payments (annuities), and interest rate.
  895. *
  896. * @param float $rate Interest rate per period
  897. * @param int $pmt Periodic payment (annuity)
  898. * @param float $pv Present Value
  899. * @param float $fv Future Value
  900. * @param int $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period
  901. * @return float
  902. */
  903. public static function NPER($rate = 0, $pmt = 0, $pv = 0, $fv = 0, $type = 0) {
  904. $rate = PHPExcel_Calculation_Functions::flattenSingleValue($rate);
  905. $pmt = PHPExcel_Calculation_Functions::flattenSingleValue($pmt);
  906. $pv = PHPExcel_Calculation_Functions::flattenSingleValue($pv);
  907. $fv = PHPExcel_Calculation_Functions::flattenSingleValue($fv);
  908. $type = PHPExcel_Calculation_Functions::flattenSingleValue($type);
  909. // Validate parameters
  910. if ($type != 0 && $type != 1) {
  911. return PHPExcel_Calculation_Functions::NaN();
  912. }
  913. // Calculate
  914. if (!is_null($rate) && $rate != 0) {
  915. if ($pmt == 0 && $pv == 0) {
  916. return PHPExcel_Calculation_Functions::NaN();
  917. }
  918. return log(($pmt * (1 + $rate * $type) / $rate - $fv) / ($pv + $pmt * (1 + $rate * $type) / $rate)) / log(1 + $rate);
  919. } else {
  920. if ($pmt == 0) {
  921. return PHPExcel_Calculation_Functions::NaN();
  922. }
  923. return (-$pv -$fv) / $pmt;
  924. }
  925. } // function NPER()
  926. /**
  927. * NPV
  928. *
  929. * Returns the Net Present Value of a cash flow series given a discount rate.
  930. *
  931. * @param float Discount interest rate
  932. * @param array Cash flow series
  933. * @return float
  934. */
  935. public static function NPV() {
  936. // Return value
  937. $returnValue = 0;
  938. // Loop through arguments
  939. $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
  940. // Calculate
  941. $rate = array_shift($aArgs);
  942. for ($i = 1; $i <= count($aArgs); ++$i) {
  943. // Is it a numeric value?
  944. if (is_numeric($aArgs[$i - 1])) {
  945. $returnValue += $aArgs[$i - 1] / pow(1 + $rate, $i);
  946. }
  947. }
  948. // Return
  949. return $returnValue;
  950. } // function NPV()
  951. /**
  952. * PMT
  953. *
  954. * Returns the constant payment (annuity) for a cash flow with a constant interest rate.
  955. *
  956. * @param float $rate Interest rate per period
  957. * @param int $nper Number of periods
  958. * @param float $pv Present Value
  959. * @param float $fv Future Value
  960. * @param int $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period
  961. * @return float
  962. */
  963. public static function PMT($rate = 0, $nper = 0, $pv = 0, $fv = 0, $type = 0) {
  964. $rate = PHPExcel_Calculation_Functions::flattenSingleValue($rate);
  965. $nper = PHPExcel_Calculation_Functions::flattenSingleValue($nper);
  966. $pv = PHPExcel_Calculation_Functions::flattenSingleValue($pv);
  967. $fv = PHPExcel_Calculation_Functions::flattenSingleValue($fv);
  968. $type = PHPExcel_Calculation_Functions::flattenSingleValue($type);
  969. // Validate parameters
  970. if ($type != 0 && $type != 1) {
  971. return PHPExcel_Calculation_Functions::NaN();
  972. }
  973. // Calculate
  974. if (!is_null($rate) && $rate != 0) {
  975. return (-$fv - $pv * pow(1 + $rate, $nper)) / (1 + $rate * $type) / ((pow(1 + $rate, $nper) - 1) / $rate);
  976. } else {
  977. return (-$pv - $fv) / $nper;
  978. }
  979. } // function PMT()
  980. /**
  981. * PPMT
  982. *
  983. * Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate.
  984. *
  985. * @param float $rate Interest rate per period
  986. * @param int $per Period for which we want to find the interest
  987. * @param int $nper Number of periods
  988. * @param float $pv Present Value
  989. * @param float $fv Future Value
  990. * @param int $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period
  991. * @return float
  992. */
  993. public static function PPMT($rate, $per, $nper, $pv, $fv = 0, $type = 0) {
  994. $rate = PHPExcel_Calculation_Functions::flattenSingleValue($rate);
  995. $per = (int) PHPExcel_Calculation_Functions::flattenSingleValue($per);
  996. $nper = (int) PHPExcel_Calculation_Functions::flattenSingleValue($nper);
  997. $pv = PHPExcel_Calculation_Functions::flattenSingleValue($pv);
  998. $fv = PHPExcel_Calculation_Functions::flattenSingleValue($fv);
  999. $type = (int) PHPExcel_Calculation_Functions::flattenSingleValue($type);
  1000. // Validate parameters
  1001. if ($type != 0 && $type != 1) {
  1002. return PHPExcel_Calculation_Functions::NaN();
  1003. }
  1004. if ($per <= 0 || $per > $nper) {
  1005. return PHPExcel_Calculation_Functions::VALUE();
  1006. }
  1007. // Calculate
  1008. $interestAndPrincipal = self::_interestAndPrincipal($rate, $per, $nper, $pv, $fv, $type);
  1009. return $interestAndPrincipal[1];
  1010. } // function PPMT()
  1011. public static function PRICE($settlement, $maturity, $rate, $yield, $redemption, $frequency, $basis=0) {
  1012. $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
  1013. $maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
  1014. $rate = (float) PHPExcel_Calculation_Functions::flattenSingleValue($rate);
  1015. $yield = (float) PHPExcel_Calculation_Functions::flattenSingleValue($yield);
  1016. $redemption = (float) PHPExcel_Calculation_Functions::flattenSingleValue($redemption);
  1017. $frequency = (int) PHPExcel_Calculation_Functions::flattenSingleValue($frequency);
  1018. $basis = (is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
  1019. if (is_string($settlement = PHPExcel_Calculation_DateTime::_getDateValue($settlement))) {
  1020. return PHPExcel_Calculation_Functions::VALUE();
  1021. }
  1022. if (is_string($maturity = PHPExcel_Calculation_DateTime::_getDateValue($maturity))) {
  1023. return PHPExcel_Calculation_Functions::VALUE();
  1024. }
  1025. if (($settlement > $maturity) ||
  1026. (!self::_validFrequency($frequency)) ||
  1027. (($basis < 0) || ($basis > 4))) {
  1028. return PHPExcel_Calculation_Functions::NaN();
  1029. }
  1030. $dsc = self::COUPDAYSNC($settlement, $maturity, $frequency, $basis);
  1031. $e = self::COUPDAYS($settlement, $maturity, $frequency, $basis);
  1032. $n = self::COUPNUM($settlement, $maturity, $frequency, $basis);
  1033. $a = self::COUPDAYBS($settlement, $maturity, $frequency, $basis);
  1034. $baseYF = 1.0 + ($yield / $frequency);
  1035. $rfp = 100 * ($rate / $frequency);
  1036. $de = $dsc / $e;
  1037. $result = $redemption / pow($baseYF, (--$n + $de));
  1038. for($k = 0; $k <= $n; ++$k) {
  1039. $result += $rfp / (pow($baseYF, ($k + $de)));
  1040. }
  1041. $result -= $rfp * ($a / $e);
  1042. return $result;
  1043. } // function PRICE()
  1044. /**
  1045. * PRICEDISC
  1046. *
  1047. * Returns the price per $100 face value of a discounted security.
  1048. *
  1049. * @param mixed settlement The security's settlement date.
  1050. * The security settlement date is the date after the issue date when the security is traded to the buyer.
  1051. * @param mixed maturity The security's maturity date.
  1052. * The maturity date is the date when the security expires.
  1053. * @param int discount The security's discount rate.
  1054. * @param int redemption The security's redemption value per $100 face value.
  1055. * @param int basis The type of day count to use.
  1056. * 0 or omitted US (NASD) 30/360
  1057. * 1 Actual/actual
  1058. * 2 Actual/360
  1059. * 3 Actual/365
  1060. * 4 European 30/360
  1061. * @return float
  1062. */
  1063. public static function PRICEDISC($settlement, $maturity, $discount, $redemption, $basis=0) {
  1064. $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
  1065. $maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
  1066. $discount = (float) PHPExcel_Calculation_Functions::flattenSingleValue($discount);
  1067. $redemption = (float) PHPExcel_Calculation_Functions::flattenSingleValue($redemption);
  1068. $basis = (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
  1069. // Validate
  1070. if ((is_numeric($discount)) && (is_numeric($redemption)) && (is_numeric($basis))) {
  1071. if (($discount <= 0) || ($redemption <= 0)) {
  1072. return PHPExcel_Calculation_Functions::NaN();
  1073. }
  1074. $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $maturity, $basis);
  1075. if (!is_numeric($daysBetweenSettlementAndMaturity)) {
  1076. // return date error
  1077. return $daysBetweenSettlementAndMaturity;
  1078. }
  1079. return $redemption * (1 - $discount * $daysBetweenSettlementAndMaturity);
  1080. }
  1081. return PHPExcel_Calculation_Functions::VALUE();
  1082. } // function PRICEDISC()
  1083. /**
  1084. * PRICEMAT
  1085. *
  1086. * Returns the price per $100 face value of a security that pays interest at maturity.
  1087. *
  1088. * @param mixed settlement The security's settlement date.
  1089. * The security's settlement date is the date after the issue date when the security is traded to the buyer.
  1090. * @param mixed maturity The security's maturity date.
  1091. * The maturity date is the date when the security expires.
  1092. * @param mixed issue The security's issue date.
  1093. * @param int rate The security's interest rate at date of issue.
  1094. * @param int yield The security's annual yield.
  1095. * @param int basis The type of day count to use.
  1096. * 0 or omitted US (NASD) 30/360
  1097. * 1 Actual/actual
  1098. * 2 Actual/360
  1099. * 3 Actual/365
  1100. * 4 European 30/360
  1101. * @return float
  1102. */
  1103. public static function PRICEMAT($settlement, $maturity, $issue, $rate, $yield, $basis=0) {
  1104. $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
  1105. $maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
  1106. $issue = PHPExcel_Calculation_Functions::flattenSingleValue($issue);
  1107. $rate = PHPExcel_Calculation_Functions::flattenSingleValue($rate);
  1108. $yield = PHPExcel_Calculation_Functions::flattenSingleValue($yield);
  1109. $basis = (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
  1110. // Validate
  1111. if (is_numeric($rate) && is_numeric($yield)) {
  1112. if (($rate <= 0) || ($yield <= 0)) {
  1113. return PHPExcel_Calculation_Functions::NaN();
  1114. }
  1115. $daysPerYear = self::_daysPerYear(PHPExcel_Calculation_DateTime::YEAR($settlement),$basis);
  1116. if (!is_numeric($daysPerYear)) {
  1117. return $daysPerYear;
  1118. }
  1119. $daysBetweenIssueAndSettlement = PHPExcel_Calculation_DateTime::YEARFRAC($issue, $settlement, $basis);
  1120. if (!is_numeric($daysBetweenIssueAndSettlement)) {
  1121. // return date error
  1122. return $daysBetweenIssueAndSettlement;
  1123. }
  1124. $daysBetweenIssueAndSettlement *= $daysPerYear;
  1125. $daysBetweenIssueAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($issue, $maturity, $basis);
  1126. if (!is_numeric($daysBetweenIssueAndMaturity)) {
  1127. // return date error
  1128. return $daysBetweenIssueAndMaturity;
  1129. }
  1130. $daysBetweenIssueAndMaturity *= $daysPerYear;
  1131. $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $maturity, $basis);
  1132. if (!is_numeric($daysBetweenSettlementAndMaturity)) {
  1133. // return date error
  1134. return $daysBetweenSettlementAndMaturity;
  1135. }
  1136. $daysBetweenSettlementAndMaturity *= $daysPerYear;
  1137. return ((100 + (($daysBetweenIssueAndMaturity / $daysPerYear) * $rate *

Large files files are truncated, but you can click here to view the full file