File Source for Financial.php

PHPExcel_Calculation

# Source for file Financial.php

Documentation is available at Financial.php

1. <?php
2. /**
3. * PHPExcel
4. *
5. * Copyright (c) 2006 - 2011 PHPExcel
6. *
7. * This library is free software; you can redistribute it and/or
8. * modify it under the terms of the GNU Lesser General Public
9. * License as published by the Free Software Foundation; either
10. * version 2.1 of the License, or (at your option) any later version.
11. *
12. * This library is distributed in the hope that it will be useful,
13. * but WITHOUT ANY WARRANTY; without even the implied warranty of
14. * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
15. * Lesser General Public License for more details.
16. *
17. * You should have received a copy of the GNU Lesser General Public
18. * License along with this library; if not, write to the Free Software
19. * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
20. *
21. * @category PHPExcel
22. * @package PHPExcel_Calculation
23. * @copyright Copyright (c) 2006 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
24. * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
25. * @version 1.7.6, 2011-02-27
26. */
29. /** PHPExcel root directory */
30. if (! defined('PHPEXCEL_ROOT')) {
31. /**
32. * @ignore
33. */
34. define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
35. require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
36. }
39. /** FINANCIAL_MAX_ITERATIONS */
40. define('FINANCIAL_MAX_ITERATIONS', 128);
42. /** FINANCIAL_PRECISION */
43. define('FINANCIAL_PRECISION', 1.0e-08);
46. /**
47. * PHPExcel_Calculation_Financial
48. *
49. * @category PHPExcel
50. * @package PHPExcel_Calculation
51. * @copyright Copyright (c) 2006 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
52. */
53. class PHPExcel_Calculation_Financial {
55. private static function _lastDayOfMonth($testDate) {
56. $date = clone $testDate;
57. $date->modify('+1 day');
58. return ($date->format('d') == 1);
59. } // function _lastDayOfMonth()
62. private static function _firstDayOfMonth($testDate) {
63. $date = clone $testDate;
64. return ($date->format('d') == 1);
65. } // function _lastDayOfMonth()
68. private static function _coupFirstPeriodDate($settlement, $maturity, $frequency, $next) {
69. $months = 12 / $frequency;
71. $result = PHPExcel_Shared_Date:: ExcelToPHPObject($maturity);
72. $eom = self::_lastDayOfMonth($result);
74. while ($settlement < PHPExcel_Shared_Date:: PHPToExcel($result)) {
75. $result->modify('-'.$months.' months');
76. }
77. if ($next) {
78. $result->modify('+'.$months.' months');
79. }
81. if ($eom) {
82. $result->modify('-1 day');
83. }
85. return PHPExcel_Shared_Date:: PHPToExcel($result);
86. } // function _coupFirstPeriodDate()
89. private static function _validFrequency($frequency) {
90. if (($frequency == 1) || ($frequency == 2) || ($frequency == 4)) {
91. return true;
92. }
93. if (( PHPExcel_Calculation_Functions:: getCompatibilityMode() == PHPExcel_Calculation_Functions:: COMPATIBILITY_GNUMERIC) &&
94. (($frequency == 6) || ($frequency == 12))) {
95. return true;
96. }
97. return false;
98. } // function _validFrequency()
101. private static function _daysPerYear($year,$basis) {
102. switch ($basis) {
103. case 0 :
104. case 2 :
105. case 4 :
106. $daysPerYear = 360;
107. break;
108. case 3 :
109. $daysPerYear = 365;
110. break;
111. case 1 :
112. if ( PHPExcel_Calculation_DateTime::_isLeapYear($year)) {
113. $daysPerYear = 366;
114. } else {
115. $daysPerYear = 365;
116. }
117. break;
118. default :
119. return PHPExcel_Calculation_Functions:: NaN();
120. }
121. return $daysPerYear;
122. } // function _daysPerYear()
125. private static function _interestAndPrincipal($rate=0, $per=0, $nper=0, $pv=0, $fv=0, $type=0) {
126. $pmt = self:: PMT($rate, $nper, $pv, $fv, $type);
127. $capital = $pv;
128. for ($i = 1; $i<= $per; ++$i) {
129. $interest = ($type && $i == 1) ? 0 : -$capital * $rate;
130. $principal = $pmt - $interest;
131. $capital += $principal;
132. }
133. return array($interest, $principal);
134. } // function _interestAndPrincipal()
137. /**
138. * ACCRINT
139. *
140. * Returns the discount rate for a security.
141. *
142. * @param mixed issue The security's issue date.
143. * @param mixed firstinter The security's first interest date.
144. * @param mixed settlement The security's settlement date.
145. * @param float rate The security's annual coupon rate.
146. * @param float par The security's par value.
147. * @param int basis The type of day count to use.
148. * 0 or omitted US (NASD) 30/360
149. * 1 Actual/actual
150. * 2 Actual/360
151. * 3 Actual/365
152. * 4 European 30/360
153. * @return float
154. */
155. public static function ACCRINT($issue, $firstinter, $settlement, $rate, $par=1000, $frequency=1, $basis=0) {
156. $issue = PHPExcel_Calculation_Functions:: flattenSingleValue($issue);
157. $firstinter = PHPExcel_Calculation_Functions:: flattenSingleValue($firstinter);
158. $settlement = PHPExcel_Calculation_Functions:: flattenSingleValue($settlement);
159. $rate = (float) PHPExcel_Calculation_Functions:: flattenSingleValue($rate);
160. $par = ( is_null($par)) ? 1000 : (float) PHPExcel_Calculation_Functions:: flattenSingleValue($par);
161. $frequency = ( is_null($frequency)) ? 1 : (int) PHPExcel_Calculation_Functions:: flattenSingleValue($frequency);
162. $basis = ( is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions:: flattenSingleValue($basis);
164. // Validate
165. if (( is_numeric($rate)) && ( is_numeric($par))) {
166. if (($rate <= 0) || ($par <= 0)) {
167. return PHPExcel_Calculation_Functions:: NaN();
168. }
169. $daysBetweenIssueAndSettlement = PHPExcel_Calculation_DateTime:: YEARFRAC($issue, $settlement, $basis);
170. if (! is_numeric($daysBetweenIssueAndSettlement)) {
171. // return date error
172. return $daysBetweenIssueAndSettlement;
173. }
175. return $par * $rate * $daysBetweenIssueAndSettlement;
176. }
177. return PHPExcel_Calculation_Functions:: VALUE();
178. } // function ACCRINT()
181. /**
182. * ACCRINTM
183. *
184. * Returns the discount rate for a security.
185. *
186. * @param mixed issue The security's issue date.
187. * @param mixed settlement The security's settlement date.
188. * @param float rate The security's annual coupon rate.
189. * @param float par The security's par value.
190. * @param int basis The type of day count to use.
191. * 0 or omitted US (NASD) 30/360
192. * 1 Actual/actual
193. * 2 Actual/360
194. * 3 Actual/365
195. * 4 European 30/360
196. * @return float
197. */
198. public static function ACCRINTM($issue, $settlement, $rate, $par=1000, $basis=0) {
199. $issue = PHPExcel_Calculation_Functions:: flattenSingleValue($issue);
200. $settlement = PHPExcel_Calculation_Functions:: flattenSingleValue($settlement);
201. $rate = (float) PHPExcel_Calculation_Functions:: flattenSingleValue($rate);
202. $par = ( is_null($par)) ? 1000 : (float) PHPExcel_Calculation_Functions:: flattenSingleValue($par);
203. $basis = ( is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions:: flattenSingleValue($basis);
205. // Validate
206. if (( is_numeric($rate)) && ( is_numeric($par))) {
207. if (($rate <= 0) || ($par <= 0)) {
208. return PHPExcel_Calculation_Functions:: NaN();
209. }
210. $daysBetweenIssueAndSettlement = PHPExcel_Calculation_DateTime:: YEARFRAC($issue, $settlement, $basis);
211. if (! is_numeric($daysBetweenIssueAndSettlement)) {
212. // return date error
213. return $daysBetweenIssueAndSettlement;
214. }
215. return $par * $rate * $daysBetweenIssueAndSettlement;
216. }
217. return PHPExcel_Calculation_Functions:: VALUE();
218. } // function ACCRINTM()
221. public static function AMORDEGRC($cost, $purchased, $firstPeriod, $salvage, $period, $rate, $basis=0) {
222. $cost = PHPExcel_Calculation_Functions:: flattenSingleValue($cost);
223. $purchased = PHPExcel_Calculation_Functions:: flattenSingleValue($purchased);
224. $firstPeriod = PHPExcel_Calculation_Functions:: flattenSingleValue($firstPeriod);
225. $salvage = PHPExcel_Calculation_Functions:: flattenSingleValue($salvage);
226. $period = floor( PHPExcel_Calculation_Functions:: flattenSingleValue($period));
227. $rate = PHPExcel_Calculation_Functions:: flattenSingleValue($rate);
228. $basis = ( is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions:: flattenSingleValue($basis);
230. $fUsePer = 1.0 / $rate;
232. if ($fUsePer < 3.0) {
233. $amortiseCoeff = 1.0;
234. } elseif ($fUsePer < 5.0) {
235. $amortiseCoeff = 1.5;
236. } elseif ($fUsePer <= 6.0) {
237. $amortiseCoeff = 2.0;
238. } else {
239. $amortiseCoeff = 2.5;
240. }
242. $rate *= $amortiseCoeff;
243. $fNRate = round( PHPExcel_Calculation_DateTime:: YEARFRAC($purchased, $firstPeriod, $basis) * $rate * $cost,0);
244. $cost -= $fNRate;
245. $fRest = $cost - $salvage;
247. for ($n = 0; $n < $period; ++$n) {
248. $fNRate = round($rate * $cost,0);
249. $fRest -= $fNRate;
251. if ($fRest < 0.0) {
252. switch ($period - $n) {
253. case 0 :
254. case 1 : return round($cost * 0.5,0);
255. break;
256. default : return 0.0;
257. break;
258. }
259. }
260. $cost -= $fNRate;
261. }
262. return $fNRate;
263. } // function AMORDEGRC()
266. public static function AMORLINC($cost, $purchased, $firstPeriod, $salvage, $period, $rate, $basis=0) {
267. $cost = PHPExcel_Calculation_Functions:: flattenSingleValue($cost);
268. $purchased = PHPExcel_Calculation_Functions:: flattenSingleValue($purchased);
269. $firstPeriod = PHPExcel_Calculation_Functions:: flattenSingleValue($firstPeriod);
270. $salvage = PHPExcel_Calculation_Functions:: flattenSingleValue($salvage);
271. $period = PHPExcel_Calculation_Functions:: flattenSingleValue($period);
272. $rate = PHPExcel_Calculation_Functions:: flattenSingleValue($rate);
273. $basis = ( is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions:: flattenSingleValue($basis);
275. $fOneRate = $cost * $rate;
276. $fCostDelta = $cost - $salvage;
277. // Note, quirky variation for leap years on the YEARFRAC for this function
278. $purchasedYear = PHPExcel_Calculation_DateTime:: YEAR($purchased);
279. $yearFrac = PHPExcel_Calculation_DateTime:: YEARFRAC($purchased, $firstPeriod, $basis);
281. if (($basis == 1) && ($yearFrac < 1) && ( PHPExcel_Calculation_DateTime::_isLeapYear($purchasedYear))) {
282. $yearFrac *= 365 / 366;
283. }
285. $f0Rate = $yearFrac * $rate * $cost;
286. $nNumOfFullPeriods = intval(($cost - $salvage - $f0Rate) / $fOneRate);
288. if ($period == 0) {
289. return $f0Rate;
290. } elseif ($period <= $nNumOfFullPeriods) {
291. return $fOneRate;
292. } elseif ($period == ($nNumOfFullPeriods + 1)) {
293. return ($fCostDelta - $fOneRate * $nNumOfFullPeriods - $f0Rate);
294. } else {
295. return 0.0;
296. }
297. } // function AMORLINC()
300. public static function COUPDAYBS($settlement, $maturity, $frequency, $basis=0) {
301. $settlement = PHPExcel_Calculation_Functions:: flattenSingleValue($settlement);
302. $maturity = PHPExcel_Calculation_Functions:: flattenSingleValue($maturity);
303. $frequency = (int) PHPExcel_Calculation_Functions:: flattenSingleValue($frequency);
304. $basis = ( is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions:: flattenSingleValue($basis);
306. if ( is_string($settlement = PHPExcel_Calculation_DateTime::_getDateValue($settlement))) {
307. return PHPExcel_Calculation_Functions:: VALUE();
308. }
309. if ( is_string($maturity = PHPExcel_Calculation_DateTime::_getDateValue($maturity))) {
310. return PHPExcel_Calculation_Functions:: VALUE();
311. }
313. if (($settlement > $maturity) ||
314. (! self::_validFrequency($frequency)) ||
315. (($basis < 0) || ($basis > 4))) {
316. return PHPExcel_Calculation_Functions:: NaN();
317. }
319. $daysPerYear = self::_daysPerYear( PHPExcel_Calculation_DateTime:: YEAR($settlement),$basis);
320. $prev = self::_coupFirstPeriodDate($settlement, $maturity, $frequency, False);
322. return PHPExcel_Calculation_DateTime:: YEARFRAC($prev, $settlement, $basis) * $daysPerYear;
323. } // function COUPDAYBS()
326. public static function COUPDAYS($settlement, $maturity, $frequency, $basis=0) {
327. $settlement = PHPExcel_Calculation_Functions:: flattenSingleValue($settlement);
328. $maturity = PHPExcel_Calculation_Functions:: flattenSingleValue($maturity);
329. $frequency = (int) PHPExcel_Calculation_Functions:: flattenSingleValue($frequency);
330. $basis = ( is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions:: flattenSingleValue($basis);
332. if ( is_string($settlement = PHPExcel_Calculation_DateTime::_getDateValue($settlement))) {
333. return PHPExcel_Calculation_Functions:: VALUE();
334. }
335. if ( is_string($maturity = PHPExcel_Calculation_DateTime::_getDateValue($maturity))) {
336. return PHPExcel_Calculation_Functions:: VALUE();
337. }
339. if (($settlement > $maturity) ||
340. (! self::_validFrequency($frequency)) ||
341. (($basis < 0) || ($basis > 4))) {
342. return PHPExcel_Calculation_Functions:: NaN();
343. }
345. switch ($basis) {
346. case 3: // Actual/365
347. return 365 / $frequency;
348. case 1: // Actual/actual
349. if ($frequency == 1) {
350. $daysPerYear = self::_daysPerYear( PHPExcel_Calculation_DateTime:: YEAR($maturity),$basis);
351. return ($daysPerYear / $frequency);
352. } else {
353. $prev = self::_coupFirstPeriodDate($settlement, $maturity, $frequency, False);
354. $next = self::_coupFirstPeriodDate($settlement, $maturity, $frequency, True);
355. return ($next - $prev);
356. }
357. default: // US (NASD) 30/360, Actual/360 or European 30/360
358. return 360 / $frequency;
359. }
360. return PHPExcel_Calculation_Functions:: VALUE();
361. } // function COUPDAYS()
364. public static function COUPDAYSNC($settlement, $maturity, $frequency, $basis=0) {
365. $settlement = PHPExcel_Calculation_Functions:: flattenSingleValue($settlement);
366. $maturity = PHPExcel_Calculation_Functions:: flattenSingleValue($maturity);
367. $frequency = (int) PHPExcel_Calculation_Functions:: flattenSingleValue($frequency);
368. $basis = ( is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions:: flattenSingleValue($basis);
370. if ( is_string($settlement = PHPExcel_Calculation_DateTime::_getDateValue($settlement))) {
371. return PHPExcel_Calculation_Functions:: VALUE();
372. }
373. if ( is_string($maturity = PHPExcel_Calculation_DateTime::_getDateValue($maturity))) {
374. return PHPExcel_Calculation_Functions:: VALUE();
375. }
377. if (($settlement > $maturity) ||
378. (! self::_validFrequency($frequency)) ||
379. (($basis < 0) || ($basis > 4))) {
380. return PHPExcel_Calculation_Functions:: NaN();
381. }
383. $daysPerYear = self::_daysPerYear( PHPExcel_Calculation_DateTime:: YEAR($settlement),$basis);
384. $next = self::_coupFirstPeriodDate($settlement, $maturity, $frequency, True);
386. return PHPExcel_Calculation_DateTime:: YEARFRAC($settlement, $next, $basis) * $daysPerYear;
387. } // function COUPDAYSNC()
390. public static function COUPNCD($settlement, $maturity, $frequency, $basis=0) {
391. $settlement = PHPExcel_Calculation_Functions:: flattenSingleValue($settlement);
392. $maturity = PHPExcel_Calculation_Functions:: flattenSingleValue($maturity);
393. $frequency = (int) PHPExcel_Calculation_Functions:: flattenSingleValue($frequency);
394. $basis = ( is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions:: flattenSingleValue($basis);
396. if ( is_string($settlement = PHPExcel_Calculation_DateTime::_getDateValue($settlement))) {
397. return PHPExcel_Calculation_Functions:: VALUE();
398. }
399. if ( is_string($maturity = PHPExcel_Calculation_DateTime::_getDateValue($maturity))) {
400. return PHPExcel_Calculation_Functions:: VALUE();
401. }
403. if (($settlement > $maturity) ||
404. (! self::_validFrequency($frequency)) ||
405. (($basis < 0) || ($basis > 4))) {
406. return PHPExcel_Calculation_Functions:: NaN();
407. }
409. return self::_coupFirstPeriodDate($settlement, $maturity, $frequency, True);
410. } // function COUPNCD()
413. public static function COUPNUM($settlement, $maturity, $frequency, $basis=0) {
414. $settlement = PHPExcel_Calculation_Functions:: flattenSingleValue($settlement);
415. $maturity = PHPExcel_Calculation_Functions:: flattenSingleValue($maturity);
416. $frequency = (int) PHPExcel_Calculation_Functions:: flattenSingleValue($frequency);
417. $basis = ( is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions:: flattenSingleValue($basis);
419. if ( is_string($settlement = PHPExcel_Calculation_DateTime::_getDateValue($settlement))) {
420. return PHPExcel_Calculation_Functions:: VALUE();
421. }
422. if ( is_string($maturity = PHPExcel_Calculation_DateTime::_getDateValue($maturity))) {
423. return PHPExcel_Calculation_Functions:: VALUE();
424. }
426. if (($settlement > $maturity) ||
427. (! self::_validFrequency($frequency)) ||
428. (($basis < 0) || ($basis > 4))) {
429. return PHPExcel_Calculation_Functions:: NaN();
430. }
432. $settlement = self::_coupFirstPeriodDate($settlement, $maturity, $frequency, True);
433. $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime:: YEARFRAC($settlement, $maturity, $basis) * 365;
435. switch ($frequency) {
436. case 1: // annual payments
437. return ceil($daysBetweenSettlementAndMaturity / 360);
438. case 2: // half-yearly
439. return ceil($daysBetweenSettlementAndMaturity / 180);
440. case 4: // quarterly
441. return ceil($daysBetweenSettlementAndMaturity / 90);
442. case 6: // bimonthly
443. return ceil($daysBetweenSettlementAndMaturity / 60);
444. case 12: // monthly
445. return ceil($daysBetweenSettlementAndMaturity / 30);
446. }
447. return PHPExcel_Calculation_Functions:: VALUE();
448. } // function COUPNUM()
451. public static function COUPPCD($settlement, $maturity, $frequency, $basis=0) {
452. $settlement = PHPExcel_Calculation_Functions:: flattenSingleValue($settlement);
453. $maturity = PHPExcel_Calculation_Functions:: flattenSingleValue($maturity);
454. $frequency = (int) PHPExcel_Calculation_Functions:: flattenSingleValue($frequency);
455. $basis = ( is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions:: flattenSingleValue($basis);
457. if ( is_string($settlement = PHPExcel_Calculation_DateTime::_getDateValue($settlement))) {
458. return PHPExcel_Calculation_Functions:: VALUE();
459. }
460. if ( is_string($maturity = PHPExcel_Calculation_DateTime::_getDateValue($maturity))) {
461. return PHPExcel_Calculation_Functions:: VALUE();
462. }
464. if (($settlement > $maturity) ||
465. (! self::_validFrequency($frequency)) ||
466. (($basis < 0) || ($basis > 4))) {
467. return PHPExcel_Calculation_Functions:: NaN();
468. }
470. return self::_coupFirstPeriodDate($settlement, $maturity, $frequency, False);
471. } // function COUPPCD()
474. /**
475. * CUMIPMT
476. *
477. * Returns the cumulative interest paid on a loan between start_period and end_period.
478. *
479. * @param float $rate Interest rate per period
480. * @param int $nper Number of periods
481. * @param float $pv Present Value
482. * @param int start The first period in the calculation.
483. * Payment periods are numbered beginning with 1.
484. * @param int end The last period in the calculation.
485. * @param int $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period
486. * @return float
487. */
488. public static function CUMIPMT($rate, $nper, $pv, $start, $end, $type = 0) {
489. $rate = PHPExcel_Calculation_Functions:: flattenSingleValue($rate);
490. $nper = (int) PHPExcel_Calculation_Functions:: flattenSingleValue($nper);
491. $pv = PHPExcel_Calculation_Functions:: flattenSingleValue($pv);
492. $start = (int) PHPExcel_Calculation_Functions:: flattenSingleValue($start);
493. $end = (int) PHPExcel_Calculation_Functions:: flattenSingleValue($end);
494. $type = (int) PHPExcel_Calculation_Functions:: flattenSingleValue($type);
496. // Validate parameters
497. if ($type != 0 && $type != 1) {
498. return PHPExcel_Calculation_Functions:: NaN();
499. }
500. if ($start < 1 || $start > $end) {
501. return PHPExcel_Calculation_Functions:: VALUE();
502. }
504. // Calculate
505. $interest = 0;
506. for ($per = $start; $per <= $end; ++$per) {
507. $interest += self:: IPMT($rate, $per, $nper, $pv, 0, $type);
508. }
510. return $interest;
511. } // function CUMIPMT()
514. /**
515. * CUMPRINC
516. *
517. * Returns the cumulative principal paid on a loan between start_period and end_period.
518. *
519. * @param float $rate Interest rate per period
520. * @param int $nper Number of periods
521. * @param float $pv Present Value
522. * @param int start The first period in the calculation.
523. * Payment periods are numbered beginning with 1.
524. * @param int end The last period in the calculation.
525. * @param int $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period
526. * @return float
527. */
528. public static function CUMPRINC($rate, $nper, $pv, $start, $end, $type = 0) {
529. $rate = PHPExcel_Calculation_Functions:: flattenSingleValue($rate);
530. $nper = (int) PHPExcel_Calculation_Functions:: flattenSingleValue($nper);
531. $pv = PHPExcel_Calculation_Functions:: flattenSingleValue($pv);
532. $start = (int) PHPExcel_Calculation_Functions:: flattenSingleValue($start);
533. $end = (int) PHPExcel_Calculation_Functions:: flattenSingleValue($end);
534. $type = (int) PHPExcel_Calculation_Functions:: flattenSingleValue($type);
536. // Validate parameters
537. if ($type != 0 && $type != 1) {
538. return PHPExcel_Calculation_Functions:: NaN();
539. }
540. if ($start < 1 || $start > $end) {
541. return PHPExcel_Calculation_Functions:: VALUE();
542. }
544. // Calculate
545. $principal = 0;
546. for ($per = $start; $per <= $end; ++$per) {
547. $principal += self:: PPMT($rate, $per, $nper, $pv, 0, $type);
548. }
550. return $principal;
551. } // function CUMPRINC()
554. /**
555. * DB
556. *
557. * Returns the depreciation of an asset for a specified period using the fixed-declining balance method.
558. * This form of depreciation is used if you want to get a higher depreciation value at the beginning of the depreciation
559. * (as opposed to linear depreciation). The depreciation value is reduced with every depreciation period by the
560. * depreciation already deducted from the initial cost.
561. *
562. * @param float cost Initial cost of the asset.
563. * @param float salvage Value at the end of the depreciation. (Sometimes called the salvage value of the asset)
564. * @param int life Number of periods over which the asset is depreciated. (Sometimes called the useful life of the asset)
565. * @param int period The period for which you want to calculate the depreciation. Period must use the same units as life.
566. * @param float month Number of months in the first year. If month is omitted, it defaults to 12.
567. * @return float
568. */
569. public static function DB($cost, $salvage, $life, $period, $month=12) {
570. $cost = (float) PHPExcel_Calculation_Functions:: flattenSingleValue($cost);
571. $salvage = (float) PHPExcel_Calculation_Functions:: flattenSingleValue($salvage);
572. $life = (int) PHPExcel_Calculation_Functions:: flattenSingleValue($life);
573. $period = (int) PHPExcel_Calculation_Functions:: flattenSingleValue($period);
574. $month = (int) PHPExcel_Calculation_Functions:: flattenSingleValue($month);
576. // Validate
577. if (( is_numeric($cost)) && ( is_numeric($salvage)) && ( is_numeric($life)) && ( is_numeric($period)) && ( is_numeric($month))) {
578. if ($cost == 0) {
579. return 0.0;
580. } elseif (($cost < 0) || (($salvage / $cost) < 0) || ($life <= 0) || ($period < 1) || ($month < 1)) {
581. return PHPExcel_Calculation_Functions:: NaN();
582. }
583. // Set Fixed Depreciation Rate
584. $fixedDepreciationRate = 1 - pow(($salvage / $cost), (1 / $life));
585. $fixedDepreciationRate = round($fixedDepreciationRate, 3);
587. // Loop through each period calculating the depreciation
588. $previousDepreciation = 0;
589. for ($per = 1; $per <= $period; ++$per) {
590. if ($per == 1) {
591. $depreciation = $cost * $fixedDepreciationRate * $month / 12;
592. } elseif ($per == ($life + 1)) {
593. $depreciation = ($cost - $previousDepreciation) * $fixedDepreciationRate * (12 - $month) / 12;
594. } else {
595. $depreciation = ($cost - $previousDepreciation) * $fixedDepreciationRate;
596. }
597. $previousDepreciation += $depreciation;
598. }
599. if ( PHPExcel_Calculation_Functions:: getCompatibilityMode() == PHPExcel_Calculation_Functions:: COMPATIBILITY_GNUMERIC) {
600. $depreciation = round($depreciation,2);
601. }
602. return $depreciation;
603. }
604. return PHPExcel_Calculation_Functions:: VALUE();
605. } // function DB()
608. /**
609. * DDB
610. *
611. * Returns the depreciation of an asset for a specified period using the double-declining balance method or some other method you specify.
612. *
613. * @param float cost Initial cost of the asset.
614. * @param float salvage Value at the end of the depreciation. (Sometimes called the salvage value of the asset)
615. * @param int life Number of periods over which the asset is depreciated. (Sometimes called the useful life of the asset)
616. * @param int period The period for which you want to calculate the depreciation. Period must use the same units as life.
617. * @param float factor The rate at which the balance declines.
618. * If factor is omitted, it is assumed to be 2 (the double-declining balance method).
619. * @return float
620. */
621. public static function DDB($cost, $salvage, $life, $period, $factor=2.0) {
622. $cost = (float) PHPExcel_Calculation_Functions:: flattenSingleValue($cost);
623. $salvage = (float) PHPExcel_Calculation_Functions:: flattenSingleValue($salvage);
624. $life = (int) PHPExcel_Calculation_Functions:: flattenSingleValue($life);
625. $period = (int) PHPExcel_Calculation_Functions:: flattenSingleValue($period);
626. $factor = (float) PHPExcel_Calculation_Functions:: flattenSingleValue($factor);
628. // Validate
629. if (( is_numeric($cost)) && ( is_numeric($salvage)) && ( is_numeric($life)) && ( is_numeric($period)) && ( is_numeric($factor))) {
630. if (($cost <= 0) || (($salvage / $cost) < 0) || ($life <= 0) || ($period < 1) || ($factor <= 0.0) || ($period > $life)) {
631. return PHPExcel_Calculation_Functions:: NaN();
632. }
633. // Set Fixed Depreciation Rate
634. $fixedDepreciationRate = 1 - pow(($salvage / $cost), (1 / $life));
635. $fixedDepreciationRate = round($fixedDepreciationRate, 3);
637. // Loop through each period calculating the depreciation
638. $previousDepreciation = 0;
639. for ($per = 1; $per <= $period; ++$per) {
640. $depreciation = min( ($cost - $previousDepreciation) * ($factor / $life), ($cost - $salvage - $previousDepreciation) );
641. $previousDepreciation += $depreciation;
642. }
643. if ( PHPExcel_Calculation_Functions:: getCompatibilityMode() == PHPExcel_Calculation_Functions:: COMPATIBILITY_GNUMERIC) {
644. $depreciation = round($depreciation,2);
645. }
646. return $depreciation;
647. }
648. return PHPExcel_Calculation_Functions:: VALUE();
649. } // function DDB()
652. /**
653. * DISC
654. *
655. * Returns the discount rate for a security.
656. *
657. * @param mixed settlement The security's settlement date.
658. * The security settlement date is the date after the issue date when the security is traded to the buyer.
659. * @param mixed maturity The security's maturity date.
660. * The maturity date is the date when the security expires.
661. * @param int price The security's price per $100 face value.
662. * @param int redemption the security's redemption value per $100 face value.
663. * @param int basis The type of day count to use.
664. * 0 or omitted US (NASD) 30/360
665. * 1 Actual/actual
666. * 2 Actual/360
667. * 3 Actual/365
668. * 4 European 30/360
669. * @return float
670. */
671. public static function DISC($settlement, $maturity, $price, $redemption, $basis=0) {
672. $settlement = PHPExcel_Calculation_Functions:: flattenSingleValue($settlement);
673. $maturity = PHPExcel_Calculation_Functions:: flattenSingleValue($maturity);
674. $price = (float) PHPExcel_Calculation_Functions:: flattenSingleValue($price);
675. $redemption = (float) PHPExcel_Calculation_Functions:: flattenSingleValue($redemption);
676. $basis = (int) PHPExcel_Calculation_Functions:: flattenSingleValue($basis);
678. // Validate
679. if (( is_numeric($price)) && ( is_numeric($redemption)) && ( is_numeric($basis))) {
680. if (($price <= 0) || ($redemption <= 0)) {
681. return PHPExcel_Calculation_Functions:: NaN();
682. }
683. $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime:: YEARFRAC($settlement, $maturity, $basis);
684. if (! is_numeric($daysBetweenSettlementAndMaturity)) {
685. // return date error
686. return $daysBetweenSettlementAndMaturity;
687. }
689. return ((1 - $price / $redemption) / $daysBetweenSettlementAndMaturity);
690. }
691. return PHPExcel_Calculation_Functions:: VALUE();
692. } // function DISC()
695. /**
696. * DOLLARDE
697. *
698. * Converts a dollar price expressed as an integer part and a fraction part into a dollar price expressed as a decimal number.
699. * Fractional dollar numbers are sometimes used for security prices.
700. *
701. * @param float $fractional_dollar Fractional Dollar
702. * @param int $fraction Fraction
703. * @return float
704. */
705. public static function DOLLARDE($fractional_dollar = Null, $fraction = 0) {
706. $fractional_dollar = PHPExcel_Calculation_Functions:: flattenSingleValue($fractional_dollar);
707. $fraction = (int)PHPExcel_Calculation_Functions:: flattenSingleValue($fraction);
709. // Validate parameters
710. if ( is_null($fractional_dollar) || $fraction < 0) {
711. return PHPExcel_Calculation_Functions:: NaN();
712. }
713. if ($fraction == 0) {
714. return PHPExcel_Calculation_Functions:: DIV0();
715. }
717. $dollars = floor($fractional_dollar);
718. $cents = fmod($fractional_dollar,1);
719. $cents /= $fraction;
720. $cents *= pow(10, ceil( log10($fraction)));
721. return $dollars + $cents;
722. } // function DOLLARDE()
725. /**
726. * DOLLARFR
727. *
728. * Converts a dollar price expressed as a decimal number into a dollar price expressed as a fraction.
729. * Fractional dollar numbers are sometimes used for security prices.
730. *
731. * @param float $decimal_dollar Decimal Dollar
732. * @param int $fraction Fraction
733. * @return float
734. */
735. public static function DOLLARFR($decimal_dollar = Null, $fraction = 0) {
736. $decimal_dollar = PHPExcel_Calculation_Functions:: flattenSingleValue($decimal_dollar);
737. $fraction = (int)PHPExcel_Calculation_Functions:: flattenSingleValue($fraction);
739. // Validate parameters
740. if ( is_null($decimal_dollar) || $fraction < 0) {
741. return PHPExcel_Calculation_Functions:: NaN();
742. }
743. if ($fraction == 0) {
744. return PHPExcel_Calculation_Functions:: DIV0();
745. }
747. $dollars = floor($decimal_dollar);
748. $cents = fmod($decimal_dollar,1);
749. $cents *= $fraction;
750. $cents *= pow(10,-ceil( log10($fraction)));
751. return $dollars + $cents;
752. } // function DOLLARFR()
755. /**
756. * EFFECT
757. *
758. * Returns the effective interest rate given the nominal rate and the number of compounding payments per year.
759. *
760. * @param float $nominal_rate Nominal interest rate
761. * @param int $npery Number of compounding payments per year
762. * @return float
763. */
764. public static function EFFECT($nominal_rate = 0, $npery = 0) {
765. $nominal_rate = PHPExcel_Calculation_Functions:: flattenSingleValue($nominal_rate);
766. $npery = (int)PHPExcel_Calculation_Functions:: flattenSingleValue($npery);
768. // Validate parameters
769. if ($nominal_rate <= 0 || $npery < 1) {
770. return PHPExcel_Calculation_Functions:: NaN();
771. }
773. return pow((1 + $nominal_rate / $npery), $npery) - 1;
774. } // function EFFECT()
777. /**
778. * FV
779. *
780. * Returns the Future Value of a cash flow with constant payments and interest rate (annuities).
781. *
782. * @param float $rate Interest rate per period
783. * @param int $nper Number of periods
784. * @param float $pmt Periodic payment (annuity)
785. * @param float $pv Present Value
786. * @param int $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period
787. * @return float
788. */
789. public static function FV($rate = 0, $nper = 0, $pmt = 0, $pv = 0, $type = 0) {
790. $rate = PHPExcel_Calculation_Functions:: flattenSingleValue($rate);
791. $nper = PHPExcel_Calculation_Functions:: flattenSingleValue($nper);
792. $pmt = PHPExcel_Calculation_Functions:: flattenSingleValue($pmt);
793. $pv = PHPExcel_Calculation_Functions:: flattenSingleValue($pv);
794. $type = PHPExcel_Calculation_Functions:: flattenSingleValue($type);
796. // Validate parameters
797. if ($type != 0 && $type != 1) {
798. return PHPExcel_Calculation_Functions:: NaN();
799. }
801. // Calculate
802. if (! is_null($rate) && $rate != 0) {
803. return -$pv * pow(1 + $rate, $nper) - $pmt * (1 + $rate * $type) * ( pow(1 + $rate, $nper) - 1) / $rate;
804. } else {
805. return -$pv - $pmt * $nper;
806. }
807. } // function FV()
810. /**
811. * FVSCHEDULE
812. *
813. */
814. public static function FVSCHEDULE($principal, $schedule) {
815. $principal = PHPExcel_Calculation_Functions:: flattenSingleValue($principal);
816. $schedule = PHPExcel_Calculation_Functions:: flattenArray($schedule);
818. foreach($schedule as $n) {
819. $principal *= 1 + $n;
820. }
822. return $principal;
823. } // function FVSCHEDULE()
826. /**
827. * INTRATE
828. *
829. * Returns the interest rate for a fully invested security.
830. *
831. * @param mixed settlement The security's settlement date.
832. * The security settlement date is the date after the issue date when the security is traded to the buyer.
833. * @param mixed maturity The security's maturity date.
834. * The maturity date is the date when the security expires.
835. * @param int investment The amount invested in the security.
836. * @param int redemption The amount to be received at maturity.
837. * @param int basis The type of day count to use.
838. * 0 or omitted US (NASD) 30/360
839. * 1 Actual/actual
840. * 2 Actual/360
841. * 3 Actual/365
842. * 4 European 30/360
843. * @return float
844. */
845. public static function INTRATE($settlement, $maturity, $investment, $redemption, $basis=0) {
846. $settlement = PHPExcel_Calculation_Functions:: flattenSingleValue($settlement);
847. $maturity = PHPExcel_Calculation_Functions:: flattenSingleValue($maturity);
848. $investment = (float) PHPExcel_Calculation_Functions:: flattenSingleValue($investment);
849. $redemption = (float) PHPExcel_Calculation_Functions:: flattenSingleValue($redemption);
850. $basis = (int) PHPExcel_Calculation_Functions:: flattenSingleValue($basis);
852. // Validate
853. if (( is_numeric($investment)) && ( is_numeric($redemption)) && ( is_numeric($basis))) {
854. if (($investment <= 0) || ($redemption <= 0)) {
855. return PHPExcel_Calculation_Functions:: NaN();
856. }
857. $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime:: YEARFRAC($settlement, $maturity, $basis);
858. if (! is_numeric($daysBetweenSettlementAndMaturity)) {
859. // return date error
860. return $daysBetweenSettlementAndMaturity;
861. }
863. return (($redemption / $investment) - 1) / ($daysBetweenSettlementAndMaturity);
864. }
865. return PHPExcel_Calculation_Functions:: VALUE();
866. } // function INTRATE()
869. /**
870. * IPMT
871. *
872. * Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate.
873. *
874. * @param float $rate Interest rate per period
875. * @param int $per Period for which we want to find the interest
876. * @param int $nper Number of periods
877. * @param float $pv Present Value
878. * @param float $fv Future Value
879. * @param int $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period
880. * @return float
881. */
882. public static function IPMT($rate, $per, $nper, $pv, $fv = 0, $type = 0) {
883. $rate = PHPExcel_Calculation_Functions:: flattenSingleValue($rate);
884. $per = (int) PHPExcel_Calculation_Functions:: flattenSingleValue($per);
885. $nper = (int) PHPExcel_Calculation_Functions:: flattenSingleValue($nper);
886. $pv = PHPExcel_Calculation_Functions:: flattenSingleValue($pv);
887. $fv = PHPExcel_Calculation_Functions:: flattenSingleValue($fv);
888. $type = (int) PHPExcel_Calculation_Functions:: flattenSingleValue($type);
890. // Validate parameters
891. if ($type != 0 && $type != 1) {
892. return PHPExcel_Calculation_Functions:: NaN();
893. }
894. if ($per <= 0 || $per > $nper) {
895. return PHPExcel_Calculation_Functions:: VALUE();
896. }
898. // Calculate
899. $interestAndPrincipal = self::_interestAndPrincipal($rate, $per, $nper, $pv, $fv, $type);
900. return $interestAndPrincipal[0];
901. } // function IPMT()
904. public static function IRR($values, $guess = 0.1) {
905. if (! is_array($values)) return PHPExcel_Calculation_Functions:: VALUE();
906. $values = PHPExcel_Calculation_Functions:: flattenArray($values);
907. $guess = PHPExcel_Calculation_Functions:: flattenSingleValue($guess);
909. // create an initial range, with a root somewhere between 0 and guess
910. $x1 = 0.0;
911. $x2 = $guess;
912. $f1 = self:: NPV($x1, $values);
913. $f2 = self:: NPV($x2, $values);
914. for ($i = 0; $i < FINANCIAL_MAX_ITERATIONS; ++$i) {
915. if (($f1 * $f2) < 0.0) break;
916. if ( abs($f1) < abs($f2)) {
917. $f1 = self:: NPV($x1 += 1.6 * ($x1 - $x2), $values);
918. } else {
919. $f2 = self:: NPV($x2 += 1.6 * ($x2 - $x1), $values);
920. }
921. }
922. if (($f1 * $f2) > 0.0) return PHPExcel_Calculation_Functions:: VALUE();
924. $f = self:: NPV($x1, $values);
925. if ($f < 0.0) {
926. $rtb = $x1;
927. $dx = $x2 - $x1;
928. } else {
929. $rtb = $x2;
930. $dx = $x1 - $x2;
931. }
933. for ($i = 0; $i < FINANCIAL_MAX_ITERATIONS; ++$i) {
934. $dx *= 0.5;
935. $x_mid = $rtb + $dx;
936. $f_mid = self:: NPV($x_mid, $values);
937. if ($f_mid <= 0.0) $rtb = $x_mid;
938. if (( abs($f_mid) < FINANCIAL_PRECISION) || ( abs($dx) < FINANCIAL_PRECISION)) return $x_mid;
939. }
940. return PHPExcel_Calculation_Functions:: VALUE();
941. } // function IRR()
944. /**
945. * ISPMT
946. *
947. * Returns the interest payment for an investment based on an interest rate and a constant payment schedule.
948. *
949. * Excel Function:
950. * =ISPMT(interest_rate, period, number_payments, PV)
951. *
952. * interest_rate is the interest rate for the investment
953. *
954. * period is the period to calculate the interest rate. It must be betweeen 1 and number_payments.
955. *
956. * number_payments is the number of payments for the annuity
957. *
958. * PV is the loan amount or present value of the payments
959. */
960. public static function ISPMT() {
961. // Return value
962. $returnValue = 0;
964. // Get the parameters
965. $aArgs = PHPExcel_Calculation_Functions:: flattenArray( func_get_args());
966. $interestRate = array_shift($aArgs);
967. $period = array_shift($aArgs);
968. $numberPeriods = array_shift($aArgs);
969. $principleRemaining = array_shift($aArgs);
971. // Calculate
972. $principlePayment = ($principleRemaining * 1.0) / ($numberPeriods * 1.0);
973. for($i=0; $i <= $period; ++$i) {
974. $returnValue = $interestRate * $principleRemaining * -1;
975. $principleRemaining -= $principlePayment;
976. // principle needs to be 0 after the last payment, don't let floating point screw it up
977. if($i == $numberPeriods) {
978. $returnValue = 0;
979. }
980. }
981. return($returnValue);
982. } // function ISPMT()
985. public static function MIRR($values, $finance_rate, $reinvestment_rate) {
986. if (! is_array($values)) return PHPExcel_Calculation_Functions:: VALUE();
987. $values = PHPExcel_Calculation_Functions:: flattenArray($values);
988. $finance_rate = PHPExcel_Calculation_Functions:: flattenSingleValue($finance_rate);
989. $reinvestment_rate = PHPExcel_Calculation_Functions:: flattenSingleValue($reinvestment_rate);
990. $n = count($values);
992. $rr = 1.0 + $reinvestment_rate;
993. $fr = 1.0 + $finance_rate;
995. $npv_pos = $npv_neg = 0.0;
996. foreach($values as $i => $v) {
997. if ($v >= 0) {
998. $npv_pos += $v / pow($rr, $i);
999. } else {
1000. $npv_neg += $v / pow($fr, $i);
1001. }
1002. }
1004. if (($npv_neg == 0) || ($npv_pos == 0) || ($reinvestment_rate <= -1)) {
1005. return PHPExcel_Calculation_Functions:: VALUE();
1006. }
1008. $mirr = pow((-$npv_pos * pow($rr, $n))
1009. / ($npv_neg * ($rr)), (1.0 / ($n - 1))) - 1.0;
1011. return ( is_finite($mirr) ? $mirr : PHPExcel_Calculation_Functions:: VALUE());
1012. } // function MIRR()
1015. /**
1016. * NOMINAL
1017. *
1018. * Returns the nominal interest rate given the effective rate and the number of compounding payments per year.
1019. *
1020. * @param float $effect_rate Effective interest rate
1021. * @param int $npery Number of compounding payments per year
1022. * @return float
1023. */
1024. public static function NOMINAL($effect_rate = 0, $npery = 0) {
1025. $effect_rate = PHPExcel_Calculation_Functions:: flattenSingleValue($effect_rate);
1026. $npery = (int)PHPExcel_Calculation_Functions:: flattenSingleValue($npery);
1028. // Validate parameters
1029. if ($effect_rate <= 0 || $npery < 1) {
1030. return PHPExcel_Calculation_Functions:: NaN();
1031. }
1033. // Calculate
1034. return $npery * ( pow($effect_rate + 1, 1 / $npery) - 1);
1035. } // function NOMINAL()
1038. /**
1039. * NPER
1040. *
1041. * Returns the number of periods for a cash flow with constant periodic payments (annuities), and interest rate.
1042. *
1043. * @param float $rate Interest rate per period
1044. * @param int $pmt Periodic payment (annuity)
1045. * @param float $pv Present Value
1046. * @param float $fv Future Value
1047. * @param int $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period
1048. * @return float
1049. */
1050. public static function NPER($rate = 0, $pmt = 0, $pv = 0, $fv = 0, $type = 0) {
1051. $rate = PHPExcel_Calculation_Functions:: flattenSingleValue($rate);
1052. $pmt = PHPExcel_Calculation_Functions:: flattenSingleValue($pmt);
1053. $pv = PHPExcel_Calculation_Functions:: flattenSingleValue($pv);
1054. $fv = PHPExcel_Calculation_Functions:: flattenSingleValue($fv);
1055. $type = PHPExcel_Calculation_Functions:: flattenSingleValue($type);
1057. // Validate parameters
1058. if ($type != 0 && $type != 1) {
1059. return PHPExcel_Calculation_Functions:: NaN();
1060. }
1062. // Calculate
1063. if (! is_null($rate) && $rate != 0) {
1064. if ($pmt == 0 && $pv == 0) {
1065. return PHPExcel_Calculation_Functions:: NaN();
1066. }
1067. return log(($pmt * (1 + $rate * $type) / $rate - $fv) / ($pv + $pmt * (1 + $rate * $type) / $rate)) / log(1 + $rate);
1068. } else {
1069. if ($pmt == 0) {
1070. return PHPExcel_Calculation_Functions:: NaN();
1071. }
1072. return (-$pv -$fv) / $pmt;
1073. }
1074. } // function NPER()
1077. /**
1078. * NPV
1079. *
1080. * Returns the Net Present Value of a cash flow series given a discount rate.
1081. *
1082. * @param float Discount interest rate
1083. * @param array Cash flow series
1084. * @return float
1085. */
1086. public static function NPV() {
1087. // Return value
1088. $returnValue = 0;
1090. // Loop through arguments
1091. $aArgs = PHPExcel_Calculation_Functions:: flattenArray( func_get_args());
1093. // Calculate
1094. $rate = array_shift($aArgs);
1095. for ($i = 1; $i <= count($aArgs); ++$i) {
1096. // Is it a numeric value?
1097. if ( is_numeric($aArgs[$i - 1])) {
1098. $returnValue += $aArgs[$i - 1] / pow(1 + $rate, $i);
1099. }
1100. }
1102. // Return
1103. return $returnValue;
1104. } // function NPV()
1107. /**
1108. * PMT
1109. *
1110. * Returns the constant payment (annuity) for a cash flow with a constant interest rate.
1111. *
1112. * @param float $rate Interest rate per period
1113. * @param int $nper Number of periods
1114. * @param float $pv Present Value
1115. * @param float $fv Future Value
1116. * @param int $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period
1117. * @return float
1118. */
1119. public static function PMT($rate = 0, $nper = 0, $pv = 0, $fv = 0, $type = 0) {
1120. $rate = PHPExcel_Calculation_Functions:: flattenSingleValue($rate);
1121. $nper = PHPExcel_Calculation_Functions:: flattenSingleValue($nper);
1122. $pv = PHPExcel_Calculation_Functions:: flattenSingleValue($pv);
1123. $fv = PHPExcel_Calculation_Functions:: flattenSingleValue($fv);
1124. $type = PHPExcel_Calculation_Functions:: flattenSingleValue($type);
1126. // Validate parameters
1127. if ($type != 0 && $type != 1) {
1128. return PHPExcel_Calculation_Functions:: NaN();
1129. }
1131. // Calculate
1132. if (! is_null($rate) && $rate != 0) {
1133. return (-$fv - $pv * pow(1 + $rate, $nper)) / (1 + $rate * $type) / (( pow(1 + $rate, $nper) - 1) / $rate);
1134. } else {
1135. return (-$pv - $fv) / $nper;
1136. }
1137. } // function PMT()
1140. /**
1141. * PPMT
1142. *
1143. * Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate.
1144. *
1145. * @param float $rate Interest rate per period
1146. * @param int $per Period for which we want to find the interest
1147. * @param int $nper Number of periods
1148. * @param float $pv Present Value
1149. * @param float $fv Future Value
1150. * @param int $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period
1151. * @return float
1152. */
1153. public static function PPMT($rate, $per, $nper, $pv, $fv = 0, $type = 0) {
1154. $rate = PHPExcel_Calculation_Functions:: flattenSingleValue($rate);
1155. $per = (int) PHPExcel_Calculation_Functions:: flattenSingleValue($per);
1156. $nper = (int) PHPExcel_Calculation_Functions:: flattenSingleValue($nper);
1157. $pv = PHPExcel_Calculation_Functions:: flattenSingleValue($pv);
1158. $fv = PHPExcel_Calculation_Functions:: flattenSingleValue($fv);
1159. $type = (int) PHPExcel_Calculation_Functions:: flattenSingleValue($type);
1161. // Validate parameters
1162. if ($type != 0 && $type != 1) {
1163. return PHPExcel_Calculation_Functions:: NaN();
1164. }
1165. if ($per <= 0 || $per > $nper) {
1166. return PHPExcel_Calculation_Functions:: VALUE();
1167. }
1169. // Calculate
1170. $interestAndPrincipal = self::_interestAndPrincipal($rate, $per, $nper, $pv, $fv, $type);
1171. return $interestAndPrincipal[1];
1172. } // function PPMT()
1175. public static function PRICE($settlement, $maturity, $rate, $yield, $redemption, $frequency, $basis=0) {
1176. $settlement = PHPExcel_Calculation_Functions:: flattenSingleValue($settlement);
1177. $maturity = PHPExcel_Calculation_Functions:: flattenSingleValue($maturity);
1178. $rate = (float) PHPExcel_Calculation_Functions:: flattenSingleValue($rate);
1179. $yield = (float) PHPExcel_Calculation_Functions:: flattenSingleValue($yield);
1180. $redemption = (float) PHPExcel_Calculation_Functions:: flattenSingleValue($redemption);
1181. $frequency = (int) PHPExcel_Calculation_Functions:: flattenSingleValue($frequency);
1182. $basis = ( is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions:: flattenSingleValue($basis);
1184. if ( is_string($settlement = PHPExcel_Calculation_DateTime::_getDateValue($settlement))) {
1185. return PHPExcel_Calculation_Functions:: VALUE();
1186. }
1187. if ( is_string($maturity = PHPExcel_Calculation_DateTime::_getDateValue($maturity))) {
1188. return PHPExcel_Calculation_Functions:: VALUE();
1189. }
1191. if (($settlement > $maturity) ||
1192. (! self::_validFrequency($frequency)) ||
1193. (($basis < 0) || ($basis > 4))) {
1194. return PHPExcel_Calculation_Functions:: NaN();
1195. }
1197. $dsc = self:: COUPDAYSNC($settlement, $maturity, $frequency, $basis);
1198. $e = self:: COUPDAYS($settlement, $maturity, $frequency, $basis);
1199. $n = self:: COUPNUM($settlement, $maturity, $frequency, $basis);
1200. $a = self:: COUPDAYBS($settlement, $maturity, $frequency, $basis);
1202. $baseYF = 1.0 + ($yield / $frequency);
1203. $rfp = 100 * ($rate / $frequency);
1204. $de = $dsc / $e;
1206. $result = $redemption / pow($baseYF, (--$n + $de));
1207. for($k = 0; $k <= $n; ++$k) {
1208. $result += $rfp / ( pow($baseYF, ($k + $de)));
1209. }
1210. $result -= $rfp * ($a / $e);
1212. return $result;
1213. } // function PRICE()
1216. /**
1217. * PRICEDISC
1218. *
1219. * Returns the price per $100 face value of a discounted security.
1220. *
1221. * @param mixed settlement The security's settlement date.
1222. * The security settlement date is the date after the issue date when the security is traded to the buyer.
1223. * @param mixed maturity The security's maturity date.
1224. * The maturity date is the date when the security expires.
1225. * @param int discount The security's discount rate.
1226. * @param int redemption The security's redemption value per $100 face value.
1227. * @param int basis The type of day count to use.
1228. * 0 or omitted US (NASD) 30/360
1229. * 1 Actual/actual
1230. * 2 Actual/360
1231. * 3 Actual/365
1232. * 4 European 30/360
1233. * @return float
1234. */
1235. public static function PRICEDISC($settlement, $maturity, $discount, $redemption, $basis=0) {
1236. $settlement = PHPExcel_Calculation_Functions:: flattenSingleValue($settlement);
1237. $maturity = PHPExcel_Calculation_Functions:: flattenSingleValue($maturity);
1238. $discount = (float) PHPExcel_Calculation_Functions:: flattenSingleValue($discount);
1239. $redemption = (float) PHPExcel_Calculation_Functions:: flattenSingleValue($redemption);
1240. $basis = (int) PHPExcel_Calculation_Functions:: flattenSingleValue($basis);
1242. // Validate
1243. if (( is_numeric($discount)) && ( is_numeric($redemption)) && ( is_numeric($basis))) {
1244. if (($discount <= 0) || ($redemption <= 0)) {
1245. return PHPExcel_Calculation_Functions:: NaN();
1246. }
1247. $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime:: YEARFRAC($settlement, $maturity, $basis);
1248. if (! is_numeric($daysBetweenSettlementAndMaturity)) {
1249. // return date error
1250. return $daysBetweenSettlementAndMaturity;
1251. }
1253. return $redemption * (1 - $discount * $daysBetweenSettlementAndMaturity);
1254. }
1255. return PHPExcel_Calculation_Functions:: VALUE();
1256. } // function PRICEDISC()
1259. /**
1260. * PRICEMAT
1261. *
1262. * Returns the price per $100 face value of a security that pays interest at maturity.
1263. *
1264. * @param mixed settlement The security's settlement date.
1265. * The security's settlement date is the date after the issue date when the security is traded to the buyer.
1266. * @param mixed maturity The security's maturity date.
1267. * The maturity date is the date when the security expires.
1268. * @param mixed issue The security's issue date.
1269. * @param int rate The security's interest rate at date of issue.
1270. * @param int yield The security's annual yield.
1271. * @param int basis The type of day count to use.
1272. * 0 or omitted US (NASD) 30/360
1273. * 1 Actual/actual
1274. * 2 Actual/360
1275. * 3 Actual/365
1276. * 4 European 30/360
1277. * @return float
1278. */
1279. public static function PRICEMAT($settlement, $maturity, $issue, $rate, $yield, $basis=0) {
1280. $settlement = PHPExcel_Calculation_Functions:: flattenSingleValue($settlement);
1281. $maturity = PHPExcel_Calculation_Functions:: flattenSingleValue($maturity);
1282. $issue = PHPExcel_Calculation_Functions:: flattenSingleValue($issue);
1283. $rate = PHPExcel_Calculation_Functions:: flattenSingleValue($rate);
1284. $yield = PHPExcel_Calculation_Functions:: flattenSingleValue($yield);
1285. $basis = (int) PHPExcel_Calculation_Functions:: flattenSingleValue($basis);
1287. // Validate
1288. if ( is_numeric($rate) && is_numeric($yield)) {
1289. if (($rate <= 0) || ($yield <= 0)) {
1290. return PHPExcel_Calculation_Functions:: NaN();
1291. }
1292. $daysPerYear = self::_daysPerYear( PHPExcel_Calculation_DateTime:: YEAR($settlement),$basis);
1293. if (! is_numeric($daysPerYear)) {
1294. return $daysPerYear;
1295. }
1296. $daysBetweenIssueAndSettlement = PHPExcel_Calculation_DateTime:: YEARFRAC($issue, $settlement, $basis);
1297. if (! is_numeric($daysBetweenIssueAndSettlement)) {
1298. // return date error
1299. return $daysBetweenIssueAndSettlement;
1300. }
1301. $daysBetweenIssueAndSettlement *= $daysPerYear;
1302. $daysBetweenIssueAndMaturity = PHPExcel_Calculation_DateTime:: YEARFRAC($issue, $maturity, $basis);
1303. if (! is_numeric($daysBetweenIssueAndMaturity)) {
1304. // return date error
1305. return $daysBetweenIssueAndMaturity;
1306. }
1307. $daysBetweenIssueAndMaturity *= $daysPerYear;
1308. $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime:: YEARFRAC($settlement, $maturity, $basis);
1309. if (! is_numeric($daysBetweenSettlementAndMaturity)) {
1310. // return date error
1311. return $daysBetweenSettlementAndMaturity;
1312. }
1313. $daysBetweenSettlementAndMaturity *= $daysPerYear;
1315. return ((100 + (($daysBetweenIssueAndMaturity / $daysPerYear) * $rate * 100)) /
1316. (1 + (($daysBetweenSettlementAndMaturity / $daysPerYear) * $yield)) -
1317. (($daysBetweenIssueAndSettlement / $daysPerYear) * $rate * 100));
1318. }
1319. return PHPExcel_Calculation_Functions:: VALUE();
1320. } // function PRICEMAT()
1323. /**
1324. * PV
1325. *
1326. * Returns the Present Value of a cash flow with constant payments and interest rate (annuities).
1327. *
1328. * @param float $rate Interest rate per period
1329. * @param int $nper Number of periods
1330. * @param float $pmt Periodic payment (annuity)
1331. * @param float $fv Future Value
1332. * @param int $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period
1333. * @return float
1334. */
1335. public static function PV($rate = 0, $nper = 0, $pmt = 0, $fv = 0, $type = 0) {
1336. $rate = PHPExcel_Calculation_Functions:: flattenSingleValue($rate);
1337. $nper = PHPExcel_Calculation_Functions:: flattenSingleValue($nper);
1338. $pmt = PHPExcel_Calculation_Functions:: flattenSingleValue($pmt);
1339. $fv = PHPExcel_Calculation_Functions:: flattenSingleValue($fv);
1340. $type = PHPExcel_Calculation_Functions:: flattenSingleValue($type);
1342. // Validate parameters
1343. if ($type != 0 && $type != 1) {
1344. return PHPExcel_Calculation_Functions:: NaN();
1345. }
1347. // Calculate
1348. if (! is_null($rate) && $rate != 0) {
1349. return (-$pmt * (1 + $rate * $type) * (( pow(1 + $rate, $nper) - 1) / $rate) - $fv) / pow(1 + $rate, $nper);
1350. } else {
1351. return -$fv - $pmt * $nper;
1352. }
1353. } // function PV()
1356. /**
1357. * RATE
1358. *
1359. **/
1361. public static function RATE($nper, $pmt, $pv, $fv = 0.0, $type = 0, $guess = 0.1) {
1362. $nper = (int) PHPExcel_Calculation_Functions:: flattenSingleValue($nper);
1363. $pmt = PHPExcel_Calculation_Functions:: flattenSingleValue($pmt);
1364. $pv = PHPExcel_Calculation_Functions:: flattenSingleValue($pv);
1365. $fv = ( is_null($fv)) ? 0.0 : PHPExcel_Calculation_Functions:: flattenSingleValue($fv);
1366. $type = ( is_null($type)) ? 0 : (int) PHPExcel_Calculation_Functions:: flattenSingleValue($type);
1367. $guess = ( is_null($guess)) ? 0.1 : PHPExcel_Calculation_Functions:: flattenSingleValue($guess);
1369. $rate = $guess;
1370. if ( abs($rate) < FINANCIAL_PRECISION) {
1371. $y = $pv * (1 + $nper * $rate) + $pmt * (1 + $rate * $type) * $nper + $fv;
1372. } else {
1373. $f = exp($nper * log(1 + $rate));
1374. $y = $pv * $f + $pmt * (1 / $rate + $type) * ($f - 1) + $fv;
1375. }
1376. $y0 = $pv + $pmt * $nper + $fv;
1377. $y1 = $pv * $f + $pmt * (1 / $rate + $type) * ($f - 1) + $fv;
1379. // find root by secant method
1380. $i = $x0 = 0.0;
1381. $x1 = $rate;
1382. while (( abs($y0 - $y1) > FINANCIAL_PRECISION) && ($i < FINANCIAL_MAX_ITERATIONS)) {
1383. $rate = ($y1 * $x0 - $y0 * $x1) / ($y1 - $y0);
1384. $x0 = $x1;
1385. $x1 = $rate;
1387. if ( abs($rate) < FINANCIAL_PRECISION) {
1388. $y = $pv * (1 + $nper * $rate) + $pmt * (1 + $rate * $type) * $nper + $fv;
1389. } else {
1390. $f = exp($nper * log(1 + $rate));
1391. $y = $pv * $f + $pmt * (1 / $rate + $type) * ($f - 1) + $fv;
1392. }
1394. $y0 = $y1;
1395. $y1 = $y;
1396. ++$i;
1397. }
1398. return $rate;
1399. } // function RATE()
1402. /**
1403. * RECEIVED
1404. *
1405. * Returns the price per $100 face value of a discounted security.
1406. *
1407. * @param mixed settlement The security's settlement date.
1408. * The security settlement date is the date after the issue date when the security is traded to the buyer.
1409. * @param mixed maturity The security's maturity date.
1410. * The maturity date is the date when the security expires.
1411. * @param int investment The amount invested in the security.
1412. * @param int discount The security's discount rate.
1413. * @param int basis The type of day count to use.
1414. * 0 or omitted US (NASD) 30/360
1415. * 1 Actual/actual
1416. * 2 Actual/360
1417. * 3 Actual/365
1418. * 4 European 30/360
1419. * @return float
1420. */
1421. public static function RECEIVED($settlement, $maturity, $investment, $discount, $basis=0) {
1422. $settlement = PHPExcel_Calculation_Functions:: flattenSingleValue($settlement);
1423. $maturity = PHPExcel_Calculation_Functions:: flattenSingleValue($maturity);
1424. $investment = (float) PHPExcel_Calculation_Functions:: flattenSingleValue($investment);
1425. $discount = (float) PHPExcel_Calculation_Functions:: flattenSingleValue($discount);
1426. $basis = (int) PHPExcel_Calculation_Functions:: flattenSingleValue($basis);
1428. // Validate
1429. if (( is_numeric($investment)) && ( is_numeric($discount)) && ( is_numeric($basis))) {
1430. if (($investment <= 0) || ($discount <= 0)) {
1431. return PHPExcel_Calculation_Functions:: NaN();
1432. }
1433. $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime:: YEARFRAC($settlement, $maturity, $basis);
1434. if (! is_numeric($daysBetweenSettlementAndMaturity)) {
1435. // return date error
1436. return $daysBetweenSettlementAndMaturity;
1437. }
1439. return $investment / ( 1 - ($discount * $daysBetweenSettlementAndMaturity));
1440. }
1441. return PHPExcel_Calculation_Functions:: VALUE();
1442. } // function RECEIVED()
1445. /**
1446. * SLN
1447. *
1448. * Returns the straight-line depreciation of an asset for one period
1449. *
1450. * @param cost Initial cost of the asset
1451. * @param salvage Value at the end of the depreciation
1452. * @param life Number of periods over which the asset is depreciated
1453. * @return float
1454. */
1455. public static function SLN($cost, $salvage, $life) {
1456. $cost = PHPExcel_Calculation_Functions:: flattenSingleValue($cost);
1457. $salvage = PHPExcel_Calculation_Functions:: flattenSingleValue($salvage);
1458. $life = PHPExcel_Calculation_Functions:: flattenSingleValue($life);
1460. // Calculate
1461. if (( is_numeric($cost)) && ( is_numeric($salvage)) && ( is_numeric($life))) {
1462. if ($life < 0) {
1463. return PHPExcel_Calculation_Functions:: NaN();
1464. }
1465. return ($cost - $salvage) / $life;
1466. }
1467. return PHPExcel_Calculation_Functions:: VALUE();
1468. } // function SLN()
1471. /**
1472. * SYD
1473. *
1474. * Returns the sum-of-years' digits depreciation of an asset for a specified period.
1475. *
1476. * @param cost Initial cost of the asset
1477. * @param salvage Value at the end of the depreciation
1478. * @param life Number of periods over which the asset is depreciated
1479. * @param period Period
1480. * @return float
1481. */
1482. public static function SYD($cost, $salvage, $life, $period) {
1483. $cost = PHPExcel_Calculation_Functions:: flattenSingleValue($cost);
1484. $salvage = PHPExcel_Calculation_Functions:: flattenSingleValue($salvage);
1485. $life = PHPExcel_Calculation_Functions:: flattenSingleValue($life);
1486. $period = PHPExcel_Calculation_Functions:: flattenSingleValue($period);
1488. // Calculate
1489. if (( is_numeric($cost)) && ( is_numeric($salvage)) && ( is_numeric($life)) && ( is_numeric($period))) {
1490. if (($life < 1) || ($period > $life)) {
1491. return PHPExcel_Calculation_Functions:: NaN();
1492. }
1493. return (($cost - $salvage) * ($life - $period + 1) * 2) / ($life * ($life + 1));
1494. }
1495. return PHPExcel_Calculation_Functions:: VALUE();
1496. } // function SYD()
1499. /**
1500. * TBILLEQ
1501. *
1502. * Returns the bond-equivalent yield for a Treasury bill.
1503. *
1504. * @param mixed settlement The Treasury bill's settlement date.
1505. * The Treasury bill's settlement date is the date after the issue date when the Treasury bill is traded to the buyer.
1506. * @param mixed maturity The Treasury bill's maturity date.
1507. * The maturity date is the date when the Treasury bill expires.
1508. * @param int discount The Treasury bill's discount rate.
1509. * @return float
1510. */
1511. public static function TBILLEQ($settlement, $maturity, $discount) {
1512. $settlement = PHPExcel_Calculation_Functions:: flattenSingleValue($settlement);
1513. $maturity = PHPExcel_Calculation_Functions:: flattenSingleValue($maturity);
1514. $discount = PHPExcel_Calculation_Functions:: flattenSingleValue($discount);
1516. // Use TBILLPRICE for validation
1517. $testValue = self:: TBILLPRICE($settlement, $maturity, $discount);
1518. if ( is_string($testValue)) {
1519. return $testValue;
1520. }
1522. if ( is_string($maturity = PHPExcel_Calculation_DateTime::_getDateValue($maturity))) {
1523. return PHPExcel_Calculation_Functions:: VALUE();
1524. }
1526. if ( PHPExcel_Calculation_Functions:: getCompatibilityMode() == PHPExcel_Calculation_Functions:: COMPATIBILITY_OPENOFFICE) {
1527. ++$maturity;
1528. $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime:: YEARFRAC($settlement, $maturity) * 360;
1529. } else {
1530. $daysBetweenSettlementAndMaturity = ( PHPExcel_Calculation_DateTime::_getDateValue($maturity) - PHPExcel_Calculation_DateTime::_getDateValue($settlement));
1531. }
1533. return (365 * $discount) / (360 - $discount * $daysBetweenSettlementAndMaturity);
1534. } // function TBILLEQ()
1537. /**
1538. * TBILLPRICE
1539. *
1540. * Returns the yield for a Treasury bill.
1541. *
1542. * @param mixed settlement The Treasury bill's settlement date.
1543. * The Treasury bill's settlement date is the date after the issue date when the Treasury bill is traded to the buyer.
1544. * @param mixed maturity The Treasury bill's maturity date.
1545. * The maturity date is the date when the Treasury bill expires.
1546. * @param int discount The Treasury bill's discount rate.
1547. * @return float
1548. */
1549. public static function TBILLPRICE($settlement, $maturity, $discount) {
1550. $settlement = PHPExcel_Calculation_Functions:: flattenSingleValue($settlement);
1551. $maturity = PHPExcel_Calculation_Functions:: flattenSingleValue($maturity);
1552. $discount = PHPExcel_Calculation_Functions:: flattenSingleValue($discount);
1554. if ( is_string($maturity = PHPExcel_Calculation_DateTime::_getDateValue($maturity))) {
1555. return PHPExcel_Calculation_Functions:: VALUE();
1556. }
1558. // Validate
1559. if ( is_numeric($discount)) {
1560. if ($discount <= 0) {
1561. return PHPExcel_Calculation_Functions:: NaN();
1562. }
1564. if ( PHPExcel_Calculation_Functions:: getCompatibilityMode() == PHPExcel_Calculation_Functions:: COMPATIBILITY_OPENOFFICE) {
1565. ++$maturity;
1566. $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime:: YEARFRAC($settlement, $maturity) * 360;
1567. if (! is_numeric($daysBetweenSettlementAndMaturity)) {
1568. // return date error
1569. return $daysBetweenSettlementAndMaturity;
1570. }
1571. } else {
1572. $daysBetweenSettlementAndMaturity = ( PHPExcel_Calculation_DateTime::_getDateValue($maturity) - PHPExcel_Calculation_DateTime::_getDateValue($settlement));
1573. }
1575. if ($daysBetweenSettlementAndMaturity > 360) {
1576. return PHPExcel_Calculation_Functions:: NaN();
1577. }
1579. $price = 100 * (1 - (($discount * $daysBetweenSettlementAndMaturity) / 360));
1580. if ($price <= 0) {
1581. return PHPExcel_Calculation_Functions:: NaN();
1582. }
1583. return $price;
1584. }
1585. return PHPExcel_Calculation_Functions:: VALUE();
1586. } // function TBILLPRICE()
1589. /**
1590. * TBILLYIELD
1591. *
1592. * Returns the yield for a Treasury bill.
1593. *
1594. * @param mixed settlement The Treasury bill's settlement date.
1595. * The Treasury bill's settlement date is the date after the issue date when the Treasury bill is traded to the buyer.
1596. * @param mixed maturity The Treasury bill's maturity date.
1597. * The maturity date is the date when the Treasury bill expires.
1598. * @param int price The Treasury bill's price per $100 face value.
1599. * @return float
1600. */
1601. public static function TBILLYIELD($settlement, $maturity, $price) {
1602. $settlement = PHPExcel_Calculation_Functions:: flattenSingleValue($settlement);
1603. $maturity = PHPExcel_Calculation_Functions:: flattenSingleValue($maturity);
1604. $price = PHPExcel_Calculation_Functions:: flattenSingleValue($price);
1606. // Validate
1607. if ( is_numeric($price)) {
1608. if ($price <= 0) {
1609. return PHPExcel_Calculation_Functions:: NaN();
1610. }
1612. if ( PHPExcel_Calculation_Functions:: getCompatibilityMode() == PHPExcel_Calculation_Functions:: COMPATIBILITY_OPENOFFICE) {
1613. ++$maturity;
1614. $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime:: YEARFRAC($settlement, $maturity) * 360;
1615. if (! is_numeric($daysBetweenSettlementAndMaturity)) {
1616. // return date error
1617. return $daysBetweenSettlementAndMaturity;
1618. }
1619. } else {
1620. $daysBetweenSettlementAndMaturity = ( PHPExcel_Calculation_DateTime::_getDateValue($maturity) - PHPExcel_Calculation_DateTime::_getDateValue($settlement));
1621. }
1623. if ($daysBetweenSettlementAndMaturity > 360) {
1624. return PHPExcel_Calculation_Functions:: NaN();
1625. }
1627. return ((100 - $price) / $price) * (360 / $daysBetweenSettlementAndMaturity);
1628. }
1629. return PHPExcel_Calculation_Functions:: VALUE();
1630. } // function TBILLYIELD()
1633. public static function XIRR($values, $dates, $guess = 0.1) {
1634. if ((! is_array($values)) && (! is_array($dates))) return PHPExcel_Calculation_Functions:: VALUE();
1635. $values = PHPExcel_Calculation_Functions:: flattenArray($values);
1636. $dates = PHPExcel_Calculation_Functions:: flattenArray($dates);
1637. $guess = PHPExcel_Calculation_Functions:: flattenSingleValue($guess);
1638. if ( count($values) != count($dates)) return PHPExcel_Calculation_Functions:: NaN();
1640. // create an initial range, with a root somewhere between 0 and guess
1641. $x1 = 0.0;
1642. $x2 = $guess;
1643. $f1 = self:: XNPV($x1, $values, $dates);
1644. $f2 = self:: XNPV($x2, $values, $dates);
1645. for ($i = 0; $i < FINANCIAL_MAX_ITERATIONS; ++$i) {
1646. if (($f1 * $f2) < 0.0) break;
1647. if ( abs($f1) < abs($f2)) {
1648. $f1 = self:: XNPV($x1 += 1.6 * ($x1 - $x2), $values, $dates);
1649. } else {
1650. $f2 = self:: XNPV($x2 += 1.6 * ($x2 - $x1), $values, $dates);
1651. }
1652. }
1653. if (($f1 * $f2) > 0.0) return PHPExcel_Calculation_Functions:: VALUE();
1655. $f = self:: XNPV($x1, $values, $dates);
1656. if ($f < 0.0) {
1657. $rtb = $x1;
1658. $dx = $x2 - $x1;
1659. } else {
1660. $rtb = $x2;
1661. $dx = $x1 - $x2;
1662. }
1664. for ($i = 0; $i < FINANCIAL_MAX_ITERATIONS; ++$i) {
1665. $dx *= 0.5;
1666. $x_mid = $rtb + $dx;
1667. $f_mid = self:: XNPV($x_mid, $values, $dates);
1668. if ($f_mid <= 0.0) $rtb = $x_mid;
1669. if (( abs($f_mid) < FINANCIAL_PRECISION) || ( abs($dx) < FINANCIAL_PRECISION)) return $x_mid;
1670. }
1671. return PHPExcel_Calculation_Functions:: VALUE();
1672. }
1675. /**
1676. * XNPV
1677. *
1678. * Returns the net present value for a schedule of cash flows that is not necessarily periodic.
1679. * To calculate the net present value for a series of cash flows that is periodic, use the NPV function.
1680. *
1681. * Excel Function:
1682. * =XNPV(rate,values,dates)
1683. *
1684. * @param float $rate The discount rate to apply to the cash flows.
1685. * @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.
1686. * @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.
1687. * @return float
1688. */
1689. public static function XNPV($rate, $values, $dates) {
1690. $rate = PHPExcel_Calculation_Functions:: flattenSingleValue($rate);
1691. if (! is_numeric($rate)) return PHPExcel_Calculation_Functions:: VALUE();
1692. if ((! is_array($values)) || (! is_array($dates))) return PHPExcel_Calculation_Functions:: VALUE();
1693. $values = PHPExcel_Calculation_Functions:: flattenArray($values);
1694. $dates = PHPExcel_Calculation_Functions:: flattenArray($dates);
1695. $valCount = count($values);
1696. if ($valCount != count($dates)) return PHPExcel_Calculation_Functions:: NaN();
1697. if (( min($values) > 0) || ( max($values) < 0)) return PHPExcel_Calculation_Functions:: VALUE();
1699. $xnpv = 0.0;
1700. for ($i = 0; $i < $valCount; ++$i) {
1701. if (! is_numeric($values[$i])) return PHPExcel_Calculation_Functions:: VALUE();
1702. $xnpv += $values[$i] / pow(1 + $rate, PHPExcel_Calculation_DateTime:: DATEDIF($dates[0],$dates[$i],'d') / 365);
1703. }
1704. return ( is_finite($xnpv)) ? $xnpv : PHPExcel_Calculation_Functions:: VALUE();
1705. } // function XNPV()
1708. /**
1709. * YIELDDISC
1710. *
1711. * Returns the annual yield of a security that pays interest at maturity.
1712. *
1713. * @param mixed settlement The security's settlement date.
1714. * The security's settlement date is the date after the issue date when the security is traded to the buyer.
1715. * @param mixed maturity The security's maturity date.
1716. * The maturity date is the date when the security expires.
1717. * @param int price The security's price per $100 face value.
1718. * @param int redemption The security's redemption value per $100 face value.
1719. * @param int basis The type of day count to use.
1720. * 0 or omitted US (NASD) 30/360
1721. * 1 Actual/actual
1722. * 2 Actual/360
1723. * 3 Actual/365
1724. * 4 European 30/360
1725. * @return float
1726. */
1727. public static function YIELDDISC($settlement, $maturity, $price, $redemption, $basis=0) {
1728. $settlement = PHPExcel_Calculation_Functions:: flattenSingleValue($settlement);
1729. $maturity = PHPExcel_Calculation_Functions:: flattenSingleValue($maturity);
1730. $price = PHPExcel_Calculation_Functions:: flattenSingleValue($price);
1731. $redemption = PHPExcel_Calculation_Functions:: flattenSingleValue($redemption);
1732. $basis = (int) PHPExcel_Calculation_Functions:: flattenSingleValue($basis);
1734. // Validate
1735. if ( is_numeric($price) && is_numeric($redemption)) {
1736. if (($price <= 0) || ($redemption <= 0)) {
1737. return PHPExcel_Calculation_Functions:: NaN();
1738. }
1739. $daysPerYear = self::_daysPerYear( PHPExcel_Calculation_DateTime:: YEAR($settlement),$basis);
1740. if (! is_numeric($daysPerYear)) {
1741. return $daysPerYear;
1742. }
1743. $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime:: YEARFRAC($settlement, $maturity,$basis);
1744. if (! is_numeric($daysBetweenSettlementAndMaturity)) {
1745. // return date error
1746. return $daysBetweenSettlementAndMaturity;
1747. }
1748. $daysBetweenSettlementAndMaturity *= $daysPerYear;
1750. return (($redemption - $price) / $price) * ($daysPerYear / $daysBetweenSettlementAndMaturity);
1751. }
1752. return PHPExcel_Calculation_Functions:: VALUE();
1753. } // function YIELDDISC()
1756. /**
1757. * YIELDMAT
1758. *
1759. * Returns the annual yield of a security that pays interest at maturity.
1760. *
1761. * @param mixed settlement The security's settlement date.
1762. * The security's settlement date is the date after the issue date when the security is traded to the buyer.
1763. * @param mixed maturity The security's maturity date.
1764. * The maturity date is the date when the security expires.
1765. * @param mixed issue The security's issue date.
1766. * @param int rate The security's interest rate at date of issue.
1767. * @param int price The security's price per $100 face value.
1768. * @param int basis The type of day count to use.
1769. * 0 or omitted US (NASD) 30/360
1770. * 1 Actual/actual
1771. * 2 Actual/360
1772. * 3 Actual/365
1773. * 4 European 30/360
1774. * @return float
1775. */
1776. public static function YIELDMAT($settlement, $maturity, $issue, $rate, $price, $basis=0) {
1777. $settlement = PHPExcel_Calculation_Functions:: flattenSingleValue($settlement);
1778. $maturity = PHPExcel_Calculation_Functions:: flattenSingleValue($maturity);
1779. $issue = PHPExcel_Calculation_Functions:: flattenSingleValue($issue);
1780. $rate = PHPExcel_Calculation_Functions:: flattenSingleValue($rate);
1781. $price = PHPExcel_Calculation_Functions:: flattenSingleValue($price);
1782. $basis = (int) PHPExcel_Calculation_Functions:: flattenSingleValue($basis);
1784. // Validate
1785. if ( is_numeric($rate) && is_numeric($price)) {
1786. if (($rate <= 0) || ($price <= 0)) {
1787. return PHPExcel_Calculation_Functions:: NaN();
1788. }
1789. $daysPerYear = self::_daysPerYear( PHPExcel_Calculation_DateTime:: YEAR($settlement),$basis);
1790. if (! is_numeric($daysPerYear)) {
1791. return $daysPerYear;
1792. }
1793. $daysBetweenIssueAndSettlement = PHPExcel_Calculation_DateTime:: YEARFRAC($issue, $settlement, $basis);
1794. if (! is_numeric($daysBetweenIssueAndSettlement)) {
1795. // return date error
1796. return $daysBetweenIssueAndSettlement;
1797. }
1798. $daysBetweenIssueAndSettlement *= $daysPerYear;
1799. $daysBetweenIssueAndMaturity = PHPExcel_Calculation_DateTime:: YEARFRAC($issue, $maturity, $basis);
1800. if (! is_numeric($daysBetweenIssueAndMaturity)) {
1801. // return date error
1802. return $daysBetweenIssueAndMaturity;
1803. }
1804. $daysBetweenIssueAndMaturity *= $daysPerYear;
1805. $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime:: YEARFRAC($settlement, $maturity, $basis);
1806. if (! is_numeric($daysBetweenSettlementAndMaturity)) {
1807. // return date error
1808. return $daysBetweenSettlementAndMaturity;
1809. }
1810. $daysBetweenSettlementAndMaturity *= $daysPerYear;
1812. return ((1 + (($daysBetweenIssueAndMaturity / $daysPerYear) * $rate) - (($price / 100) + (($daysBetweenIssueAndSettlement / $daysPerYear) * $rate))) /
1813. (($price / 100) + (($daysBetweenIssueAndSettlement / $daysPerYear) * $rate))) *
1814. ($daysPerYear / $daysBetweenSettlementAndMaturity);
1815. }
1816. return PHPExcel_Calculation_Functions:: VALUE();
1817. } // function YIELDMAT()
1819. } // class PHPExcel_Calculation_Financial

Documentation generated on Sun, 27 Feb 2011 16:31:38 -0800 by phpDocumentor 1.4.3