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