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

/common/libraries/plugin/phpexcel/PHPExcel/Calculation/Financial.php

https://bitbucket.org/renaatdemuynck/chamilo
PHP | 2064 lines | 1284 code | 231 blank | 549 comment | 316 complexity | 29cfe0668c547fcf32a0ebce829d0a60 MD5 | raw file
Possible License(s): BSD-3-Clause, LGPL-2.1, LGPL-3.0, GPL-3.0, MIT, GPL-2.0 
    

  1. <?php

    2. 

  2. /**
    3. 

  3.  * PHPExcel
    4. 

  4.  *
    5. 

  5.  * Copyright (c) 2006 - 2011 PHPExcel
    6. 

  6.  *
    7. 

  7.  * This library is free software; you can redistribute it and/or
    8. 

  8.  * modify it under the terms of the GNU Lesser General Public
    9. 

  9.  * License as published by the Free Software Foundation; either
    10. 

  10.  * version 2.1 of the License, or (at your option) any later version.
    11. 

  11.  *
    12. 

  12.  * This library is distributed in the hope that it will be useful,
    13. 

  13.  * but WITHOUT ANY WARRANTY; without even the implied warranty of
    14. 

  14.  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
    15. 

  15.  * Lesser General Public License for more details.
    16. 

  16.  *
    17. 

  17.  * You should have received a copy of the GNU Lesser General Public
    18. 

  18.  * License along with this library; if not, write to the Free Software
    19. 

  19.  * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
    20. 

  20.  *
    21. 

  21.  * @category	PHPExcel
    22. 

  22.  * @package		PHPExcel_Calculation
    23. 

  23.  * @copyright	Copyright (c) 2006 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
    24. 

  24.  * @license		http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt	LGPL
    25. 

  25.  * @version		1.7.6, 2011-02-27
    26. 

  26.  */

    27. 

  27. 

    28. 

  28. /** PHPExcel root directory */

    29. 

  29. if (! defined('PHPEXCEL_ROOT'))

    30. 

  30. {

    31. 

  31.     /**
    32. 

  32.      * @ignore
    33. 

  33.      */

    34. 

  34.     define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');

    35. 

  35.     require (PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');

    36. 

  36. }

    37. 

  37. 

    38. 

  38. /** FINANCIAL_MAX_ITERATIONS */

    39. 

  39. define('FINANCIAL_MAX_ITERATIONS', 128);

    40. 

  40. 

    41. 

  41. /** FINANCIAL_PRECISION */

    42. 

  42. define('FINANCIAL_PRECISION', 1.0e-08);

    43. 

  43. 

    44. 

  44. /**
    45. 

  45.  * PHPExcel_Calculation_Financial
    46. 

  46.  *
    47. 

  47.  * @category	PHPExcel
    48. 

  48.  * @package		PHPExcel_Calculation
    49. 

  49.  * @copyright	Copyright (c) 2006 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
    50. 

  50.  */

    51. 

  51. class PHPExcel_Calculation_Financial

    52. 

  52. {

    53. 

  53. 

    54. 

  54.     private static function _lastDayOfMonth($testDate)

    55. 

  55.     {

    56. 

  56.         $date = clone $testDate;

    57. 

  57.         $date->modify('+1 day');

    58. 

  58.         return ($date->format('d') == 1);

    59. 

  59.     } //	function _lastDayOfMonth()
    60. 

  60. 

    61. 

  61.     

    62. 

  62.     private static function _firstDayOfMonth($testDate)

    63. 

  63.     {

    64. 

  64.         $date = clone $testDate;

    65. 

  65.         return ($date->format('d') == 1);

    66. 

  66.     } //	function _lastDayOfMonth()
    67. 

  67. 

    68. 

  68.     

    69. 

  69.     private static function _coupFirstPeriodDate($settlement, $maturity, $frequency, $next)

    70. 

  70.     {

    71. 

  71.         $months = 12 / $frequency;

    72. 

  72.         

    73. 

  73.         $result = PHPExcel_Shared_Date :: ExcelToPHPObject($maturity);

    74. 

  74.         $eom = self :: _lastDayOfMonth($result);

    75. 

  75.         

    76. 

  76.         while ($settlement < PHPExcel_Shared_Date :: PHPToExcel($result))

    77. 

  77.         {

    78. 

  78.             $result->modify('-' . $months . ' months');

    79. 

  79.         }

    80. 

  80.         if ($next)

    81. 

  81.         {

    82. 

  82.             $result->modify('+' . $months . ' months');

    83. 

  83.         }

    84. 

  84.         

    85. 

  85.         if ($eom)

    86. 

  86.         {

    87. 

  87.             $result->modify('-1 day');

    88. 

  88.         }

    89. 

  89.         

    90. 

  90.         return PHPExcel_Shared_Date :: PHPToExcel($result);

    91. 

  91.     } //	function _coupFirstPeriodDate()
    92. 

  92. 

    93. 

  93.     

    94. 

  94.     private static function _validFrequency($frequency)

    95. 

  95.     {

    96. 

  96.         if (($frequency == 1) || ($frequency == 2) || ($frequency == 4))

    97. 

  97.         {

    98. 

  98.             return true;

    99. 

  99.         }

    100. 

  100.         if ((PHPExcel_Calculation_Functions :: getCompatibilityMode() == PHPExcel_Calculation_Functions :: COMPATIBILITY_GNUMERIC) && (($frequency == 6) || ($frequency == 12)))

    101. 

  101.         {

    102. 

  102.             return true;

    103. 

  103.         }

    104. 

  104.         return false;

    105. 

  105.     } //	function _validFrequency()
    106. 

  106. 

    107. 

  107.     

    108. 

  108.     private static function _daysPerYear($year, $basis)

    109. 

  109.     {

    110. 

  110.         switch ($basis)

    111. 

  111.         {

    112. 

  112.             case 0 :

    113. 

  113.             case 2 :

    114. 

  114.             case 4 :

    115. 

  115.                 $daysPerYear = 360;

    116. 

  116.                 break;

    117. 

  117.             case 3 :

    118. 

  118.                 $daysPerYear = 365;

    119. 

  119.                 break;

    120. 

  120.             case 1 :

    121. 

  121.                 if (PHPExcel_Calculation_DateTime :: _isLeapYear($year))

    122. 

  122.                 {

    123. 

  123.                     $daysPerYear = 366;

    124. 

  124.                 }

    125. 

  125.                 else

    126. 

  126.                 {

    127. 

  127.                     $daysPerYear = 365;

    128. 

  128.                 }

    129. 

  129.                 break;

    130. 

  130.             default :

    131. 

  131.                 return PHPExcel_Calculation_Functions :: NaN();

    132. 

  132.         }

    133. 

  133.         return $daysPerYear;

    134. 

  134.     } //	function _daysPerYear()
    135. 

  135. 

    136. 

  136.     

    137. 

  137.     private static function _interestAndPrincipal($rate = 0, $per = 0, $nper = 0, $pv = 0, $fv = 0, $type = 0)

    138. 

  138.     {

    139. 

  139.         $pmt = self :: PMT($rate, $nper, $pv, $fv, $type);

    140. 

  140.         $capital = $pv;

    141. 

  141.         for($i = 1; $i <= $per; ++ $i)

    142. 

  142.         {

    143. 

  143.             $interest = ($type && $i == 1) ? 0 : - $capital * $rate;

    144. 

  144.             $principal = $pmt - $interest;

    145. 

  145.             $capital += $principal;

    146. 

  146.         }

    147. 

  147.         return array($interest, $principal);

    148. 

  148.     } //	function _interestAndPrincipal()
    149. 

  149. 

    150. 

  150.     

    151. 

  151.     /**
    152. 

  152.      * ACCRINT
    153. 

  153.      *
    154. 

  154.      * Returns the discount rate for a security.
    155. 

  155.      *
    156. 

  156.      * @param	mixed	issue		The security's issue date.
    157. 

  157.      * @param	mixed	firstinter	The security's first interest date.
    158. 

  158.      * @param	mixed	settlement	The security's settlement date.
    159. 

  159.      * @param	float	rate		The security's annual coupon rate.
    160. 

  160.      * @param	float	par			The security's par value.
    161. 

  161.      * @param	int		basis		The type of day count to use.
    162. 

  162.      * 0 or omitted	US (NASD) 30/360
    163. 

  163.      * 1				Actual/actual
    164. 

  164.      * 2				Actual/360
    165. 

  165.      * 3				Actual/365
    166. 

  166.      * 4				European 30/360
    167. 

  167.      * @return	float
    168. 

  168.      */

    169. 

  169.     public static function ACCRINT($issue, $firstinter, $settlement, $rate, $par = 1000, $frequency = 1, $basis = 0)

    170. 

  170.     {

    171. 

  171.         $issue = PHPExcel_Calculation_Functions :: flattenSingleValue($issue);

    172. 

  172.         $firstinter = PHPExcel_Calculation_Functions :: flattenSingleValue($firstinter);

    173. 

  173.         $settlement = PHPExcel_Calculation_Functions :: flattenSingleValue($settlement);

    174. 

  174.         $rate = (float) PHPExcel_Calculation_Functions :: flattenSingleValue($rate);

    175. 

  175.         $par = (is_null($par)) ? 1000 : (float) PHPExcel_Calculation_Functions :: flattenSingleValue($par);

    176. 

  176.         $frequency = (is_null($frequency)) ? 1 : (int) PHPExcel_Calculation_Functions :: flattenSingleValue($frequency);

    177. 

  177.         $basis = (is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions :: flattenSingleValue($basis);

    178. 

  178.         

    179. 

  179.         //	Validate
    180. 

  180.         if ((is_numeric($rate)) && (is_numeric($par)))

    181. 

  181.         {

    182. 

  182.             if (($rate <= 0) || ($par <= 0))

    183. 

  183.             {

    184. 

  184.                 return PHPExcel_Calculation_Functions :: NaN();

    185. 

  185.             }

    186. 

  186.             $daysBetweenIssueAndSettlement = PHPExcel_Calculation_DateTime :: YEARFRAC($issue, $settlement, $basis);

    187. 

  187.             if (! is_numeric($daysBetweenIssueAndSettlement))

    188. 

  188.             {

    189. 

  189.                 //	return date error
    190. 

  190.                 return $daysBetweenIssueAndSettlement;

    191. 

  191.             }

    192. 

  192.             

    193. 

  193.             return $par * $rate * $daysBetweenIssueAndSettlement;

    194. 

  194.         }

    195. 

  195.         return PHPExcel_Calculation_Functions :: VALUE();

    196. 

  196.     } //	function ACCRINT()
    197. 

  197. 

    198. 

  198.     

    199. 

  199.     /**
    200. 

  200.      * ACCRINTM
    201. 

  201.      *
    202. 

  202.      * Returns the discount rate for a security.
    203. 

  203.      *
    204. 

  204.      * @param	mixed	issue		The security's issue date.
    205. 

  205.      * @param	mixed	settlement	The security's settlement date.
    206. 

  206.      * @param	float	rate		The security's annual coupon rate.
    207. 

  207.      * @param	float	par			The security's par value.
    208. 

  208.      * @param	int		basis		The type of day count to use.
    209. 

  209.      * 0 or omitted	US (NASD) 30/360
    210. 

  210.      * 1				Actual/actual
    211. 

  211.      * 2				Actual/360
    212. 

  212.      * 3				Actual/365
    213. 

  213.      * 4				European 30/360
    214. 

  214.      * @return	float
    215. 

  215.      */

    216. 

  216.     public static function ACCRINTM($issue, $settlement, $rate, $par = 1000, $basis = 0)

    217. 

  217.     {

    218. 

  218.         $issue = PHPExcel_Calculation_Functions :: flattenSingleValue($issue);

    219. 

  219.         $settlement = PHPExcel_Calculation_Functions :: flattenSingleValue($settlement);

    220. 

  220.         $rate = (float) PHPExcel_Calculation_Functions :: flattenSingleValue($rate);

    221. 

  221.         $par = (is_null($par)) ? 1000 : (float) PHPExcel_Calculation_Functions :: flattenSingleValue($par);

    222. 

  222.         $basis = (is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions :: flattenSingleValue($basis);

    223. 

  223.         

    224. 

  224.         //	Validate
    225. 

  225.         if ((is_numeric($rate)) && (is_numeric($par)))

    226. 

  226.         {

    227. 

  227.             if (($rate <= 0) || ($par <= 0))

    228. 

  228.             {

    229. 

  229.                 return PHPExcel_Calculation_Functions :: NaN();

    230. 

  230.             }

    231. 

  231.             $daysBetweenIssueAndSettlement = PHPExcel_Calculation_DateTime :: YEARFRAC($issue, $settlement, $basis);

    232. 

  232.             if (! is_numeric($daysBetweenIssueAndSettlement))

    233. 

  233.             {

    234. 

  234.                 //	return date error
    235. 

  235.                 return $daysBetweenIssueAndSettlement;

    236. 

  236.             }

    237. 

  237.             return $par * $rate * $daysBetweenIssueAndSettlement;

    238. 

  238.         }

    239. 

  239.         return PHPExcel_Calculation_Functions :: VALUE();

    240. 

  240.     } //	function ACCRINTM()
    241. 

  241. 

    242. 

  242.     

    243. 

  243.     public static function AMORDEGRC($cost, $purchased, $firstPeriod, $salvage, $period, $rate, $basis = 0)

    244. 

  244.     {

    245. 

  245.         $cost = PHPExcel_Calculation_Functions :: flattenSingleValue($cost);

    246. 

  246.         $purchased = PHPExcel_Calculation_Functions :: flattenSingleValue($purchased);

    247. 

  247.         $firstPeriod = PHPExcel_Calculation_Functions :: flattenSingleValue($firstPeriod);

    248. 

  248.         $salvage = PHPExcel_Calculation_Functions :: flattenSingleValue($salvage);

    249. 

  249.         $period = floor(PHPExcel_Calculation_Functions :: flattenSingleValue($period));

    250. 

  250.         $rate = PHPExcel_Calculation_Functions :: flattenSingleValue($rate);

    251. 

  251.         $basis = (is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions :: flattenSingleValue($basis);

    252. 

  252.         

    253. 

  253.         $fUsePer = 1.0 / $rate;

    254. 

  254.         

    255. 

  255.         if ($fUsePer < 3.0)

    256. 

  256.         {

    257. 

  257.             $amortiseCoeff = 1.0;

    258. 

  258.         }

    259. 

  259.         elseif ($fUsePer < 5.0)

    260. 

  260.         {

    261. 

  261.             $amortiseCoeff = 1.5;

    262. 

  262.         }

    263. 

  263.         elseif ($fUsePer <= 6.0)

    264. 

  264.         {

    265. 

  265.             $amortiseCoeff = 2.0;

    266. 

  266.         }

    267. 

  267.         else

    268. 

  268.         {

    269. 

  269.             $amortiseCoeff = 2.5;

    270. 

  270.         }

    271. 

  271.         

    272. 

  272.         $rate *= $amortiseCoeff;

    273. 

  273.         $fNRate = round(PHPExcel_Calculation_DateTime :: YEARFRAC($purchased, $firstPeriod, $basis) * $rate * $cost, 0);

    274. 

  274.         $cost -= $fNRate;

    275. 

  275.         $fRest = $cost - $salvage;

    276. 

  276.         

    277. 

  277.         for($n = 0; $n < $period; ++ $n)

    278. 

  278.         {

    279. 

  279.             $fNRate = round($rate * $cost, 0);

    280. 

  280.             $fRest -= $fNRate;

    281. 

  281.             

    282. 

  282.             if ($fRest < 0.0)

    283. 

  283.             {

    284. 

  284.                 switch ($period - $n)

    285. 

  285.                 {

    286. 

  286.                     case 0 :

    287. 

  287.                     case 1 :

    288. 

  288.                         return round($cost * 0.5, 0);

    289. 

  289.                         break;

    290. 

  290.                     default :

    291. 

  291.                         return 0.0;

    292. 

  292.                         break;

    293. 

  293.                 }

    294. 

  294.             }

    295. 

  295.             $cost -= $fNRate;

    296. 

  296.         }

    297. 

  297.         return $fNRate;

    298. 

  298.     } //	function AMORDEGRC()
    299. 

  299. 

    300. 

  300.     

    301. 

  301.     public static function AMORLINC($cost, $purchased, $firstPeriod, $salvage, $period, $rate, $basis = 0)

    302. 

  302.     {

    303. 

  303.         $cost = PHPExcel_Calculation_Functions :: flattenSingleValue($cost);

    304. 

  304.         $purchased = PHPExcel_Calculation_Functions :: flattenSingleValue($purchased);

    305. 

  305.         $firstPeriod = PHPExcel_Calculation_Functions :: flattenSingleValue($firstPeriod);

    306. 

  306.         $salvage = PHPExcel_Calculation_Functions :: flattenSingleValue($salvage);

    307. 

  307.         $period = PHPExcel_Calculation_Functions :: flattenSingleValue($period);

    308. 

  308.         $rate = PHPExcel_Calculation_Functions :: flattenSingleValue($rate);

    309. 

  309.         $basis = (is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions :: flattenSingleValue($basis);

    310. 

  310.         

    311. 

  311.         $fOneRate = $cost * $rate;

    312. 

  312.         $fCostDelta = $cost - $salvage;

    313. 

  313.         //	Note, quirky variation for leap years on the YEARFRAC for this function
    314. 

  314.         $purchasedYear = PHPExcel_Calculation_DateTime :: YEAR($purchased);

    315. 

  315.         $yearFrac = PHPExcel_Calculation_DateTime :: YEARFRAC($purchased, $firstPeriod, $basis);

    316. 

  316.         

    317. 

  317.         if (($basis == 1) && ($yearFrac < 1) && (PHPExcel_Calculation_DateTime :: _isLeapYear($purchasedYear)))

    318. 

  318.         {

    319. 

  319.             $yearFrac *= 365 / 366;

    320. 

  320.         }

    321. 

  321.         

    322. 

  322.         $f0Rate = $yearFrac * $rate * $cost;

    323. 

  323.         $nNumOfFullPeriods = intval(($cost - $salvage - $f0Rate) / $fOneRate);

    324. 

  324.         

    325. 

  325.         if ($period == 0)

    326. 

  326.         {

    327. 

  327.             return $f0Rate;

    328. 

  328.         }

    329. 

  329.         elseif ($period <= $nNumOfFullPeriods)

    330. 

  330.         {

    331. 

  331.             return $fOneRate;

    332. 

  332.         }

    333. 

  333.         elseif ($period == ($nNumOfFullPeriods + 1))

    334. 

  334.         {

    335. 

  335.             return ($fCostDelta - $fOneRate * $nNumOfFullPeriods - $f0Rate);

    336. 

  336.         }

    337. 

  337.         else

    338. 

  338.         {

    339. 

  339.             return 0.0;

    340. 

  340.         }

    341. 

  341.     } //	function AMORLINC()
    342. 

  342. 

    343. 

  343.     

    344. 

  344.     public static function COUPDAYBS($settlement, $maturity, $frequency, $basis = 0)

    345. 

  345.     {

    346. 

  346.         $settlement = PHPExcel_Calculation_Functions :: flattenSingleValue($settlement);

    347. 

  347.         $maturity = PHPExcel_Calculation_Functions :: flattenSingleValue($maturity);

    348. 

  348.         $frequency = (int) PHPExcel_Calculation_Functions :: flattenSingleValue($frequency);

    349. 

  349.         $basis = (is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions :: flattenSingleValue($basis);

    350. 

  350.         

    351. 

  351.         if (is_string($settlement = PHPExcel_Calculation_DateTime :: _getDateValue($settlement)))

    352. 

  352.         {

    353. 

  353.             return PHPExcel_Calculation_Functions :: VALUE();

    354. 

  354.         }

    355. 

  355.         if (is_string($maturity = PHPExcel_Calculation_DateTime :: _getDateValue($maturity)))

    356. 

  356.         {

    357. 

  357.             return PHPExcel_Calculation_Functions :: VALUE();

    358. 

  358.         }

    359. 

  359.         

    360. 

  360.         if (($settlement > $maturity) || (! self :: _validFrequency($frequency)) || (($basis < 0) || ($basis > 4)))

    361. 

  361.         {

    362. 

  362.             return PHPExcel_Calculation_Functions :: NaN();

    363. 

  363.         }

    364. 

  364.         

    365. 

  365.         $daysPerYear = self :: _daysPerYear(PHPExcel_Calculation_DateTime :: YEAR($settlement), $basis);

    366. 

  366.         $prev = self :: _coupFirstPeriodDate($settlement, $maturity, $frequency, False);

    367. 

  367.         

    368. 

  368.         return PHPExcel_Calculation_DateTime :: YEARFRAC($prev, $settlement, $basis) * $daysPerYear;

    369. 

  369.     } //	function COUPDAYBS()
    370. 

  370. 

    371. 

  371.     

    372. 

  372.     public static function COUPDAYS($settlement, $maturity, $frequency, $basis = 0)

    373. 

  373.     {

    374. 

  374.         $settlement = PHPExcel_Calculation_Functions :: flattenSingleValue($settlement);

    375. 

  375.         $maturity = PHPExcel_Calculation_Functions :: flattenSingleValue($maturity);

    376. 

  376.         $frequency = (int) PHPExcel_Calculation_Functions :: flattenSingleValue($frequency);

    377. 

  377.         $basis = (is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions :: flattenSingleValue($basis);

    378. 

  378.         

    379. 

  379.         if (is_string($settlement = PHPExcel_Calculation_DateTime :: _getDateValue($settlement)))

    380. 

  380.         {

    381. 

  381.             return PHPExcel_Calculation_Functions :: VALUE();

    382. 

  382.         }

    383. 

  383.         if (is_string($maturity = PHPExcel_Calculation_DateTime :: _getDateValue($maturity)))

    384. 

  384.         {

    385. 

  385.             return PHPExcel_Calculation_Functions :: VALUE();

    386. 

  386.         }

    387. 

  387.         

    388. 

  388.         if (($settlement > $maturity) || (! self :: _validFrequency($frequency)) || (($basis < 0) || ($basis > 4)))

    389. 

  389.         {

    390. 

  390.             return PHPExcel_Calculation_Functions :: NaN();

    391. 

  391.         }

    392. 

  392.         

    393. 

  393.         switch ($basis)

    394. 

  394.         {

    395. 

  395.             case 3 : // Actual/365
    396. 

  396.                 return 365 / $frequency;

    397. 

  397.             case 1 : // Actual/actual
    398. 

  398.                 if ($frequency == 1)

    399. 

  399.                 {

    400. 

  400.                     $daysPerYear = self :: _daysPerYear(PHPExcel_Calculation_DateTime :: YEAR($maturity), $basis);

    401. 

  401.                     return ($daysPerYear / $frequency);

    402. 

  402.                 }

    403. 

  403.                 else

    404. 

  404.                 {

    405. 

  405.                     $prev = self :: _coupFirstPeriodDate($settlement, $maturity, $frequency, False);

    406. 

  406.                     $next = self :: _coupFirstPeriodDate($settlement, $maturity, $frequency, True);

    407. 

  407.                     return ($next - $prev);

    408. 

  408.                 }

    409. 

  409.             default : // US (NASD) 30/360, Actual/360 or European 30/360
    410. 

  410.                 return 360 / $frequency;

    411. 

  411.         }

    412. 

  412.         return PHPExcel_Calculation_Functions :: VALUE();

    413. 

  413.     } //	function COUPDAYS()
    414. 

  414. 

    415. 

  415.     

    416. 

  416.     public static function COUPDAYSNC($settlement, $maturity, $frequency, $basis = 0)

    417. 

  417.     {

    418. 

  418.         $settlement = PHPExcel_Calculation_Functions :: flattenSingleValue($settlement);

    419. 

  419.         $maturity = PHPExcel_Calculation_Functions :: flattenSingleValue($maturity);

    420. 

  420.         $frequency = (int) PHPExcel_Calculation_Functions :: flattenSingleValue($frequency);

    421. 

  421.         $basis = (is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions :: flattenSingleValue($basis);

    422. 

  422.         

    423. 

  423.         if (is_string($settlement = PHPExcel_Calculation_DateTime :: _getDateValue($settlement)))

    424. 

  424.         {

    425. 

  425.             return PHPExcel_Calculation_Functions :: VALUE();

    426. 

  426.         }

    427. 

  427.         if (is_string($maturity = PHPExcel_Calculation_DateTime :: _getDateValue($maturity)))

    428. 

  428.         {

    429. 

  429.             return PHPExcel_Calculation_Functions :: VALUE();

    430. 

  430.         }

    431. 

  431.         

    432. 

  432.         if (($settlement > $maturity) || (! self :: _validFrequency($frequency)) || (($basis < 0) || ($basis > 4)))

    433. 

  433.         {

    434. 

  434.             return PHPExcel_Calculation_Functions :: NaN();

    435. 

  435.         }

    436. 

  436.         

    437. 

  437.         $daysPerYear = self :: _daysPerYear(PHPExcel_Calculation_DateTime :: YEAR($settlement), $basis);

    438. 

  438.         $next = self :: _coupFirstPeriodDate($settlement, $maturity, $frequency, True);

    439. 

  439.         

    440. 

  440.         return PHPExcel_Calculation_DateTime :: YEARFRAC($settlement, $next, $basis) * $daysPerYear;

    441. 

  441.     } //	function COUPDAYSNC()
    442. 

  442. 

    443. 

  443.     

    444. 

  444.     public static function COUPNCD($settlement, $maturity, $frequency, $basis = 0)

    445. 

  445.     {

    446. 

  446.         $settlement = PHPExcel_Calculation_Functions :: flattenSingleValue($settlement);

    447. 

  447.         $maturity = PHPExcel_Calculation_Functions :: flattenSingleValue($maturity);

    448. 

  448.         $frequency = (int) PHPExcel_Calculation_Functions :: flattenSingleValue($frequency);

    449. 

  449.         $basis = (is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions :: flattenSingleValue($basis);

    450. 

  450.         

    451. 

  451.         if (is_string($settlement = PHPExcel_Calculation_DateTime :: _getDateValue($settlement)))

    452. 

  452.         {

    453. 

  453.             return PHPExcel_Calculation_Functions :: VALUE();

    454. 

  454.         }

    455. 

  455.         if (is_string($maturity = PHPExcel_Calculation_DateTime :: _getDateValue($maturity)))

    456. 

  456.         {

    457. 

  457.             return PHPExcel_Calculation_Functions :: VALUE();

    458. 

  458.         }

    459. 

  459.         

    460. 

  460.         if (($settlement > $maturity) || (! self :: _validFrequency($frequency)) || (($basis < 0) || ($basis > 4)))

    461. 

  461.         {

    462. 

  462.             return PHPExcel_Calculation_Functions :: NaN();

    463. 

  463.         }

    464. 

  464.         

    465. 

  465.         return self :: _coupFirstPeriodDate($settlement, $maturity, $frequency, True);

    466. 

  466.     } //	function COUPNCD()
    467. 

  467. 

    468. 

  468.     

    469. 

  469.     public static function COUPNUM($settlement, $maturity, $frequency, $basis = 0)

    470. 

  470.     {

    471. 

  471.         $settlement = PHPExcel_Calculation_Functions :: flattenSingleValue($settlement);

    472. 

  472.         $maturity = PHPExcel_Calculation_Functions :: flattenSingleValue($maturity);

    473. 

  473.         $frequency = (int) PHPExcel_Calculation_Functions :: flattenSingleValue($frequency);

    474. 

  474.         $basis = (is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions :: flattenSingleValue($basis);

    475. 

  475.         

    476. 

  476.         if (is_string($settlement = PHPExcel_Calculation_DateTime :: _getDateValue($settlement)))

    477. 

  477.         {

    478. 

  478.             return PHPExcel_Calculation_Functions :: VALUE();

    479. 

  479.         }

    480. 

  480.         if (is_string($maturity = PHPExcel_Calculation_DateTime :: _getDateValue($maturity)))

    481. 

  481.         {

    482. 

  482.             return PHPExcel_Calculation_Functions :: VALUE();

    483. 

  483.         }

    484. 

  484.         

    485. 

  485.         if (($settlement > $maturity) || (! self :: _validFrequency($frequency)) || (($basis < 0) || ($basis > 4)))

    486. 

  486.         {

    487. 

  487.             return PHPExcel_Calculation_Functions :: NaN();

    488. 

  488.         }

    489. 

  489.         

    490. 

  490.         $settlement = self :: _coupFirstPeriodDate($settlement, $maturity, $frequency, True);

    491. 

  491.         $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime :: YEARFRAC($settlement, $maturity, $basis) * 365;

    492. 

  492.         

    493. 

  493.         switch ($frequency)

    494. 

  494.         {

    495. 

  495.             case 1 : // annual payments
    496. 

  496.                 return ceil($daysBetweenSettlementAndMaturity / 360);

    497. 

  497.             case 2 : // half-yearly
    498. 

  498.                 return ceil($daysBetweenSettlementAndMaturity / 180);

    499. 

  499.             case 4 : // quarterly
    500. 

  500.                 return ceil($daysBetweenSettlementAndMaturity / 90);

    501. 

  501.             case 6 : // bimonthly
    502. 

  502.                 return ceil($daysBetweenSettlementAndMaturity / 60);

    503. 

  503.             case 12 : // monthly
    504. 

  504.                 return ceil($daysBetweenSettlementAndMaturity / 30);

    505. 

  505.         }

    506. 

  506.         return PHPExcel_Calculation_Functions :: VALUE();

    507. 

  507.     } //	function COUPNUM()
    508. 

  508. 

    509. 

  509.     

    510. 

  510.     public static function COUPPCD($settlement, $maturity, $frequency, $basis = 0)

    511. 

  511.     {

    512. 

  512.         $settlement = PHPExcel_Calculation_Functions :: flattenSingleValue($settlement);

    513. 

  513.         $maturity = PHPExcel_Calculation_Functions :: flattenSingleValue($maturity);

    514. 

  514.         $frequency = (int) PHPExcel_Calculation_Functions :: flattenSingleValue($frequency);

    515. 

  515.         $basis = (is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions :: flattenSingleValue($basis);

    516. 

  516.         

    517. 

  517.         if (is_string($settlement = PHPExcel_Calculation_DateTime :: _getDateValue($settlement)))

    518. 

  518.         {

    519. 

  519.             return PHPExcel_Calculation_Functions :: VALUE();

    520. 

  520.         }

    521. 

  521.         if (is_string($maturity = PHPExcel_Calculation_DateTime :: _getDateValue($maturity)))

    522. 

  522.         {

    523. 

  523.             return PHPExcel_Calculation_Functions :: VALUE();

    524. 

  524.         }

    525. 

  525.         

    526. 

  526.         if (($settlement > $maturity) || (! self :: _validFrequency($frequency)) || (($basis < 0) || ($basis > 4)))

    527. 

  527.         {

    528. 

  528.             return PHPExcel_Calculation_Functions :: NaN();

    529. 

  529.         }

    530. 

  530.         

    531. 

  531.         return self :: _coupFirstPeriodDate($settlement, $maturity, $frequency, False);

    532. 

  532.     } //	function COUPPCD()
    533. 

  533. 

    534. 

  534.     

    535. 

  535.     /**
    536. 

  536.      * CUMIPMT
    537. 

  537.      *
    538. 

  538.      * Returns the cumulative interest paid on a loan between start_period and end_period.
    539. 

  539.      *
    540. 

  540.      * @param	float	$rate	Interest rate per period
    541. 

  541.      * @param	int		$nper	Number of periods
    542. 

  542.      * @param	float	$pv		Present Value
    543. 

  543.      * @param	int		start	The first period in the calculation.
    544. 

  544.      * Payment periods are numbered beginning with 1.
    545. 

  545.      * @param	int		end		The last period in the calculation.
    546. 

  546.      * @param	int		$type	Payment type: 0 = at the end of each period, 1 = at the beginning of each period
    547. 

  547.      * @return	float
    548. 

  548.      */

    549. 

  549.     public static function CUMIPMT($rate, $nper, $pv, $start, $end, $type = 0)

    550. 

  550.     {

    551. 

  551.         $rate = PHPExcel_Calculation_Functions :: flattenSingleValue($rate);

    552. 

  552.         $nper = (int) PHPExcel_Calculation_Functions :: flattenSingleValue($nper);

    553. 

  553.         $pv = PHPExcel_Calculation_Functions :: flattenSingleValue($pv);

    554. 

  554.         $start = (int) PHPExcel_Calculation_Functions :: flattenSingleValue($start);

    555. 

  555.         $end = (int) PHPExcel_Calculation_Functions :: flattenSingleValue($end);

    556. 

  556.         $type = (int) PHPExcel_Calculation_Functions :: flattenSingleValue($type);

    557. 

  557.         

    558. 

  558.         // Validate parameters
    559. 

  559.         if ($type != 0 && $type != 1)

    560. 

  560.         {

    561. 

  561.             return PHPExcel_Calculation_Functions :: NaN();

    562. 

  562.         }

    563. 

  563.         if ($start < 1 || $start > $end)

    564. 

  564.         {

    565. 

  565.             return PHPExcel_Calculation_Functions :: VALUE();

    566. 

  566.         }

    567. 

  567.         

    568. 

  568.         // Calculate
    569. 

  569.         $interest = 0;

    570. 

  570.         for($per = $start; $per <= $end; ++ $per)

    571. 

  571.         {

    572. 

  572.             $interest += self :: IPMT($rate, $per, $nper, $pv, 0, $type);

    573. 

  573.         }

    574. 

  574.         

    575. 

  575.         return $interest;

    576. 

  576.     } //	function CUMIPMT()
    577. 

  577. 

    578. 

  578.     

    579. 

  579.     /**
    580. 

  580.      * CUMPRINC
    581. 

  581.      *
    582. 

  582.      * Returns the cumulative principal paid on a loan between start_period and end_period.
    583. 

  583.      *
    584. 

  584.      * @param	float	$rate	Interest rate per period
    585. 

  585.      * @param	int		$nper	Number of periods
    586. 

  586.      * @param	float	$pv		Present Value
    587. 

  587.      * @param	int		start	The first period in the calculation.
    588. 

  588.      * Payment periods are numbered beginning with 1.
    589. 

  589.      * @param	int		end		The last period in the calculation.
    590. 

  590.      * @param	int		$type	Payment type: 0 = at the end of each period, 1 = at the beginning of each period
    591. 

  591.      * @return	float
    592. 

  592.      */

    593. 

  593.     public static function CUMPRINC($rate, $nper, $pv, $start, $end, $type = 0)

    594. 

  594.     {

    595. 

  595.         $rate = PHPExcel_Calculation_Functions :: flattenSingleValue($rate);

    596. 

  596.         $nper = (int) PHPExcel_Calculation_Functions :: flattenSingleValue($nper);

    597. 

  597.         $pv = PHPExcel_Calculation_Functions :: flattenSingleValue($pv);

    598. 

  598.         $start = (int) PHPExcel_Calculation_Functions :: flattenSingleValue($start);

    599. 

  599.         $end = (int) PHPExcel_Calculation_Functions :: flattenSingleValue($end);

    600. 

  600.         $type = (int) PHPExcel_Calculation_Functions :: flattenSingleValue($type);

    601. 

  601.         

    602. 

  602.         // Validate parameters
    603. 

  603.         if ($type != 0 && $type != 1)

    604. 

  604.         {

    605. 

  605.             return PHPExcel_Calculation_Functions :: NaN();

    606. 

  606.         }

    607. 

  607.         if ($start < 1 || $start > $end)

    608. 

  608.         {

    609. 

  609.             return PHPExcel_Calculation_Functions :: VALUE();

    610. 

  610.         }

    611. 

  611.         

    612. 

  612.         // Calculate
    613. 

  613.         $principal = 0;

    614. 

  614.         for($per = $start; $per <= $end; ++ $per)

    615. 

  615.         {

    616. 

  616.             $principal += self :: PPMT($rate, $per, $nper, $pv, 0, $type);

    617. 

  617.         }

    618. 

  618.         

    619. 

  619.         return $principal;

    620. 

  620.     } //	function CUMPRINC()
    621. 

  621. 

    622. 

  622.     

    623. 

  623.     /**
    624. 

  624.      * DB
    625. 

  625.      *
    626. 

  626.      * Returns the depreciation of an asset for a specified period using the fixed-declining balance method.
    627. 

  627.      * This form of depreciation is used if you want to get a higher depreciation value at the beginning of the depreciation
    628. 

  628.      * (as opposed to linear depreciation). The depreciation value is reduced with every depreciation period by the
    629. 

  629.      * depreciation already deducted from the initial cost.
    630. 

  630.      *
    631. 

  631.      * @param	float	cost		Initial cost of the asset.
    632. 

  632.      * @param	float	salvage		Value at the end of the depreciation. (Sometimes called the salvage value of the asset)
    633. 

  633.      * @param	int		life		Number of periods over which the asset is depreciated. (Sometimes called the useful life of the asset)
    634. 

  634.      * @param	int		period		The period for which you want to calculate the depreciation. Period must use the same units as life.
    635. 

  635.      * @param	float	month		Number of months in the first year. If month is omitted, it defaults to 12.
    636. 

  636.      * @return	float
    637. 

  637.      */

    638. 

  638.     public static function DB($cost, $salvage, $life, $period, $month = 12)

    639. 

  639.     {

    640. 

  640.         $cost = (float) PHPExcel_Calculation_Functions :: flattenSingleValue($cost);

    641. 

  641.         $salvage = (float) PHPExcel_Calculation_Functions :: flattenSingleValue($salvage);

    642. 

  642.         $life = (int) PHPExcel_Calculation_Functions :: flattenSingleValue($life);

    643. 

  643.         $period = (int) PHPExcel_Calculation_Functions :: flattenSingleValue($period);

    644. 

  644.         $month = (int) PHPExcel_Calculation_Functions :: flattenSingleValue($month);

    645. 

  645.         

    646. 

  646.         //	Validate
    647. 

  647.         if ((is_numeric($cost)) && (is_numeric($salvage)) && (is_numeric($life)) && (is_numeric($period)) && (is_numeric($month)))

    648. 

  648.         {

    649. 

  649.             if ($cost == 0)

    650. 

  650.             {

    651. 

  651.                 return 0.0;

    652. 

  652.             }

    653. 

  653.             elseif (($cost < 0) || (($salvage / $cost) < 0) || ($life <= 0) || ($period < 1) || ($month < 1))

    654. 

  654.             {

    655. 

  655.                 return PHPExcel_Calculation_Functions :: NaN();

    656. 

  656.             }

    657. 

  657.             //	Set Fixed Depreciation Rate
    658. 

  658.             $fixedDepreciationRate = 1 - pow(($salvage / $cost), (1 / $life));

    659. 

  659.             $fixedDepreciationRate = round($fixedDepreciationRate, 3);

    660. 

  660.             

    661. 

  661.             //	Loop through each period calculating the depreciation
    662. 

  662.             $previousDepreciation = 0;

    663. 

  663.             for($per = 1; $per <= $period; ++ $per)

    664. 

  664.             {

    665. 

  665.                 if ($per == 1)

    666. 

  666.                 {

    667. 

  667.                     $depreciation = $cost * $fixedDepreciationRate * $month / 12;

    668. 

  668.                 }

    669. 

  669.                 elseif ($per == ($life + 1))

    670. 

  670.                 {

    671. 

  671.                     $depreciation = ($cost - $previousDepreciation) * $fixedDepreciationRate * (12 - $month) / 12;

    672. 

  672.                 }

    673. 

  673.                 else

    674. 

  674.                 {

    675. 

  675.                     $depreciation = ($cost - $previousDepreciation) * $fixedDepreciationRate;

    676. 

  676.                 }

    677. 

  677.                 $previousDepreciation += $depreciation;

    678. 

  678.             }

    679. 

  679.             if (PHPExcel_Calculation_Functions :: getCompatibilityMode() == PHPExcel_Calculation_Functions :: COMPATIBILITY_GNUMERIC)

    680. 

  680.             {

    681. 

  681.                 $depreciation = round($depreciation, 2);

    682. 

  682.             }

    683. 

  683.             return $depreciation;

    684. 

  684.         }

    685. 

  685.         return PHPExcel_Calculation_Functions :: VALUE();

    686. 

  686.     } //	function DB()
    687. 

  687. 

    688. 

  688.     

    689. 

  689.     /**
    690. 

  690.      * DDB
    691. 

  691.      *
    692. 

  692.      * Returns the depreciation of an asset for a specified period using the double-declining balance method or some other method you specify.
    693. 

  693.      *
    694. 

  694.      * @param	float	cost		Initial cost of the asset.
    695. 

  695.      * @param	float	salvage		Value at the end of the depreciation. (Sometimes called the salvage value of the asset)
    696. 

  696.      * @param	int		life		Number of periods over which the asset is depreciated. (Sometimes called the useful life of the asset)
    697. 

  697.      * @param	int		period		The period for which you want to calculate the depreciation. Period must use the same units as life.
    698. 

  698.      * @param	float	factor		The rate at which the balance declines.
    699. 

  699.      * If factor is omitted, it is assumed to be 2 (the double-declining balance method).
    700. 

  700.      * @return	float
    701. 

  701.      */

    702. 

  702.     public static function DDB($cost, $salvage, $life, $period, $factor = 2.0)

    703. 

  703.     {

    704. 

  704.         $cost = (float) PHPExcel_Calculation_Functions :: flattenSingleValue($cost);

    705. 

  705.         $salvage = (float) PHPExcel_Calculation_Functions :: flattenSingleValue($salvage);

    706. 

  706.         $life = (int) PHPExcel_Calculation_Functions :: flattenSingleValue($life);

    707. 

  707.         $period = (int) PHPExcel_Calculation_Functions :: flattenSingleValue($period);

    708. 

  708.         $factor = (float) PHPExcel_Calculation_Functions :: flattenSingleValue($factor);

    709. 

  709.         

    710. 

  710.         //	Validate
    711. 

  711.         if ((is_numeric($cost)) && (is_numeric($salvage)) && (is_numeric($life)) && (is_numeric($period)) && (is_numeric($factor)))

    712. 

  712.         {

    713. 

  713.             if (($cost <= 0) || (($salvage / $cost) < 0) || ($life <= 0) || ($period < 1) || ($factor <= 0.0) || ($period > $life))

    714. 

  714.             {

    715. 

  715.                 return PHPExcel_Calculation_Functions :: NaN();

    716. 

  716.             }

    717. 

  717.             //	Set Fixed Depreciation Rate
    718. 

  718.             $fixedDepreciationRate = 1 - pow(($salvage / $cost), (1 / $life));

    719. 

  719.             $fixedDepreciationRate = round($fixedDepreciationRate, 3);

    720. 

  720.             

    721. 

  721.             //	Loop through each period calculating the depreciation
    722. 

  722.             $previousDepreciation = 0;

    723. 

  723.             for($per = 1; $per <= $period; ++ $per)

    724. 

  724.             {

    725. 

  725.                 $depreciation = min(($cost - $previousDepreciation) * ($factor / $life), ($cost - $salvage - $previousDepreciation));

    726. 

  726.                 $previousDepreciation += $depreciation;

    727. 

  727.             }

    728. 

  728.             if (PHPExcel_Calculation_Functions :: getCompatibilityMode() == PHPExcel_Calculation_Functions :: COMPATIBILITY_GNUMERIC)

    729. 

  729.             {

    730. 

  730.                 $depreciation = round($depreciation, 2);

    731. 

  731.             }

    732. 

  732.             return $depreciation;

    733. 

  733.         }

    734. 

  734.         return PHPExcel_Calculation_Functions :: VALUE();

    735. 

  735.     } //	function DDB()
    736. 

  736. 

    737. 

  737.     

    738. 

  738.     /**
    739. 

  739.      * DISC
    740. 

  740.      *
    741. 

  741.      * Returns the discount rate for a security.
    742. 

  742.      *
    743. 

  743.      * @param	mixed	settlement	The security's settlement date.
    744. 

  744.      * The security settlement date is the date after the issue date when the security is traded to the buyer.
    745. 

  745.      * @param	mixed	maturity	The security's maturity date.
    746. 

  746.      * The maturity date is the date when the security expires.
    747. 

  747.      * @param	int		price		The security's price per $100 face value.
    748. 

  748.      * @param	int		redemption	the security's redemption value per $100 face value.
    749. 

  749.      * @param	int		basis		The type of day count to use.
    750. 

  750.      * 0 or omitted	US (NASD) 30/360
    751. 

  751.      * 1				Actual/actual
    752. 

  752.      * 2				Actual/360
    753. 

  753.      * 3				Actual/365
    754. 

  754.      * 4				European 30/360
    755. 

  755.      * @return	float
    756. 

  756.      */

    757. 

  757.     public static function DISC($settlement, $maturity, $price, $redemption, $basis = 0)

    758. 

  758.     {

    759. 

  759.         $settlement = PHPExcel_Calculation_Functions :: flattenSingleValue($settlement);

    760. 

  760.         $maturity = PHPExcel_Calculation_Functions :: flattenSingleValue($maturity);

    761. 

  761.         $price = (float) PHPExcel_Calculation_Functions :: flattenSingleValue($price);

    762. 

  762.         $redemption = (float) PHPExcel_Calculation_Functions :: flattenSingleValue($redemption);

    763. 

  763.         $basis = (int) PHPExcel_Calculation_Functions :: flattenSingleValue($basis);

    764. 

  764.         

    765. 

  765.         //	Validate
    766. 

  766.         if ((is_numeric($price)) && (is_numeric($redemption)) && (is_numeric($basis)))

    767. 

  767.         {

    768. 

  768.             if (($price <= 0) || ($redemption <= 0))

    769. 

  769.             {

    770. 

  770.                 return PHPExcel_Calculation_Functions :: NaN();

    771. 

  771.             }

    772. 

  772.             $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime :: YEARFRAC($settlement, $maturity, $basis);

    773. 

  773.             if (! is_numeric($daysBetweenSettlementAndMaturity))

    774. 

  774.             {

    775. 

  775.                 //	return date error
    776. 

  776.                 return $daysBetweenSettlementAndMaturity;

    777. 

  777.             }

    778. 

  778.             

    779. 

  779.             return ((1 - $price / $redemption) / $daysBetweenSettlementAndMaturity);

    780. 

  780.         }

    781. 

  781.         return PHPExcel_Calculation_Functions :: VALUE();

    782. 

  782.     } //	function DISC()
    783. 

  783. 

    784. 

  784.     

    785. 

  785.     /**
    786. 

  786.      * DOLLARDE
    787. 

  787.      *
    788. 

  788.      * Converts a dollar price expressed as an integer part and a fraction part into a dollar price expressed as a decimal number.
    789. 

  789.      * Fractional dollar numbers are sometimes used for security prices.
    790. 

  790.      *
    791. 

  791.      * @param	float	$fractional_dollar	Fractional Dollar
    792. 

  792.      * @param	int		$fraction			Fraction
    793. 

  793.      * @return	float
    794. 

  794.      */

    795. 

  795.     public static function DOLLARDE($fractional_dollar = Null, $fraction = 0)

    796. 

  796.     {

    797. 

  797.         $fractional_dollar = PHPExcel_Calculation_Functions :: flattenSingleValue($fractional_dollar);

    798. 

  798.         $fraction = (int) PHPExcel_Calculation_Functions :: flattenSingleValue($fraction);

    799. 

  799.         

    800. 

  800.         // Validate parameters
    801. 

  801.         if (is_null($fractional_dollar) || $fraction < 0)

    802. 

  802.         {

    803. 

  803.             return PHPExcel_Calculation_Functions :: NaN();

    804. 

  804.         }

    805. 

  805.         if ($fraction == 0)

    806. 

  806.         {

    807. 

  807.             return PHPExcel_Calculation_Functions :: DIV0();

    808. 

  808.         }

    809. 

  809.         

    810. 

  810.         $dollars = floor($fractional_dollar);

    811. 

  811.         $cents = fmod($fractional_dollar, 1);

    812. 

  812.         $cents /= $fraction;

    813. 

  813.         $cents *= pow(10, ceil(log10($fraction)));

    814. 

  814.         return $dollars + $cents;

    815. 

  815.     } //	function DOLLARDE()
    816. 

  816. 

    817. 

  817.     

    818. 

  818.     /**
    819. 

  819.      * DOLLARFR
    820. 

  820.      *
    821. 

  821.      * Converts a dollar price expressed as a decimal number into a dollar price expressed as a fraction.
    822. 

  822.      * Fractional dollar numbers are sometimes used for security prices.
    823. 

  823.      *
    824. 

  824.      * @param	float	$decimal_dollar		Decimal Dollar
    825. 

  825.      * @param	int		$fraction			Fraction
    826. 

  826.      * @return	float
    827. 

  827.      */

    828. 

  828.     public static function DOLLARFR($decimal_dollar = Null, $fraction = 0)

    829. 

  829.     {

    830. 

  830.         $decimal_dollar = PHPExcel_Calculation_Functions :: flattenSingleValue($decimal_dollar);

    831. 

  831.         $fraction = (int) PHPExcel_Calculation_Functions :: flattenSingleValue($fraction);

    832. 

  832.         

    833. 

  833.         // Validate parameters
    834. 

  834.         if (is_null($decimal_dollar) || $fraction < 0)

    835. 

  835.         {

    836. 

  836.             return PHPExcel_Calculation_Functions :: NaN();

    837. 

  837.         }

    838. 

  838.         if ($fraction == 0)

    839. 

  839.         {

    840. 

  840.             return PHPExcel_Calculation_Functions :: DIV0();

    841. 

  841.         }

    842. 

  842.         

    843. 

  843.         $dollars = floor($decimal_dollar);

    844. 

  844.         $cents = fmod($decimal_dollar, 1);

    845. 

  845.         $cents *= $fraction;

    846. 

  846.         $cents *= pow(10, - ceil(log10($fraction)));

    847. 

  847.         return $dollars + $cents;

    848. 

  848.     } //	function DOLLARFR()
    849. 

  849. 

    850. 

  850.     

    851. 

  851.     /**
    852. 

  852.      * EFFECT
    853. 

  853.      *
    854. 

  854.      * Returns the effective interest rate given the nominal rate and the number of compounding payments per year.
    855. 

  855.      *
    856. 

  856.      * @param	float	$nominal_rate		Nominal interest rate
    857. 

  857.      * @param	int		$npery				Number of compounding payments per year
    858. 

  858.      * @return	float
    859. 

  859.      */

    860. 

  860.     public static function EFFECT($nominal_rate = 0, $npery = 0)

    861. 

  861.     {

    862. 

  862.         $nominal_rate = PHPExcel_Calculation_Functions :: flattenSingleValue($nominal_rate);

    863. 

  863.         $npery = (int) PHPExcel_Calculation_Functions :: flattenSingleValue($npery);

    864. 

  864.         

    865. 

  865.         // Validate parameters
    866. 

  866.         if ($nominal_rate <= 0 || $npery < 1)

    867. 

  867.         {

    868. 

  868.             return PHPExcel_Calculation_Functions :: NaN();

    869. 

  869.         }

    870. 

  870.         

    871. 

  871.         return pow((1 + $nominal_rate / $npery), $npery) - 1;

    872. 

  872.     } //	function EFFECT()
    873. 

  873. 

    874. 

  874.     

    875. 

  875.     /**
    876. 

  876.      * FV
    877. 

  877.      *
    878. 

  878.      * Returns the Future Value of a cash flow with constant payments and interest rate (annuities).
    879. 

  879.      *
    880. 

  880.      * @param	float	$rate	Interest rate per period
    881. 

  881.      * @param	int		$nper	Number of periods
    882. 

  882.      * @param	float	$pmt	Periodic payment (annuity)
    883. 

  883.      * @param	float	$pv		Present Value
    884. 

  884.      * @param	int		$type	Payment type: 0 = at the end of each period, 1 = at the beginning of each period
    885. 

  885.      * @return	float
    886. 

  886.      */

    887. 

  887.     public static function FV($rate = 0, $nper = 0, $pmt = 0, $pv = 0, $type = 0)

    888. 

  888.     {

    889. 

  889.         $rate = PHPExcel_Calculation_Functions :: flattenSingleValue($rate);

    890. 

  890.         $nper = PHPExcel_Calculation_Functions :: flattenSingleValue($nper);

    891. 

  891.         $pmt = PHPExcel_Calculation_Functions :: flattenSingleValue($pmt);

    892. 

  892.         $pv = PHPExcel_Calculation_Functions :: flattenSingleValue($pv);

    893. 

  893.         $type = PHPExcel_Calculation_Functions :: flattenSingleValue($type);

    894. 

  894.         

    895. 

  895.         // Validate parameters
    896. 

  896.         if ($type != 0 && $type != 1)

    897. 

  897.         {

    898. 

  898.             return PHPExcel_Calculation_Functions :: NaN();

    899. 

  899.         }

    900. 

  900.         

    901. 

  901.         // Calculate
    902. 

  902.         if (! is_null($rate) && $rate != 0)

    903. 

  903.         {

    904. 

  904.             return - $pv * pow(1 + $rate, $nper) - $pmt * (1 + $rate * $type) * (pow(1 + $rate, $nper) - 1) / $rate;

    905. 

  905.         }

    906. 

  906.         else

    907. 

  907.         {

    908. 

  908.             return - $pv - $pmt * $nper;

    909. 

  909.         }

    910. 

  910.     } //	function FV()
    911. 

  911. 

    912. 

  912.     

    913. 

  913.     /**
    914. 

  914.      * FVSCHEDULE
    915. 

  915.      *
    916. 

  916.      */

    917. 

  917.     public static function FVSCHEDULE($principal, $schedule)

    918. 

  918.     {

    919. 

  919.         $principal = PHPExcel_Calculation_Functions :: flattenSingleValue($principal);

    920. 

  920.         $schedule = PHPExcel_Calculation_Functions :: flattenArray($schedule);

    921. 

  921.         

    922. 

  922.         foreach ($schedule as $n)

    923. 

  923.         {

    924. 

  924.             $principal *= 1 + $n;

    925. 

  925.         }

    926. 

  926.         

    927. 

  927.         return $principal;

    928. 

  928.     } //	function FVSCHEDULE()
    929. 

  929. 

    930. 

  930.     

    931. 

  931.     /**
    932. 

  932.      * INTRATE
    933. 

  933.      *
    934. 

  934.      * Returns the interest rate for a fully invested security.
    935. 

  935.      *
    936. 

  936.      * @param	mixed	settlement	The security's settlement date.
    937. 

  937.      * The security settlement date is the date after the issue date when the security is traded to the buyer.
    938. 

  938.      * @param	mixed	maturity	The security's maturity date.
    939. 

  939.      * The maturity date is the date when the security expires.
    940. 

  940.      * @param	int		investment	The amount invested in the security.
    941. 

  941.      * @param	int		redemption	The amount to be received at maturity.
    942. 

  942.      * @param	int		basis		The type of day count to use.
    943. 

  943.      * 0 or omitted	US (NASD) 30/360
    944. 

  944.      * 1				Actual/actual
    945. 

  945.      * 2				Actual/360
    946. 

  946.      * 3				Actual/365
    947. 

  947.      * 4				European 30/360
    948. 

  948.      * @return	float
    949. 

  949.      */

    950. 

  950.     public static function INTRATE($settlement, $maturity, $investment, $redemption, $basis = 0)

    951. 

  951.     {

    952. 

  952.         $settlement = PHPExcel_Calculation_Functions :: flattenSingleValue($settlement);

    953. 

  953.         $maturity = PHPExcel_Calculation_Functions :: flattenSingleValue($maturity);

    954. 

  954.         $investment = (float) PHPExcel_Calculation_Functions :: flattenSingleValue($investment);

    955. 

  955.         $redemption = (float) PHPExcel_Calculation_Functions :: flattenSingleValue($redemption);

    956. 

  956.         $basis = (int) PHPExcel_Calculation_Functions :: flattenSingleValue($basis);

    957. 

  957.         

    958. 

  958.         //	Validate
    959. 

  959.         if ((is_numeric($investment)) && (is_numeric($redemption)) && (is_numeric($basis)))

    960. 

  960.         {

    961. 

  961.             if (($investment <= 0) || ($redemption <= 0))

    962. 

  962.             {

    963. 

  963.                 return PHPExcel_Calculation_Functions :: NaN();

    964. 

  964.             }

    965. 

  965.             $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime :: YEARFRAC($settlement, $maturity, $basis);

    966. 

  966.             if (! is_numeric($daysBetweenSettlementAndMaturity))

    967. 

  967.             {

    968. 

  968.                 //	return date error
    969. 

  969.                 return $daysBetweenSettlementAndMaturity;

    970. 

  970.             }

    971. 

  971.             

    972. 

  972.             return (($redemption / $investment) - 1) / ($daysBetweenSettlementAndMaturity);

    973. 

  973.         }

    974. 

  974.         return PHPExcel_Calculation_Functions :: VALUE();

    975. 

  975.     } //	function INTRATE()
    976. 

  976. 

    977. 

  977.     

    978. 

  978.     /**
    979. 

  979.      * IPMT
    980. 

  980.      *
    981. 

  981.      * Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate.
    982. 

  982.      *
    983. 

  983.      * @param	float	$rate	Interest rate per period
    984. 

  984.      * @param	int		$per	Period for which we want to find the interest
    985. 

  985.      * @param	int		$nper	Number of periods
    986. 

  986.      * @param	float	$pv		Present Value
    987. 

  987.      * @param	float	$fv		Future Value
    988. 

  988.      * @param	int		$type	Payment type: 0 = at the end of each period, 1 = at the beginning of each period
    989. 

  989.      * @return	float
    990. 

  990.      */

    991. 

  991.     public static function IPMT($rate, $per, $nper, $pv, $fv = 0, $type = 0)

    992. 

  992.     {

    993. 

  993.         $rate = PHPExcel_Calculation_Functions :: flattenSingleValue($rate);

    994. 

  994.         $per = (int) PHPExcel_Calculation_Functions :: flattenSingleValue($per);

    995. 

  995.         $nper = (int) PHPExcel_Calculation_Functions :: flattenSingleValue($nper);

    996. 

  996.         $pv = PHPExcel_Calculation_Functions :: flattenSingleValue($pv);

    997. 

  997.         $fv = PHPExcel_Calculation_Functions :: flattenSingleValue($fv);

    998. 

  998.         $type = (int) PHPExcel_Calculation_Functions :: flattenSingleValue($type);

    999. 

  999.         

    1000. 

  1000.         // Validate parameters
    1001. 

  1001.         if ($type != 0 && $type != 1)

    1002. 

  1002.         {

    1003. 

  1003.             return PHPExcel_Calculation_Functions :: NaN();

    1004. 

  1004.         }

    1005. 

  1005.         if ($per <= 0 || $per > $nper)

    1006. 

  1006.         {

    1007. 

  1007.             return PHPExcel_Calculation_Functions :: VALUE();

    1008. 

  1008.         }

    1009. 

  1009.         

    1010. 

  1010.         // Calculate
    1011. 

  1011.         $interestAndPrincipal = self :: _interestAndPrincipal($rate, $per, $nper, $pv, $fv, $type);

    1012. 

  1012.         return $interestAndPrincipal[0];

    1013. 

  1013.     } //	function IPMT()
    1014. 

  1014. 

    1015. 

  1015.     

    1016. 

  1016.     public static function IRR($values, $guess = 0.1)

    1017. 

  1017.     {

    1018. 

  1018.         if (! is_array($values))

    1019. 

  1019.             return PHPExcel_Calculation_Functions :: VALUE();

    1020. 

  1020.         $values = PHPExcel_Calculation_Functions :: flattenArray($values);

    1021. 

  1021.         $guess = PHPExcel_Calculation_Functions :: flattenSingleValue($guess);

    1022. 

  1022.         

    1023. 

  1023.         // create an initial range, with a root somewhere between 0 and guess
    1024. 

  1024.         $x1 = 0.0;

    1025. 

  1025.         $x2 = $guess;

    1026. 

  1026.         $f1 = self :: NPV($x1, $values);

    1027. 

  1027.         $f2 = self :: NPV($x2, $values);

    1028. 

  1028.         for($i = 0; $i < FINANCIAL_MAX_ITERATIONS; ++ $i)

    1029. 

  1029.         {

    1030. 

  1030.             if (($f1 * $f2) < 0.0)

    1031. 

  1031.                 break;

    1032. 

  1032.             if (abs($f1) < abs($f2))

    1033. 

  1033.             {

    1034. 

  1034.                 $f1 = self :: NPV($x1 += 1.6 * ($x1 - $x2), $values);

    1035. 

  1035.             }

    1036. 

  1036.             else

    1037. 

  1037.             {

    1038. 

  1038.                 $f2 = self :: NPV($x2 += 1.6 * ($x2 - $x1), $values);

    1039. 

  1039.             }

    1040. 

  1040.         }

    1041. 

  1041.         if (($f1 * $f2) > 0.0)

    1042. 

  1042.             return PHPExcel_Calculation_Functions :: VALUE();

    1043. 

  1043.         

    1044. 

  1044.         $f = self :: NPV($x1, $values);

    1045. 

  1045.         if ($f < 0.0)

    1046. 

  1046.         {

    1047. 

  1047.             $rtb = $x1;

    1048. 

  1048.             $dx = $x2 - $x1;

    1049. 

  1049.         }

    1050. 

  1050.         else

    1051. 

  1051.         {

    1052. 

  1052.             $rtb = $x2;

    1053. 

  1053.             $dx = $x1 - $x2;

    1054. 

  1054.         }

    1055. 

  1055.         

    1056. 

  1056.         for($i = 0; $i < FINANCIAL_MAX_ITERATIONS; ++ $i)

    1057. 

  1057.         {

    1058. 

  1058.             $dx *= 0.5;

    1059. 

  1059.             $x_mid = $rtb + $dx;

    1060. 

  1060.             $f_mid = self :: NPV($x_mid, $values);

    1061. 

  1061.             if ($f_mid <= 0.0)

    1062. 

  1062.                 $rtb = $x_mid;

    1063. 

  1063.             if ((abs($f_mid) < FINANCIAL_PRECISION) || (abs($dx) < FINANCIAL_PRECISION))

    1064. 

  1064.                 return $x_mid;

    1065. 

  1065.         }

    1066. 

  1066.         return PHPExcel_Calculation_Functions :: VALUE();

    1067. 

  1067.     } //	function IRR()
    1068. 

  1068. 

    1069. 

  1069.     

    1070. 

  1070.     /**
    1071. 

  1071.      * ISPMT
    1072. 

  1072.      *
    1073. 

  1073.      * Returns the interest payment for an investment based on an interest rate and a constant payment schedule.
    1074. 

  1074.      *
    1075. 

  1075.      * Excel Function:
    1076. 

  1076.      * =ISPMT(interest_rate, period, number_payments, PV)
    1077. 

  1077.      *
    1078. 

  1078.      * interest_rate is the interest rate for the investment
    1079. 

  1079.      *
    1080. 

  1080.      * period is the period to calculate the interest rate.  It must be betweeen 1 and number_payments.
    1081. 

  1081.      *
    1082. 

  1082.      * number_payments is the number of payments for the annuity
    1083. 

  1083.      *
    1084. 

  1084.      * PV is the loan amount or present value of the payments
    1085. 

  1085.      */

    1086. 

  1086.     public static function ISPMT()

    1087. 

  1087.     {

    1088. 

  1088.         // Return value
    1089. 

  1089.         $returnValue = 0;

    1090. 

  1090.         

    1091. 

  1091.         // Get the parameters
    1092. 

  1092.         $aArgs = PHPExcel_Calculation_Functions :: flattenArray(func_get_args());

    1093. 

  1093.         $interestRate = array_shift($aArgs);

    1094. 

  1094.         $period = array_shift($aArgs);

    1095. 

  1095.         $numberPeriods = array_shift($aArgs);

    1096. 

  1096.         $principleRemaining = array_shift($aArgs);

    1097. 

  1097.         

    1098. 

  1098.         // Calculate
    1099. 

  1099.         $principlePayment = ($principleRemaining * 1.0) / ($numberPeriods * 1.0);

    1100. 

  1100.         for($i = 0; $i <= $period; ++ $i)

    1101. 

  1101.         {

    1102. 

  1102.             $returnValue = $interestRate * $principleRemaining * - 1;

    1103. 

  1103.             $principleRemaining -= $principlePayment;

    1104. 

  1104.             // principle needs to be 0 after the last payment, don't let floating point screw it up
    1105. 

  1105.             if ($i == $numberPeriods)

    1106. 

  1106.             {

    1107. 

  1107.                 $returnValue = 0;

    1108. 

  1108.             }

    1109. 

  1109.         }

    1110. 

  1110.         return ($returnValue);

    1111. 

  1111.     } //	function ISPMT()
    1112. 

  1112. 

    1113. 

  1113.     

    1114. 

  1114.     public static function MIRR($values, $finance_rate, $reinvestment_rate)

    1115. 

  1115.     {

    1116. 

  1116.         if (! is_array($values))

    1117. 

  1117.             return PHPExcel_Calculation_Functions :: VALUE();

    1118. 

  1118.         $values = PHPExcel_Calculation_Functions :: flattenArray($values);

    1119. 

  1119.         $finance_rate = PHPExcel_Calculation_Functions :: flattenSingleValue($finance_rate);

    1120. 

  1120.         $reinvestment_rate = PHPExcel_Calculation_Functions :: flattenSingleValue($reinvestment_rate);

    1121. 

  1121.         $n = count($values);

    1122. 

  1122.         

    1123. 

  1123.         $rr = 1.0 + $reinvestment_rate;

    1124. 

  1124.         $fr = 1.0 + $finance_rate;

    1125. 

  1125.         

    1126. 

  1126.         $npv_pos = $npv_neg = 0.0;

    1127. 

  1127.         foreach ($values as $i => $v)

    1128. 

  1128.         {

    1129. 

  1129.             if ($v >= 0)

    1130. 

  1130.             {

    1131. 

  1131.                 $npv_pos += $v / pow($rr, $i);

    1132. 

  1132.             }

    1133. 

  1133.             else

    1134. 

  1134.             {

    1135. 

  1135.                 $npv_neg += $v / pow($fr, $i);

    1136. 

  1136.             }

    1137. 

  1137.         }

    1138. 

  1138.         

    1139. 

  1139.         if (($npv_neg == 0) || ($npv_pos == 0) || ($reinvestment_rate <= - 1))

    1140. 

  1140.         {

    1141. 

  1141.             return PHPExcel_Calculation_Functions :: VALUE();

    1142. 

  1142.         }

    1143. 

  1143.         

    1144. 

  1144.         $mirr = pow((- $npv_pos * pow($rr, $n)) / ($npv_neg * ($rr)), (1.0 / ($n - 1))) - 1.0;

    1145. 

  1145.         

    1146. 

  1146.         return (is_finite($mirr) ? $mirr : PHPExcel_Calculation_Functions :: VALUE());

    1147. 

  1147.     } //	function MIRR()
    1148. 

  1148. 

    1149. 

  1149.     

    1150. 

  1150.     /**
    1151. 

  1151.      * NOMINAL
    1152. 

  1152.      *
    1153. 

  1153.      * Returns the nominal interest rate given the effective rate and the number of compounding payments per year.
    1154. 

  1154.      *
    1155. 

  1155.      * @param	float	$effect_rate	Effective interest rate
    1156. 

  1156.      * @param	int		$npery			Number of compounding payments per year
    1157. 

  1157.      * @return	float
    1158. 

  1158.      */

    1159. 

  1159.     public static function NOMINAL($effect_rate = 0, $npery = 0)

    1160. 

  1160.     {

    1161. 

  1161.         $effect_rate = PHPExcel_Calculation_Functions :: flattenSingleValue($effect_rate);

    1162. 

  1162.         $npery = (int) PHPExcel_Calculation_Functions :: flattenSingleValue($npery);

    1163. 

  1163.         

    1164. 

  1164.         // Validate parameters
    1165. 

  1165.         if ($effect_rate <= 0 || $npery < 1)

    1166. 

  1166.         {

    1167. 

  1167.             return PHPExcel_Calculation_Functions :: NaN();

    1168. 

  1168.         }

    1169. 

  1169.         

    1170. 

  1170.         // Calculate
    1171. 

  1171.         return $npery * (pow($effect_rate + 1, 1 / $npery) - 1);

    1172. 

  1172.     } //	function NOMINAL()
    1173. 

  1173. 

    1174. 

  1174.     

    1175. 

  1175.     /**
    1176. 

  1176.      * NPER
    1177. 

  1177.      *
    1178. 

  1178.      * Returns the number of periods for a cash flow with constant periodic payments (annuities), and interest rate.
    1179. 

  1179.      *
    1180. 

  1180.      * @param	float	$rate	Interest rate per period
    1181. 

  1181.      * @param	int		$pmt	Periodic payment (annuity)
    1182. 

  1182.      * @param	float	$pv		Present Value
    1183. 

  1183.      * @param	float	$fv		Future Value
    1184. 

  1184.      * @param	int		$type	Payment type: 0 = at the end of each period, 1 = at the beginning of each period
    1185. 

  1185.      * @return	float
    1186. 

  1186.      */

    1187. 

  1187.     public static function NPER($rate = 0, $pmt = 0, $pv = 0, $fv = 0, $type = 0)

    1188. 

  1188.     {

    1189. 

  1189.         $rate = PHPExcel_Calculation_Functions :: flattenSingleValue($rate);

    1190. 

  1190.         $pmt = PHPExcel_Calculation_Functions :: flattenSingleValue($pmt);

    1191. 

  1191.         $pv = PHPExcel_Calculation_Functions :: flattenSingleValue($pv);

    1192. 

  1192.         $fv = PHPExcel_Calculation_Functions :: flattenSingleValue($fv);

    1193. 

  1193.         $type = PHPExcel_Calculation_Functions :: flattenSingleValue($type);

    1194. 

  1194.         

    1195. 

  1195.         // Validate parameters
    1196. 

  1196.         if ($type != 0 && $type != 1)

    1197. 

  1197.         {

    1198. 

  1198.             return PHPExcel_Calculation_Functions :: NaN();

    1199. 

  1199.         }

    1200. 

  1200.         

    1201. 

  1201.         // Calculate
    1202. 

  1202.         if (! is_null($rate) && $rate != 0)

    1203. 

  1203.         {

    1204. 

  1204.             if ($pmt == 0 && $pv == 0)

    1205. 

  1205.             {

    1206. 

  1206.                 return PHPExcel_Calculation_Functions :: NaN();

    1207. 

  1207.             }

    1208. 

  1208.             return log(($pmt * (1 + $rate * $type) / $rate - $fv) / ($pv + $pmt * (1 + $rate * $type) / $rate)) / log(1 + $rate);

    1209. 

  1209.         }

    1210. 

  1210.         else

    1211. 

  1211.         {

    1212. 

  1212.             if ($pmt == 0)

    1213. 

  1213.             {

    1214. 

  1214.                 return PHPExcel_Calculation_Functions :: NaN();

    1215. 

  1215.             }

    1216. 

  1216.             return (- $pv - $fv) / $pmt;

    1217. 

  1217.         }

    1218. 

  1218.     } //	function NPER()
    1219. 

  1219. 

    1220. 

  1220.     

    1221. 

  1221.     /**
    1222. 

  1222.      * NPV
    1223. 

  1223.      *
    1224. 

  1224.      * Returns the Net Present Value of a cash flow series given a discount rate.
    1225. 

  1225.      *
    1226. 

  1226.      * @param	float	Discount interest rate
    1227. 

  1227.      * @param	array	Cash flow series
    1228. 

  1228.      * @return	float
    1229. 

  1229.      */

    1230. 

  1230.     public static function NPV()

    1231. 

  1231.     {

    1232. 

  1232.         // Return value
    1233. 

  1233.         $returnValue = 0;

    1234. 

  1234.         

    1235. 

  1235.         // Loop through arguments
    1236. 

  1236.         $aArgs = PHPExcel_Calculation_Functions :: flattenArray(func_get_args());

    1237. 

  1237.         

    1238. 

  1238.         // Calculate
    1239. 

  1239.         $rate = array_shift($aArgs);

    1240. 

  1240.         for($i = 1; $i <= count($aArgs); ++ $i)

    1241. 

  1241.         {

    1242. 

  1242.             // Is it a numeric value?
    1243. 

  1243.             if (is_numeric($aArgs[$i - 1]))

    1244. 

  1244.             {

    1245. 

  1245.                 $returnValue += $aArgs[$i - 1] / pow(1 + $rate, $i);

    1246. 

  1246.             }

    1247. 

  1247.         }

    1248. 

  1248.         

    1249. 

  1249.         // Return
    1250. 

  1250.         return $returnValue;

    1251. 

  1251.     } //	function NPV()
    1252. 

  1252. 

    1253. 

  1253.     

    1254. 

  1254.     /**
    1255. 

  1255.      * PMT
    1256. 

  1256.      *
    1257. 

  1257.      * Returns the constant payment (annuity) for a cash flow with a constant interest rate.
    1258. 

  1258.      *
    1259. 

  1259.      * @param	float	$rate	Interest rate per period
    1260. 

  1260.      * @param	int		$nper	Number of periods
    1261. 

  1261.      * @param	float	$pv		Present Value
    1262. 

  1262.      * @param	float	$fv		Future Value
    1263. 

  1263.      * @param	int		$type	Payment type: 0 = at the end of each period, 1 = at the beginning of each period
    1264. 

  1264.      * @return	float
    1265. 

  1265.      */

    1266. 

  1266.     public static function PMT($rate = 0, $nper = 0, $pv = 0, $fv = 0, $type = 0)

    1267. 

  1267.     {

    1268. 

  1268.         $rate = PHPExcel_Calculation_Functions :: flattenSingleValue($rate);

    1269. 

  1269.         $nper = PHPExcel_Calculation_Functions :: flattenSingleValue($nper);

    1270. 

  1270.         $pv = PHPExcel_Calculation_Functions :: flattenSingleValue($pv);

    1271. 

  1271.         $fv = PHPExcel_Calculation_Functions :: flattenSingleValue($fv);

    1272. 

  1272.         $type = PHPExcel_Calculation_Functions :: flattenSingleValue($type);

    1273. 

  1273.         

    1274. 

  1274.         // Validate parameters
    1275. 

  1275.         if ($type != 0 && $type != 1)

    1276. 

  1276.         {

    1277. 

  1277.             return PHPExcel_Calculation_Functions :: NaN();

    1278. 

  1278.         }

    1279. 

  1279.         

    1280. 

  1280.         // Calculate
    1281. 

  1281.         if (! is_null($rate) && $rate != 0)

    1282. 

  1282.         {

    1283. 

  1283.             return (- $fv - $pv * pow(1 + $rate, $nper)) / (1 + $rate * $type) / ((pow(1 + $rate, $nper) - 1) / $rate);

    1284. 

  1284.         }

    1285. 

  1285.         else

    1286. 

  1286.         {

    1287. 

  1287.             return (- $pv - $fv) / $nper;

    1288. 

  1288.         }

    1289. 

  1289.     } //	function PMT()
    1290. 

  1290. 

    1291. 

  1291.     

    1292. 

  1292.     /**
    1293. 

  1293.      * PPMT
    1294. 

  1294.      *
    1295. 

  1295.      * Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate.
    1296. 

  1296.      *
    1297. 

  1297.      * @param	float	$rate	Interest rate per period
    1298. 

  1298.      * @param	int		$per	Period for which we want to find the interest
    1299. 

  1299.      * @param	int		$nper	Number of periods
    1300. 

  1300.      * @param	float	$pv		Present Value
    1301. 

  1301.      * @param	float	$fv		Future Value
    1302. 

  1302.      * @param	int		$type	Payment type: 0 = at the end of each period, 1 = at the beginning of each period
    1303. 

  1303.      * @return	float
    1304. 

  1304.      */

    1305. 

  1305.     public static function PPMT($rate, $per, $nper, $pv, $fv = 0, $type = 0)

    1306. 

  1306.     {

    1307. 

  1307.         $rate = PHPExcel_Calculation_Functions :: flattenSingleValue($rate);

    1308. 

  1308.         $per = (int) PHPExcel_Calculation_Functions :: flattenSingleValue($per);

    1309. 

  1309.         $nper = (int) PHPExcel_Calculation_Functions :: flattenSingleValue($nper);

    1310. 

  1310.         $pv = PHPExcel_Calculation_Functions :: flattenSingleValue($pv);

    1311. 

  1311.         $fv = PHPExcel_Calculation_Functions :: flattenSingleValue($fv);

    1312. 

  1312.         $type = (int) PHPExcel_Calculation_Functions :: flattenSingleValue($type);

    1313. 

  1313.         

    1314. 

  1314.         // Validate parameters
    1315. 

  1315.         if ($type != 0 && $type != 1)

    1316. 

  1316.         {

    1317. 

  1317.             return PHPExcel_Calculation_Functions :: NaN();

    1318. 

  1318.         }

    1319. 

  1319.         if ($per <= 0 || $per > $nper)

    1320. 

  1320.         {

    1321. 

  1321.             return PHPExcel_Calculation_Functions :: VALUE();

    1322. 

  1322.         }

    1323. 

  1323.         

    1324. 

  1324.         // Calculate
    1325. 

  1325.         $interestAndPrincipal = self :: _interestAndPrincipal($rate, $per, $nper, $pv, $fv, $type);

    1326. 

  1326.         return $interestAndPrincipal[1];

    1327. 

  1327.     } //	function PPMT()
    1328. 

  1328. 

    1329. 

  1329.     

    1330. 

  1330.     public static function PRICE($settlement, $maturity, $rate, $yield, $redemption, $frequency, $basis = 0)

    1331. 

  1331.     {

    1332. 

  1332.         $settlement = PHPExcel_Calculation_Functions :: flattenSingleValue($settlement);

    1333. 

  1333.         $maturity = PHPExcel_Calculation_Functions :: flattenSingleValue($maturity);

    1334. 

  1334.         $rate = (float) PHPExcel_Calculation_Functions :: flattenSingleValue($rate);

    1335. 

  1335.         $yield = (float) PHPExcel_Calculation_Functions :: flattenSingleValue($yield);

    1336. 

  1336.         $redemption = (float) PHPExcel_Calculation_Functions :: flattenSingleValue($redemption);

    1337. 

  1337.         $frequency = (int) PHPExcel_Calculation_Functions :: flattenSingleValue($frequency);

    1338. 

  1338.         $basis = (is_null($basis)) ? 0 : (int) PHPExcel_Calculation_Functions :: flattenSingleValue($basis);

    1339. 

  1339.         

    1340. 

  1340.         if (is_string($settlement = PHPExcel_Calculation_DateTime :: _getDateValue($settlement)))

    1341. 

  1341.         {

    1342. 

  1342.             return PHPExcel_Calculation_Functions :: VALUE();

    1343. 

  1343.         }

    1344. 

  1344.         if (is_string($maturity = PHPExcel_Calculation_DateTime :: _getDateValue($maturity)))

    1345. 

  1345.         {

    1346. 

  1346.             return PHPExcel_Calculation_Functions :: VALUE();

    1347. 

  1347.         }

    1348. 

  1348.         

    1349. 

  1349.         if (($settlement > $maturity) || (! self :: _validFrequency($frequency)) || (($basis < 0) || ($basis > 4)))

    1350. 

  1350.         {

    1351. 

  1351.             return PHPExcel_Calculation_Functions :: NaN();

    1352. 

  1352.         }

    1353. 

  1353.         

    1354. 

  1354.         $dsc = self :: COUPDAYSNC($settlement, $maturity, $frequency, $basis);

    1355. 

  1355.         $e = self :: COUPDAYS($settlement, $maturity, $frequency, $basis);

    1356. 

  1356.         $n = self :: COUPNUM($settlement, $maturity, $frequency, $basis);

    1357. 

  1357.         $a = self :: COUPDAYBS($settlement, $maturity, $frequency, $basis);

    1358. 

  1358.         

    1359. 

  1359.         $baseYF = 1.0 + ($yield / $frequency);

    1360. 

  1360.         $rfp = 100 * ($rate / $frequency);

    1361. 

  1361.         $de = $dsc / $e;

    1362. 

  1362.         

    1363. 

  1363.         $result = $redemption / pow($baseYF, (-- $n + $de));

    1364. 

  1364.         for($k = 0; $k <= $n; ++ $k)

    1365. 

  1365.         {

    1366. 

  1366.             $result += $rfp / (pow($baseYF, ($k + $de)));

    1367. 

  1367.         }

    1368. 

  1368.         $result -= $rfp * ($a / $e);

    1369. 

  1369.         

    1370. 

  1370.         return $result;

    1371. 

  1371.     } //	function PRICE()
    1372. 

  1372. 

    1373. 

  1373.     

    1374. 

  1374.     /**
    1375. 

  1375.      * PRICEDISC
    1376. 

  1376.      *
    1377. 

  1377.      * Returns the price per $100 face value of a discounted security.
    1378. 

  1378.      *
    1379. 

  1379.      * @param	mixed	settlement	The security's settlement date.
    1380. 

  1380.      * The security settlement date is the date after the issue date when the security is traded to the buyer.
    1381. 

  1381.      * @param	mixed	maturity	The security's maturity date.
    1382. 

  1382.      * The maturity date is the date when the security expires.
    1383. 

  1383.      * @param	int		discount	The security's discount rate.
    1384. 

  1384.      * @param	int		redemption	The security's redemption value per $100 face value.
    1385. 

  1385.      * @param	int		basis		The type of day count to use.
    1386. 

  1386.      * 0 or omitted	US (NASD) 30/360
    1387. 

  1387.      * 1				Actual/actual
    1388. 

  1388.      * 2				Actual/360
    1389. 

  1389.      * 3				Actual/365
    1390. 

  1390.      * 4				European 30/360
    1391. 

  1391.      * @return	float
    1392. 

  1392.      */

    1393. 

  1393.     public static function PRICEDISC($settlement, $maturity, $discount, $redemption, $basis = 0)

    1394. 

  1394.     {

    1395. 

  1395.         $settlement = PHPExcel_Calculation_Functions :: flattenSingleValue($settlement);

    1396. 

  1396.         $maturity = PHPExcel_Calculation_Functions :: flattenSingleValue($maturity);

    1397. 

  1397.         $discount = (float) PHPExcel_Calculation_Functions :: flattenSingleValue($discount);

    1398. 

  1398.         $redemption = (float) PHPExcel_Calculation_Functions :: flattenSingleValue($redemption);

    1399. 

  1399.         $basis = (int) PHPExcel_Calculation_Functions :: flattenSingleValue($basis);

    1400. 

  1400.         

    1401. 

  1401.         //	Validate
    1402. 

  1402.         if ((is_numeric($discount)) && (is_numeric($redemption)) && (is_numeric($basis)))

    1403. 

  1403.         {

    1404. 

  1404.             if (($discount <= 0) || ($redemption <= 0))

    1405. 

  1405.             {

    1406. 

  1406.                 return PHPExcel_Calculation_Functions :: NaN();

    1407. 

  1407.             }

    1408. 

  1408.             $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime :: YEARFRAC($settlement, $maturity, $basis);

    1409. 

  1409.             if (! is_numeric($daysBetweenSettlementAndMaturity))

    1410. 

  1410.             {

    1411. 

  1411.                 //	return date error
    1412. 

  1412.                 return $daysBetweenSettlementAndMaturity;

    1413. 

  1413.             }

    1414. 

  1414.             

    1415. 

  1415.             return $redemption * (1 - $discount * $daysBetweenSettlementAndMaturity);

    1416. 

  1416.         }

    1417. 

  1417.         return PHPExcel_Calculation_Functions :: VALUE();

    1418. 

  1418.     } //	function PRICEDISC()
    1419. 

  1419. 

    1420. 

  1420.     

    1421. 

  1421.     /**
    1422. 

  1422.      * PRICEMAT
    1423. 

  1423.      *
    1424. 

  1424.      * Returns the price per $100 face value of a security that pays interest at maturity.
    1425. 

  1425.      *
    1426. 

  1426.      * @param	mixed	settlement	The security's settlement date.
    1427. 

  1427.      * The security's settlement date is the date after the issue date when the security is traded to the buyer.
    1428. 

  1428.      * @param	mixed	maturity	The security's maturity date.
    1429. 

  1429.      * The maturity date is the date when the security expires.
    1430. 

  1430.      * @param	mixed	issue		The security's issue date.
    1431. 

  1431.      * @param	int		rate		The security's interest rate at date of issue.
    1432. 

  1432.      * @param	int		yield		The security's annual yield.
    1433. 

  1433.      * @param	int		basis		The type of day count to use.
    1434. 

  1434.      * 0 or omitted	US (NASD) 30/360
    1435. 

  1435.      * 1				Actual/actual
    1436. 

  1436.      * 2				Actual/360
    1437. 

  1437.      * 3				Actual/365
    1438. 

  1438.      * 4				European 30/360
    1439. 

  1439.      * @return	float
    1440. 

  1440.      */

    1441. 

  1441.     public static function PRICEMAT($settlement, $maturity, $issue, $rate, $yield, $basis = 0)

    1442. 

  1442.     {

    1443. 

  1443.         $settlement = PHPExcel_Calculation_Functions :: flattenSingleValue($settlement);

    1444. 

  1444.         $maturity = PHPExcel_Calculation_Functions :: flattenSingleValue($maturity);

    1445. 

  1445.         $issue = PHPExcel_Calculation_Functions :: flattenSingleValue($issue);

    1446. 

  1446.         $rate = PHPExcel_Calculation_Functions :: flattenSingleValue($rate);

    1447. 

  1447.         $yield = PHPExcel_Calculation_Functions :: flattenSingleValue($yield);

    1448. 

  1448.         $basis = (int) PHPExcel_Calculation_Functions :: flattenSingleValue($basis);

    1449. 

  1449.         

    1450. 

  1450.         //	Validate
    1451. 

  1451.         if (is_numeric($rate) && is_numeric($yield))

    1452. 

  1452.         {

    1453. 

  1453.             if (($rate <= 0) || ($yield <= 0))

    1454. 

  1454.             {

    1455. 

  1455.                 return PHPExcel_Calculation_Functions :: NaN();

    1456. 

  1456.             }

    1457. 

  1457.             $daysPerYear = self :: _daysPerYear(PHPExcel_Calculation_DateTime :: YEAR($settlement), $basis);

    1458. 

  1458.             if (! is_numeric($daysPerYear))

    1459. 

  1459.             {

    1460. 

  1460.                 return $daysPerYear;

    1461. 

  1461.             }

    1462. 

  1462.             $daysBetweenIssueAndSettlement = PHPExcel_Calculation_DateTime :: YEARFRAC($issue, $settlement, $basis);

    1463. 

  1463.             if (! is_numeric($daysBetweenIssueAndSettlement))

    1464. 

  1464.             {

    1465. 

  1465.                 //	return date error
    1466. 

  1466.                 return $daysBetweenIssueAndSettlement;

    1467. 

  1467.             }

    1468. 

  1468.             $daysBetweenIssueAndSettlement *= $daysPerYear;

    1469. 

  1469.             $daysBetweenIssueAndMaturity = PHPExcel_Calculation_DateTime :: YEARFRAC($issue, $maturity, $basis);

    1470. 

  1470.             if (! is_numeric($daysBetweenIssueAndMaturity))

    1471. 

  1471.             {

    1472. 

  1472.                 //	return date error
    1473. 

  1473.                 return $daysBetweenIssueAndMaturity;

    1474. 

  1474.             }

    1475. 

  1475.             $daysBetweenIssueAndMaturity *= $daysPerYear;

    1476. 

  1476.             $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime :: YEARFRAC($settlement, $maturity, $basis);

    1477. 

  1477.             if (! is_numeric($daysBetweenSettlementAndMaturity))

    1478. 

  1478.             {

    1479. 

  1479.                 //	return date error
    1480. 

  1480.                 return $daysBetweenSettlementAndMaturity;

    1481. 

  1481.             }

    1482. 

  1482.             $daysBetweenSettlementAndMaturity *= $daysPerYear;

    1483. 

  1483.             

    1484. 

  1484.             return ((100 + (($daysBetweenIssueAndMaturity / $daysPerYear) * $rate * 100)) / (1 + (($daysBetweenSettlementAndMaturity / $daysPerYear) * $yield)) - (($daysBetweenIssueAndSettlement / $daysPerYear) * $rate * 100));

    1485. 

  1485.         }

    1486. 

  1486.         return PHPExcel_Calculation_Functions :: VALUE();

    1487. 

  1487.     } //	function PRICEMAT()
    1488. 

  1488. 

    1489. 

  1489.     

    1490. 

  1490.     /**
    1491. 

  1491.      * PV
    1492. 

  1492.      *
    1493. 

  1493.      * Returns the Present Value of a cash flow with constant payments and interest rate (annuities).
    1494. 

  1494.      *
    1495. 

  1495.      * @param	float	$rate	Interest rate per period
    1496. 

  1496.      * @param	int		$nper	Number of periods
    1497. 

  1497.      * @param	float	$pmt	Periodic payment (annuity)
    1498. 

  1498.      * @param	float	$fv		Future Value
    1499. 

  1499.      * @param	int		$type	Payment type: 0 = at the end of each period, 1 = at the beginning of each period
    1500. 

  1500.      * @return	float
    1501. 

  1501.      */

    1502. 

  1502.     public static function PV($rate = 0, $nper = 0, $pmt = 0, $fv = 0, $type = 0)

    1503. 

  1503.     {

    1504. 

  1504.         $rate = PHPExcel_Calculation_Functions :: flattenSingleValue($rate);

    1505. 

  1505.         $nper = PHPExcel_Calculation_Functions :: flattenSingleValue($nper);

    1506. 

  1506.         $pmt = PHPExcel_Calculation_Functions :: flattenSingleValue($pmt);

    1507. 

  1507.         $fv = PHPExcel_Calculation_Functions :: flattenSingleValue($fv);

    1508. 

  1508.         $type = PHPExcel_Calculation_Functions :: flattenSingleValue($type);

    1509. 

  1509.         

    1510. 

  1510.         // Validate parameters
    1511. 

  1511.         if ($type != 0 && $type != 1)

    1512. 

  1512.         {

    1513. 

  1513.             return PHPExcel_Calculation_Functions :: NaN();

    1514. 

  1514.         }

    1515. 

  1515.         

    1516. 

  1516.         // Calculate
    1517. 

  1517.         if (! is_null($rate) && $rate != 0)

    1518. 

  1518.         {

    1519. 

  1519.             return (- $pmt * (1 + $rate * $type) * ((pow(1 + $rate, $nper) - 1) / $rate) - $fv) / pow(1 + $rate, $nper);

    1520. 

  1520.         }

    1521. 

  1521.         else

    1522. 

  1522.         {

    1523. 

  1523.             return - $fv - $pmt * $nper;

    1524. 

  1524.         }

    1525. 

  1525.     } //	function PV()
    1526. 

  1526. 

    1527. 

  1527.     

    1528. 

  1528.     /**
    1529. 

  1529.      * RATE
    1530. 

  1530.      *
    1531. 

  1531.      **/

    1532. 

  1532.     public static function RATE($nper, $pmt, $pv, $fv = 0.0, $type = 0, $guess = 0.1)

    1533. 

  1533.     {

    1534. 

  1534.         $nper = (int) PHPExcel_Calculation_Functions :: flattenSingleValue($nper);

    1535. 

  1535.         $pmt = PHPExcel_Calculation_Functions :: flattenSingleValue($pmt);

    1536. 

  1536.         $pv = PHPExcel_Calculation_Functions :: flattenSingleValue($pv);

    1537. 

  1537.         $fv = (is_null($fv)) ? 0.0 : PHPExcel_Calculation_Functions :: flattenSingleValue($fv);

    1538. 

  1538.         $type = (is_null($type)) ? 0 : (int) PHPExcel_Calculation_Functions :: flattenSingleValue($type);

    1539. 

  1539.         $guess = (is_null($guess)) ? 0.1 : PHPExcel_Calculation_Functions :: flattenSingleValue($guess);

    1540. 

  1540.         

    1541. 

  1541.         $rate = $guess;

    1542. 

  1542.         if (abs($rate) < FINANCIAL_PRECISION)

    1543. 

  1543.         {

    1544. 

  1544.             $y = $pv * (1 + $nper * $rate) + $pmt * (1 + $rate * $type) * $nper + $fv;

    1545. 

  1545.         }

    1546. 

  1546.         else

    1547. 

  1547.         {

    1548. 

  1548.             $f = exp($nper * log(1 + $rate));

    1549. 

  1549.             $y = $pv * $f + $pmt * (1 / $rate + $type) * ($f - 1) + $fv;

    1550. 

  1550.         }

    1551. 

  1551.         $y0 = $pv + $pmt * $nper + $fv;

    1552. 

  1552.         $y1 = $pv * $f + $pmt * (1 / $rate + $type) * ($f - 1) + $fv;

    1553. 

  1553.         

    1554. 

  1554.         // find root by secant method
    1555. 

  1555.         $i = $x0 = 0.0;

    1556. 

  1556.         $x1 = $rate;

    1557. 

  1557.         while ((abs($y0 - $y1) > FINANCIAL_PRECISION) && ($i < FINANCIAL_MAX_ITERATIONS))

    1558. 

  1558.         {

    1559. 

  1559.             $rate = ($y1 * $x0 - $y0 * $x1) / ($y1 - $y0);

    1560. 

  1560.             $x0 = $x1;

    1561. 

  1561.             $x1 = $rate;

    1562. 

  1562.             

    1563. 

  1563.             if (abs($rate) < FINANCIAL_PRECISION)

    1564. 

  1564.             {

    1565. 

  1565.                 $y = $pv * (1 + $nper * $rate) + $pmt * (1 + $rate * $type) * $nper + $fv;

    1566. 

  1566.             }

    1567. 

  1567.             else

    1568. 

  1568.             {

    1569. 

  1569.                 $f = exp($nper * log(1 + $rate));

    1570. 

  1570.                 $y = $pv * $f + $pmt * (1 / $rate + $type) * ($f - 1) + $fv;

    1571. 

  1571.             }

    1572. 

  1572.             

    1573. 

  1573.             $y0 = $y1;

    1574. 

  1574.             $y1 = $y;

    1575. 

  1575.             ++ $i;

    1576. 

  1576.         }

    1577. 

  1577.         return $rate;

    1578. 

  1578.     } //	function RATE()
    1579. 

  1579. 

    1580. 

  1580.     

    1581. 

  1581.     /**
    1582. 

  1582.      * RECEIVED
    1583. 

  1583.      *
    1584. 

  1584.      * Returns the price per $100 face value of a discounted security.
    1585. 

  1585.      *
    1586. 

  1586.      * @param	mixed	settlement	The security's settlement date.
    1587. 

  1587.      * The security settlement date is the date after the issue date when the security is traded to the buyer.
    1588. 

  1588.      * @param	mixed	maturity	The security's maturity date.
    1589. 

  1589.      * The maturity date is the date when the security expires.
    1590. 

  1590.      * @param	int		investment	The amount invested in the security.
    1591. 

  1591.      * @param	int		discount	The security's discount rate.
    1592. 

  1592.      * @param	int		basis		The type of day count to use.
    1593. 

  1593.      * 0 or omitted	US (NASD) 30/360
    1594. 

  1594.      * 1				Actual/actual
    1595. 

  1595.      * 2				Actual/360
    1596. 

  1596.      * 3				Actual/365
    1597. 

  1597.      * 4				European 30/360
    1598. 

  1598.      * @return	float
    1599. 

  1599.      */

    1600. 

  1600.     public static function RECEIVED($settlement, $maturity, $investment, $discount, $basis = 0)

    1601. 

  1601.     {

    1602. 

  1602.         $settlement = PHPExcel_Calculation_Functions :: flattenSingleValue($settlement);

    1603. 

  1603.         $maturity = PHPExcel_Calculation_Functions :: flattenSingleValue($maturity);

    1604. 

  1604.         $investment = (float) PHPExcel_Calculation_Functions :: flattenSingleValue($investment);

    1605. 

  1605.         $discount = (float) PHPExcel_Calculation_Functions :: flattenSingleValue($discount);

    1606. 

  1606.         $basis = (int) PHPExcel_Calculation_Functions :: flattenSingleValue($basis);

    1607. 

  1607.         

    1608. 

  1608.         //	Validate
    1609. 

  1609.         if ((is_numeric($investment)) && (is_numeric($discount)) && (is_numeric($basis)))

    1610. 

  1610.         {

    1611. 

  1611.             if (($investment <= 0) || ($discount <= 0))

    1612. 

  1612.             {

    1613. 

  1613.                 return PHPExcel_Calculation_Functions :: NaN();

    1614. 

  1614.             }

    1615. 

  1615.             $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime :: YEARFRAC($settlement, $maturity, $basis);

    1616. 

  1616.             if (! is_numeric($daysBetweenSettlementAndMaturity))

    1617. 

  1617.             {

    1618. 

  1618.                 //	return date error
    1619. 

  1619.                 return $daysBetweenSettlementAndMaturity;

    1620. 

  1620.             }

    1621. 

  1621.             

    1622. 

  1622.             return $investment / (1 - ($discount * $daysBetweenSettlementAndMaturity));

    1623. 

  1623.         }

    1624. 

  1624.         return PHPExcel_Calculation_Functions :: VALUE();

    1625. 

  1625.     } //	function RECEIVED()
    1626. 

  1626. 

    1627. 

  1627.     

    1628. 

  1628.     /**
    1629. 

  1629.      * SLN
    1630. 

  1630.      *
    1631. 

  1631.      * Returns the straight-line depreciation of an asset for one period
    1632. 

  1632.      *
    1633. 

  1633.      * @param	cost		Initial cost of the asset
    1634. 

  1634.      * @param	salvage		Value at the end of the depreciation
    1635. 

  1635.      * @param	life		Number of periods over which the asset is depreciated
    1636. 

  1636.      * @return	float
    1637. 

  1637.      */

    1638. 

  1638.     public static function SLN($cost, $salvage, $life)

    1639. 

  1639.     {

    1640. 

  1640.         $cost = PHPExcel_Calculation_Functions :: flattenSingleValue($cost);

    1641. 

  1641.         $salvage = PHPExcel_Calculation_Functions :: flattenSingleValue($salvage);

    1642. 

  1642.         $life = PHPExcel_Calculation_Functions :: flattenSingleValue($life);

    1643. 

  1643.         

    1644. 

  1644.         // Calculate
    1645. 

  1645.         if ((is_numeric($cost)) && (is_numeric($salvage)) && (is_numeric($life)))

    1646. 

  1646.         {

    1647. 

  1647.             if ($life < 0)

    1648. 

  1648.             {

    1649. 

  1649.                 return PHPExcel_Calculation_Functions :: NaN();

    1650. 

  1650.             }

    1651. 

  1651.             return ($cost - $salvage) / $life;

    1652. 

  1652.         }

    1653. 

  1653.         return PHPExcel_Calculation_Functions :: VALUE();

    1654. 

  1654.     } //	function SLN()
    1655. 

  1655. 

    1656. 

  1656.     

    1657. 

  1657.     /**
    1658. 

  1658.      * SYD
    1659. 

  1659.      *
    1660. 

  1660.      * Returns the sum-of-years' digits depreciation of an asset for a specified period.
    1661. 

  1661.      *
    1662. 

  1662.      * @param	cost		Initial cost of the asset
    1663. 

  1663.      * @param	salvage		Value at the end of the depreciation
    1664. 

  1664.      * @param	life		Number of periods over which the asset is depreciated
    1665. 

  1665.      * @param	period		Period
    1666. 

  1666.      * @return	float
    1667. 

  1667.      */

    1668. 

  1668.     public static function SYD($cost, $salvage, $life, $period)

    1669. 

  1669.     {

    1670. 

  1670.         $cost = PHPExcel_Calculation_Functions :: flattenSingleValue($cost);

    1671. 

  1671.         $salvage = PHPExcel_Calculation_Functions :: flattenSingleValue($salvage);

    1672. 

  1672.         $life = PHPExcel_Calculation_Functions :: flattenSingleValue($life);

    1673. 

  1673.         $period = PHPExcel_Calculation_Functions :: flattenSingleValue($period);

    1674. 

  1674.         

    1675. 

  1675.         // Calculate
    1676. 

  1676.         if ((is_numeric($cost)) && (is_numeric($salvage)) && (is_numeric($life)) && (is_numeric($period)))

    1677. 

  1677.         {

    1678. 

  1678.             if (($life < 1) || ($period > $life))

    1679. 

  1679.             {

    1680. 

  1680.                 return PHPExcel_Calculation_Functions :: NaN();

    1681. 

  1681.             }

    1682. 

  1682.             return (($cost - $salvage) * ($life - $period + 1) * 2) / ($life * ($life + 1));

    1683. 

  1683.         }

    1684. 

  1684.         return PHPExcel_Calculation_Functions :: VALUE();

    1685. 

  1685.     } //	function SYD()
    1686. 

  1686. 

    1687. 

  1687.     

    1688. 

  1688.     /**
    1689. 

  1689.      * TBILLEQ
    1690. 

  1690.      *
    1691. 

  1691.      * Returns the bond-equivalent yield for a Treasury bill.
    1692. 

  1692.      *
    1693. 

  1693.      * @param	mixed	settlement	The Treasury bill's settlement date.
    1694. 

  1694.      * The Treasury bill's settlement date is the date after the issue date when the Treasury bill is traded to the buyer.
    1695. 

  1695.      * @param	mixed	maturity	The Treasury bill's maturity date.
    1696. 

  1696.      * The maturity date is the date when the Treasury bill expires.
    1697. 

  1697.      * @param	int		discount	The Treasury bill's discount rate.
    1698. 

  1698.      * @return	float
    1699. 

  1699.      */

    1700. 

  1700.     public static function TBILLEQ($settlement, $maturity, $discount)

    1701. 

  1701.     {

    1702. 

  1702.         $settlement = PHPExcel_Calculation_Functions :: flattenSingleValue($settlement);

    1703. 

  1703.         $maturity = PHPExcel_Calculation_Functions :: flattenSingleValue($maturity);

    1704. 

  1704.         $discount = PHPExcel_Calculation_Functions :: flattenSingleValue($discount);

    1705. 

  1705.         

    1706. 

  1706.         //	Use TBILLPRICE for validation
    1707. 

  1707.         $testValue = self :: TBILLPRICE($settlement, $maturity, $discount);

    1708. 

  1708.         if (is_string($testValue))

    1709. 

  1709.         {

    1710. 

  1710.             return $testValue;

    1711. 

  1711.         }

    1712. 

  1712.         

    1713. 

  1713.         if (is_string($maturity = PHPExcel_Calculation_DateTime :: _getDateValue($maturity)))

    1714. 

  1714.         {

    1715. 

  1715.             return PHPExcel_Calculation_Functions :: VALUE();

    1716. 

  1716.         }

    1717. 

  1717.         

    1718. 

  1718.         if (PHPExcel_Calculation_Functions :: getCompatibilityMode() == PHPExcel_Calculation_Functions :: COMPATIBILITY_OPENOFFICE)

    1719. 

  1719.         {

    1720. 

  1720.             ++ $maturity;

    1721. 

  1721.             $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime :: YEARFRAC($settlement, $maturity) * 360;

    1722. 

  1722.         }

    1723. 

  1723.         else

    1724. 

  1724.         {

    1725. 

  1725.             $daysBetweenSettlementAndMaturity = (PHPExcel_Calculation_DateTime :: _getDateValue($maturity) - PHPExcel_Calculation_DateTime :: _getDateValue($settlement));

    1726. 

  1726.         }

    1727. 

  1727.         

    1728. 

  1728.         return (365 * $discount) / (360 - $discount * $daysBetweenSettlementAndMaturity);

    1729. 

  1729.     } //	function TBILLEQ()
    1730. 

  1730. 

    1731. 

  1731.     

    1732. 

  1732.     /**
    1733. 

  1733.      * TBILLPRICE
    1734. 

  1734.      *
    1735. 

  1735.      * Returns the yield for a Treasury bill.
    1736. 

  1736.      *
    1737. 

  1737.      * @param	mixed	settlement	The Treasury bill's settlement date.
    1738. 

  1738.      * The Treasury bill's settlement date is the date after the issue date when the Treasury bill is traded to the buyer.
    1739. 

  1739.      * @param	mixed	maturity	The Treasury bill's maturity date.
    1740. 

  1740.      * The maturity date is the date when the Treasury bill expires.
    1741. 

  1741.      * @param	int		discount	The Treasury bill's discount rate.
    1742. 

  1742.      * @return	float
    1743. 

  1743.      */

    1744. 

  1744.     public static function TBILLPRICE($settlement, $maturity, $discount)

    1745. 

  1745.     {

    1746. 

  1746.         $settlement = PHPExcel_Calculation_Functions :: flattenSingleValue($settlement);

    1747. 

  1747.         $maturity = PHPExcel_Calculation_Functions :: flattenSingleValue($maturity);

    1748. 

  1748.         $discount = PHPExcel_Calculation_Functions :: flattenSingleValue($discount);

    1749. 

  1749.         

    1750. 

  1750.         if (is_string($maturity = PHPExcel_Calculation_DateTime :: _getDateValue($maturity)))

    1751. 

  1751.         {

    1752. 

  1752.             return PHPExcel_Calculation_Functions :: VALUE();

    1753. 

  1753.         }

    1754. 

  1754.         

    1755. 

  1755.         //	Validate
    1756. 

  1756.         if (is_numeric($discount))

    1757. 

  1757.         {

    1758. 

  1758.             if ($discount <= 0)

    1759. 

  1759.             {

    1760. 

  1760.                 return PHPExcel_Calculation_Functions :: NaN();

    1761. 

  1761.             }

    1762. 

  1762.             

    1763. 

  1763.             if (PHPExcel_Calculation_Functions :: getCompatibilityMode() == PHPExcel_Calculation_Functions :: COMPATIBILITY_OPENOFFICE)

    1764. 

  1764.             {

    1765. 

  1765.                 ++ $maturity;

    1766. 

  1766.                 $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime :: YEARFRAC($settlement, $maturity) * 360;

    1767. 

  1767.                 if (! is_numeric($daysBetweenSettlementAndMaturity))

    1768. 

  1768.                 {

    1769. 

  1769.                     //	return date error
    1770. 

  1770.                     return $daysBetweenSettlementAndMaturity;

    1771. 

  1771.                 }

    1772. 

  1772.             }

    1773. 

  1773.             else

    1774. 

  1774.             {

    1775. 

  1775.                 $daysBetweenSettlementAndMaturity = (PHPExcel_Calculation_DateTime :: _getDateValue($maturity) - PHPExcel_Calculation_DateTime :: _getDateValue($settlement));

    1776. 

  1776.             }

    1777. 

  1777.             

    1778. 

  1778.             if ($daysBetweenSettlementAndMaturity > 360)

    1779. 

  1779.             {

    1780. 

  1780.                 return PHPExcel_Calculation_Functions :: NaN();

    1781. 

  1781.             }

    1782. 

  1782.             

    1783. 

  1783.             $price = 100 * (1 - (($discount * $daysBetweenSettlementAndMaturity) / 360));

    1784. 

  1784.             if ($price <= 0)

    1785. 

  1785.             {

    1786. 

  1786.                 return PHPExcel_Calculation_Functions :: NaN();

    1787. 

  1787.             }

    1788. 

  1788.             return $price;

    1789. 

  1789.         }

    1790. 

  1790.         return PHPExcel_Calculation_Functions :: VALUE();

    1791. 

  1791.     } //	function TBILLPRICE()
    1792. 

  1792. 

    1793. 

  1793.     

    1794. 

  1794.     /**
    1795. 

  1795.      * TBILLYIELD
    1796. 

  1796.      *
    1797. 

  1797.      * Returns the yield for a Treasury bill.
    1798. 

  1798.      *
    1799. 

  1799.      * @param	mixed	settlement	The Treasury bill's settlement date.
    1800. 

  1800.      * The Treasury bill's settlement date is the date after the issue date when the Treasury bill is traded to the buyer.
    1801. 

  1801.      * @param	mixed	maturity	The Treasury bill's maturity date.
    1802. 

  1802.      * The maturity date is the date when the Treasury bill expires.
    1803. 

  1803.      * @param	int		price		The Treasury bill's price per $100 face value.
    1804. 

  1804.      * @return	float
    1805. 

  1805.      */

    1806. 

  1806.     public static function TBILLYIELD($settlement, $maturity, $price)

    1807. 

  1807.     {

    1808. 

  1808.         $settlement = PHPExcel_Calculation_Functions :: flattenSingleValue($settlement);

    1809. 

  1809.         $maturity = PHPExcel_Calculation_Functions :: flattenSingleValue($maturity);

    1810. 

  1810.         $price = PHPExcel_Calculation_Functions :: flattenSingleValue($price);

    1811. 

  1811.         

    1812. 

  1812.         //	Validate
    1813. 

  1813.         if (is_numeric($price))

    1814. 

  1814.         {

    1815. 

  1815.             if ($price <= 0)

    1816. 

  1816.             {

    1817. 

  1817.                 return PHPExcel_Calculation_Functions :: NaN();

    1818. 

  1818.             }

    1819. 

  1819.             

    1820. 

  1820.             if (PHPExcel_Calculation_Functions :: getCompatibilityMode() == PHPExcel_Calculation_Functions :: COMPATIBILITY_OPENOFFICE)

    1821. 

  1821.             {

    1822. 

  1822.                 ++ $maturity;

    1823. 

  1823.                 $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime :: YEARFRAC($settlement, $maturity) * 360;

    1824. 

  1824.                 if (! is_numeric($daysBetweenSettlementAndMaturity))

    1825. 

  1825.                 {

    1826. 

  1826.                     //	return date error
    1827. 

  1827.                     return $daysBetweenSettlementAndMaturity;

    1828. 

  1828.                 }

    1829. 

  1829.             }

    1830. 

  1830.             else

    1831. 

  1831.             {

    1832. 

  1832.                 $daysBetweenSettlementAndMaturity = (PHPExcel_Calculation_DateTime :: _getDateValue($maturity) - PHPExcel_Calculation_DateTime :: _getDateValue($settlement));

    1833. 

  1833.             }

    1834. 

  1834.             

    1835. 

  1835.             if ($daysBetweenSettlementAndMaturity > 360)

    1836. 

  1836.             {

    1837. 

  1837.                 return PHPExcel_Calculation_Functions :: NaN();

    1838. 

  1838.             }

    1839. 

  1839.             

    1840. 

  1840.             return ((100 - $price) / $price) * (360 / $daysBetweenSettlementAndMaturity);

    1841. 

  1841.         }

    1842. 

  1842.         return PHPExcel_Calculation_Functions :: VALUE();

    1843. 

  1843.     } //	function TBILLYIELD()
    1844. 

  1844. 

    1845. 

  1845.     

    1846. 

  1846.     public static function XIRR($values, $dates, $guess = 0.1)

    1847. 

  1847.     {

    1848. 

  1848.         if ((! is_array($values)) && (! is_array($dates)))

    1849. 

  1849.             return PHPExcel_Calculation_Functions :: VALUE();

    1850. 

  1850.         $values = PHPExcel_Calculation_Functions :: flattenArray($values);

    1851. 

  1851.         $dates = PHPExcel_Calculation_Functions :: flattenArray($dates);

    1852. 

  1852.         $guess = PHPExcel_Calculation_Functions :: flattenSingleValue($guess);

    1853. 

  1853.         if (count($values) != count($dates))

    1854. 

  1854.             return PHPExcel_Calculation_Functions :: NaN();

    1855. 

  1855.         

    1856. 

  1856.      // create an initial range, with a root somewhere between 0 and guess
    1857. 

  1857.         $x1 = 0.0;

    1858. 

  1858.         $x2 = $guess;

    1859. 

  1859.         $f1 = self :: XNPV($x1, $values, $dates);

    1860. 

  1860.         $f2 = self :: XNPV($x2, $values, $dates);

    1861. 

  1861.         for($i = 0; $i < FINANCIAL_MAX_ITERATIONS; ++ $i)

    1862. 

  1862.         {

    1863. 

  1863.             if (($f1 * $f2) < 0.0)

    1864. 

  1864.                 break;

    1865. 

  1865.             if (abs($f1) < abs($f2))

    1866. 

  1866.             {

    1867. 

  1867.                 $f1 = self :: XNPV($x1 += 1.6 * ($x1 - $x2), $values, $dates);

    1868. 

  1868.             }

    1869. 

  1869.             else

    1870. 

  1870.             {

    1871. 

  1871.                 $f2 = self :: XNPV($x2 += 1.6 * ($x2 - $x1), $values, $dates);

    1872. 

  1872.             }

    1873. 

  1873.         }

    1874. 

  1874.         if (($f1 * $f2) > 0.0)

    1875. 

  1875.             return PHPExcel_Calculation_Functions :: VALUE();

    1876. 

  1876.         

    1877. 

  1877.         $f = self :: XNPV($x1, $values, $dates);

    1878. 

  1878.         if ($f < 0.0)

    1879. 

  1879.         {

    1880. 

  1880.             $rtb = $x1;

    1881. 

  1881.             $dx = $x2 - $x1;

    1882. 

  1882.         }

    1883. 

  1883.         else

    1884. 

  1884.         {

    1885. 

  1885.             $rtb = $x2;

    1886. 

  1886.             $dx = $x1 - $x2;

    1887. 

  1887.         }

    1888. 

  1888.         

    1889. 

  1889.         for($i = 0; $i < FINANCIAL_MAX_ITERATIONS; ++ $i)

    1890. 

  1890.         {

    1891. 

  1891.             $dx *= 0.5;

    1892. 

  1892.             $x_mid = $rtb + $dx;

    1893. 

  1893.             $f_mid = self :: XNPV($x_mid, $values, $dates);

    1894. 

  1894.             if ($f_mid <= 0.0)

    1895. 

  1895.                 $rtb = $x_mid;

    1896. 

  1896.             if ((abs($f_mid) < FINANCIAL_PRECISION) || (abs($dx) < FINANCIAL_PRECISION))

    1897. 

  1897.                 return $x_mid;

    1898. 

  1898.         }

    1899. 

  1899.         return PHPExcel_Calculation_Functions :: VALUE();

    1900. 

  1900.     }

    1901. 

  1901. 

    1902. 

  1902.     /**
    1903. 

  1903.      * XNPV
    1904. 

  1904.      *
    1905. 

  1905.      * Returns the net present value for a schedule of cash flows that is not necessarily periodic.
    1906. 

  1906.      * To calculate the net present value for a series of cash flows that is periodic, use the NPV function.
    1907. 

  1907.      *
    1908. 

  1908.      * Excel Function:
    1909. 

  1909.      * =XNPV(rate,values,dates)
    1910. 

  1910.      *
    1911. 

  1911.      * @param	float			$rate		The discount rate to apply to the cash flows.
    1912. 

  1912.      * @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.
    1913. 

  1913.      * @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.
    1914. 

  1914.      * @return	float
    1915. 

  1915.      */

    1916. 

  1916.     public static function XNPV($rate, $values, $dates)

    1917. 

  1917.     {

    1918. 

  1918.         $rate = PHPExcel_Calculation_Functions :: flattenSingleValue($rate);

    1919. 

  1919.         if (! is_numeric($rate))

    1920. 

  1920.             return PHPExcel_Calculation_Functions :: VALUE();

    1921. 

  1921.         if ((! is_array($values)) || (! is_array($dates)))

    1922. 

  1922.             return PHPExcel_Calculation_Functions :: VALUE();

    1923. 

  1923.         $values = PHPExcel_Calculation_Functions :: flattenArray($values);

    1924. 

  1924.         $dates = PHPExcel_Calculation_Functions :: flattenArray($dates);

    1925. 

  1925.         $valCount = count($values);

    1926. 

  1926.         if ($valCount != count($dates))

    1927. 

  1927.             return PHPExcel_Calculation_Functions :: NaN();

    1928. 

  1928.         if ((min($values) > 0) || (max($values) < 0))

    1929. 

  1929.             return PHPExcel_Calculation_Functions :: VALUE();

    1930. 

  1930.         

    1931. 

  1931.         $xnpv = 0.0;

    1932. 

  1932.         for($i = 0; $i < $valCount; ++ $i)

    1933. 

  1933.         {

    1934. 

  1934.             if (! is_numeric($values[$i]))

    1935. 

  1935.                 return PHPExcel_Calculation_Functions :: VALUE();

    1936. 

  1936.             $xnpv += $values[$i] / pow(1 + $rate, PHPExcel_Calculation_DateTime :: DATEDIF($dates[0], $dates[$i], 'd') / 365);

    1937. 

  1937.         }

    1938. 

  1938.         return (is_finite($xnpv)) ? $xnpv : PHPExcel_Calculation_Functions :: VALUE();

    1939. 

  1939.     } //	function XNPV()
    1940. 

  1940. 

    1941. 

  1941.     

    1942. 

  1942.     /**
    1943. 

  1943.      * YIELDDISC
    1944. 

  1944.      *
    1945. 

  1945.      * Returns the annual yield of a security that pays interest at maturity.
    1946. 

  1946.      *
    1947. 

  1947.      * @param	mixed	settlement	The security's settlement date.
    1948. 

  1948.      * The security's settlement date is the date after the issue date when the security is traded to the buyer.
    1949. 

  1949.      * @param	mixed	maturity	The security's maturity date.
    1950. 

  1950.      * The maturity date is the date when the security expires.
    1951. 

  1951.      * @param	int		price		The security's price per $100 face value.
    1952. 

  1952.      * @param	int		redemption	The security's redemption value per $100 face value.
    1953. 

  1953.      * @param	int		basis		The type of day count to use.
    1954. 

  1954.      * 0 or omitted	US (NASD) 30/360
    1955. 

  1955.      * 1				Actual/actual
    1956. 

  1956.      * 2				Actual/360
    1957. 

  1957.      * 3				Actual/365
    1958. 

  1958.      * 4				European 30/360
    1959. 

  1959.      * @return	float
    1960. 

  1960.      */

    1961. 

  1961.     public static function YIELDDISC($settlement, $maturity, $price, $redemption, $basis = 0)

    1962. 

  1962.     {

    1963. 

  1963.         $settlement = PHPExcel_Calculation_Functions :: flattenSingleValue($settlement);

    1964. 

  1964.         $maturity = PHPExcel_Calculation_Functions :: flattenSingleValue($maturity);

    1965. 

  1965.         $price = PHPExcel_Calculation_Functions :: flattenSingleValue($price);

    1966. 

  1966.         $redemption = PHPExcel_Calculation_Functions :: flattenSingleValue($redemption);

    1967. 

  1967.         $basis = (int) PHPExcel_Calculation_Functions :: flattenSingleValue($basis);

    1968. 

  1968.         

    1969. 

  1969.         //	Validate
    1970. 

  1970.         if (is_numeric($price) && is_numeric($redemption))

    1971. 

  1971.         {

    1972. 

  1972.             if (($price <= 0) || ($redemption <= 0))

    1973. 

  1973.             {

    1974. 

  1974.                 return PHPExcel_Calculation_Functions :: NaN();

    1975. 

  1975.             }

    1976. 

  1976.             $daysPerYear = self :: _daysPerYear(PHPExcel_Calculation_DateTime :: YEAR($settlement), $basis);

    1977. 

  1977.             if (! is_numeric($daysPerYear))

    1978. 

  1978.             {

    1979. 

  1979.                 return $daysPerYear;

    1980. 

  1980.             }

    1981. 

  1981.             $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime :: YEARFRAC($settlement, $maturity, $basis);

    1982. 

  1982.             if (! is_numeric($daysBetweenSettlementAndMaturity))

    1983. 

  1983.             {

    1984. 

  1984.                 //	return date error
    1985. 

  1985.                 return $daysBetweenSettlementAndMaturity;

    1986. 

  1986.             }

    1987. 

  1987.             $daysBetweenSettlementAndMaturity *= $daysPerYear;

    1988. 

  1988.             

    1989. 

  1989.             return (($redemption - $price) / $price) * ($daysPerYear / $daysBetweenSettlementAndMaturity);

    1990. 

  1990.         }

    1991. 

  1991.         return PHPExcel_Calculation_Functions :: VALUE();

    1992. 

  1992.     } //	function YIELDDISC()
    1993. 

  1993. 

    1994. 

  1994.     

    1995. 

  1995.     /**
    1996. 

  1996.      * YIELDMAT
    1997. 

  1997.      *
    1998. 

  1998.      * Returns the annual yield of a security that pays interest at maturity.
    1999. 

  1999.      *
    2000. 

  2000.      * @param	mixed	settlement	The security's settlement date.
    2001. 

  2001.      * The security's settlement date is the date after the issue date when the security is traded to the buyer.
    2002. 

  2002.      * @param	mixed	maturity	The security's maturity date.
    2003. 

  2003.      * The maturity date is the date when the security expires.
    2004. 

  2004.      * @param	mixed	issue		The security's issue date.
    2005. 

  2005.      * @param	int		rate		The security's interest rate at date of issue.
    2006. 

  2006.      * @param	int		price		The security's price per $100 face value.
    2007. 

  2007.      * @param	int		basis		The type of day count to use.
    2008. 

  2008.      * 0 or omitted	US (NASD) 30/360
    2009. 

  2009.      * 1				Actual/actual
    2010. 

  2010.      * 2				Actual/360
    2011. 

  2011.      * 3				Actual/365
    2012. 

  2012.      * 4				European 30/360
    2013. 

  2013.      * @return	float
    2014. 

  2014.      */

    2015. 

  2015.     public static function YIELDMAT($settlement, $maturity, $issue, $rate, $price, $basis = 0)

    2016. 

  2016.     {

    2017. 

  2017.         $settlement = PHPExcel_Calculation_Functions :: flattenSingleValue($settlement);

    2018. 

  2018.         $maturity = PHPExcel_Calculation_Functions :: flattenSingleValue($maturity);

    2019. 

  2019.         $issue = PHPExcel_Calculation_Functions :: flattenSingleValue($issue);

    2020. 

  2020.         $rate = PHPExcel_Calculation_Functions :: flattenSingleValue($rate);

    2021. 

  2021.         $price = PHPExcel_Calculation_Functions :: flattenSingleValue($price);

    2022. 

  2022.         $basis = (int) PHPExcel_Calculation_Functions :: flattenSingleValue($basis);

    2023. 

  2023.         

    2024. 

  2024.         //	Validate
    2025. 

  2025.         if (is_numeric($rate) && is_numeric($price))

    2026. 

  2026.         {

    2027. 

  2027.             if (($rate <= 0) || ($price <= 0))

    2028. 

  2028.             {

    2029. 

  2029.                 return PHPExcel_Calculation_Functions :: NaN();

    2030. 

  2030.             }

    2031. 

  2031.             $daysPerYear = self :: _daysPerYear(PHPExcel_Calculation_DateTime :: YEAR($settlement), $basis);

    2032. 

  2032.             if (! is_numeric($daysPerYear))

    2033. 

  2033.             {

    2034. 

  2034.                 return $daysPerYear;

    2035. 

  2035.             }

    2036. 

  2036.             $daysBetweenIssueAndSettlement = PHPExcel_Calculation_DateTime :: YEARFRAC($issue, $settlement, $basis);

    2037. 

  2037.             if (! is_numeric($daysBetweenIssueAndSettlement))

    2038. 

  2038.             {

    2039. 

  2039.                 //	return date error
    2040. 

  2040.                 return $daysBetweenIssueAndSettlement;

    2041. 

  2041.             }

    2042. 

  2042.             $daysBetweenIssueAndSettlement *= $daysPerYear;

    2043. 

  2043.             $daysBetweenIssueAndMaturity = PHPExcel_Calculation_DateTime :: YEARFRAC($issue, $maturity, $basis);

    2044. 

  2044.             if (! is_numeric($daysBetweenIssueAndMaturity))

    2045. 

  2045.             {

    2046. 

  2046.                 //	return date error
    2047. 

  2047.                 return $daysBetweenIssueAndMaturity;

    2048. 

  2048.             }

    2049. 

  2049.             $daysBetweenIssueAndMaturity *= $daysPerYear;

    2050. 

  2050.             $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime :: YEARFRAC($settlement, $maturity, $basis);

    2051. 

  2051.             if (! is_numeric($daysBetweenSettlementAndMaturity))

    2052. 

  2052.             {

    2053. 

  2053.                 //	return date error
    2054. 

  2054.                 return $daysBetweenSettlementAndMaturity;

    2055. 

  2055.             }

    2056. 

  2056.             $daysBetweenSettlementAndMaturity *= $daysPerYear;

    2057. 

  2057.             

    2058. 

  2058.             return ((1 + (($daysBetweenIssueAndMaturity / $daysPerYear) * $rate) - (($price / 100) + (($daysBetweenIssueAndSettlement / $daysPerYear) * $rate))) / (($price / 100) + (($daysBetweenIssueAndSettlement / $daysPerYear) * $rate))) * ($daysPerYear / $daysBetweenSettlementAndMaturity);

    2059. 

  2059.         }

    2060. 

  2060.         return PHPExcel_Calculation_Functions :: VALUE();

    2061. 

  2061.     } //	function YIELDMAT()
    2062. 

  2062. 

    2063. 

  2063. 

    2064. 

  2064. }	//	class PHPExcel_Calculation_Financial
    2065. 

  2065.