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