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