tablepress / libraries / vendor / PhpSpreadsheet / Calculation / Financial / CashFlow / Variable / Periodic.php

Periodic.php in TablePress – Tables in WordPress made easy 3.4, at libraries/vendor/PhpSpreadsheet/Calculation/Financial/CashFlow/Variable/Periodic.php

168 lines 4.9 KB

Raw Download Zip

1	<?php
2
3	namespace TablePress\PhpOffice\PhpSpreadsheet\Calculation\Financial\CashFlow\Variable;
4
5	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Functions;
6	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
7
8	class Periodic
9	{
10	const FINANCIAL_MAX_ITERATIONS = 128;
11
12	const FINANCIAL_PRECISION = 1.0e-08;
13
14	/**
15	* IRR.
16	*
17	* Returns the internal rate of return for a series of cash flows represented by the numbers in values.
18	* These cash flows do not have to be even, as they would be for an annuity. However, the cash flows must occur
19	* at regular intervals, such as monthly or annually. The internal rate of return is the interest rate received
20	* for an investment consisting of payments (negative values) and income (positive values) that occur at regular
21	* periods.
22	*
23	* Excel Function:
24	* IRR(values[,guess])
25	*
26	* @param mixed $values An array or a reference to cells that contain numbers for which you want
27	* to calculate the internal rate of return.
28	* Values must contain at least one positive value and one negative value to
29	* calculate the internal rate of return.
30	* @param mixed $guess A number that you guess is close to the result of IRR
31	* @return string\|float
32	*/
33	public static function rate($values, $guess = 0.1)
34	{
35	if (!is_array($values)) {
36	return ExcelError::VALUE();
37	}
38	$values = Functions::flattenArray($values);
39	$guess = Functions::flattenSingleValue($guess);
40	if (!is_numeric($guess)) {
41	return ExcelError::VALUE();
42	}
43
44	// create an initial range, with a root somewhere between 0 and guess
45	$x1 = 0.0;
46	$x2 = $guess;
47	$f1 = self::presentValue($x1, $values);
48	$f2 = self::presentValue($x2, $values);
49	for ($i = 0; $i < self::FINANCIAL_MAX_ITERATIONS; ++$i) {
50	if (($f1 * $f2) < 0.0) {
51	break;
52	}
53	if (abs($f1) < abs($f2)) {
54	$f1 = self::presentValue($x1 += 1.6 * ($x1 - $x2), $values);
55	} else {
56	$f2 = self::presentValue($x2 += 1.6 * ($x2 - $x1), $values);
57	}
58	}
59	if (($f1 * $f2) > 0.0) {
60	return ExcelError::VALUE();
61	}
62
63	$f = self::presentValue($x1, $values);
64	if ($f < 0.0) {
65	$rtb = $x1;
66	$dx = $x2 - $x1;
67	} else {
68	$rtb = $x2;
69	$dx = $x1 - $x2;
70	}
71
72	for ($i = 0; $i < self::FINANCIAL_MAX_ITERATIONS; ++$i) {
73	$dx *= 0.5;
74	$x_mid = $rtb + $dx;
75	$f_mid = self::presentValue($x_mid, $values);
76	if ($f_mid <= 0.0) {
77	$rtb = $x_mid;
78	}
79	if ((abs($f_mid) < self::FINANCIAL_PRECISION) \|\| (abs($dx) < self::FINANCIAL_PRECISION)) {
80	return $x_mid;
81	}
82	}
83
84	return ExcelError::VALUE();
85	}
86
87	/**
88	* MIRR.
89	*
90	* Returns the modified internal rate of return for a series of periodic cash flows. MIRR considers both
91	* the cost of the investment and the interest received on reinvestment of cash.
92	*
93	* Excel Function:
94	* MIRR(values,finance_rate, reinvestment_rate)
95	*
96	* @param mixed $values An array or a reference to cells that contain a series of payments and
97	* income occurring at regular intervals.
98	* Payments are negative value, income is positive values.
99	* @param mixed $financeRate The interest rate you pay on the money used in the cash flows
100	* @param mixed $reinvestmentRate The interest rate you receive on the cash flows as you reinvest them
101	*
102	* @return float\|string Result, or a string containing an error
103	*/
104	public static function modifiedRate($values, $financeRate, $reinvestmentRate)
105	{
106	if (!is_array($values)) {
107	return ExcelError::DIV0();
108	}
109	$values = Functions::flattenArray($values);
110	/** @var float */
111	$financeRate = Functions::flattenSingleValue($financeRate);
112	/** @var float */
113	$reinvestmentRate = Functions::flattenSingleValue($reinvestmentRate);
114	$n = count($values);
115
116	$rr = 1.0 + $reinvestmentRate;
117	$fr = 1.0 + $financeRate;
118
119	$npvPos = $npvNeg = 0.0;
120	foreach ($values as $i => $v) {
121	/** @var float $v */
122	if ($v >= 0) {
123	$npvPos += $v / $rr ** $i;
124	} else {
125	$npvNeg += $v / $fr ** $i;
126	}
127	}
128
129	if ($npvNeg === 0.0 \|\| $npvPos === 0.0) {
130	return ExcelError::DIV0();
131	}
132
133	$mirr = ((-$npvPos * $rr ** $n)
134	/ ($npvNeg * ($rr))) ** (1.0 / ($n - 1)) - 1.0;
135
136	return is_finite($mirr) ? $mirr : ExcelError::NAN();
137	}
138
139	/**
140	* NPV.
141	*
142	* Returns the Net Present Value of a cash flow series given a discount rate.
143	*
144	* @param array<mixed> $args
145	* @return int\|float
146	* @param mixed $rate
147	*/
148	public static function presentValue($rate, ...$args)
149	{
150	$returnValue = 0;
151
152	/** @var float */
153	$rate = Functions::flattenSingleValue($rate);
154	$aArgs = Functions::flattenArray($args);
155
156	// Calculate
157	$countArgs = count($aArgs);
158	for ($i = 1; $i <= $countArgs; ++$i) {
159	// Is it a numeric value?
160	if (is_numeric($aArgs[$i - 1])) {
161	$returnValue += $aArgs[$i - 1] / (1 + $rate) ** $i;
162	}
163	}
164
165	return $returnValue;
166	}
167	}
168