PageRenderTime 57ms CodeModel.GetById 20ms RepoModel.GetById 0ms app.codeStats 0ms

/protected/extensions/PHPExcel/vendor/PHPExcel/Calculation/Financial.php

https://bitbucket.org/y_widyatama/ijepa2
PHP | 2292 lines | 1059 code | 230 blank | 1003 comment | 319 complexity | db193522b50743c32a4ea8b2ec01a00a MD5 | raw file
Possible License(s): LGPL-2.1, BSD-3-Clause
  1. <?php
  2. /**
  3. * PHPExcel
  4. *
  5. * Copyright (c) 2006 - 2014 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 - 2014 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 - 2014 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. * Returns the future value of an initial principal after applying a series of compound interest rates.
  1096. * Use FVSCHEDULE to calculate the future value of an investment with a variable or adjustable rate.
  1097. *
  1098. * Excel Function:
  1099. * FVSCHEDULE(principal,schedule)
  1100. *
  1101. * @param float $principal The present value.
  1102. * @param float[] $schedule An array of interest rates to apply.
  1103. * @return float
  1104. */
  1105. public static function FVSCHEDULE($principal, $schedule) {
  1106. $principal = PHPExcel_Calculation_Functions::flattenSingleValue($principal);
  1107. $schedule = PHPExcel_Calculation_Functions::flattenArray($schedule);
  1108. foreach($schedule as $rate) {
  1109. $principal *= 1 + $rate;
  1110. }
  1111. return $principal;
  1112. } // function FVSCHEDULE()
  1113. /**
  1114. * INTRATE
  1115. *
  1116. * Returns the interest rate for a fully invested security.
  1117. *
  1118. * Excel Function:
  1119. * INTRATE(settlement,maturity,investment,redemption[,basis])
  1120. *
  1121. * @param mixed $settlement The security's settlement date.
  1122. * The security settlement date is the date after the issue date when the security is traded to the buyer.
  1123. * @param mixed $maturity The security's maturity date.
  1124. * The maturity date is the date when the security expires.
  1125. * @param integer $investment The amount invested in the security.
  1126. * @param integer $redemption The amount to be received at maturity.
  1127. * @param integer $basis The type of day count to use.
  1128. * 0 or omitted US (NASD) 30/360
  1129. * 1 Actual/actual
  1130. * 2 Actual/360
  1131. * 3 Actual/365
  1132. * 4 European 30/360
  1133. * @return float
  1134. */
  1135. public static function INTRATE($settlement, $maturity, $investment, $redemption, $basis=0) {
  1136. $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
  1137. $maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
  1138. $investment = PHPExcel_Calculation_Functions::flattenSingleValue($investment);
  1139. $redemption = PHPExcel_Calculation_Functions::flattenSingleValue($redemption);
  1140. $basis = PHPExcel_Calculation_Functions::flattenSingleValue($basis);
  1141. // Validate
  1142. if ((is_numeric($investment)) && (is_numeric($redemption)) && (is_numeric($basis))) {
  1143. $investment = (float) $investment;
  1144. $redemption = (float) $redemption;
  1145. $basis = (int) $basis;
  1146. if (($investment <= 0) || ($redemption <= 0)) {
  1147. return PHPExcel_Calculation_Functions::NaN();
  1148. }
  1149. $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $maturity, $basis);
  1150. if (!is_numeric($daysBetweenSettlementAndMaturity)) {
  1151. // return date error
  1152. return $daysBetweenSettlementAndMaturity;
  1153. }
  1154. return (($redemption / $investment) - 1) / ($daysBetweenSettlementAndMaturity);
  1155. }
  1156. return PHPExcel_Calculation_Functions::VALUE();
  1157. } // function INTRATE()
  1158. /**
  1159. * IPMT
  1160. *
  1161. * Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate.
  1162. *
  1163. * Excel Function:
  1164. * IPMT(rate,per,nper,pv[,fv][,type])
  1165. *
  1166. * @param float $rate Interest rate per period
  1167. * @param int $per Period for which we want to find the interest
  1168. * @param int $nper Number of periods
  1169. * @param float $pv Present Value
  1170. * @param float $fv Future Value
  1171. * @param int $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period
  1172. * @return float
  1173. */
  1174. public static function IPMT($rate, $per, $nper, $pv, $fv = 0, $type = 0) {
  1175. $rate = PHPExcel_Calculation_Functions::flattenSingleValue($rate);
  1176. $per = (int) PHPExcel_Calculation_Functions::flattenSingleValue($per);
  1177. $nper = (int) PHPExcel_Calculation_Functions::flattenSingleValue($nper);
  1178. $pv = PHPExcel_Calculation_Functions::flattenSingleValue($pv);
  1179. $fv = PHPExcel_Calculation_Functions::flattenSingleValue($fv);
  1180. $type = (int) PHPExcel_Calculation_Functions::flattenSingleValue($type);
  1181. // Validate parameters
  1182. if ($type != 0 && $type != 1) {
  1183. return PHPExcel_Calculation_Functions::NaN();
  1184. }
  1185. if ($per <= 0 || $per > $nper) {
  1186. return PHPExcel_Calculation_Functions::VALUE();
  1187. }
  1188. // Calculate
  1189. $interestAndPrincipal = self::_interestAndPrincipal($rate, $per, $nper, $pv, $fv, $type);
  1190. return $interestAndPrincipal[0];
  1191. } // function IPMT()
  1192. /**
  1193. * IRR
  1194. *
  1195. * Returns the internal rate of return for a series of cash flows represented by the numbers in values.
  1196. * These cash flows do not have to be even, as they would be for an annuity. However, the cash flows must occur
  1197. * at regular intervals, such as monthly or annually. The internal rate of return is the interest rate received
  1198. * for an investment consisting of payments (negative values) and income (positive values) that occur at regular
  1199. * periods.
  1200. *
  1201. * Excel Function:
  1202. * IRR(values[,guess])
  1203. *
  1204. * @param float[] $values An array or a reference to cells that contain numbers for which you want
  1205. * to calculate the internal rate of return.
  1206. * Values must contain at least one positive value and one negative value to
  1207. * calculate the internal rate of return.
  1208. * @param float $guess A number that you guess is close to the result of IRR
  1209. * @return float
  1210. */
  1211. public static function IRR($values, $guess = 0.1) {
  1212. if (!is_array($values)) return PHPExcel_Calculation_Functions::VALUE();
  1213. $values = PHPExcel_Calculation_Functions::flattenArray($values);
  1214. $guess = PHPExcel_Calculation_Functions::flattenSingleValue($guess);
  1215. // create an initial range, with a root somewhere between 0 and guess
  1216. $x1 = 0.0;
  1217. $x2 = $guess;
  1218. $f1 = self::NPV($x1, $values);
  1219. $f2 = self::NPV($x2, $values);
  1220. for ($i = 0; $i < FINANCIAL_MAX_ITERATIONS; ++$i) {
  1221. if (($f1 * $f2) < 0.0) break;
  1222. if (abs($f1) < abs($f2)) {
  1223. $f1 = self::NPV($x1 += 1.6 * ($x1 - $x2), $values);
  1224. } else {
  1225. $f2 = self::NPV($x2 += 1.6 * ($x2 - $x1), $values);
  1226. }
  1227. }
  1228. if (($f1 * $f2) > 0.0) return PHPExcel_Calculation_Functions::VALUE();
  1229. $f = self::NPV($x1, $values);
  1230. if ($f < 0.0) {
  1231. $rtb = $x1;
  1232. $dx = $x2 - $x1;
  1233. } else {
  1234. $rtb = $x2;
  1235. $dx = $x1 - $x2;
  1236. }
  1237. for ($i = 0; $i < FINANCIAL_MAX_ITERATIONS; ++$i) {
  1238. $dx *= 0.5;
  1239. $x_mid = $rtb + $dx;
  1240. $f_mid = self::NPV($x_mid, $values);
  1241. if ($f_mid <= 0.0)
  1242. $rtb = $x_mid;
  1243. if ((abs($f_mid) < FINANCIAL_PRECISION) || (abs($dx) < FINANCIAL_PRECISION))
  1244. return $x_mid;
  1245. }
  1246. return PHPExcel_Calculation_Functions::VALUE();
  1247. } // function IRR()
  1248. /**
  1249. * ISPMT
  1250. *
  1251. * Returns the interest payment for an investment based on an interest rate and a constant payment schedule.
  1252. *
  1253. * Excel Function:
  1254. * =ISPMT(interest_rate, period, number_payments, PV)
  1255. *
  1256. * interest_rate is the interest rate for the investment
  1257. *
  1258. * period is the period to calculate the interest rate. It must be betweeen 1 and number_payments.
  1259. *
  1260. * number_payments is the number of payments for the annuity
  1261. *
  1262. * PV is the loan amount or present value of the payments
  1263. */
  1264. public static function ISPMT() {
  1265. // Return value
  1266. $returnValue = 0;
  1267. // Get the parameters
  1268. $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
  1269. $interestRate = array_shift($aArgs);
  1270. $period = array_shift($aArgs);
  1271. $numberPeriods = array_shift($aArgs);
  1272. $principleRemaining = array_shift($aArgs);
  1273. // Calculate
  1274. $principlePayment = ($principleRemaining * 1.0) / ($numberPeriods * 1.0);
  1275. for($i=0; $i <= $period; ++$i) {
  1276. $returnValue = $interestRate * $principleRemaining * -1;
  1277. $principleRemaining -= $principlePayment;
  1278. // principle needs to be 0 after the last payment, don't let floating point screw it up
  1279. if($i == $numberPeriods) {
  1280. $returnValue = 0;
  1281. }
  1282. }
  1283. return($returnValue);
  1284. } // function ISPMT()
  1285. /**
  1286. * MIRR
  1287. *
  1288. * Returns the modified internal rate of return for a series of periodic cash flows. MIRR considers both
  1289. * the cost of the investment and the interest received on reinvestment of cash.
  1290. *
  1291. * Excel Function:
  1292. * MIRR(values,finance_rate, reinvestment_rate)
  1293. *
  1294. * @param float[] $values An array or a reference to cells that contain a series of payments and
  1295. * income occurring at regular intervals.
  1296. * Payments are negative value, income is positive values.
  1297. * @param float $finance_rate The interest rate you pay on the money used in the cash flows
  1298. * @param float $reinvestment_rate The interest rate you receive on the cash flows as you reinvest them
  1299. * @return float
  1300. */
  1301. public static function MIRR($values, $finance_rate, $reinvestment_rate) {
  1302. if (!is_array($values)) return PHPExcel_Calculation_Functions::VALUE();
  1303. $values = PHPExcel_Calculation_Functions::flattenArray($values);
  1304. $finance_rate = PHPExcel_Calculation_Functions::flattenSingleValue($finance_rate);
  1305. $reinvestment_rate = PHPExcel_Calculation_Functions::flattenSingleValue($reinvestment_rate);
  1306. $n = count($values);
  1307. $rr = 1.0 + $reinvestment_rate;
  1308. $fr = 1.0 + $finance_rate;
  1309. $npv_pos = $npv_neg = 0.0;
  1310. foreach($values as $i => $v) {
  1311. if ($v >= 0) {
  1312. $npv_pos += $v / pow($rr, $i);
  1313. } else {
  1314. $npv_neg += $v / pow($fr, $i);
  1315. }
  1316. }
  1317. if (($npv_neg == 0) || ($npv_pos == 0) || ($reinvestment_rate <= -1)) {
  1318. return PHPExcel_Calculation_Functions::VALUE();
  1319. }
  1320. $mirr = pow((-$npv_pos * pow($rr, $n))
  1321. / ($npv_neg * ($rr)), (1.0 / ($n - 1))) - 1.0;
  1322. return (is_finite($mirr) ? $mirr : PHPExcel_Calculation_Functions::VALUE());
  1323. } // function MIRR()
  1324. /**
  1325. * NOMINAL
  1326. *
  1327. * Returns the nominal interest rate given the effective rate and the number of compounding payments per year.
  1328. *
  1329. * @param float $effect_rate Effective interest rate
  1330. * @param int $npery Number of compounding payments per year
  1331. * @return float
  1332. */
  1333. public static function NOMINAL($effect_rate = 0, $npery = 0) {
  1334. $effect_rate = PHPExcel_Calculation_Functions::flattenSingleValue($effect_rate);
  1335. $npery = (int)PHPExcel_Calculation_Functions::flattenSingleValue($npery);
  1336. // Validate parameters
  1337. if ($effect_rate <= 0 || $npery < 1) {
  1338. return PHPExcel_Calculation_Functions::NaN();
  1339. }
  1340. // Calculate
  1341. return $npery * (pow($effect_rate + 1, 1 / $npery) - 1);
  1342. } // function NOMINAL()
  1343. /**
  1344. * NPER
  1345. *
  1346. * Returns the number of periods for a cash flow with constant periodic payments (annuities), and interest rate.
  1347. *
  1348. * @param float $rate Interest rate per period
  1349. * @param int $pmt Periodic payment (annuity)
  1350. * @param float $pv Present Value
  1351. * @param float $fv Future Value
  1352. * @param int $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period
  1353. * @return float
  1354. */
  1355. public static function NPER($rate = 0, $pmt = 0, $pv = 0, $fv = 0, $type = 0) {
  1356. $rate = PHPExcel_Calculation_Functions::flattenSingleValue($rate);
  1357. $pmt = PHPExcel_Calculation_Functions::flattenSingleValue($pmt);
  1358. $pv = PHPExcel_Calculation_Functions::flattenSingleValue($pv);
  1359. $fv = PHPExcel_Calculation_Functions::flattenSingleValue($fv);
  1360. $type = PHPExcel_Calculation_Functions::flattenSingleValue($type);
  1361. // Validate parameters
  1362. if ($type != 0 && $type != 1) {
  1363. return PHPExcel_Calculation_Functions::NaN();
  1364. }
  1365. // Calculate
  1366. if (!is_null($rate) && $rate != 0) {
  1367. if ($pmt == 0 && $pv == 0) {
  1368. return PHPExcel_Calculation_Functions::NaN();
  1369. }
  1370. return log(($pmt * (1 + $rate * $type) / $rate - $fv) / ($pv + $pmt * (1 + $rate * $type) / $rate)) / log(1 + $rate);
  1371. } else {
  1372. if ($pmt == 0) {
  1373. return PHPExcel_Calculation_Functions::NaN();
  1374. }
  1375. return (-$pv -$fv) / $pmt;
  1376. }
  1377. } // function NPER()
  1378. /**
  1379. * NPV
  1380. *
  1381. * Returns the Net Present Value of a cash flow series given a discount rate.
  1382. *
  1383. * @return float
  1384. */
  1385. public static function NPV() {
  1386. // Return value
  1387. $returnValue = 0;
  1388. // Loop through arguments
  1389. $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
  1390. // Calculate
  1391. $rate = array_shift($aArgs);
  1392. for ($i = 1; $i <= count($aArgs); ++$i) {
  1393. // Is it a numeric value?
  1394. if (is_numeric($aArgs[$i - 1])) {
  1395. $returnValue += $aArgs[$i - 1] / pow(1 + $rate, $i);
  1396. }
  1397. }
  1398. // Return
  1399. return $returnValue;
  1400. } // function NPV()
  1401. /**
  1402. * PMT
  1403. *
  1404. * Returns the constant payment (annuity) for a cash flow with a constant interest rate.
  1405. *
  1406. * @param float $rate Interest rate per period
  1407. * @param int $nper Number of periods
  1408. * @param float $pv Present Value
  1409. * @param float $fv Future Value
  1410. * @param int $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period
  1411. * @return float
  1412. */
  1413. public static function PMT($rate = 0, $nper = 0, $pv = 0, $fv = 0, $type = 0) {
  1414. $rate = PHPExcel_Calculation_Functions::flattenSingleValue($rate);
  1415. $nper = PHPExcel_Calculation_Functions::flattenSingleValue($nper);
  1416. $pv = PHPExcel_Calculation_Functions::flattenSingleValue($pv);
  1417. $fv = PHPExcel_Calculation_Functions::flattenSingleValue($fv);
  1418. $type = PHPExcel_Calculation_Functions::flattenSingleValue($type);
  1419. // Validate parameters
  1420. if ($type != 0 && $type != 1) {
  1421. return PHPExcel_Calculation_Functions::NaN();
  1422. }
  1423. // Calculate
  1424. if (!is_null($rate) && $rate != 0) {
  1425. return (-$fv - $pv * pow(1 + $rate, $nper)) / (1 + $rate * $type) / ((pow(1 + $rate, $nper) - 1) / $rate);
  1426. } else {
  1427. return (-$pv - $fv) / $nper;
  1428. }
  1429. } // function PMT()
  1430. /**
  1431. * PPMT
  1432. *
  1433. * Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate.
  1434. *
  1435. * @param float $rate Interest rate per period
  1436. * @param int $per Period for which we want to find the interest
  1437. * @param int $nper Number of periods
  1438. * @param float $pv Present Value
  1439. * @param float $fv Future Value
  1440. * @param int $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period
  1441. * @return float
  1442. */
  1443. public static function PPMT($rate, $per, $nper, $pv, $fv = 0, $type = 0) {
  1444. $rate = PHPExcel_Calculation_Functions::flattenSingleValue($rate);
  1445. $per = (int) PHPExcel_Calculation_Functions::flattenSingleValue($per);
  1446. $nper = (int) PHPExcel_Calculation_Functions::flattenSingleValue($nper);
  1447. $pv = PHPExcel_Calculation_Functions::flattenSingleValue($pv);
  1448. $fv = PHPExcel_Calculation_Functions::flattenSingleValue($fv);
  1449. $type = (int) PHPExcel_Calculation_Functions::flattenSingleValue($type);
  1450. // Validate parameters
  1451. if ($type != 0 && $type != 1) {
  1452. return PHPExcel_Calculation_Functions::NaN();
  1453. }
  1454. if ($per <= 0 || $per > $nper) {
  1455. return PHPExcel_Calculation_Functions::VALUE();
  1456. }
  1457. // Calculate
  1458. $interestAndPrincipal = self::_interestAndPrincipal($rate, $per, $nper, $pv, $fv, $type);
  1459. return $interestAndPrincipal[1];
  1460. } // function PPMT()
  1461. public static function PRICE($settlement, $maturity, $rate, $yield, $redemption, $frequency, $basis=0) {
  1462. $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
  1463. $maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
  1464. $rate = (float) PHPExcel_Calculation_Functions::flattenSingleValue($rate);
  1465. $yield = (float) PHPExcel_Calculation_Functions::flattenSingleValue($yield);
  1466. $redemption = (float) PHPExcel_Calculation_Functions::flattenSingleValue($redemption);
  1467. $frequency = (int) PHPExcel_Calculation_Functions::flattenSingleValue($frequency);
  1468. $basis = (is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
  1469. if (is_string($settlement = PHPExcel_Calculation_DateTime::_getDateValue($settlement))) {
  1470. return PHPExcel_Calculation_Functions::VALUE();
  1471. }
  1472. if (is_string($maturity = PHPExcel_Calculation_DateTime::_getDateValue($maturity))) {
  1473. return PHPExcel_Calculation_Functions::VALUE();
  1474. }
  1475. if (($settlement > $maturity) ||
  1476. (!self::_validFrequency($frequency)) ||
  1477. (($basis < 0) || ($basis > 4))) {
  1478. return PHPExcel_Calculation_Functions::NaN();
  1479. }
  1480. $dsc = self::COUPDAYSNC($settlement, $maturity, $frequency, $basis);
  1481. $e = self::COUPDAYS($settlement, $maturity, $frequency, $basis);
  1482. $n = self::COUPNUM($settlement, $maturity, $frequency, $basis);
  1483. $a = self::COUPDAYBS($settlement, $maturity, $frequency, $basis);
  1484. $baseYF = 1.0 + ($yield / $frequency);
  1485. $rfp = 100 * ($rate / $frequency);
  1486. $de = $dsc / $e;
  1487. $result = $redemption / pow($baseYF, (--$n + $de));
  1488. for($k = 0; $k <= $n; ++$k) {
  1489. $result += $rfp / (pow($baseYF, ($k + $de)));
  1490. }
  1491. $result -= $rfp * ($a / $e);
  1492. return $result;
  1493. } // function PRICE()
  1494. /**
  1495. * PRICEDISC
  1496. *
  1497. * Returns the price per $100 face value of a discounted security.
  1498. *
  1499. * @param mixed settlement The security's settlement date.
  1500. * The security settlement date is the date after the issue date when the security is traded to the buyer.
  1501. * @param mixed maturity The security's maturity date.
  1502. * The maturity date is the date when the security expires.
  1503. * @param int discount The security's discount rate.
  1504. * @param int redemption The security's redemption value per $100 face value.
  1505. * @param int basis The type of day count to use.
  1506. * 0 or omitted US (NASD) 30/360
  1507. * 1 Actual/actual
  1508. * 2 Actual/360
  1509. * 3 Actual/365
  1510. * 4 European 30/360
  1511. * @return float
  1512. */
  1513. public static function PRICEDISC($settlement, $maturity, $discount, $redemption, $basis=0) {
  1514. $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
  1515. $maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
  1516. $discount = (float) PHPExcel_Calculation_Functions::flattenSingleValue($discount);
  1517. $redemption = (float) PHPExcel_Calculation_Functions::flattenSingleValue($redemption);
  1518. $basis = (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
  1519. // Validate
  1520. if ((is_numeric($discount)) && (is_numeric($redemption)) && (is_numeric($basis))) {
  1521. if (($discount <= 0) || ($redemption <= 0)) {
  1522. return PHPExcel_Calculation_Functions::NaN();
  1523. }
  1524. $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $maturity, $basis);
  1525. if (!is_numeric($daysBetweenSettlementAndMaturity)) {
  1526. // return date error
  1527. return $daysBetweenSettlementAndMaturity;
  1528. }
  1529. return $redemption * (1 - $discount * $daysBetweenSettlementAndMaturity);
  1530. }
  1531. return PHPExcel_Calculation_Functions::VALUE();
  1532. } // function PRICEDISC()
  1533. /**
  1534. * PRICEMAT
  1535. *
  1536. * Returns the price per $100 face value of a security that pays interest at maturity.
  1537. *
  1538. * @param mixed settlement The security's settlement date.
  1539. * The security's settlement date is the date after the issue date when the security is traded to the buyer.
  1540. * @param mixed maturity The security's maturity date.
  1541. * The maturity date is the date when the security expires.
  1542. * @param mixed issue The security's issue date.
  1543. * @param int rate The security's interest rate at date of issue.
  1544. * @param int yield The security's annual yield.
  1545. * @param int basis The type of day count to use.
  1546. * 0 or omitted US (NASD) 30/360
  1547. * 1 Actual/actual
  1548. * 2 Actual/360
  1549. * 3 Actual/365
  1550. * 4 European 30/360
  1551. * @return float
  1552. */
  1553. public static function PRICEMAT($settlement, $maturity, $issue, $rate, $yield, $basis=0) {
  1554. $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
  1555. $maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
  1556. $issue = PHPExcel_Calculation_Functions::flattenSingleValue($issue);
  1557. $rate = PHPExcel_Calculation_Functions::flattenSingleValue($rate);
  1558. $yield = PHPExcel_Calculation_Functions::flattenSingleValue($yield);
  1559. $basis = (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
  1560. // Validate
  1561. if (is_numeric($rate) && is_numeric($yield)) {
  1562. if (($rate <= 0) || ($yield <= 0)) {
  1563. return PHPExcel_Calculation_Functions::NaN();
  1564. }
  1565. $daysPerYear = self::_daysPerYear(PHPExcel_Calculation_DateTime::YEAR($settlement),$basis);
  1566. if (!is_numeric($daysPerYear)) {
  1567. return $daysPerYear;
  1568. }
  1569. $daysBetweenIssueAndSettlement = PHPExcel_Calculation_DateTime::YEARFRAC($issue, $settlement, $basis);
  1570. if (!is_numeric($daysBetweenIssueAndSettlement)) {
  1571. // return date error
  1572. return $daysBetweenIssueAndSettlement;
  1573. }
  1574. $daysBetweenIssueAndSettlement *= $daysPerYear;
  1575. $daysBetweenIssueAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($issue, $maturity, $basis);
  1576. if (!is_numeric($daysBetweenIssueAndMaturity)) {
  1577. // return date error
  1578. return $daysBetweenIssueAndMaturity;
  1579. }
  1580. $daysBetweenIssueAndMaturity *= $daysPerYear;
  1581. $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $maturity, $basis);
  1582. if (!is_numeric($daysBetweenSettlementAndMaturity)) {
  1583. // return date error
  1584. return $daysBetweenSettlementAndMaturity;
  1585. }
  1586. $daysBetweenSettlementAndMaturity *= $daysPerYear;
  1587. return ((100 + (($daysBetweenIssueAndMaturity / $daysPerYear) * $rate * 100)) /
  1588. (1 + (($daysBetweenSettlementAndMaturity / $daysPerYear) * $yield)) -
  1589. (($daysBetweenIssueAndSettlement / $daysPerYear) * $rate * 100));
  1590. }
  1591. return PHPExcel_Calculation_Functions::VALUE();
  1592. } // function PRICEMAT()
  1593. /**
  1594. * PV
  1595. *
  1596. * Returns the Present Value of a cash flow with constant payments and interest rate (annuities).
  1597. *
  1598. * @param float $rate Interest rate per period
  1599. * @param int $nper Number of periods
  1600. * @param float $pmt Periodic payment (annuity)
  1601. * @param float $fv Future Value
  1602. * @param int $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period
  1603. * @return float
  1604. */
  1605. public static function PV($rate = 0, $nper = 0, $pmt = 0, $fv = 0, $type = 0) {
  1606. $rate = PHPExcel_Calculation_Functions::flattenSingleValue($rate);
  1607. $nper = PHPExcel_Calculation_Functions::flattenSingleValue($nper);
  1608. $pmt = PHPExcel_Calculation_Functions::flattenSingleValue($pmt);
  1609. $fv = PHPExcel_Calculation_Functions::flattenSingleValue($fv);
  1610. $type = PHPExcel_Calculation_Functions::flattenSingleValue($type);
  1611. // Validate parameters
  1612. if ($type != 0 && $type != 1) {
  1613. return PHPExcel_Calculation_Functions::NaN();
  1614. }
  1615. // Calculate
  1616. if (!is_null($rate) && $rate != 0) {
  1617. return (-$pmt * (1 + $rate * $type) * ((pow(1 + $rate, $nper) - 1) / $rate) - $fv) / pow(1 + $rate, $nper);
  1618. } else {
  1619. return -$fv - $pmt * $nper;
  1620. }
  1621. } // function PV()
  1622. /**
  1623. * RATE
  1624. *
  1625. * Returns the interest rate per period of an annuity.
  1626. * RATE is calculated by iteration and can have zero or more solutions.
  1627. * If the successive results of RATE do not converge to within 0.0000001 after 20 iterations,
  1628. * RATE returns the #NUM! error value.
  1629. *
  1630. * Excel Function:
  1631. * RATE(nper,pmt,pv[,fv[,type[,guess]]])
  1632. *
  1633. * @access public
  1634. * @category Financial Functions
  1635. * @param float nper The total number of payment periods in an annuity.
  1636. * @param float pmt The payment made each period and cannot change over the life
  1637. * of the annuity.
  1638. * Typically, pmt includes principal and interest but no other
  1639. * fees or taxes.
  1640. * @param float pv The present value - the total amount that a series of future
  1641. * payments is worth now.
  1642. * @param float fv The future value, or a cash balance you want to attain after
  1643. * the last payment is made. If fv is omitted, it is assumed
  1644. * to be 0 (the future value of a loan, for example, is 0).
  1645. * @param integer type A number 0 or 1 and indicates when payments are due:
  1646. * 0 or omitted At the end of the period.
  1647. * 1 At the beginning of the period.
  1648. * @param float guess Your guess for what the rate will be.
  1649. * If you omit guess, it is assumed to be 10 percent.
  1650. * @return float
  1651. **/
  1652. public static function RATE($nper, $pmt, $pv, $fv = 0.0, $type = 0, $guess = 0.1) {
  1653. $nper = (int) PHPExcel_Calculation_Functions::flattenSingleValue($nper);
  1654. $pmt = PHPExcel_Calculation_Functions::flattenSingleValue($pmt);
  1655. $pv = PHPExcel_Calculation_Functions::flattenSingleValue($pv);
  1656. $fv = (is_null($fv)) ? 0.0 : PHPExcel_Calculation_Functions::flattenSingleValue($fv);
  1657. $type = (is_null($type)) ? 0 : (int) PHPExcel_Calculation_Functions::flattenSingleValue($type);
  1658. $guess = (is_null($guess)) ? 0.1 : PHPExcel_Calculation_Functions::flattenSingleValue($guess);
  1659. $rate = $guess;
  1660. if (abs($rate) < FINANCIAL_PRECISION) {
  1661. $y = $pv * (1 + $nper * $rate) + $pmt * (1 + $rate * $type) * $nper + $fv;
  1662. } else {
  1663. $f = exp($nper * log(1 + $rate));
  1664. $y = $pv * $f + $pmt * (1 / $rate + $type) * ($f - 1) + $fv;
  1665. }
  1666. $y0 = $pv + $pmt * $nper + $fv;
  1667. $y1 = $pv * $f + $pmt * (1 / $rate + $type) * ($f - 1) + $fv;
  1668. // find root by secant method
  1669. $i = $x0 = 0.0;
  1670. $x1 = $rate;
  1671. while ((abs($y0 - $y1) > FINANCIAL_PRECISION) && ($i < FINANCIAL_MAX_ITERATIONS)) {
  1672. $rate = ($y1 * $x0 - $y0 * $x1) / ($y1 - $y0);
  1673. $x0 = $x1;
  1674. $x1 = $rate;
  1675. if (($nper * abs($pmt)) > ($pv - $fv))
  1676. $x1 = abs($x1);
  1677. if (abs($rate) < FINANCIAL_PRECISION) {
  1678. $y = $pv * (1 + $nper * $rate) + $pmt * (1 + $rate * $type) * $nper + $fv;
  1679. } else {
  1680. $f = exp($nper * log(1 + $rate));
  1681. $y = $pv * $f + $pmt * (1 / $rate + $type) * ($f - 1) + $fv;
  1682. }
  1683. $y0 = $y1;
  1684. $y1 = $y;
  1685. ++$i;
  1686. }
  1687. return $rate;
  1688. } // function RATE()
  1689. /**
  1690. * RECEIVED
  1691. *
  1692. * Returns the price per $100 face value of a discounted security.
  1693. *
  1694. * @param mixed settlement The security's settlement date.
  1695. * The security settlement date is the date after the issue date when the security is traded to the buyer.
  1696. * @param mixed maturity The security's maturity date.
  1697. * The maturity date is the date when the security expires.
  1698. * @param int investment The amount invested in the security.
  1699. * @param int discount The security's discount rate.
  1700. * @param int basis The type of day count to use.
  1701. * 0 or omitted US (NASD) 30/360
  1702. * 1 Actual/actual
  1703. * 2 Actual/360
  1704. * 3 Actual/365
  1705. * 4 European 30/360
  1706. * @return float
  1707. */
  1708. public static function RECEIVED($settlement, $maturity, $investment, $discount, $basis=0) {
  1709. $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
  1710. $maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
  1711. $investment = (float) PHPExcel_Calculation_Functions::flattenSingleValue($investment);
  1712. $discount = (float) PHPExcel_Calculation_Functions::flattenSingleValue($discount);
  1713. $basis = (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
  1714. // Validate
  1715. if ((is_numeric($investment)) && (is_numeric($discount)) && (is_numeric($basis))) {
  1716. if (($investment <= 0) || ($discount <= 0)) {
  1717. return PHPExcel_Calculation_Functions::NaN();
  1718. }
  1719. $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $maturity, $basis);
  1720. if (!is_numeric($daysBetweenSettlementAndMaturity)) {
  1721. // return date error
  1722. return $daysBetweenSettlementAndMaturity;
  1723. }
  1724. return $investment / ( 1 - ($discount * $daysBetweenSettlementAndMaturity));
  1725. }
  1726. return PHPExcel_Calculation_Functions::VALUE();
  1727. } // function RECEIVED()
  1728. /**
  1729. * SLN
  1730. *
  1731. * Returns the straight-line depreciation of an asset for one period
  1732. *
  1733. * @param cost Initial cost of the asset
  1734. * @param salvage Value at the end of the depreciation
  1735. * @param life Number of periods over which the asset is depreciated
  1736. * @return float
  1737. */
  1738. public static function SLN($cost, $salvage, $life) {
  1739. $cost = PHPExcel_Calculation_Functions::flattenSingleValue($cost);
  1740. $salvage = PHPExcel_Calculation_Functions::flattenSingleValue($salvage);
  1741. $life = PHPExcel_Calculation_Functions::flattenSingleValue($life);
  1742. // Calculate
  1743. if ((is_numeric($cost)) && (is_numeric($salvage)) && (is_numeric($life))) {
  1744. if ($life < 0) {
  1745. return PHPExcel_Calculation_Functions::NaN();
  1746. }
  1747. return ($cost - $salvage) / $life;
  1748. }
  1749. return PHPExcel_Calculation_Functions::VALUE();
  1750. } // function SLN()
  1751. /**
  1752. * SYD
  1753. *
  1754. * Returns the sum-of-years' digits depreciation of an asset for a specified period.
  1755. *
  1756. * @param cost Initial cost of the asset
  1757. * @param salvage Value at the end of the depreciation
  1758. * @param life Number of periods over which the asset is depreciated
  1759. * @param period Period
  1760. * @return float
  1761. */
  1762. public static function SYD($cost, $salvage, $life, $period) {
  1763. $cost = PHPExcel_Calculation_Functions::flattenSingleValue($cost);
  1764. $salvage = PHPExcel_Calculation_Functions::flattenSingleValue($salvage);
  1765. $life = PHPExcel_Calculation_Functions::flattenSingleValue($life);
  1766. $period = PHPExcel_Calculation_Functions::flattenSingleValue($period);
  1767. // Calculate
  1768. if ((is_numeric($cost)) && (is_numeric($salvage)) && (is_numeric($life)) && (is_numeric($period))) {
  1769. if (($life < 1) || ($period > $life)) {
  1770. return PHPExcel_Calculation_Functions::NaN();
  1771. }
  1772. return (($cost - $salvage) * ($life - $period + 1) * 2) / ($life * ($life + 1));
  1773. }
  1774. return PHPExcel_Calculation_Functions::VALUE();
  1775. } // function SYD()
  1776. /**
  1777. * TBILLEQ
  1778. *
  1779. * Returns the bond-equivalent yield for a Treasury bill.
  1780. *
  1781. * @param mixed settlement The Treasury bill's settlement date.
  1782. * The Treasury bill's settlement date is the date after the issue date when the Treasury bill is traded to the buyer.
  1783. * @param mixed maturity The Treasury bill's maturity date.
  1784. * The maturity date is the date when the Treasury bill expires.
  1785. * @param int discount The Treasury bill's discount rate.
  1786. * @return float
  1787. */
  1788. public static function TBILLEQ($settlement, $maturity, $discount) {
  1789. $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
  1790. $maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
  1791. $discount = PHPExcel_Calculation_Functions::flattenSingleValue($discount);
  1792. // Use TBILLPRICE for validation
  1793. $testValue = self::TBILLPRICE($settlement, $maturity, $discount);
  1794. if (is_string($testValue)) {
  1795. return $testValue;
  1796. }
  1797. if (is_string($maturity = PHPExcel_Calculation_DateTime::_getDateValue($maturity))) {
  1798. return PHPExcel_Calculation_Functions::VALUE();
  1799. }
  1800. if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE) {
  1801. ++$maturity;
  1802. $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $maturity) * 360;
  1803. } else {
  1804. $daysBetweenSettlementAndMaturity = (PHPExcel_Calculation_DateTime::_getDateValue($maturity) - PHPExcel_Calculation_DateTime::_getDateValue($settlement));
  1805. }
  1806. return (365 * $discount) / (360 - $discount * $daysBetweenSettlementAndMaturity);
  1807. } // function TBILLEQ()
  1808. /**
  1809. * TBILLPRICE
  1810. *
  1811. * Returns the yield for a Treasury bill.
  1812. *
  1813. * @param mixed settlement The Treasury bill's settlement date.
  1814. * The Treasury bill's settlement date is the date after the issue date when the Treasury bill is traded to the buyer.
  1815. * @param mixed maturity The Treasury bill's maturity date.
  1816. * The maturity date is the date when the Treasury bill expires.
  1817. * @param int discount The Treasury bill's discount rate.
  1818. * @return float
  1819. */
  1820. public static function TBILLPRICE($settlement, $maturity, $discount) {
  1821. $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
  1822. $maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
  1823. $discount = PHPExcel_Calculation_Functions::flattenSingleValue($discount);
  1824. if (is_string($maturity = PHPExcel_Calculation_DateTime::_getDateValue($maturity))) {
  1825. return PHPExcel_Calculation_Functions::VALUE();
  1826. }
  1827. // Validate
  1828. if (is_numeric($discount)) {
  1829. if ($discount <= 0) {
  1830. return PHPExcel_Calculation_Functions::NaN();
  1831. }
  1832. if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE) {
  1833. ++$maturity;
  1834. $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $maturity) * 360;
  1835. if (!is_numeric($daysBetweenSettlementAndMaturity)) {
  1836. // return date error
  1837. return $daysBetweenSettlementAndMaturity;
  1838. }
  1839. } else {
  1840. $daysBetweenSettlementAndMaturity = (PHPExcel_Calculation_DateTime::_getDateValue($maturity) - PHPExcel_Calculation_DateTime::_getDateValue($settlement));
  1841. }
  1842. if ($daysBetweenSettlementAndMaturity > 360) {
  1843. return PHPExcel_Calculation_Functions::NaN();
  1844. }
  1845. $price = 100 * (1 - (($discount * $daysBetweenSettlementAndMaturity) / 360));
  1846. if ($price <= 0) {
  1847. return PHPExcel_Calculation_Functions::NaN();
  1848. }
  1849. return $price;
  1850. }
  1851. return PHPExcel_Calculation_Functions::VALUE();
  1852. } // function TBILLPRICE()
  1853. /**
  1854. * TBILLYIELD
  1855. *
  1856. * Returns the yield for a Treasury bill.
  1857. *
  1858. * @param mixed settlement The Treasury bill's settlement date.
  1859. * The Treasury bill's settlement date is the date after the issue date when the Treasury bill is traded to the buyer.
  1860. * @param mixed maturity The Treasury bill's maturity date.
  1861. * The maturity date is the date when the Treasury bill expires.
  1862. * @param int price The Treasury bill's price per $100 face value.
  1863. * @return float
  1864. */
  1865. public static function TBILLYIELD($settlement, $maturity, $price) {
  1866. $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
  1867. $maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
  1868. $price = PHPExcel_Calculation_Functions::flattenSingleValue($price);
  1869. // Validate
  1870. if (is_numeric($price)) {
  1871. if ($price <= 0) {
  1872. return PHPExcel_Calculation_Functions::NaN();
  1873. }
  1874. if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE) {
  1875. ++$maturity;
  1876. $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $maturity) * 360;
  1877. if (!is_numeric($daysBetweenSettlementAndMaturity)) {
  1878. // return date error
  1879. return $daysBetweenSettlementAndMaturity;
  1880. }
  1881. } else {
  1882. $daysBetweenSettlementAndMaturity = (PHPExcel_Calculation_DateTime::_getDateValue($maturity) - PHPExcel_Calculation_DateTime::_getDateValue($settlement));
  1883. }
  1884. if ($daysBetweenSettlementAndMaturity > 360) {
  1885. return PHPExcel_Calculation_Functions::NaN();
  1886. }
  1887. return ((100 - $price) / $price) * (360 / $daysBetweenSettlementAndMaturity);
  1888. }
  1889. return PHPExcel_Calculation_Functions::VALUE();
  1890. } // function TBILLYIELD()
  1891. public static function XIRR($values, $dates, $guess = 0.1) {
  1892. if ((!is_array($values)) && (!is_array($dates))) return PHPExcel_Calculation_Functions::VALUE();
  1893. $values = PHPExcel_Calculation_Functions::flattenArray($values);
  1894. $dates = PHPExcel_Calculation_Functions::flattenArray($dates);
  1895. $guess = PHPExcel_Calculation_Functions::flattenSingleValue($guess);
  1896. if (count($values) != count($dates)) return PHPExcel_Calculation_Functions::NaN();
  1897. // create an initial range, with a root somewhere between 0 and guess
  1898. $x1 = 0.0;
  1899. $x2 = $guess;
  1900. $f1 = self::XNPV($x1, $values, $dates);
  1901. $f2 = self::XNPV($x2, $values, $dates);
  1902. for ($i = 0; $i < FINANCIAL_MAX_ITERATIONS; ++$i) {
  1903. if (($f1 * $f2) < 0.0) break;
  1904. if (abs($f1) < abs($f2)) {
  1905. $f1 = self::XNPV($x1 += 1.6 * ($x1 - $x2), $values, $dates);
  1906. } else {
  1907. $f2 = self::XNPV($x2 += 1.6 * ($x2 - $x1), $values, $dates);
  1908. }
  1909. }
  1910. if (($f1 * $f2) > 0.0) return PHPExcel_Calculation_Functions::VALUE();
  1911. $f = self::XNPV($x1, $values, $dates);
  1912. if ($f < 0.0) {
  1913. $rtb = $x1;
  1914. $dx = $x2 - $x1;
  1915. } else {
  1916. $rtb = $x2;
  1917. $dx = $x1 - $x2;
  1918. }
  1919. for ($i = 0; $i < FINANCIAL_MAX_ITERATIONS; ++$i) {
  1920. $dx *= 0.5;
  1921. $x_mid = $rtb + $dx;
  1922. $f_mid = self::XNPV($x_mid, $values, $dates);
  1923. if ($f_mid <= 0.0) $rtb = $x_mid;
  1924. if ((abs($f_mid) < FINANCIAL_PRECISION) || (abs($dx) < FINANCIAL_PRECISION)) return $x_mid;
  1925. }
  1926. return PHPExcel_Calculation_Functions::VALUE();
  1927. }
  1928. /**
  1929. * XNPV
  1930. *
  1931. * Returns the net present value for a schedule of cash flows that is not necessarily periodic.
  1932. * To calculate the net present value for a series of cash flows that is periodic, use the NPV function.
  1933. *
  1934. * Excel Function:
  1935. * =XNPV(rate,values,dates)
  1936. *
  1937. * @param float $rate The discount rate to apply to the cash flows.
  1938. * @param array of float $values A series of cash flows that corresponds to a schedule of payments in dates. The first payment is optional and corresponds to a cost or payment that occurs at the beginning of the investment. If the first value is a cost or payment, it must be a negative value. All succeeding payments are discounted based on a 365-day year. The series of values must contain at least one positive value and one negative value.
  1939. * @param array of mixed $dates A schedule of payment dates that corresponds to the cash flow payments. The first payment date indicates the beginning of the schedule of payments. All other dates must be later than this date, but they may occur in any order.
  1940. * @return float
  1941. */
  1942. public static function XNPV($rate, $values, $dates) {
  1943. $rate = PHPExcel_Calculation_Functions::flattenSingleValue($rate);
  1944. if (!is_numeric($rate)) return PHPExcel_Calculation_Functions::VALUE();
  1945. if ((!is_array($values)) || (!is_array($dates))) return PHPExcel_Calculation_Functions::VALUE();
  1946. $values = PHPExcel_Calculation_Functions::flattenArray($values);
  1947. $dates = PHPExcel_Calculation_Functions::flattenArray($dates);
  1948. $valCount = count($values);
  1949. if ($valCount != count($dates)) return PHPExcel_Calculation_Functions::NaN();
  1950. if ((min($values) > 0) || (max($values) < 0)) return PHPExcel_Calculation_Functions::VALUE();
  1951. $xnpv = 0.0;
  1952. for ($i = 0; $i < $valCount; ++$i) {
  1953. if (!is_numeric($values[$i])) return PHPExcel_Calculation_Functions::VALUE();
  1954. $xnpv += $values[$i] / pow(1 + $rate, PHPExcel_Calculation_DateTime::DATEDIF($dates[0],$dates[$i],'d') / 365);
  1955. }
  1956. return (is_finite($xnpv)) ? $xnpv : PHPExcel_Calculation_Functions::VALUE();
  1957. } // function XNPV()
  1958. /**
  1959. * YIELDDISC
  1960. *
  1961. * Returns the annual yield of a security that pays interest at maturity.
  1962. *
  1963. * @param mixed settlement The security's settlement date.
  1964. * The security's settlement date is the date after the issue date when the security is traded to the buyer.
  1965. * @param mixed maturity The security's maturity date.
  1966. * The maturity date is the date when the security expires.
  1967. * @param int price The security's price per $100 face value.
  1968. * @param int redemption The security's redemption value per $100 face value.
  1969. * @param int basis The type of day count to use.
  1970. * 0 or omitted US (NASD) 30/360
  1971. * 1 Actual/actual
  1972. * 2 Actual/360
  1973. * 3 Actual/365
  1974. * 4 European 30/360
  1975. * @return float
  1976. */
  1977. public static function YIELDDISC($settlement, $maturity, $price, $redemption, $basis=0) {
  1978. $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
  1979. $maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
  1980. $price = PHPExcel_Calculation_Functions::flattenSingleValue($price);
  1981. $redemption = PHPExcel_Calculation_Functions::flattenSingleValue($redemption);
  1982. $basis = (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
  1983. // Validate
  1984. if (is_numeric($price) && is_numeric($redemption)) {
  1985. if (($price <= 0) || ($redemption <= 0)) {
  1986. return PHPExcel_Calculation_Functions::NaN();
  1987. }
  1988. $daysPerYear = self::_daysPerYear(PHPExcel_Calculation_DateTime::YEAR($settlement),$basis);
  1989. if (!is_numeric($daysPerYear)) {
  1990. return $daysPerYear;
  1991. }
  1992. $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $maturity,$basis);
  1993. if (!is_numeric($daysBetweenSettlementAndMaturity)) {
  1994. // return date error
  1995. return $daysBetweenSettlementAndMaturity;
  1996. }
  1997. $daysBetweenSettlementAndMaturity *= $daysPerYear;
  1998. return (($redemption - $price) / $price) * ($daysPerYear / $daysBetweenSettlementAndMaturity);
  1999. }
  2000. return PHPExcel_Calculation_Functions::VALUE();
  2001. } // function YIELDDISC()
  2002. /**
  2003. * YIELDMAT
  2004. *
  2005. * Returns the annual yield of a security that pays interest at maturity.
  2006. *
  2007. * @param mixed settlement The security's settlement date.
  2008. * The security's settlement date is the date after the issue date when the security is traded to the buyer.
  2009. * @param mixed maturity The security's maturity date.
  2010. * The maturity date is the date when the security expires.
  2011. * @param mixed issue The security's issue date.
  2012. * @param int rate The security's interest rate at date of issue.
  2013. * @param int price The security's price per $100 face value.
  2014. * @param int basis The type of day count to use.
  2015. * 0 or omitted US (NASD) 30/360
  2016. * 1 Actual/actual
  2017. * 2 Actual/360
  2018. * 3 Actual/365
  2019. * 4 European 30/360
  2020. * @return float
  2021. */
  2022. public static function YIELDMAT($settlement, $maturity, $issue, $rate, $price, $basis=0) {
  2023. $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement);
  2024. $maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity);
  2025. $issue = PHPExcel_Calculation_Functions::flattenSingleValue($issue);
  2026. $rate = PHPExcel_Calculation_Functions::flattenSingleValue($rate);
  2027. $price = PHPExcel_Calculation_Functions::flattenSingleValue($price);
  2028. $basis = (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis);
  2029. // Validate
  2030. if (is_numeric($rate) && is_numeric($price)) {
  2031. if (($rate <= 0) || ($price <= 0)) {
  2032. return PHPExcel_Calculation_Functions::NaN();
  2033. }
  2034. $daysPerYear = self::_daysPerYear(PHPExcel_Calculation_DateTime::YEAR($settlement),$basis);
  2035. if (!is_numeric($daysPerYear)) {
  2036. return $daysPerYear;
  2037. }
  2038. $daysBetweenIssueAndSettlement = PHPExcel_Calculation_DateTime::YEARFRAC($issue, $settlement, $basis);
  2039. if (!is_numeric($daysBetweenIssueAndSettlement)) {
  2040. // return date error
  2041. return $daysBetweenIssueAndSettlement;
  2042. }
  2043. $daysBetweenIssueAndSettlement *= $daysPerYear;
  2044. $daysBetweenIssueAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($issue, $maturity, $basis);
  2045. if (!is_numeric($daysBetweenIssueAndMaturity)) {
  2046. // return date error
  2047. return $daysBetweenIssueAndMaturity;
  2048. }
  2049. $daysBetweenIssueAndMaturity *= $daysPerYear;
  2050. $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $maturity, $basis);
  2051. if (!is_numeric($daysBetweenSettlementAndMaturity)) {
  2052. // return date error
  2053. return $daysBetweenSettlementAndMaturity;
  2054. }
  2055. $daysBetweenSettlementAndMaturity *= $daysPerYear;
  2056. return ((1 + (($daysBetweenIssueAndMaturity / $daysPerYear) * $rate) - (($price / 100) + (($daysBetweenIssueAndSettlement / $daysPerYear) * $rate))) /
  2057. (($price / 100) + (($daysBetweenIssueAndSettlement / $daysPerYear) * $rate))) *
  2058. ($daysPerYear / $daysBetweenSettlementAndMaturity);
  2059. }
  2060. return PHPExcel_Calculation_Functions::VALUE();
  2061. } // function YIELDMAT()
  2062. } // class PHPExcel_Calculation_Financial