Financial.php 106 KB

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