tablepress / libraries / vendor / PhpSpreadsheet / Calculation / Financial / CashFlow / Constant / Periodic / Interest.php

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

220 lines 8.3 KB

Raw Download Zip

1	<?php
2
3	namespace TablePress\PhpOffice\PhpSpreadsheet\Calculation\Financial\CashFlow\Constant\Periodic;
4
5	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Exception;
6	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Financial\CashFlow\CashFlowValidations;
7	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Financial\Constants as FinancialConstants;
8	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Functions;
9	use TablePress\PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;
10
11	class Interest
12	{
13	private const FINANCIAL_MAX_ITERATIONS = 128;
14
15	private const FINANCIAL_PRECISION = 1.0e-08;
16
17	/**
18	* IPMT.
19	*
20	* Returns the interest payment for a given period for an investment based on periodic, constant payments
21	* and a constant interest rate.
22	*
23	* Excel Function:
24	* IPMT(rate,per,nper,pv[,fv][,type])
25	*
26	* @param mixed $interestRate Interest rate per period
27	* @param mixed $period Period for which we want to find the interest
28	* @param mixed $numberOfPeriods Number of periods
29	* @param mixed $presentValue Present Value
30	* @param mixed $futureValue Future Value
31	* @param mixed $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period
32	* @return string\|float
33	*/
34	public static function payment(
35	$interestRate,
36	$period,
37	$numberOfPeriods,
38	$presentValue,
39	$futureValue = 0,
40	$type = FinancialConstants::PAYMENT_END_OF_PERIOD
41	) {
42	$interestRate = Functions::flattenSingleValue($interestRate);
43	$period = Functions::flattenSingleValue($period);
44	$numberOfPeriods = Functions::flattenSingleValue($numberOfPeriods);
45	$presentValue = Functions::flattenSingleValue($presentValue);
46	$futureValue = ($futureValue === null) ? 0.0 : Functions::flattenSingleValue($futureValue);
47	$type = Functions::flattenSingleValue($type) ?? FinancialConstants::PAYMENT_END_OF_PERIOD;
48
49	try {
50	$interestRate = CashFlowValidations::validateRate($interestRate);
51	$period = CashFlowValidations::validateInt($period);
52	$numberOfPeriods = CashFlowValidations::validateInt($numberOfPeriods);
53	$presentValue = CashFlowValidations::validatePresentValue($presentValue);
54	$futureValue = CashFlowValidations::validateFutureValue($futureValue);
55	$type = CashFlowValidations::validatePeriodType($type);
56	} catch (Exception $e) {
57	return $e->getMessage();
58	}
59
60	// Validate parameters
61	if ($period <= 0 \|\| $period > $numberOfPeriods) {
62	return ExcelError::NAN();
63	}
64
65	// Calculate
66	$interestAndPrincipal = new InterestAndPrincipal(
67	$interestRate,
68	$period,
69	$numberOfPeriods,
70	$presentValue,
71	$futureValue,
72	$type
73	);
74
75	return $interestAndPrincipal->interest();
76	}
77
78	/**
79	* ISPMT.
80	*
81	* Returns the interest payment for an investment based on an interest rate and a constant payment schedule.
82	*
83	* Excel Function:
84	* =ISPMT(interest_rate, period, number_payments, pv)
85	*
86	* @param mixed $interestRate is the interest rate for the investment
87	* @param mixed $period is the period to calculate the interest rate. It must be between 1 and number_payments.
88	* @param mixed $numberOfPeriods is the number of payments for the annuity
89	* @param mixed $principleRemaining is the loan amount or present value of the payments
90	* @return string\|float
91	*/
92	public static function schedulePayment($interestRate, $period, $numberOfPeriods, $principleRemaining)
93	{
94	$interestRate = Functions::flattenSingleValue($interestRate);
95	$period = Functions::flattenSingleValue($period);
96	$numberOfPeriods = Functions::flattenSingleValue($numberOfPeriods);
97	$principleRemaining = Functions::flattenSingleValue($principleRemaining);
98
99	try {
100	$interestRate = CashFlowValidations::validateRate($interestRate);
101	$period = CashFlowValidations::validateInt($period);
102	$numberOfPeriods = CashFlowValidations::validateInt($numberOfPeriods);
103	$principleRemaining = CashFlowValidations::validateFloat($principleRemaining);
104	} catch (Exception $e) {
105	return $e->getMessage();
106	}
107
108	// Validate parameters
109	if ($period <= 0 \|\| $period > $numberOfPeriods) {
110	return ExcelError::NAN();
111	}
112
113	// Return value
114	$returnValue = 0;
115
116	// Calculate
117	$principlePayment = ($principleRemaining * 1.0) / ($numberOfPeriods * 1.0);
118	for ($i = 0; $i <= $period; ++$i) {
119	$returnValue = $interestRate * $principleRemaining * -1;
120	$principleRemaining -= $principlePayment;
121	// principle needs to be 0 after the last payment, don't let floating point screw it up
122	if ($i == $numberOfPeriods) {
123	$returnValue = 0.0;
124	}
125	}
126
127	return $returnValue;
128	}
129
130	/**
131	* RATE.
132	*
133	* Returns the interest rate per period of an annuity.
134	* RATE is calculated by iteration and can have zero or more solutions.
135	* If the successive results of RATE do not converge to within 0.0000001 after 20 iterations,
136	* RATE returns the #NUM! error value.
137	*
138	* Excel Function:
139	* RATE(nper,pmt,pv[,fv[,type[,guess]]])
140	*
141	* @param mixed $numberOfPeriods The total number of payment periods in an annuity
142	* @param mixed $payment The payment made each period and cannot change over the life of the annuity.
143	* Typically, pmt includes principal and interest but no other fees or taxes.
144	* @param mixed $presentValue The present value - the total amount that a series of future payments is worth now
145	* @param mixed $futureValue The future value, or a cash balance you want to attain after the last payment is made.
146	* If fv is omitted, it is assumed to be 0 (the future value of a loan,
147	* for example, is 0).
148	* @param mixed $type A number 0 or 1 and indicates when payments are due:
149	* 0 or omitted At the end of the period.
150	* 1 At the beginning of the period.
151	* @param mixed $guess Your guess for what the rate will be.
152	* If you omit guess, it is assumed to be 10 percent.
153	* @return string\|float
154	*/
155	public static function rate(
156	$numberOfPeriods,
157	$payment,
158	$presentValue,
159	$futureValue = 0.0,
160	$type = FinancialConstants::PAYMENT_END_OF_PERIOD,
161	$guess = 0.1
162	) {
163	$numberOfPeriods = Functions::flattenSingleValue($numberOfPeriods);
164	$payment = Functions::flattenSingleValue($payment);
165	$presentValue = Functions::flattenSingleValue($presentValue);
166	$futureValue = Functions::flattenSingleValue($futureValue) ?? 0.0;
167	$type = Functions::flattenSingleValue($type) ?? FinancialConstants::PAYMENT_END_OF_PERIOD;
168	$guess = Functions::flattenSingleValue($guess) ?? 0.1;
169
170	try {
171	$numberOfPeriods = CashFlowValidations::validateFloat($numberOfPeriods);
172	$payment = CashFlowValidations::validateFloat($payment);
173	$presentValue = CashFlowValidations::validatePresentValue($presentValue);
174	$futureValue = CashFlowValidations::validateFutureValue($futureValue);
175	$type = CashFlowValidations::validatePeriodType($type);
176	$guess = CashFlowValidations::validateFloat($guess);
177	} catch (Exception $e) {
178	return $e->getMessage();
179	}
180
181	$rate = $guess;
182	// rest of code adapted from python/numpy
183	$close = false;
184	$iter = 0;
185	while (!$close && $iter < self::FINANCIAL_MAX_ITERATIONS) {
186	$nextdiff = self::rateNextGuess($rate, $numberOfPeriods, $payment, $presentValue, $futureValue, $type);
187	if (!is_numeric($nextdiff)) {
188	break;
189	}
190	$rate1 = $rate - $nextdiff;
191	$close = abs($rate1 - $rate) < self::FINANCIAL_PRECISION;
192	++$iter;
193	$rate = $rate1;
194	}
195
196	return $close ? $rate : ExcelError::NAN();
197	}
198
199	/**
200	* @return string\|float
201	*/
202	private static function rateNextGuess(float $rate, float $numberOfPeriods, float $payment, float $presentValue, float $futureValue, int $type)
203	{
204	if ($rate == 0.0) {
205	return ExcelError::NAN();
206	}
207	$tt1 = ($rate + 1) ** $numberOfPeriods;
208	$tt2 = ($rate + 1) ** ($numberOfPeriods - 1);
209	$numerator = $futureValue + $tt1 * $presentValue + $payment * ($tt1 - 1) * ($rate * $type + 1) / $rate;
210	$denominator = $numberOfPeriods * $tt2 * $presentValue - $payment * ($tt1 - 1)
211	* ($rate * $type + 1) / ($rate * $rate) + $numberOfPeriods
212	* $payment * $tt2 * ($rate * $type + 1) / $rate + $payment * ($tt1 - 1) * $type / $rate;
213	if ($denominator == 0) {
214	return ExcelError::NAN();
215	}
216
217	return $numerator / $denominator;
218	}
219	}
220