PageRenderTime 57ms CodeModel.GetById 23ms RepoModel.GetById 0ms app.codeStats 1ms

/Classes/PHPExcel/Calculation/Financial.php

https://bitbucket.org/crowdguru/phpexcel
PHP | 2248 lines | 1057 code | 233 blank | 958 comment | 319 complexity | 7e02a6a9892568f9bd14fcfe09361ef4 MD5 | raw file

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

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